1.Base基础/3.Icon图标/操作/search备份
1.Base基础/3.Icon图标/操作/search备份
EN
文档
关于AntDB
部署与升级
快速入门
运维
调优
工具和插件
高级服务
数据安全
参考
  • 文档首页 /
  • 使用教程 /
  • Oracle兼容 /
  • 修改说明

修改说明

更新时间:2024-07-01 14:39:47

数据对象

数据类型转换规则

ORACLEAntDB
VARCHAR2varchar
CHAR(N)CHAR(N)
DATEtimestamp(时间日期型)、date(日期)、time(时间)
NUMBER(n)smallint、int、bigint
NUMBER(p,n)numeric(p,n)(低效)、float(高效)
Clobtext
Blobbytea
sysdatelocaltimestamp 不带时区
sysdatenow() 带时区
sysdatecurrent_timestamp 带时区

在日期截取的时候:

trunc (时间) → date_trunc()

Oracle:

SELECT trunc(sysdate,'year') FROM dual ;
TRUNC(SYSDATE,'YEAR')
---------------------
2015/1/1

AntDB:

antdb=# SELECT date_trunc('year',now());
date_trunc
------------------------
2015-01-01 0:00:00+08

举例在能力开发平台中:

SELECT *
 FROM (SELECT *
 	      FROM (SELECT T1.*, T2.SERVICE_NAME, T2.description
               FROM AOP_SRV_WF_PACK T1, AOP_SRV_SERVICE_INFO T2
               WHERE T1.SERVICE_ID = T2.SERVICE_ID
               AND T2.SERVICE_TYPE = '3'
               AND T1.Status in ('u', 'U')
               AND t2.status in ('u', 'U')
               AND t2.expire_date >= trunc(sysdate, 'DD'))) AS MS__
WHERE 1 = 1
--取当前时间到天,在 AntDB 中:
SELECT *
 FROM (SELECT *
       FROM (SELECT T1.*, T2.SERVICE_NAME, T2.description
              FROM AOP_SRV_WF_PACK T1, AOP_SRV_SERVICE_INFO T2
              WHERE T1.SERVICE_ID = T2.SERVICE_ID
              AND T2.SERVICE_TYPE = '3'
              AND T1.Status in ('u', 'U')
              AND t2.status in ('u', 'U')
              AND t2.expire_date >= date_trunc('day', now())) AS aaa) AS MS__
WHERE 1 = 1

to_char, to_number 在都需要指定格式

antdb=# SELECT to_number('5.05','99.99');
to_number
-----------
 5.05
(1 row)
--需要指定格式

函数

请根据《AntDB 与 Oracle 对比》中的”常用函数对比“章节中的描述进行转换评估。需要注意的几个函数如下:

Decode

Oracle 中的 DECODE 需要转换为 CASE WHEN:

SQL> SELECT decode(null,null,1,0) AS REULT FROM dual;
RESULT
----------
 1

AntDB 中转换:

antdb=# SELECT CASE WHEN null=null THEN 1 ELSE 0 END result;
result 
--------
 0
(1 row)

NVL

Oracle 中的 NVL 需要转换为 coalesce:

SQL> SELECT nvl(null,'postgres') FROM dual;
NVL(NULL,'POSTGRES')
--------------------
postgres

Postgres 中转换:

antdb=# SELECT coalesce(null,'postgres');
coalesce 
----------
postgres
(1 row)

保留字

这里主要列出 AntDB 中为保留字,但是 Oracle 或其他数据库来说为非保留字的,在表名、字段名等中 Oracle 可以使用,但 AntDB 不支持的:

