前言

OceanBase 2.2 自官网提供试用下载后,受到不少数据库爱好者的关注。也有些数据库开发同学想看看OceanBase到底怎么用。最近用了近2个月时间编写《OceanBase 2.2 开发者指南》,详细面向开发同学介绍OceanBase 2.2的使用方法。包括连接方法、DDL、DML、事务、存储过程、PL/SQL、Package等常用对象的用法。文档预计下周可以对外发布。本文节选文档中几个测试示例简单介绍一下OceanBase 2.2在ORACLE兼容性方面的进展。


OceanBase 2.2.3 ORACLE 兼容性


列在这里的都是测试过的,可能有遗漏 。



一级

分类


二级分类

三级分类

功能点

备注


支持的

客户端


命令行客户端


mysql

支持5.5/5.6/5.7 用于连接集群的sys租户(兼容mysql)。

obclient


编程语言


Java

通过JDBC驱动连接OB。

C/go

通过ODBC驱动连接OB。


表的

数据类型


数值类型


整形

定点

浮点



number

number(p)

number(p, s)



p和s都有表示定点数。p(precision)为精度,s(scale)表示小数点右边的数字个数,精度最大值为38,scale的取值范围为-84到127。

s为0表示整形。

p和s都不指定,表示浮点数,最大精度38。



字符串

类型


定长

char(N)

最大256 字节,字符集默认UTF8MB4。


binary

最大256 字节 ,字符集BINARY。

变长

varchar(N)


最长256KB,字符集默认UTF8MB4。



varbinary

最长256KB,字符集BINARY。

enum

最多65535个元素,每个元素最长255个字符,字符集UTF8MB4。

set

最多64个元素,每个元素最长255个字符,字符集UTF8MB4。


国家字

符集


nchar

字符集由国家字符集变量确定,默认是





AL16UTF16 。









nvarchar

时间类型


date

YYYY-MM-DD,只包含日期。

time

HH:MM:SS[.fraction],只包含时间。

datetime

YYYY-MM-DD HH:MM:SS[.fraction],包含日期时间(不考虑时区)。

Timestamp

日期时间(考虑时区)。

year

YYYY,[1901, 2155]。

大对象


Text / blob

最大64K。

Longtext /longblob

最大48M。

DDL

普通表

create table


支持有主键、无主键。

支持分区表


create table as select

只赋值表的数据、列类型但不赋值约束、索引、触发器等。

alter table


支持新增列

支持部分列的属性变更(如值域扩大)

不支持加主键


分区表

分区策略


支持一级分区:hash/range/range columns/list/list columns

支持二级分区:range-hash/list hash-range/list



全局索引

本地索引



复制表


在分布式数据库中间件里这个功能叫“广播表”。

视图

 普通视图

create or replace view


序列


create sequence


alter sequence


同义词


create synonym


触发器


触发器

类型


行级触发器

create trigger


伪劣


:NEW

:OLD




DML类型

检测



INSERTING

UPDATING

DELETING


表示本次触发是被INSERT/UPDATE/DELETE触发


触发器

变更



启用/禁用

触发器



enable

disable



替换触发器

replace trigger


删除触发器




PL/SQL

存储过程

函数



专有

数据类型



boolean


record


ref cursor


自定义子类型


声明

变量



常量

 constant


类型


%type

%rowtype


用于复制其他变量或者行列的类型

变量赋值


:=


select into


fetch into


returning into


控制语句

条件选择


if then 

else




case when


循环

for loop



while loop




loop

exit when




记录

(record)


声明

type ... is record



type ... is ...

%rowtype



作为参数



游标

属性


%FOUND

%NOTFOUND

%ROWCOUNT

%ISOPEN



声明游标

cursor c1 is ...



声明游标

(带参数)


cursor c1(p1) is ...


打开游标


open c1

open c1(p1)



获取数据

fetch c1 into ...


关闭游标

close c1


游标变量

声明

Type c1 is ref cursor

游标变量跟游标类似,区别是不限于特定的查询。


打开

游标变量


open c1 for ...



关联数组

(表变量)


元素类型


PLS_INTEGER



也叫密集型数组。

VARCHAR2

也叫稀疏型数组。


声明

数组类型


type ... is table of ... index by ...


填充数组

select into


select bulk collect into


属性

.COUNT

数组大小。

.NEXT

取下一个元素,暂不支持。

SQL

