作者介绍
洪烨，openGauss Contributor，多年银行业系统架构设计及DBA实战经验，《DB2数据库内部解析与性能调优》作者。
对于数据库兼容性来说，主要分为数据的兼容性以及应用的兼容性。数据库应用最核心的部分就是SQL语言。SQL语言是非过程化编程语言，主要分为数据查询语言（SELECT）、数据操作语言（INSERT、UPDATE和DELETE）、事务控制语言（COMMIT、SAVEPOINT、ROLLBACK）、权限控制语言（GRANT、REVOKE）、数据定义语言（CREATE、ALTER和DROP）、指针控制语言（DECLARE CURSOR）。
SQL语法的标准是由ANSI和国际标准化组织（ISO）作为ISO/IEC 9075标准维护，熟知的比如SQL92标准、SQL99标准等。但在各个厂商打造数据库产品的过程中，由于面向的用户群及场景不同，各个数据库产品基本都有一部分不属于在标准范围内的语法，通常称之为SQL方言。本文主要在openGauss中验证Oracle方言的兼容性，分为查询语法、函数、存储过程、触发器、游标等几个部分。
一、查询语法
在常用的查询语句中，Oracle方言中常见的关键字有ROWNUM、DUAL、CONNECT BY递归等。
ROWNUM应该算是Oracle的标志性功能之一，通过ROWNUM可以控制结果集的行数，但其他数据库如MySQL、PostgreSQL等，均不支持ROWNUM关键字。在openGauss中支持ROWNUM关键字，应用可直接在查询语句中使用ROWNUM关键字。
postgres=# select sysdate from test where rownum < 2;
sysdate
---------------------
2020-10-26 22:31:09
(1 row)
DUAL是一个虚拟表，也是Oracle提供的最小的工作表，Oracle保证DUAL表里面永远只有一条记录（X）。DUAL表通常用来进行功能验证，openGauss中DUAL表的用法与Oracle相同。
postgres=# select * from dual;
dummy
-------
X
(1 row)
在某些复杂查询的场景下，需要使用递归功能。通过CONNECT BY实现递归SQL是Oracle特有的方言之一，目前openGauss无法兼容CONNECT BY关键字，需要通过使用递归CTE查询替代。
postgres=# SELECT *, LEVEL
FROM te1
CONNECT BY PRIOR id = pid;
ERROR: syntax error at or near "BY"
LINE 3: CONNECT BY PRIOR id = pid;
postgres=# WITH RECURSIVE t(n) AS (
VALUES (1)
union ALL
SELECT n+1 FROM t WHERE n < 100)
SELECT sum(n) FROM t;
sum
------
5050
(1 row)
HINT是RBO（基于规则的优化器）时代的标志功能，目前尽管当前生成执行计划已经主要依赖CBO（基于成本的优化器）了。但是在性能优化过程中，对于优化器生成非最优执行计划的时候，还是需要管理员介入。在openGauss中，HINT与Oracle完全一致，也是通过类似注释的方式实现。但需要注意的是，openGauss中HINT操作符与Oracle不同，例如在Oracle中索引扫描为ixscan，在openGauss中为indexscan。openGauss中的具体操作符列表详见官方文档。（https://opengauss.org/zh/docs/1.0.1/docs/）
在product表上name字段创建索引my_index，由于product表中数据量过低，所以默认执行计划是全表扫描（Seq Scan）。通过HINT操作，强制执行计划进行索引扫描。
postgres=# CREATE INDEX my_index ON product USING btree (name) TABLESPACE pg_default;
CREATE INDEX
postgres=# explain select name from product;
Seq Scan on product (cost=0.00..24.08 rows=1408 width=24)
postgres=# explain select /*+ indexonlyscan(product my_index) */ name from product;
Index Only Scan using my_index on product (cost=0.00..65.37 rows=1408 width=24)
执行计划是SQL优化的重要手段，在openGauss中不支持autotrace方式查看执行计划，实时的执行计划可以通过explain命令直接查看。与Oracle类似的是，openGauss支持通过explain plan命令将执行计划存入系统表中，不过与Oracle稍有区别，openGauss中会将执行计划存入PLAN_TABLE表。
EXPLAIN SUCCESS
postgres=# SELECT * FROM PLAN_TABLE;
statement_id | plan_id | id | operation | options | object_name | object_type | object_owner | projection
--------------+-----------------+----+--------------+----------+-------------+-------------+--------------+------------
| 281474976710867 | 1 | TABLE ACCESS | SEQ SCAN | test | TABLE | public | id
(1 row)
postgres=# explain select * from test;
Seq Scan on test (cost=0.00..34.02 rows=2402 width=4)
二、函数
应用开发中，函数是必不可少的功能，经常会用到系统自带函数，常见的SQL函数主要有DECODE、时间函数、空函数、自定义函数等。
DECODE是Oracle公司独家提供的功能，它是一个功能很强的函数。它虽然不是SQL的标准，但对于性能非常有用。openGauss中也提供了DECODE的功能。
postgres=# select DECODE(3, 1,'One', 2,'Two', 3,'Three', 'Not found');
decode
--------
Three
(1 row)
Oracle中提供了一系列时间函数，最常用的是SYSDATE及SYSTIMESTAMP，openGauss中支持SYSDATE，但SYSTIMESTAMP需要替代为LOCALTIMESTAMP。
postgres=# select sysdate;
sysdate
---------------------
2020-10-21 17:04:14
(1 row)
postgres=# select systimestamp from dual;
ERROR: column "systimestamp" does not exist
LINE 1: select systimestamp from dual;
CONTEXT: referenced column: systimestamp
postgres=# select localtimestamp from dual;
2020-11-02 09:39:22.382455
空值处理是实际中会经常遇到的情况，通常是通过NVL函数处理，NVL(E1, E2)的功能为：如果E1为NULL，则函数返回E2，否则返回E1本身。但此函数有一定局限，所以Oracle在NVL函数的功能上扩展，提供了NVL2函数。NVL2(E1, E2, E3)的功能为：如果E1为NULL，则函数返回E3，若E1不为NULL，则返回E2。openGauss当前版本只支持NVL函数，NVL2的功能可用DECODE进行替代。
postgres=# select NVL(9, 0) from dual;
nvl
-----
9
(1 row)
postgres=# select nvl2(100,1,2) from dual;
ERROR: function nvl2(integer, integer, integer) does not exist
LINE 1: select nvl2(100,1,2) from dual;
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
CONTEXT: referenced column: nvl2
PL/SQL语法是Oracle的特有语法，在创建UDF函数、存储过程或者执行程序块都需要按照PL/SQL的语法规则进行执行。openGauss中很好的兼容了PL/SQL语法，自定义函数无需修改即可移植。
postgres=# CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar2, v_version varchar2)
RETURN varchar2 IS
BEGIN
IF v_version IS NULL THEN
RETURN v_name;
END IF;
RETURN v_name || '/' || v_version;
END;
/
CREATE FUNCTION
三、PL/SQL存储过程
由于openGauss可以兼容PL/SQL语法，存储过程创建与函数类似，无需修改即可移植。
postgres=# CREATE OR REPLACE PROCEDURE cs_parse_url(
v_url IN VARCHAR2,
v_host OUT VARCHAR2, -- This will be passed back
v_path OUT VARCHAR2, -- This one too
v_query OUT VARCHAR2) -- And this one
IS
a_pos1 INTEGER;
a_pos2 INTEGER;
BEGIN
v_host := NULL;
v_path := NULL;
v_query := NULL;
a_pos1 := instr(v_url, '//');
IF a_pos1 = 0 THEN
RETURN;
END IF;
a_pos2 := instr(v_url, '/', a_pos1 + 2);
IF a_pos2 = 0 THEN
v_host := substr(v_url, a_pos1 + 2);
v_path := '/';
RETURN;
END IF;
v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
a_pos1 := instr(v_url, '?', a_pos2 + 1);
IF a_pos1 = 0 THEN
v_path := substr(v_url, a_pos2);
RETURN;
END IF;
v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
v_query := substr(v_url, a_pos1 + 1);
END;
/
CREATE PROCEDURE
四、触发器TRIGGERS
openGauss中支持触发器，但需要注意的是，openGauss中的触发器语法与Oracle差异较大，需要进行重写。
postgres=# create or replace trigger modify_stu
before insert on student
for each row
declare
next_id number;
begin
select seq_test.nextval into next_id from dual;
:new.id :=next_id;
end;
/
ERROR: syntax error at or near "trigger"
LINE 1: create or replace trigger modify_stu
五、游标CURSOR
PLSQL中游标常常用于联机交易，调用存储过程所返回的结果集也常用游标去存储的。游标的定义语句比较简单，如 cursor my_cursor is select 1 from dual。openGauss中不兼容游标定义的IS关键字，需要改写为FOR。
postgres=# CURSOR prd_cursor IS select name from product;
ERROR: syntax error at or near "IS"
LINE 1: CURSOR prd_cursor IS
^
postgres=# CURSOR emp_cursor for select name from product;
ERROR: DECLARE CURSOR can only be used in transaction blocks
六、数组VARRAYS
varrays类似于C语言中的数组，可以在表，记录，对象定义中使用。Oracle中的VARRAYS定义可以直接移植到openGauss中。
postgres=# declare
type integer_varray is varray(3) of integer;
var_int integer_varray:=integer_varray();
begin
for i in 1..3 loop
var_int.extend;
var_int(i):=10+i;
end loop;
end;
/
ANONYMOUS BLOCK EXECUTE
七、总结
按照惯例，兼容性总结如下：
|
对象类型 |
分类 |
是否兼容 |
备注 |
|
查询语法 |
ROWNUM |
完全兼容 |
|
|
DUAL |
完全兼容 |
||
|
CONNECT BY |
不兼容 |
需要通过CTE改写 |
|
|
HINT |
部分兼容 |
HINT关键字有区别 |
|
|
执行计划 |
部分兼容 |
不支持autotrace |
|
|
函数 |
DECODE |
完全兼容 |
|
|
时间函数 |
部分兼容 |
SYSTIMESTAMP需改写 |
|
|
空值处理 |
部分兼容 |
NVL2需改写 |
|
|
自定义函数 |
完全兼容 |
||
|
存储过程 |
完全兼容 |
||
|
触发器 |
不兼容 |
需重写 |
|
|
游标 |
不兼容 |
IS关键字需改为FOR |
|
|
数组 |
完全兼容 |
相关阅读：
作者介绍
洪烨，openGauss Contributor，多年银行业系统架构设计及DBA实战经验，《DB2数据库内部解析与性能调优》作者。
对于数据库兼容性来说，主要分为数据的兼容性以及应用的兼容性。数据库应用最核心的部分就是SQL语言。SQL语言是非过程化编程语言，主要分为数据查询语言（SELECT）、数据操作语言（INSERT、UPDATE和DELETE）、事务控制语言（COMMIT、SAVEPOINT、ROLLBACK）、权限控制语言（GRANT、REVOKE）、数据定义语言（CREATE、ALTER和DROP）、指针控制语言（DECLARE CURSOR）。
SQL语法的标准是由ANSI和国际标准化组织（ISO）作为ISO/IEC 9075标准维护，熟知的比如SQL92标准、SQL99标准等。但在各个厂商打造数据库产品的过程中，由于面向的用户群及场景不同，各个数据库产品基本都有一部分不属于在标准范围内的语法，通常称之为SQL方言。本文主要在openGauss中验证Oracle方言的兼容性，分为查询语法、函数、存储过程、触发器、游标等几个部分。
一、查询语法
在常用的查询语句中，Oracle方言中常见的关键字有ROWNUM、DUAL、CONNECT BY递归等。
ROWNUM应该算是Oracle的标志性功能之一，通过ROWNUM可以控制结果集的行数，但其他数据库如MySQL、PostgreSQL等，均不支持ROWNUM关键字。在openGauss中支持ROWNUM关键字，应用可直接在查询语句中使用ROWNUM关键字。
postgres=# select sysdate from test where rownum < 2;
sysdate
---------------------
2020-10-26 22:31:09
(1 row)
DUAL是一个虚拟表，也是Oracle提供的最小的工作表，Oracle保证DUAL表里面永远只有一条记录（X）。DUAL表通常用来进行功能验证，openGauss中DUAL表的用法与Oracle相同。
postgres=# select * from dual;
dummy
-------
X
(1 row)
在某些复杂查询的场景下，需要使用递归功能。通过CONNECT BY实现递归SQL是Oracle特有的方言之一，目前openGauss无法兼容CONNECT BY关键字，需要通过使用递归CTE查询替代。
postgres=# SELECT *, LEVEL
FROM te1
CONNECT BY PRIOR id = pid;
ERROR: syntax error at or near "BY"
LINE 3: CONNECT BY PRIOR id = pid;
postgres=# WITH RECURSIVE t(n) AS (
VALUES (1)
union ALL
SELECT n+1 FROM t WHERE n < 100)
SELECT sum(n) FROM t;
sum
------
5050
(1 row)
HINT是RBO（基于规则的优化器）时代的标志功能，目前尽管当前生成执行计划已经主要依赖CBO（基于成本的优化器）了。但是在性能优化过程中，对于优化器生成非最优执行计划的时候，还是需要管理员介入。在openGauss中，HINT与Oracle完全一致，也是通过类似注释的方式实现。但需要注意的是，openGauss中HINT操作符与Oracle不同，例如在Oracle中索引扫描为ixscan，在openGauss中为indexscan。openGauss中的具体操作符列表详见官方文档。（https://opengauss.org/zh/docs/1.0.1/docs/）
在product表上name字段创建索引my_index，由于product表中数据量过低，所以默认执行计划是全表扫描（Seq Scan）。通过HINT操作，强制执行计划进行索引扫描。
postgres=# CREATE INDEX my_index ON product USING btree (name) TABLESPACE pg_default;
CREATE INDEX
postgres=# explain select name from product;
Seq Scan on product (cost=0.00..24.08 rows=1408 width=24)
postgres=# explain select /*+ indexonlyscan(product my_index) */ name from product;
Index Only Scan using my_index on product (cost=0.00..65.37 rows=1408 width=24)
执行计划是SQL优化的重要手段，在openGauss中不支持autotrace方式查看执行计划，实时的执行计划可以通过explain命令直接查看。与Oracle类似的是，openGauss支持通过explain plan命令将执行计划存入系统表中，不过与Oracle稍有区别，openGauss中会将执行计划存入PLAN_TABLE表。
EXPLAIN SUCCESS
postgres=# SELECT * FROM PLAN_TABLE;
statement_id | plan_id | id | operation | options | object_name | object_type | object_owner | projection
--------------+-----------------+----+--------------+----------+-------------+-------------+--------------+------------
| 281474976710867 | 1 | TABLE ACCESS | SEQ SCAN | test | TABLE | public | id
(1 row)
postgres=# explain select * from test;
Seq Scan on test (cost=0.00..34.02 rows=2402 width=4)
二、函数
应用开发中，函数是必不可少的功能，经常会用到系统自带函数，常见的SQL函数主要有DECODE、时间函数、空函数、自定义函数等。
DECODE是Oracle公司独家提供的功能，它是一个功能很强的函数。它虽然不是SQL的标准，但对于性能非常有用。openGauss中也提供了DECODE的功能。
postgres=# select DECODE(3, 1,'One', 2,'Two', 3,'Three', 'Not found');
decode
--------
Three
(1 row)
Oracle中提供了一系列时间函数，最常用的是SYSDATE及SYSTIMESTAMP，openGauss中支持SYSDATE，但SYSTIMESTAMP需要替代为LOCALTIMESTAMP。
postgres=# select sysdate;
sysdate
---------------------
2020-10-21 17:04:14
(1 row)
postgres=# select systimestamp from dual;
ERROR: column "systimestamp" does not exist
LINE 1: select systimestamp from dual;
CONTEXT: referenced column: systimestamp
postgres=# select localtimestamp from dual;
2020-11-02 09:39:22.382455
空值处理是实际中会经常遇到的情况，通常是通过NVL函数处理，NVL(E1, E2)的功能为：如果E1为NULL，则函数返回E2，否则返回E1本身。但此函数有一定局限，所以Oracle在NVL函数的功能上扩展，提供了NVL2函数。NVL2(E1, E2, E3)的功能为：如果E1为NULL，则函数返回E3，若E1不为NULL，则返回E2。openGauss当前版本只支持NVL函数，NVL2的功能可用DECODE进行替代。
postgres=# select NVL(9, 0) from dual;
nvl
-----
9
(1 row)
postgres=# select nvl2(100,1,2) from dual;
ERROR: function nvl2(integer, integer, integer) does not exist
LINE 1: select nvl2(100,1,2) from dual;
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
CONTEXT: referenced column: nvl2
PL/SQL语法是Oracle的特有语法，在创建UDF函数、存储过程或者执行程序块都需要按照PL/SQL的语法规则进行执行。openGauss中很好的兼容了PL/SQL语法，自定义函数无需修改即可移植。
postgres=# CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar2, v_version varchar2)
RETURN varchar2 IS
BEGIN
IF v_version IS NULL THEN
RETURN v_name;
END IF;
RETURN v_name || '/' || v_version;
END;
/
CREATE FUNCTION
三、PL/SQL存储过程
由于openGauss可以兼容PL/SQL语法，存储过程创建与函数类似，无需修改即可移植。
postgres=# CREATE OR REPLACE PROCEDURE cs_parse_url(
v_url IN VARCHAR2,
v_host OUT VARCHAR2, -- This will be passed back
v_path OUT VARCHAR2, -- This one too
v_query OUT VARCHAR2) -- And this one
IS
a_pos1 INTEGER;
a_pos2 INTEGER;
BEGIN
v_host := NULL;
v_path := NULL;
v_query := NULL;
a_pos1 := instr(v_url, '//');
IF a_pos1 = 0 THEN
RETURN;
END IF;
a_pos2 := instr(v_url, '/', a_pos1 + 2);
IF a_pos2 = 0 THEN
v_host := substr(v_url, a_pos1 + 2);
v_path := '/';
RETURN;
END IF;
v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
a_pos1 := instr(v_url, '?', a_pos2 + 1);
IF a_pos1 = 0 THEN
v_path := substr(v_url, a_pos2);
RETURN;
END IF;
v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
v_query := substr(v_url, a_pos1 + 1);
END;
/
CREATE PROCEDURE
四、触发器TRIGGERS
openGauss中支持触发器，但需要注意的是，openGauss中的触发器语法与Oracle差异较大，需要进行重写。
postgres=# create or replace trigger modify_stu
before insert on student
for each row
declare
next_id number;
begin
select seq_test.nextval into next_id from dual;
:new.id :=next_id;
end;
/
ERROR: syntax error at or near "trigger"
LINE 1: create or replace trigger modify_stu
五、游标CURSOR
PLSQL中游标常常用于联机交易，调用存储过程所返回的结果集也常用游标去存储的。游标的定义语句比较简单，如 cursor my_cursor is select 1 from dual。openGauss中不兼容游标定义的IS关键字，需要改写为FOR。
postgres=# CURSOR prd_cursor IS select name from product;
ERROR: syntax error at or near "IS"
LINE 1: CURSOR prd_cursor IS
^
postgres=# CURSOR emp_cursor for select name from product;
ERROR: DECLARE CURSOR can only be used in transaction blocks
六、数组VARRAYS
varrays类似于C语言中的数组，可以在表，记录，对象定义中使用。Oracle中的VARRAYS定义可以直接移植到openGauss中。
postgres=# declare
type integer_varray is varray(3) of integer;
var_int integer_varray:=integer_varray();
begin
for i in 1..3 loop
var_int.extend;
var_int(i):=10+i;
end loop;
end;
/
ANONYMOUS BLOCK EXECUTE
七、总结
按照惯例，兼容性总结如下：
|
对象类型 |
分类 |
是否兼容 |
备注 |
|
查询语法 |
ROWNUM |
完全兼容 |
|
|
DUAL |
完全兼容 |
||
|
CONNECT BY |
不兼容 |
需要通过CTE改写 |
|
|
HINT |
部分兼容 |
HINT关键字有区别 |
|
|
执行计划 |
部分兼容 |
不支持autotrace |
|
|
函数 |
DECODE |
完全兼容 |
|
|
时间函数 |
部分兼容 |
SYSTIMESTAMP需改写 |
|
|
空值处理 |
部分兼容 |
NVL2需改写 |
|
|
自定义函数 |
完全兼容 |
||
|
存储过程 |
完全兼容 |
||
|
触发器 |
不兼容 |
需重写 |
|
|
游标 |
不兼容 |
IS关键字需改为FOR |
|
|
数组 |
完全兼容 |
相关阅读：
如果字段的最大可能长度超过255字节，那么长度值可能…
只能说作者太用心了，优秀
感谢详解
一般干个7-8年（即30岁左右），能做到年入40w-50w；有…
230721