修改说明

数据对象

数据类型转换规则

ORACLE	AntDB
VARCHAR2	varchar
CHAR(N)	CHAR(N)
DATE	timestamp（时间日期型）、date（日期）、time（时间）
NUMBER(n)	smallint、int、bigint
NUMBER(p,n)	numeric(p,n)（低效）、float（高效）
Clob	text
Blob	bytea
sysdate	localtimestamp 不带时区
sysdate	now() 带时区
sysdate	current_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 不支持的：

关键字	AntDB	ORACLE	SQL:2003	SQL:1999	SQL-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 ];