GBase 8s兼容Oracle语法测试（一）

兼容Oracle序列

验证数据库是否兼容Oracle序列：支持创建序列、查询序列、调用序列、修改序列、删除序列等基本功能，并要求并发执行时序列无重复，支持序列缓存和排序。

Database selected.

> CREATE SEQUENCE seq1 
START WITH 1 
INCREMENT BY 1 
MINVALUE 1 
NOMAXVALUE 
NOCYCLE 
CACHE 20 ;> > > > > > 

Sequence created.

> select seq1.nextval from dual;


             nextval 

                   1

1 row(s) retrieved.

> alter sequence seq1 restart with 3;

Sequence altered.

> drop sequence seq1;

Sequence dropped.

兼容ORACLE rownum伪列

> select rownum from t_user;


     rownum 

          1
          2

2 row(s) retrieved.

兼容ORACLE chr、trunc、nvl、nvl2、translate函数

> select CHR(71) from dual;


(constant) 

G

1 row(s) retrieved.


> select sysdate from dual;


(expression)              

2022-10-25 23:22:39.31906

1 row(s) retrieved.

> select today from dual;


(expression) 

10/25/2022

1 row(s) retrieved.

> select trunc(today,'MONTH') from dual;


(expression) 

10/01/2022

1 row(s) retrieved.

> select NVL(NULL::INT, '通用') from dual;


(constant)                               

通用                                  

1 row(s) retrieved.

> select nvl2(null::int,2,3) from dual;


 (constant) 

          3

1 row(s) retrieved.

> select translate('acbd','ab','AB')  from dual;



(expression)  AcBd 

1 row(s) retrieved.

兼容ORACLE merge into语法

> create table A_MERGE
(id int not null,
name VARCHAR2(12) not null,
year int);
create table B_MERGE
(id int not null,
aid int not null,
name VARCHAR2(12) not null,
year int,
city VARCHAR2(12));
create table C_MERGE
(id int not null,
name VARCHAR2(12) not null,
city VARCHAR2(12) not null);
insert into A_MERGE values(1,'liuwei',20);
insert into A_MERGE values(2,'zhangbin',21);
insert into A_MERGE values(3,'fuguo',20); 
insert into B_MERGE values(1,2,'zhangbin',30,'吉林');
insert into B_MERGE values(2,4,'yihe',33,'黑龙江');
insert into B_MERGE values(3,3,'fuguo','','山东');
MERGE INTO A_MERGE A USING (select B.AID,B.NAME,B.YEAR from B_MERGE B) C ON (A.id=C.AID)
WHEN MATCHED THEN
UPDATE SET A.YEAR=C.YEAR
WHEN NOT MATCHED THEN
 INSERT(A.ID,A.NAME,A.YEAR) VALUES(C.AID,C.NAME,C.YEAR); > > > 
Table created.

> > > > > > 
Table created.

> > > > 
Table created.

> 
1 row(s) inserted.

> 
1 row(s) inserted.

> 
1 row(s) inserted.

> 
1 row(s) inserted.

> 
1 row(s) inserted.

> 
1 row(s) inserted.

> 

3 row(s) merged.

> 

兼容Oracle start with connect by语法

> create table test_con(BILL_MONTH int,DAY_NUMBER int,MSISDN int);
insert into test_con values(200803,1,13800);
insert into test_con values(200803,3,13800);
insert into test_con values(200803,2,13800);
insert into test_con values(200803,2,13801);
insert into test_con values(200803,4,13804);
insert into test_con values(200803,5,13804);
insert into test_con values(200803,7,13804);
insert into test_con values(200803,8,13804);
insert into test_con values(200803,6,13802);
insert into test_con values(200803,6,13801);
insert into test_con values(200803,7,13801);
insert into test_con values(200803,8,13801);

 select * from test_con
   start with day_number=1
   connect by  prior day_number=day_number-1 and prior msisdn= msisdn
  ;
 drop table test_con;
Table created.

> 
1 row(s) inserted.

> 
1 row(s) inserted.

> 
1 row(s) inserted.

> 
1 row(s) inserted.

> 
1 row(s) inserted.

> 
1 row(s) inserted.

> 
1 row(s) inserted.

> 
1 row(s) inserted.

> 
1 row(s) inserted.

> 
1 row(s) inserted.

> 
1 row(s) inserted.

> 
1 row(s) inserted.

> > > > > 

 bill_month  day_number      msisdn 

     200803           1       13800
     200803           2       13800
     200803           3       13800

3 row(s) retrieved.

> 

Table dropped.