对于Oracle的SQL方言,openGauss的兼容性如何?

洪烨 2020-11-26 16:40:00
 

作者介绍

洪烨,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递归等。

 

1、ROWNUM
 

 

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)

 

2、DUAL
 

 

DUAL是一个虚拟表,也是Oracle提供的最小的工作表,Oracle保证DUAL表里面永远只有一条记录(X)。DUAL表通常用来进行功能验证,openGauss中DUAL表的用法与Oracle相同。

 

postgres=# select * from dual;  

 dummy   

-------  

 X  

(1 row) 

 

3、递归查询
 

 

在某些复杂查询的场景下,需要使用递归功能。通过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)

 

4、HINT
 

 

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)

 

5、执行计划
 

 

执行计划是SQL优化的重要手段,在openGauss中不支持autotrace方式查看执行计划,实时的执行计划可以通过explain命令直接查看。与Oracle类似的是,openGauss支持通过explain plan命令将执行计划存入系统表中,不过与Oracle稍有区别,openGauss中会将执行计划存入PLAN_TABLE表。

 

 
postgres=# explain plan for select * from test;

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、时间函数、空函数、自定义函数等。

 

1、DECODE
 

 

DECODE是Oracle公司独家提供的功能,它是一个功能很强的函数。它虽然不是SQL的标准,但对于性能非常有用。openGauss中也提供了DECODE的功能。

 

postgres=# select DECODE(3, 1,'One', 2,'Two', 3,'Three', 'Not found');  

 decode   

--------  

 Three  

(1 row) 

 

2、SYSDATE & SYSTIMESTAMP
 

 

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

 

3、NVL & NVL2
 

 

空值处理是实际中会经常遇到的情况,通常是通过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  

 

4、UDF
 

 

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递归等。

 

1、ROWNUM
 

 

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)

 

2、DUAL
 

 

DUAL是一个虚拟表,也是Oracle提供的最小的工作表,Oracle保证DUAL表里面永远只有一条记录(X)。DUAL表通常用来进行功能验证,openGauss中DUAL表的用法与Oracle相同。

 

postgres=# select * from dual;  

 dummy   

-------  

 X  

(1 row) 

 

3、递归查询
 

 

在某些复杂查询的场景下,需要使用递归功能。通过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)

 

4、HINT
 

 

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)

 

5、执行计划
 

 

执行计划是SQL优化的重要手段,在openGauss中不支持autotrace方式查看执行计划,实时的执行计划可以通过explain命令直接查看。与Oracle类似的是,openGauss支持通过explain plan命令将执行计划存入系统表中,不过与Oracle稍有区别,openGauss中会将执行计划存入PLAN_TABLE表。

 

 
postgres=# explain plan for select * from test;

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、时间函数、空函数、自定义函数等。

 

1、DECODE
 

 

DECODE是Oracle公司独家提供的功能,它是一个功能很强的函数。它虽然不是SQL的标准,但对于性能非常有用。openGauss中也提供了DECODE的功能。

 

postgres=# select DECODE(3, 1,'One', 2,'Two', 3,'Three', 'Not found');  

 decode   

--------  

 Three  

(1 row) 

 

2、SYSDATE & SYSTIMESTAMP
 

 

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

 

3、NVL & NVL2
 

 

空值处理是实际中会经常遇到的情况,通常是通过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  

 

4、UDF
 

 

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

数组

 

完全兼容

 

 

相关阅读:

最新评论
访客 2021年09月03日

有没有1000多张表

访客 2021年08月28日

metrics =》 metrix 错误

访客 2021年08月25日

只看到如何避免,如何减少书写慢 sql

访客 2021年08月25日

没看到如何治理呀

访客 2021年07月23日

果然k8s不是神!

活动预告