关键字AntDBORACLESQL:2003SQL:1999SQL-92
ANALYSE保留非保留
ANALYZE保留非保留
ASYMMETRIC保留非保留保留非保留非保留
BINARY保留非保留保留保留保留
BOTH保留非保留保留保留保留
CASE保留非保留保留保留保留
CAST保留非保留保留保留保留
COLLATE保留非保留保留保留保留
COLLATION保留非保留非保留保留保留
CROSS保留非保留保留保留保留
CURRENT_DATE保留非保留保留保留保留
CURRENT_ROLE保留非保留保留保留保留
CURRENT_TIME保留非保留保留保留保留
CURRENT_TIMESTAMP保留非保留保留保留保留
CURRENT_USER保留非保留保留保留保留
DO保留非保留保留保留保留
END保留非保留保留保留保留
EXCEPT保留非保留保留保留保留
FALSE保留非保留保留保留保留
FOREIGN保留非保留保留保留保留
FULL保留非保留保留保留保留
ILIKE保留非保留保留保留保留
INITIALLY保留非保留非保留保留保留
INNER保留非保留保留保留保留
JOIN保留非保留保留保留保留
LEADING保留非保留保留保留保留
ISNULL保留非保留
LEFT保留非保留保留保留保留
LIMIT保留非保留保留保留保留
LOCALTIME保留非保留保留保留保留
LOCALTIMESTAMP保留非保留保留保留保留
NATURAL保留非保留保留保留保留
NOTNULL保留非保留保留保留保留
OFFSET保留非保留保留保留保留
ONLY保留非保留保留保留保留
OUTER保留非保留保留保留保留
OVERLAPS保留非保留保留保留保留
PLACING保留非保留保留保留保留
PRIMARY保留非保留保留保留保留
REFERENCES保留非保留保留保留保留
SESSION_USER保留非保留保留保留保留
SIMILAR保留非保留保留保留保留
SOME保留非保留保留保留保留
TRAILING保留非保留保留保留保留
TRUE保留非保留保留保留保留
USING保留非保留保留保留保留
VERBOSE保留非保留保留保留保留
WHEN保留非保留保留保留保留

AntDB中的null

Oracle 中' '和 NULL 是相同的,但 pgsql 是不同的,所以需要将' '修改成 NULL。

Oracle:

SELECT CASE WHEN '' is null THEN 1 ELSE 0 END FLAG FROM dual ;
 FLAG
----------
 1

AntDB:

antdb=# SELECT CASE WHEN '' is null THEN 1 ELSE 0 END flag;
flag
------
 0

1、 Null 与连接符 ||

Oracle: 'abc'||null 结果是 'abc'

SELECT 'abc'||null AS FLAG FROM dual;
FLAG
----
abc

AntDB:'abc'||null 结果是 null 所以用 concat() 函数替代

antdb=# SELECT 'abc'||null AS flag;
flag
------
(1 row)
antdb=# SELECT concat('abc',null) AS flag;
flag
------
abc
(1 row)

Oracle Rownum 的转换

在 Oracle 里面 rownum 可以用来标示行号,如 :

SQL> SELECT rownum,table_name FROM dba_tables WHERE rownum<10;
 ROWNUM TABLE_NAME
---------- ------------------------------
 1 ICOL$
 2 CON$

AntDB 使用窗口函数 row_number() 可以满足同样的需求:

antdb=# SELECT * FROM (SELECT row_number() OVER() AS rownum,tablename FROM pg_tables) t WHERE rownum<3;
rownum | tablename 
--------+--------------
 1 | pg_statistic
 2 | pg_type

序列

AntDB 中创建 sequence 的语法和 Oracle 相同,使用方面有所不同。

Oracle 中使用 sequence 获取下一个序号:

SQL>SELECT seq.nextval FROM dual;

AntDB:

antdb=# SELECT nextval('seq');

另外一个区别在于 Oracle 不支持临时序列,AntDB 支持临时序列(存在于事务,事务结束自动删除):

CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUEminvalue | NO MINVALUE ] [ MAXVALUEmaxvalue | NO MAXVALUE ]
[ START [ WITH ] start ] [ CACHEcache ] [ [ NO ] CYCLE ]
[ OWNED BY { table_name.column_name | NONE } ]

表对象

DUAL表

Oracle 有 dual 表;

AntDB:

通过构建 dual 表保持兼容性
CREATE OR replace view dual AS SELECT 'X'::"unknown" AS "DUMMY" WHERE 1=1;
grant SELECT ON dual to public ;

分区表

Oracle:

支持 Range、Hash、List、组合分区表;
CREATE TABLE measurement (
city_idint NOT NULL,
logdate date NOT NULL,
peaktempint,
unitsalesint
)partition by range(logdate)
(
    partition p1 VALUES less than (to_date('2006-03-01','yyyy-mm-dd')),
    partition p2 VALUES less than (to_date('2006-04-01', 'yyyy-mm-dd')),
    ...
partition pn-1 VALUES less than (to_date('2007-08-01', 'yyyy-mm-dd')),
partitionpn VALUES less than (maxvalue)
);

AntDB 中,通过继承表实现分区功能,目前支持 Range、List 分区:

创建父表:
CREATE TABLE measurement (
city_idint NOT NULL,
logdate date NOT NULL,
peaktempint,
unitsalesint
);