动态SQL

execute immediate ...


open ... for query


异常处理


自定义

异常声明


excp_name EXCEPTION

声明异常类型。

PRAGMAEXCEPTION_INIT

将自定义异常关联到某个错误号。

上抛异常

RAISE excp_name

当满足条件时上抛自定义异常。


RAISE_APPLICATION_ERROR

上抛自定义异常,不能被异常处理捕获。

异常捕捉

EXCEPTION WHEN ... THEN ...

捕捉各种异常。

预定义异常

CASE_NOT_FOUND


CASE 语句中没有任何 WHEN 子句满足条件,且没有 ELSE 子句。



CURSOR_ALREADY_OPEN

程序尝试打开一个已经打开的游标。

NO_DATA_FOUND

SELECT INTO 语句没有返回数据,不包括调用聚合函数的SELECT INTO 语句。

TOO_MANY_ROWS

SELECT INTO 语句返回多行数据。

VALUE_ERROR

FETCH 语句的 LIMIT 子句表达式结果不是正整数。

ZERO_DIVIDE

表达式尝试除以0 。


自定义

函数



create or replace function

支持返回值

存储过程


create or replace procedure

支持IN/OUT/IN OUT参数

NLS


日期和

时间


时间格式

NLS_DATE_FORMAT

如 YYYY-MM-DD HH24:MI:SS

NLS_TIMESTAMP_FORMAT

如 YYYY/MM/DDHH.MI.SSXFF AM

NLS_TIMESTAMP_TZ_FORMAT

如 YYYY/MM/DDHH.MI.SSXFF AM TZR

字符串

字符集

NLS_CHARACTERSET

支持GBK,UTF8

国家字符集

NLS_NCHAR_CHARACTERSE

支持AL16UTF16

长度语义

NLS_LENGTH_SEMANTICS

默认是BYTE,可以选CHAR。国家字符集字长固定是CHAR。

事务


隔离级别

READ_COMMITTED



SERIALIZABLE



保存点

savepoint




测试细节

OceanBase数据库驱动

OceanBase 实现了自己的 JDBC 驱动,使 Java 能够将 SQL 语句发送到 OceanBase 的 MySQL 租户和 ORACLE 租户。OceanBase JDBC 支持为 Java 暴露 SQL 数据类型、STORED PROCEDURE 对象,并快速访问 SQL 数据。
OceanBase JDBC 驱动文件名为:oceanbase-client-[版本号].jar,可以从官网下载。
OceanBase 数据库驱动文件 1.0 相关版本的类名为: com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
OceanBase 数据库驱动文件从 1.1.0 后类名更改为:
com.alipay.oceanbase.jdbc.Driver,原类名会保留,但是不推荐使用。文章《OceanBase 2.x体验:推荐用DBeaver工具连接数据库》里有示例如何注册OceanBase数据库驱动。

OceanBase示例数据库

文章《OceanBase 2.x 体验:示例数据库》里演示了ORACLE租户的示例数据库脚本,里面也可以直接看到OceanBase存储过程的一些能力。

场景示例

外连接示例

select t1.id, t1.name, t2.id, t2.name from t1 join t2 on (t1.id=t2.id) ;
select t1.id, t1.name, t2.id, t2.name from t1 left join t2 on (t1.id=t2.id) ;
select t1.id, t1.name, t2.id, t2.name from t1 , t2 where t1.id=t2.id(+);
select t1.id, t1.name, t2.id, t2.name from t1 right join t2 on (t1.id=t2.id) ;
select t1.id, t1.name, t2.id, t2.name from t1 , t2 where t1.id(+)=t2.id ;


OceanBase 2.2 体验:ORACLE兼容性测试_触发器


分区表的分页查询示例

分页查询场景在WEB应用里非常常见,ORACLE的分页查询通常需要两层嵌套查询才可以实现。

SELECT * FROM (
SELECT rownum rn, t1.* FROM (
SELECT h_w_id,h_d_id, w_name, h_c_id,h_c_d_id,h_c_w_id,h_amount,h_data
FROM ware a, hist b
WHERE a.w_id=b.h_w_id
AND a.w_id=2
ORDER BY h_c_id ) t1
WHERE rownum < 100
) t2
WHERE t2.rn > 90;


OceanBase 2.2 体验:ORACLE兼容性测试_数据库_02


分析函数、窗口函数查询示例

