鲲鹏社区首页
EN
注册
对于Oracle的SQL方言,openGauss的兼容性如何?

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

openGauss

发表于 2020/11/26

0

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

数组

 -

完全兼容

 -

相关阅读:

(1)openGauss可替代Oracle吗?从字段类型说起……

(2)openGauss能兼容Oracle的数据库表吗?

(3)Oracle的逻辑对象,openGauss都能hold住吗?

作者介绍

洪烨,openGauss Contributor,多年银行业系统架构设计及DBA实战经验,《DB2数据库内部解析与性能调优》作者。

本页内容