创建子表继承父表:
CREATE TABLE measurement_y2006m02 (
CHECK ( logdate>= DATE ’2006-02-01’ AND logdate< DATE ’2006-03-01’ )
) INHERITS (measurement);
CREATE TABLE measurement_y2006m03 (
CHECK ( logdate>= DATE ’2006-03-01’ AND logdate< DATE ’2006-04-01’ )
) INHERITS (measurement);
...
CREATE TABLE measurement_y2007m11 (
CHECK ( logdate>= DATE ’2007-11-01’ AND logdate< DATE ’2007-12-01’ )
) INHERITS (measurement);
CREATE TABLE measurement_y2007m12 (
CHECK ( logdate>= DATE ’2007-12-01’ AND logdate< DATE ’2008-01-01’ )
) INHERITS (measurement);
CREATE TABLE measurement_y2008m01 (
CHECK ( logdate>= DATE ’2008-01-01’ AND logdate< DATE ’2008-02-01’ )
) INHERITS (measurement);

创建索引提升查询效率:
CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
...
CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);
CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);
CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);

创建触发器函数:
CREATE OR REPLACE FUNCTION measurement_INSERT_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.logdate>= DATE ’2006-02-01’ ANDNEW.logdate< DATE ’2006-03-01’ ) THEN
INSERT INTO measurement_y2006m02 VALUES (NEW.*);
ELSIF ( NEW.logdate>= DATE ’2006-03-01’ ANDNEW.logdate< DATE ’2006-04-01’ ) THEN
INSERT INTO measurement_y2006m03 VALUES (NEW.*);
...
ELSIF ( NEW.logdate>= DATE ’2008-01-01’ ANDNEW.logdate< DATE ’2008-02-01’ ) THEN
INSERT INTO measurement_y2008m01 VALUES (NEW.*);
ELSE
RAISE EXCEPTION ’Date out of range. Fix the measurement_INSERT_trigger() function’;
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

创建触发器:
CREATE TRIGGER INSERT_measurement_trigger
BEFORE INSERT ON measurement
FOR EACH ROW EXECUTE PROCEDURE measurement_INSERT_trigger();

查询

左右连接

在 Oracle 项目中左、右连接有如下写法:

SELECT *
 FROM AOP_ABILITY_BASEINFO t, AOP_ABILITY_DIRECTORY t1
WHERE T.ABILITY_ID(+) = T1.NODE_ID;

上面+号写法为 Oracle 的方言,AntDB 中需要改成:

SELECT *
 FROM AOP_ABILITY_BASEINFO t
 LEFT JOIN AOP_ABILITY_DIRECTORY t1
 ON T.ABILITY_ID = T1.NODE_ID;

左连接、右连接、全连接都必须改成上述语法。

子查询

AntDB 严格要求 SQL 中的子查询必须定义 alias 别名

Oracle 不需要:

njdev84=# SELECT * FROM (SELECT count(1) FROM aop_app_ipmlinfo);
ERROR: subquery in FROM must have an alias
LINE 1: SELECT * FROM (SELECT count(1) FROM aop_app_ipmlinfo);
 ^
HINT: FOR example, FROM (SELECT ...) [AS] foo.
njdev84=# SELECT * FROM (SELECT count(1) FROM aop_app_ipmlinfo) AS a
;
count 
-------
 11
(1 row)

树形查询

以 scott.emp 表数据为例:

ttt=# SELECT * FROM emp;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+------------+---------+---------+--------
 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30
 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | | 20
 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30
 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10
 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20
 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10
 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20
 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20
 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20
 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30
 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | | 30
 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30
 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30
 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10
(14 rows)

如需要查询 7839 所属的所有下属信息。

Oracle:

SELECT empno,ename,job,mgr FROM emp a start with empno=7839 CONNECT BY prior empno=mgr ;

AntDB:

可以使用递归查询(with recursive)的方式实现:

ttt=# with recursive cte AS(
ttt(# SELECT empno,ename,job,mgr FROM emp WHERE empno=7839
ttt(# union all
ttt(# SELECT parent.empno,parent.ename,parent.job,parent.mgr FROM empparent,cte child WHERE 
child.empno=parent.mgr
ttt(# ) 
ttt-# SELECT * FROM cte ;
empno | ename | job | mgr
-------+--------+-----------+------
 7839 | KING | PRESIDENT | 
 7698 | BLAKE | MANAGER | 7839
 7782 | CLARK | MANAGER | 7839
 7566 | JONES | MANAGER | 7839
 7900 | JAMES | CLERK | 7698
 7844 | TURNER | SALESMAN | 7698
 7499 | ALLEN | SALESMAN | 7698
 7654 | MARTIN | SALESMAN | 7698
 7521 | WARD | SALESMAN | 7698
 7934 | MILLER | CLERK | 7782
 7902 | FORD | ANALYST | 7566
 7788 | SCOTT | ANALYST | 7566
 7369 | SMITH | CLERK | 7902
 7876 | ADAMS | CLERK | 7788
(14 rows)

查询分页

Oracle:

SELECT * FROM t1 WHERE rownum<10;
OR 开窗函数:
SELECT * FROM (SELECT row_number() OVER (ORDER BY id) AS rn ,* FROM t1) WHERE rn<=10;

AntDB:

SELECT * FROM t1 LIMIT 10 OFFSET 0;
OR 开窗函数:
SELECT * FROM (SELECT row_number() OVER (ORDER BY id) AS rn ,* FROM t1) WHERE rn<=10;

高级语法

自定义异常

在 Oracle 中自定义异常如下:

CREATE OR REPLACE PROCEDURE p()
AS
 APP_EXP EXCEPTION; --自定义异常
BEGIN
 ErrorCode :='';
 RAISE APP_EXP; -- 抛出异常
EXCEPTION
 WHEN APP_EXP THEN --在处理异常
 ROLLBACK;
 WHEN OTHERS THEN 
 ROLLBACK;
END;

在 AntDB 中也使用 raise 来报告错误信息,并返还错误,语法如下:

RAISE [ level ] 'format' [, expression [, ... ]] [ USING option = expression [, ... ] ];
RAISE [ level ] condition_name [ USING option = expression [, ... ] ];
RAISE [ level ] SQLSTATE 'sqlstate' [ USING option = expression [, ... ] ];
RAISE [ level ] USING option = expression [, ... ];
RAISE ;

如:

RAISE NOTICE 'Calling cs_CREATE_job(%)', v_job_id;
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';
RAISE division_by_zero;
RAISE SQLSTATE '22012';

如何使 AntDB 支持 merge 语法

Oracle 中,merge 语法是已经存在则更新,不存在则插入。

AntDB 要想支持 merge,可以创建一个 function:

antdb=> CREATE TABLE exists_t1 (id int primary key,info text);
NOTICE: CREATE TABLE / PRIMARY KEY will CREATE implicit index "exists_t1_pkey" for TABLE 
"exists_t1"
CREATE TABLE
antdb=> CREATE OR replace function INSERT_exists_t1(i_id int,i_info text) returns void 
AS $BODY$
declare
BEGIN
  perform 1 FROM exists_t1 WHERE id=i_id;
  IF NOT found THEN
  INSERT INTO exists_t1(id,info) VALUES (i_id,i_info);
  RETURN;
ELSE
  UPDATE exists_t1 set info=i_info WHERE id=i_id;
  RETURN;
END IF;
exception WHEN others THEN
  raise exception 'Insert exists_t1(id,info) VALUES(%,%) error.',i_id,i_info;
  RETURN;
END;
$BODY$ language plpgsql;
antdb=> SELECT INSERT_exists_t1(1,'digoal');
antdb=> SELECT INSERT_exists_t1(1,'adigoal');
antdb=> SELECT * FROM exists_t1 ;
id | info 
----+---------
 1 | adigoal

Savepoint

AntDB 的 function 为原子操作,要么全部成功,要么全部失败,如果需要设置 savepoint,部分成功的话,可以使用 AntDB 的异常处理来实现,不过实现起来比较复杂,效率也很低:

CREATE OR replace function savepoint_t1 () returns int AS $BODY$
declare
point int;
BEGIN
 UPDATE user_info set qq=11 WHERE userid=1;
 point := 1;
 UPDATE user_info set qq=22 WHERE userid=2;
 point := 2;
 UPDATE user_info set qq=33 WHERE userid=3;
 point := 3;
-- 以下模拟异常
 raise exception 'b';
 UPDATE user_info set qq=44 WHERE userid=4;
 point := 4;
 RETURN 0;
exception
WHEN others THEN
 IF (point=1) THEN
 UPDATE user_info set qq=11 WHERE userid=1;
 elseif (point=2) THEN
 UPDATE user_info set qq=11 WHERE userid=1;
 UPDATE user_info set qq=22 WHERE userid=2;
 elseif (point=3) THEN
 UPDATE user_info set qq=11 WHERE userid=1;
 UPDATE user_info set qq=22 WHERE userid=2;
 UPDATE user_info set qq=33 WHERE userid=3;
 END IF;
RETURN 1;
END;
$BODY$ language plpgsql;

PL/SQL和PLPGSQL 区别

Oracle PL/SQL 块结构语言语法:

DECLARE
BEGIN 
Statements
EXCEPTION
END;

plpgSQL 是块结构的语言。完整的函数定义必须是一个块。块的定义如下:

[ DECLARE
declarations ]
BEGIN 
statements
END [ label ];
问题反馈