取每个仓库里最早的5笔订单,相同时间的订单会并列,所以排名可能会跳跃。

SELECT * FROM (
SELECT o_w_id, o_d_id, o_ol_cnt, o_entry_d, rank() OVER (PARTITION BY o_w_id order BY o_entry_d) entry_order
FROM ordr
ORDER BY o_w_id , entry_order
) WHERE entry_order <=5;


OceanBase 2.2 体验:ORACLE兼容性测试_数据库_03


取每个仓库里按进仓库时间排名前5的订单。相同时间的订单会并列排名,总的排名不会跳跃。

SELECT * FROM (
SELECT o_w_id, o_d_id, o_ol_cnt, o_entry_d, dense_rank() OVER (PARTITION BY o_w_id order BY o_entry_d) entry_order
FROM ordr
ORDER BY o_w_id , entry_order
) WHERE entry_order <=5;


OceanBase 2.2 体验:ORACLE兼容性测试_数据库_04


层次查询示例

select emp_id, emp_name, mgr_id,  lpad(' ',level*2,' ') ||emp_name,level from emp
start with emp_id=4
connect by prior emp_id=mgr_id
ORDER BY level ;


OceanBase 2.2 体验:ORACLE兼容性测试_触发器_05

闪回查询

select * from t1;
select * from t1 as of timestamp to_timestamp('2020-04-04 12:49:10','YYYY-MM-DD HH24:MI:SS');


OceanBase 2.2 体验:ORACLE兼容性测试_数据库_06


分区表示例

CREATE TABLE t_ordr_part_by_hash_range (
o_w_id int
, o_d_id int
, o_id int
, o_c_id int
, o_carrier_id int
, o_ol_cnt int
, o_all_local int
, o_entry_d date
, index idx_ordr(o_w_id, o_d_id, o_c_id, o_id) LOCAL
, primary key ( o_w_id, o_d_id, o_id, o_entry_d )
) PARTITION BY hash(o_w_id)
SUBPARTITION BY RANGE(o_entry_d)
SUBPARTITION template
(
SUBPARTITION M202001 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD'))
, SUBPARTITION M202002 VALUES LESS THAN(TO_DATE('2020/03/01','YYYY/MM/DD'))
, SUBPARTITION M202003 VALUES LESS THAN(TO_DATE('2020/04/01','YYYY/MM/DD'))
, SUBPARTITION M202004 VALUES LESS THAN(TO_DATE('2020/05/01','YYYY/MM/DD'))
, SUBPARTITION M202005 VALUES LESS THAN(TO_DATE('2020/06/01','YYYY/MM/DD'))
, SUBPARTITION M202006 VALUES LESS THAN(TO_DATE('2020/07/01','YYYY/MM/DD'))
, SUBPARTITION M202007 VALUES LESS THAN(TO_DATE('2020/08/01','YYYY/MM/DD'))
, SUBPARTITION M202008 VALUES LESS THAN(TO_DATE('2020/09/01','YYYY/MM/DD'))
, SUBPARTITION M202009 VALUES LESS THAN(TO_DATE('2020/10/01','YYYY/MM/DD'))
, SUBPARTITION M202010 VALUES LESS THAN(TO_DATE('2020/11/01','YYYY/MM/DD'))
, SUBPARTITION M202011 VALUES LESS THAN(TO_DATE('2020/12/01','YYYY/MM/DD'))
, SUBPARTITION M202012 VALUES LESS THAN(TO_DATE('2021/01/01','YYYY/MM/DD'))
, SUBPARTITION MMAX VALUES LESS THAN MAXVALUE
)
partitions 16;

