IvorySQL之Oracle兼容性

前言

IvorySQL是一款基于PostgreSQL研发的,具备Oracle兼容特性的数据库,所以本文主要介绍IvorySQL中Oracle兼容特性具体案例。

一、系统参数

(一)compatible_mode

compatible_mode是IvorySQL目前所提供的系统参数,主要用于表示当前数据库实例兼容模式,其取值有两个,一个是Oracle,一个是Postgres,我们可以在使用时根据自己需要来进行切换(全局或者会话级)。

在全局设定时,我们需要initdb的时候指定-c 来确定当前数据库实例兼容模式;

./initdb -D ../data -U ivorysql -c oracle

在当前数据库实例的postgresql.conf文件中,可以看到当前的兼容模式,如下:

# default configuration for compatibility mode
compatible_mode = oracle # postgres, oracle

会话级设定兼容模式,如下:

ivorysql=# set compatible_mode = oracle;
SET
ivorysql=# select 1 from dual;
 ?column?
----------
 1
(1 row)
ivorysql=# set compatible_mode = postgres;
SET
ivorysql=# select 1 from dual;
ERROR: relation "dual" does not exist
LINE 1: select 1 from dual;

通过上述结果可以看到当前兼容模式切换前后对伪表的操作结果。

(二)nls_length_semantics

用于指定会话中创建的数据库对象中的VARCHAR2和CHAR表列、用户定义的对象属性和PL/SQL变量的默认长度语义。

注:NCHAR、NVARCHAR2、CLOB和NCLOB列始终基于字符。

以下以创建带有varchar2和nvarchar2列的表来进行介绍:

Varchar2

ivorysql=# create table test(a varchar2(5));
CREATE TABLE
ivorysql=# SET NLS_LENGTH_SEMANTICS TO CHAR;
SET
ivorysql=# SHOW NLS_LENGTH_SEMANTICS;
 nls_length_semantics
----------------------
 char
(1 row)
ivorysql=# insert into test values ('开源数据库');
INSERT 0 1
ivorysql=# SET NLS_LENGTH_SEMANTICS TO byte;
SET
ivorysql=# insert into test values ('开源数据库');
ERROR: value too long for type varchar2(5 byte)
ivorysql=# insert into test values ('ivory');
INSERT 0 1

Nvarchar2

ivorysql=# create table test(a nvarchar2(5));
CREATE TABLE
ivorysql=# SET NLS_LENGTH_SEMANTICS TO CHAR;
SET
ivorysql=# insert into test values ('开源数据库');
INSERT 0 1
ivorysql=# SET NLS_LENGTH_SEMANTICS TO byte;
SET
ivorysql=# insert into test values ('开源数据库');
INSERT 0 1
ivorysql=# insert into test values ('开源数据库1');
ERROR: input value too long for type nvarchar2(5)
ivorysql=# insert into test values ('ivory');
INSERT 0 1
ivorysql=# insert into test values ('ivorys');
ERROR: input value too long for type nvarchar2(5)

从上述结果来看,设置成char或者byte,对于varchar2列是有效;则对于nvarchar2列来说,则只支持char,这与Oracle原参数设置效果是一致的。

二、高级特性

在当前IvorySQL的版本中,相对来说高级特性包括了层次查询以及包,层次查询主要支持通过SQL来进行汇总统计和分析使用;包则是兼容特性的一个相对来说高级基础特性,主要是由于系统包开发都是需要该特性来进行实现的。

(一)层次查询

层级查询是一种允许对分层数据进行操作的 SQL 语句。
1.语法

{
 CONNECT BY [ NOCYCLE ] [PRIOR] condition [AND [PRIOR] condition]... [ START WITH condition ]
 | START WITH condition CONNECT BY [ NOCYCLE ] [PRIOR] condition [AND [PRIOR] condition]...
}

本示例演示如何调用层次查询相关语法,构造查询结果。

创建一张测试数据表并插入数据:

CREATE TABLE employees(
 employee_id integer, 
 name varchar(25), 
manager_id integer);
insert into employees values(101, 'Kochhar', 100);
insert into employees values(108, 'Greenberg', 101);
insert into employees values(109, 'Faviet', 108);
insert into employees values(110, 'Chen', 108);
insert into employees values(111, 'Sciarra', 108);
insert into employees values(112, 'Urman', 108);

