首页 > 新闻资讯 > 技术文章分享：在openGauss中验证Oracle方言的兼容性

技术文章分享：在openGauss中验证Oracle方言的兼容性

时间：2022-08-04 21:44来源：财神爷站

　　对于数据库兼容性来说，主要分为数据的兼容性以及应用的兼容性。数据库应用最核心的部分就是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

　　-------

　　(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?aa=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?aa=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;

　　--------------+-----------------+----+--------------+----------+-------------+-------------+--------------+------------

　　(1?row)

　　postgres=#?explain?select?*?from?test;

　　Seq?Scan?on?test?(cost=0.00..34.02?rows=2402?aa=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

　　-----

　　(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

　　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

热门手机应用

热门手机游戏