前言

OceanBase 2.2.3 ORACLE 兼容性

测试细节

OceanBase数据库驱动

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 ;

分区表的分页查询示例

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;

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

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;

取每个仓库里按进仓库时间排名前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;

层次查询示例

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 ;

闪回查询

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

分区表示例

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)

异常处理示例

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);

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

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 ;

用PL/SQL实现批处理

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 ;

其他