CREATE TABLE t_log_part_by_range_hash (
log_id number NOT NULL
, log_value varchar2(50)
, log_date date NOT NULL DEFAULT sysdate
, PRIMARY key(log_id, log_date)
) PARTITION BY RANGE(log_date)
SUBPARTITION BY HASH(log_id) SUBPARTITIONS 16 (
PARTITION M202001 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD'))
, PARTITION M202002 VALUES LESS THAN(TO_DATE('2020/03/01','YYYY/MM/DD'))
, PARTITION M202003 VALUES LESS THAN(TO_DATE('2020/04/01','YYYY/MM/DD'))
, PARTITION M202004 VALUES LESS THAN(TO_DATE('2020/05/01','YYYY/MM/DD'))
, PARTITION M202005 VALUES LESS THAN(TO_DATE('2020/06/01','YYYY/MM/DD'))
, PARTITION M202006 VALUES LESS THAN(TO_DATE('2020/07/01','YYYY/MM/DD'))
, PARTITION M202007 VALUES LESS THAN(TO_DATE('2020/08/01','YYYY/MM/DD'))
, PARTITION M202008 VALUES LESS THAN(TO_DATE('2020/09/01','YYYY/MM/DD'))
, PARTITION M202009 VALUES LESS THAN(TO_DATE('2020/10/01','YYYY/MM/DD'))
, PARTITION M202010 VALUES LESS THAN(TO_DATE('2020/11/01','YYYY/MM/DD'))
, PARTITION M202011 VALUES LESS THAN(TO_DATE('2020/12/01','YYYY/MM/DD'))
, PARTITION M202012 VALUES LESS THAN(TO_DATE('2021/01/01','YYYY/MM/DD'))
, PARTITION MMAX VALUES LESS THAN MAXVALUE
);

游标遍历记录示例

delimiter /
CREATE OR REPLACE PACKAGE pkg_ware_mgmt as
PROCEDURE sp_record_print(p_w_id IN ware.w_id%type);
END;
/
delimiter ;


delimiter /
CREATE OR REPLACE PACKAGE BODY pkg_ware_mgmt
AS
PROCEDURE sp_record_print_by_record(p_record IN ware%ROWTYPE)
AS
BEGIN
dbms_output.put_line('Print a record :');
dbms_output.put_line('W_ID : ' || p_record.w_id
|| ', W_YTD : ' || p_record.w_ytd
|| ', W_TAX : ' || p_record.w_tax
|| ', W_NAME : ' || p_record.w_name
|| ', W_STREET_1 : ' || p_record.w_street_1
|| ', W_STREET_2 : ' || p_record.w_street_2
|| ', W_CITY : ' || p_record.w_city
|| ', W_STATE : ' || p_record.w_state
|| ', W_ZIP : ' || p_record.w_zip )
;
dbms_output.put_line('');
END;

PROCEDURE sp_record_print(p_w_id IN ware.w_id%TYPE )
IS
CURSOR c1(cp_w_id ware.w_id%TYPE ) IS SELECT * FROM ware WHERE w_id = cp_w_id ;
r_ware ware%ROWTYPE;
BEGIN

OPEN c1(p_w_id);
dbms_output.put_line('Open a cursor with a parameter [ ' || p_w_id || ' ].');

LOOP
FETCH c1 INTO r_ware ;
dbms_output.put_line('Fetch the cursor one time.');

EXIT WHEN c1%NOTFOUND ;

sp_record_print_by_record(r_ware);
END LOOP;

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Raise an unkown exception !');
END ;

END;
/
delimiter ;


obclient> set serveroutput on;
Query OK, 0 rows affected (0.00 sec)

obclient> call pkg_ware_mgmt.sp_record_print(1);
Query OK, 0 rows affected (0.12 sec)


OceanBase 2.2 体验:ORACLE兼容性测试_sql_07


异常处理示例

delimiter /
CREATE OR REPLACE PROCEDURE sp_test_exception(p_w_id IN ware.w_id%TYPE )
AS
TYPE TYPE_REFCURSOR IS REF CURSOR ;
ref_cursor TYPE_REFCURSOR;
ware_name ware.w_name%TYPE;
sum_ytd number(10,2) := -1;
BEGIN
OPEN ref_cursor FOR SELECT sum(w_ytd) sum_ytd FROM ware WHERE ( w_id = p_w_id AND p_w_id IS NOT NULL ) OR (p_w_id IS NULL );
FETCH ref_cursor INTO sum_ytd;
dbms_output.put_line('Fetch a ware ytd sum : ' || to_char(nvl(sum_ytd, '-999.99')) || '.');
CLOSE ref_cursor;

OPEN ref_cursor FOR SELECT w_name FROM ware WHERE ( w_id = p_w_id AND p_w_id IS NOT NULL ) OR (p_w_id IS NULL );
FETCH ref_cursor INTO ware_name ;
IF ref_cursor%NOTFOUND THEN
dbms_output.put_line('Fetch a ware name but ref cursor return NOTFOUND.');
ELSE
dbms_output.put_line('Fetch a ware name : ' || ware_name || '.');
END IF;

CLOSE ref_cursor;