--基本查询

postgres=# SELECT name, employee_id, manager_id, LEVEL
postgres-# FROM employees START WITH employee_id = 101
postgres-# CONNECT BY PRIOR employee_id = manager_id;
 name | employee_id | manager_id | LEVEL
-----------+-------------+------------+-------
 Kochhar | 101 | 100 | 1
 Greenberg | 108 | 101 | 2
 Faviet | 109 | 108 | 3
 Chen | 110 | 108 | 3
 Sciarra | 111 | 108 | 3
 Urman | 112 | 108 | 3
(6 rows)

--SYS_CONNECT_BY_PATH

postgres=# SELECT name "Employee", SYS_CONNECT_BY_PATH(name, '/') "Path"
postgres-# FROM employees
postgres-# START WITH name = 'Kochhar'
postgres-# CONNECT BY PRIOR employee_id = manager_id;
 Employee | Path
-----------+----------------------------
 Kochhar | /Kochhar
 Greenberg | /Kochhar/Greenberg
 Faviet | /Kochhar/Greenberg/Faviet
 Chen | /Kochhar/Greenberg/Chen
 Sciarra | /Kochhar/Greenberg/Sciarra
 Urman | /Kochhar/Greenberg/Urman
(6 rows)

--order by

postgres=# SELECT name, CONNECT_BY_ROOT employee_id as "Manager", SYS_CONNECT_BY_PATH(name, '/') "Path"
postgres-# From Employees START WITH manager_id is not null
postgres-# CONNECT BY PRIOR employee_id = manager_id
postgres-# ORDER BY employee_id, manager_id;
 name | Manager | Path
-----------+---------+----------------------------
 Kochhar | 101 | /Kochhar
 Greenberg | 101 | /Kochhar/Greenberg
 Greenberg | 108 | /Greenberg
 Faviet | 108 | /Greenberg/Faviet
 Faviet | 109 | /Faviet
 Faviet | 108 | /Kochhar/Greenberg/Faviet
 Chen | 108 | /Kochhar/Greenberg/Chen
 Chen | 110 | /Chen
 Chen | 108 | /Greenberg/Chen
 Sciarra | 108 | /Kochhar/Greenberg/Sciarra
 Sciarra | 111 | /Sciarra
 Sciarra | 108 | /Greenberg/Sciarra
 Urman | 112 | /Urman
 Urman | 108 | /Greenberg/Urman
 Urman | 108 | /Kochhar/Greenberg/Urman
(15 rows)

--CONNECT_BY_ROOT和SYS_CONNECT_BY_PATH

postgres=# SELECT employee_id, manager_id, name, CONNECT_BY_ROOT employee_id as "Manager", SYS_CONNECT_BY_PATH(name, '/') "Path"
postgres-# FROM employees
postgres-# START WITH manager_id is not null
postgres-# CONNECT BY PRIOR employee_id = manager_id
postgres-# order by employee_id, manager_id;
 employee_id | manager_id | name | Manager | Path
-------------+------------+-----------+---------+----------------------------
 101 | 100 | Kochhar | 101 | /Kochhar
 108 | 101 | Greenberg | 101 | /Kochhar/Greenberg
 108 | 101 | Greenberg | 108 | /Greenberg
 109 | 108 | Faviet | 108 | /Greenberg/Faviet
 109 | 108 | Faviet | 109 | /Faviet
 109 | 108 | Faviet | 108 | /Kochhar/Greenberg/Faviet
 110 | 108 | Chen | 108 | /Kochhar/Greenberg/Chen
 110 | 108 | Chen | 110 | /Chen
 110 | 108 | Chen | 108 | /Greenberg/Chen
 111 | 108 | Sciarra | 108 | /Kochhar/Greenberg/Sciarra
 111 | 108 | Sciarra | 111 | /Sciarra
 111 | 108 | Sciarra | 108 | /Greenberg/Sciarra
 112 | 108 | Urman | 112 | /Urman
 112 | 108 | Urman | 108 | /Greenberg/Urman
 112 | 108 | Urman | 108 | /Kochhar/Greenberg/Urman
(15 rows)

