|
|
前言
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列的表来进行介绍：
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
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能增加更多的兼容特性。
另外，在相关配套工具方面，包括管理工具、数据迁移、复制等，以及在云相关应用方面提供更多的可用产品。
<hr/>关于IvorySQL
IvorySQL是由瀚高开发，基于PostgreSQL的一款具备强大Oracle兼容能力的开源数据库。紧跟PG社区，快速进行版本迭代，保持与最新版本PostgreSQL数据库内核同步，并支持丰富的PostgreSQL周边工具和扩展。
IvorySQL致力于创建包容和热情的社区，坚持开放，自由，共享的精神，保持专注、专业性。
官方网址:https://www.ivorysql.org/zh-cn/
社区仓库:https://github.com/IvorySQL/IvorySQL
IvorySQL社区欢迎并赞赏所有类型的贡献，期待您的加入！
还有，别忘了在GitHub给我们一个 ⭐奥~ |
|
发表于 2023-1-18 14:41:38