兼容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.