目前此功能有以下限制:

  • 附加列可用于大多数表达式,如函数调用、CASE 语句和通用表达式,但有部分不受支持的列,如 ROW、TYPECAST、COLLATE、GROUPING 子句等。
  • 两个或多个列相同的情况下,可能需要输出列名,例如:

SELECT CONNECT_BY_ROOT col AS "col1", CONNECT_BY_ROOT col AS "col2"。

  • 不支持间接运算符或“*”。
  • 不支持循环检测(Loop detection)。

目前来看,包括伪列、函数等操作的支持还需要加强,希望后续该功能可以更加丰富。

(二)包

包简单来说就是一个对象或一组对象打包在一起。将过程、函数、变量、游标、用户定义的记录类型和引用记录的逻辑分组集合打包在自己内部,在声明包和包体定义后(架构和内容)就可以进行调用。

示例1演示如何创建包和包体,并且进行包内函数调用,脚本如下:

--声明包

postgres=# create or replace package pkg is
postgres$# x int;
postgres$#
postgres$# function tfunc(x int) return int;
postgres$# end;
postgres$# /
CREATE PACKAGE

--声明包体

postgres=# create or replace package body pkg is
postgres$#
postgres$# function tfunc(x int) return int as
postgres$# begin
postgres$# return x;
postgres$# end;
postgres$# end;
postgres$# /
CREATE PACKAGE BODY

--可以通过系统表查看包内成员

postgres=# select proname, proargtypes from pg_proc p, pg_package pk where pronamespace=pk.oid and pkgname='pkg' order by proname;
 proname | proargtypes
---------+-------------
 pkg |
 tfunc | 23
(2 rows)

--调用包内函数

postgres=# select pkg.tfunc(10);
 tfunc
-------
 10
(1 row)

示例2演示包内函数和存储过程重载如下:

--创建包定义

postgres=# create or replace package pkg
postgres-# is
postgres$#
postgres$# function tfunc return int;
postgres$#
postgres$# procedure tpro;
postgres$#
postgres$# function tfunc(x int,y int) return int;
postgres$# procedure tpro(x int);
postgres$# end;
postgres$# /
CREATE PACKAGE

--创建包体

postgres=# create or replace package body pkg
postgres-# is
postgres$# function tfunc return int
postgres$# as
postgres$# begin
postgres$# return 0;
postgres$# end;
postgres$#
postgres$# procedure tpro
postgres$# as
postgres$# pvar int;
postgres$# begin
postgres$# pvar:=10;
postgres$# end;
postgres$#
postgres$# function tfunc(x int,y int) return int
postgres$# as
postgres$# begin
postgres$# return (x+y);
postgres$# end;
postgres$#
postgres$# procedure tpro(x int)
postgres$# as
postgres$# y int;
postgres$# begin
postgres$# y := x;
postgres$# end;
postgres$# end;
postgres$# /
CREATE PACKAGE BODY

--执行函数调用

select pkg.tfunc();
select pkg.tfunc(1,2);

三、总结

上文介绍并演示了IvorySQL的几个兼容特性,作为目前唯一基于PG开源的Oracle兼容数据库来说,虽然具体功能与原生Oracle还有非常大的差距,但是毕竟仍在起步阶段,希望未来IvorySQL能增加更多的兼容特性。
另外,在相关配套工具方面,包括管理工具、数据迁移、复制等,以及在云相关应用方面提供更多的可用产品。

关于IvorySQL

IvorySQL是由瀚高开发,基于PostgreSQL的一款具备强大Oracle兼容能力的开源数据库。紧跟PG社区,快速进行版本迭代,保持与最新版本PostgreSQL数据库内核同步,并支持丰富的PostgreSQL周边工具和扩展。

IvorySQL致力于创建包容和热情的社区,坚持开放,自由,共享的精神,保持专注、专业性。

官方网址:
https://www.ivorysql.org/zh-cn/
社区仓库:
https://github.com/IvorySQL/I...
IvorySQL社区欢迎并赞赏所有类型的贡献,期待您的加入!
还有,别忘了在GitHub给我们一个 ⭐奥~

作者:IvorySQL原文地址:https://segmentfault.com/a/1190000043343126

%s 个评论

要回复文章请先登录注册