SELECT w_name INTO ware_name FROM ware WHERE ( w_id = p_w_id AND p_w_id IS NOT NULL ) OR (p_w_id IS NULL );
dbms_output.put_line('Fetch again a ware name : ' || ware_name || '.');

EXCEPTION
WHEN CURSOR_ALREADY_OPEN THEN
dbms_output.put_line('Handle a CURSOR_ALREADY_OPEN exception. ');
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Handle a NO_DATA_FOUND exception. ');
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line('Handle a TOO_MANY_ROWS exception. ');
WHEN OTHERS THEN
dbms_output.put_line('Raise an unkown exception !');
RAISE ;
END;
/
delimiter ;


obclient> set serveroutput on;
Query OK, 0 rows affected (0.00 sec)

obclient> select w_id,w_ytd,w_name from ware;
+------+-------+------------+
| W_ID | W_YTD | W_NAME |
+------+-------+------------+
| 2 | 1200 | L6xwRsbDk |
| 1 | 1200 | n1P4zYo8OH |
+------+-------+------------+
2 rows in set (0.01 sec)


obclient> call sp_test_exception(NULL);

obclient> call sp_test_exception(1);

obclient> call sp_test_exception(3);


OceanBase 2.2 体验:ORACLE兼容性测试_触发器_08


用触发器实现表的自增列填充

CREATE TABLE account(id number NOT NULL PRIMARY KEY
, name varchar2(50) NOT NULL UNIQUE
, value number NOT NULL
, gmt_create date DEFAULT sysdate NOT NULL
, gmt_modified date DEFAULT sysdate NOT NULL );
CREATE TABLE account_log(id number NOT NULL PRIMARY KEY
, acc_id number NOT NULL
, acc_name varchar2(50) NOT NULL
, old_value number NULL
, new_value number NULL
, gmt_create date DEFAULT sysdate NOT NULL );
CREATE SEQUENCE seq_account_log START WITH 1 INCREMENT BY 1 nocycle ;
CREATE SEQUENCE seq_account START WITH 1 INCREMENT BY 1 nocycle;

delimiter /
CREATE OR REPLACE TRIGGER trg_before_ins_account
BEFORE INSERT
ON account
FOR EACH ROW
BEGIN
select seq_account.nextval INTO :NEW.id FROM DUAL ;
END;
/
delimiter ;


OceanBase 2.2 体验:ORACLE兼容性测试_数据库_09


用PL/SQL实现批处理

将一个原始指令表里的记录分拆为2笔记录写入到目标表中。

CREATE TABLE raw_list(
id number NOT NULL PRIMARY KEY
, debit_id number NOT NULL
, credit_id number NOT NULL
, op_amount number NOT NULL
, op_time date NOT NULL
);
CREATE SEQUENCE seq_op_list START WITH 1 ;
CREATE TABLE op_list(
op_id number NOT NULL PRIMARY KEY
, op_code varchar2(20) NOT NULL
, account_id number NOT NULL
, amount number NOT NULL
, gmt_create date DEFAULT sysdate NOT NULL
);


delimiter /
DECLARE
CURSOR c1 IS SELECT * FROM raw_list ;
TYPE T_RAW IS TABLE OF raw_list%ROWTYPE INDEX BY binary_integer ;
t T_RAW;
row_cnt binary_integer := 0;
array_size NUMBER := 100;
BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO t LIMIT array_size ;
EXIT WHEN t.COUNT = 0 ;

FORALL i IN 1..t.COUNT
INSERT INTO op_list(op_id, op_code, account_id, amount) values(seq_op_list.nextval, '-', t(i).credit_id, t(i).op_amount);

FORALL i IN 1..t.COUNT
INSERT INTO op_list(op_id, op_code, account_id, amount) values(seq_op_list.nextval, '+', t(i).debit_id, t(i).op_amount);

COMMIT;
END LOOP;
END;
/
delimiter ;

其他

OceanBase 2.2 开发者指南预计下周会官网发布,或者在这个地址 ​https://github.com/obpilot​​ 下下载。近期OceanBase团队的SQL引擎负责人在阿里云云栖社区还会有一次针对 OceanBase 2.2 ORACLE兼容性的直播,地址在​https://yqh.aliyun.com/live​。敬请关注。对OceanBase产品感兴趣的企业朋友,欢迎公众号留言联系做OceanBase技术方案交流和试用咨询。