注意，本文内容很丰富，建议收藏后慢慢阅读。
Table of Contents
前言
当前信创环境下，由于国家相关政策，很多企业被要求必须弃用Oracle。很多企业本身并不具备数据库内核研发的能力。而使用国产数据库或者开源数据库，对Oracle数据库的兼容性始终是个避不开的问题。
那么，除了等待数据库厂商提供增强Oracle兼容能力的数据库版本，使用数据库的企业自身还能通过何种方式来提高开源或国产数据库对Oracle的兼容性？
openGauss基于postgresql 9.2，而pg的底层框架设计，很多地方是和oracle类似的，而且openGauss的sql及plpgsql语法，能高度兼容Oracle，所以本文以openGauss的商业发行版之一—“MogDB” 为例，来说一些增强对Oracle兼容性的手段。
注意，本文MogDB版本为 3.0.1，其他版本可能会有部分区别，暂不区分讨论。
另外，兼容性良好的表现，至少分两层，一是不需要改写代码即可支持，二是可以通过改写代码来支持。
一、已有的插件或兼容工具包
MogDB的oracle兼容三大件
- whale（extension)
https://docs.mogdb.io/zh/mogdb/v3.0/whale - orafce（extension)(第三方插件，个人不推荐使用，除非要用其特有的package)
https://docs.mogdb.io/zh/mogdb/v3.0/orafce - compat-tools (sql_script)
https://gitee.com/enmotech/compat-tools
1.1 extension 安装
方法一：手动安装下载插件压缩包并解压
https://mogdb.io/downloads/mogdb/
点介质下载，选择需要的操作系统版本，然后选择"Plugins"开头的压缩包下载
将下载后的压缩包放到数据库服务器上，解压，然后执行下列命令(用数据库操作系统用户)
cp `find plugins -name *.so` $GAUSSHOME/lib/postgresql/
cp `find plugins -name "*.control" -or -name "*.sql"` $GAUSSHOME/share/postgresql/extension/
然后再手动连接数据库，执行
create extension whale;
create extension orafce;
方法二：通过PTK安装的MogDB,可以使用以下命令
ptk cluster install-plugin -n $cluster_name
该命令会自动下载插件压缩包，并自动解压到插件目录，之后再进数据库create extension即可
（$cluster_name请修改为 ptk ls中查询到的名称）
参考文档：https://docs.mogdb.io/zh/ptk/v0.3/usage-install-plugin
另外请注意，whale插件，只支持在A兼容模式下安装
https://docs.mogdb.io/zh/mogdb/v3.0/CREATE-DATABASE
DBCOMPATIBILITY [ = ] compatibility_type
指定兼容的数据库的类型。取值范围: A、B、C、PG。分别表示兼容Oracle、MySQL、Teradata和PostgreSQL。但是C目前已经放弃支持，因此常用的取值是A、B、PG，默认兼容A。
1.2 compat-tools 安装
compat-tools是一组开源的sql脚本文件，完全使用sql及plpgsql语言编写，支持大量的兼容视图、函数、包。
从网页下载compat-tools源码,上传到服务器，并解压
https://gitee.com/enmotech/compat-tools
进入compat-tools-master目录，连接需要安装的数据库执行runMe.sql即可，比如
cd compat-tools-master
gsql -p 26000 -d postgres -f runMe.sql
安装这三大件后，在MogDB中编写sql及plsql时，就相当舒适了。
二、如何兼容内置表或视图
2.1 dual
在MogDB中，有一个这样的视图，代码是
CREATE OR REPLACE VIEW pg_catalog.sys_dummy
AS SELECT 'X'::text AS dummy;
查询出来的结果和oracle中的dual表一致，那么我们可以创建一个public的同义词，叫dual，指向sys_dummy即可。（compat-tools和whale中的dual使用此方案）。
或者使用orafce的方案,更直接
CREATE VIEW public.dual AS SELECT 'X'::varchar AS dummy;
REVOKE ALL ON public.dual FROM PUBLIC;
GRANT SELECT, REFERENCES ON public.dual TO PUBLIC;
2.2 dba_*
在oracle中，有很多DBA_/ALL_/USER_开头的视图，如果打开这些视图的代码来看，会发现这些视图中的数据来自于很多张表，这些表即为数据库对象的元数据。由于MogDB的元数据架构和oracle很类似，因此也可以用类似的方式，编写出DBA_/ALL_/USER_这样的视图。
目前compat-tools中已经基本涵盖里常见视图，所以对oracle熟悉的，又想要快速了解mogdb的数据字典的，可以看看compat-tools视图中的代码，这可能比直接看文档了解得更快。
但有一点需要注意的是，oracle和mogdb相比，schema和user的权限有所区别，在MogDB中，一个对象的owner和schema是可能不同的。owner是创建者，创建者可以把对象创建在和它不同名的schema下。compat-tools中的视图未进行特殊权限的处理，三者一致，因此如果是多schema管理，还涉及到权限，建议根据实际情况对视图进行符合业务代码规则的修改
三、如何兼容数据类型
3.1 sql数据类型
–oracle数据类型文档
https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Data-Types.html
–mogdb数据类型文档
https://docs.mogdb.io/zh/mogdb/v3.0/1-numeric-data-types
注意，oracle实际可以使用的类型不止有文档中的这些，比如在Oracle中还支持SMALLINT、NUMERIC、DEC这些类型名称，完整的应该查看sys.standard包里面的声明，但本文不对oracle这些隐藏类型进行对比
| ORACLE数据类型 | Mogdb是否有该类型名称 | 备注 |
|---|---|---|
| VARCHAR2(size [BYTE | CHAR]) | 有 | 按varchar处理，且最大长度比oracle大 |
| NVARCHAR2(size) | 有 | 存储上和oracle不一样 |
| NUMBER [ (p [, s]) ] | 有 | 按numeric处理 |
| FLOAT [§] | 有 | mogdb中的float其实对应oracle中的BINARY_FLOAT，所以建议改用numeric |
| DECIMAL | 有 | 但和oracle不一致，oracle里DECIMAL是整数，而mogdb里等同于numeric |
| LONG | 有 | 按text处理 |
| DATE | 有 | 按timestamp without time zone处理 |
| BINARY_FLOAT | 无 | 其实对应mogdb的float类型，建议用numeric替代 |
| BINARY_DOUBLE | 无 | 其实对应mogdb的float8类型，建议用numeric替代 |
| TIMESTAMP [(0-9)] | 有 | 按timestamp without time zone处理，秒后的精度只有6位，而oracle默认6位，最大9位 |
| TIMESTAMP [(0-9)] WITH TIME ZONE | 有 | 逻辑和Oracle不一致 |
| TIMESTAMP [(0-9)] WITH LOCAL TIME ZONE | 无 | 按timestamp with time zone处理，秒后的精度只有6位，而oracle默认6位，最大9位 |
| INTERVAL YEAR [(year_precision)] TO MONTH | 无 | 用INTERVAL替代 |
| INTERVAL DAY [(day_precision)] TO SECOND [(0-9)] | 无 | 用INTERVAL替代 |
| RAW(size) | 有 | 比Oracle支持的长度要大，有1G |
| LONG RAW | 无 | 用raw/blob替代 |
| ROWID | 无 | 针对select中的rowid列，目前没有很好的替代方案，但是如果是rowid类型，其实用普通的文本类型就可以替代了 |
| UROWID [(size)] | 无 | 和rowid的支持情况一样 |
| CHAR [(size [BYTE | CHAR])] | 有 | |
| NCHAR[(size)] | 有 | 存储上和oracle不一样 |
| CLOB | 有 | 长度只有1G，按TEXT处理 |
| NCLOB | 无 | 建议用TEXT或者CLOB替代 |
| BLOB | 有 | 按RAW处理 |
| BFILE | 无 | |
| JSON | 有 | 但存储机制上更接近的，应该是jsonb |
3.2 plsql 数据类型
–Oracle pl/sql 数据类型文档
https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/plsql-data-types.html
比如常见的pls_integer类型，在功能使用上，其实和普通的整型区别并不大，
在MogDB中可以创建一个自定义的基本type，但需要再创建4个转换函数（转入转出二进制、转入转出cstring），让其具有和数据库内核传输数据的基本能力。（compat-tools里已支持）
create type pg_catalog.pls_integer;
CREATE OR REPLACE FUNCTION pg_catalog.pls_integer_in(cstring)
RETURNS pls_integer
LANGUAGE internal
IMMUTABLE STRICT NOT FENCED NOT SHIPPABLE
AS $function$int4in$function$;
CREATE OR REPLACE FUNCTION pg_catalog.pls_integer_out(pls_integer)
RETURNS cstring
LANGUAGE internal
IMMUTABLE STRICT NOT FENCED NOT SHIPPABLE
AS $function$int4out$function$;
CREATE OR REPLACE FUNCTION pg_catalog.pls_integer_send(pls_integer)
RETURNS bytea
LANGUAGE internal
IMMUTABLE STRICT NOT FENCED NOT SHIPPABLE
AS $function$int4send$function$;
CREATE OR REPLACE FUNCTION pg_catalog.pls_integer_recv(internal)
RETURNS pls_integer
LANGUAGE internal
IMMUTABLE STRICT NOT FENCED NOT SHIPPABLE
AS $function$int4recv$function$;
CREATE TYPE pg_catalog.pls_integer (
INPUT = pls_integer_in,
OUTPUT = pls_integer_out,
RECEIVE = pls_integer_recv,
SEND = pls_integer_send,
INTERNALLENGTH = 4,
STORAGE = plain,
CATEGORY = N,
DELIMITER = ',',
PASSEDBYVALUE
);
CREATE CAST (pls_integer AS int4) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (int4 AS pls_integer) WITHOUT FUNCTION AS IMPLICIT;
这种方式其实有点复杂了，其实还有另一种方式，就是创建一个domain（域）。http://postgres.cn/docs/13/sql-createdomain.html
它的基础定义中存在一个type，并且可以对type中的值进行一些限定，类似于oracle中的subtype
---oracle plsql中定义一个 int_10 的子类型
subtype int_10 is number(10);
---MogDB中创建一个 int_10 的域
create domain int_10 as number(10);
而且用这种方式，可以模拟出package里定义subtype的效果
create schema pkg_subtype;
create domain pkg_subtype.varchar_20 as varchar(20);
create domain pkg_subtype.number_20_4 as number(20,4);
declare
a pkg_subtype.number_20_4;
b pkg_subtype.varchar_20;
begin
a:=1.2;
b:='abc';
end;
有了这个，如果在oracle中有的类型在MogDB中没有同名的，但有可以替代的，也可以通过创建一个domain来支持了。比如
create domain pls_integer as int4;
像前面提到的mogdb中没有的BINARY_FLOAT类型，也可以用这个方式来支持
create domain BINARY_FLOAT as float;
3.3 array type / table type
从mogdb3.0.0开始，支持创建table of的type，比如
create type test_type is table of number;
如果是 varray of 的type，目前暂不支持创建，但是也可以改写成 table of，因为这两种类型在mogdb的plsql中，使用方法基本是一样的。另外，还可以用创建domain的方式，比如
create domain test_type is number[];
四、如何兼容内置函数/过程
4.1 单行函数
4.1.1 不带关键字的函数
这种很简单，只要知道oracle里是怎么算的，就能模拟出来，可参考compat-tools,这里举几个例子，
例一：cosh （数学函数）
公式为：
则创建的自定义函数为
CREATE OR REPLACE FUNCTION pg_catalog.cosh(numeric)
returns numeric
LANGUAGE sql
as $$select (exp($1)+exp(-$1))/2 $$;
例二：lnnvl （表达式函数）
众所周知，在oracle里，大于小于是不能用来判断空值的，一般要用nvl函数处理一下，但是这样可能又用不上原有字段的索引，有lnnvl这个函数就可以避免这个问题，但是这个函数的用法和常见的函数不一样,比如
SELECT COUNT(*)
FROM employees
WHERE LNNVL(commission_pct >= 0.2)
它把"commission_pct >= 0.2"作为了函数的参数,执行结果等价于
SELECT COUNT(*)
FROM HR.employees
WHERE commission_pct is null or commission_pct < 0.2;
而对于mogdb，这种可以用boolean类型来声明，并返回boolean类型，比如
create or replace function pg_catalog.lnnvl(bool)
returns BOOl
LANGUAGE sql
as $$ select case when $1 is null or $1=false then true else false end $$;
例三：dump （多态函数）
dump函数，支持传入任意类型的值,返回它的二进制数据（这个例子暂未提交至compat-tools）
CREATE OR REPLACE FUNCTION pg_catalog.dump(anyelement)
RETURNS text
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $function$
declare
v_typsend text;
v_type text;
v_bytea bytea;
v_hexstr TEXT;
v_hexbyte TEXT;
v_tmp TEXT;
i INT;
v_len INT;
begin
select typsend,typname into v_typsend,v_type from pg_type
where oid= pg_typeof($1);
if v_type='blob' then
v_typsend:='rawsend';
elsif v_type='unknown' then
v_typsend:='textsend';
end if;
EXECUTE 'select '||v_typsend||'(:1)' into v_bytea using $1;
SELECT length(v_bytea) into v_len;
v_hexstr := 'Len=' || v_len || ' ';
v_tmp := ',';
FOR i in 1..v_len LOOP
select to_hex(get_byte(v_bytea, i-1)) into v_hexbyte;
if i = v_len then
v_tmp := '';
end if;
v_hexstr := v_hexstr || v_hexbyte || v_tmp;
END LOOP;
RETURN v_hexstr;
END;
$function$;
CREATE OR REPLACE FUNCTION pg_catalog.dump(unknown)
RETURNS text
LANGUAGE plpgsql
IMMUTABLE NOT FENCED NOT SHIPPABLE
AS $function$
declare
v_typsend text;
v_type text;
v_bytea bytea;
v_hexstr TEXT;
v_hexbyte TEXT;
v_tmp TEXT;
i INT;
v_len INT;
begin
v_typsend:='textsend';
EXECUTE 'select '||v_typsend||'(:1)' into v_bytea using $1;
SELECT length(v_bytea) into v_len;
v_hexstr := 'Len=' || v_len || ' ';
v_tmp := ',';
FOR i in 1..v_len LOOP
select to_hex(get_byte(v_bytea, i-1)) into v_hexbyte;
if i = v_len then
v_tmp := '';
end if;
v_hexstr := v_hexstr || v_hexbyte || v_tmp;
END LOOP;
RETURN v_hexstr;
END;
$function$;
注意，这里我们创建了两个同名但不同入参的函数，在mogdb中是支持这种行为的。而且顺带说下，单引号引起来的字符串，类型是’unknown’，可以隐式转换为text,但是，unknown类型的值本身，不能传入anynonarray类型入参的函数中去；另外，由于TEXT类型的重载优先于anyelement，所以这里单独写了个unknown类型的，让其优先级在最后。
另一个注意，orafce插件中也带了dump函数，建在了public下，优先级很高，但实测它都是转换成文本再获取其二进制数据，结果并不准确。
例四：unistr（字符串函数，防注入的一个例子）
CREATE OR REPLACE FUNCTION pg_catalog.unistr(text)
RETURNS text
LANGUAGE plpgsql
NOT FENCED NOT SHIPPABLE
AS $$
DECLARE
r text;
begin
IF nvl(instr($1,'\'),0)>0 THEN
EXECUTE left(REPLACE(REPLACE('select '||'U&'''||quote_nullable($1)||'''','U&''E''','U&'''),'\\','\'),-1) into r;
ELSe r:=$1;
END IF;
return r;
end; $$;
这里使用了quote_nullable，类似于oracle中的DBMS_ASSERT.ENQUOTE_NAME的用法
例五：bin_to_num（不定参数个数的函数）
CREATE OR REPLACE FUNCTION pg_catalog.bin_to_num(VARIADIC integer [])
RETURNS int
LANGUAGE sql
NOT FENCED NOT SHIPPABLE
AS $$
select int8(replace(array_to_string($1,','),',')::varbit)::int;
$$;
bin_to_num支持传入不定个数参数，这里的关键字为”VARIADIC“，后面要接一个数组类型，这样在多个参数传入的时候，就会当成一个数组被传入
4.1.2 带语法关键字的函数
比如xml/json相关函数中有path关键词，而不是作为一个参数，这种无法通过自定义plpgsql函数来创建，因为涉及到语法解析
4.2 聚合函数
其实ORACLE也支持自定义聚合函数，但用得很少；由于需要在MogDB里做出它本来不支持的聚合函数，这种情况下自定义聚合函数就用得比较多了。
常用的聚合函数分三个部分，
- 采集数据的函数(sfunc)
- 最终计算的函数(finalfunc)
- 本体(CREATE AGGREGATE)
一般情况下sfunc函数有两个参数，第一个参数已经收集到的值的集合，第二个参数是单个值，可以理解为这个函数会被循环调用，通过第二个参数逐个传入所有需要聚合的值，然后返回一个所有值的集合，再把这个集合传入到finalfunc完成最终计算
参考compat-tools中的几个具有代表意义的聚合函数（这里就不贴完整代码了）
https://gitee.com/enmotech/compat-tools/blob/master/Oracle_Functions.sql
4.2.1 例一：wm_concat
CREATE AGGREGATE pg_catalog.wm_concat(text)
(
sfunc = pg_catalog.vm_concat_state_func, --自定义函数，用来拼接字符串，第二个参数类型为聚合函数本体的输入参数类型
stype = text, --表示sfunc第一个参数的类型、sfunc返回的类型、finalfunc输入的类型
initcond = '',--sfunc第一个参数第一次执行时的值
FINALFUNC = pg_catalog.vm_concat_final_func --自定义函数，用来去掉sfunc返回值的第一个逗号，返回结果
);
4.2.2 例二：kurtosis_pop
CREATE AGGREGATE pg_catalog.kurtosis_pop(numeric)
(
sfunc = array_append, --数据库内置的一个函数，将第二个参数增加到第一个数组参数中去，并将最后的数组返回
stype = numeric[], --表示sfunc第一个参数的类型、sfunc返回的类型、finalfunc输入的类型
FINALFUNC = pg_catalog.kurtosis_pop_final_func --自定义函数，处理sfunc返回的值，并返回最终结果
);
4.2.3 例三：bit_and_agg
CREATE AGGREGATE pg_catalog.bit_and_agg(numeric)
(
sfunc = pg_catalog.bit_and_agg_state_func,--自定义函数
stype = int
---不需要最终处理函数，以sfunc返回值作为最终结果
);
4.2.4 例四：any_value
create aggregate pg_catalog.any_value(anyelement)
(
sfunc = first_transition, --内置函数，取第一个值
stype = anyelement --输入类型和输出类型一致，
);
4.3 存储过程
举个例子，比如常用的Raise_application_error，完全可以写一个简单的存储过程来进行替代
create or replace procedure pg_catalog.raise_application_error (int4,text) is
begin
raise '%:%',$1,$2;
end;
只是相比oracle弹出的信息而言，少了堆栈信息。
4.4 开窗函数/分析函数
根据我之前尝试加开窗函数的经验，基本判断，不改源码就无法新增开窗函数，所以这一点，只能尽量使用目前有的开窗函数来改写不支持的开窗函数了。但好消息是，“RATIO_TO_REPORT” 这个oracle独有的，不在sql标准里的分析函数，从mogdb3.0.0开始已经支持了。
另外，可以用下面这个sql,查出所有支持分析函数语法的函数(所有聚合函数均可over)
select distinct proname from pg_proc p where p.proiswindow or p.proisagg
五、如何兼容内置包
我把内置包分这么几类
- 数据库管理（比如dbms_job/dbms_application_info等）
- 数据处理和计算（比如dbms_lob/utl_raw等）
- 和数据库外部进行交互（比如utl_http/utl_file等）
- 对于数据库管理的，只要功能上可以匹配，那么就可以自定义封装一个这样的包；
- 对于数据处理和计算的，这种是最好实现的，只要知道算法，比如utl_encode包中的各种转换，就也可以自定义封装一个包；
- 最麻烦的当属和数据库外部进行交互了，目前仅有orafce插件中支持一个utl_file包，还不支持二进制文件读写，只能支持文本文件。但从另一个角度上来说，限制对数据库外部的交互，其实也可以规避很多风险。
下面是安装"三大件"后,mogdb中支持的package的情况，目前光compat-tools就可以支持其中的13个package，也就是上面说的前两种类型的情况
omm_oracle=# select version();
version
----------------------------------------------------------------------------------------------------------------------------------------
----------
(MogDB 3.0.1 build 1a363ea9) compiled at 2022-08-05 17:31:04 commit 0 last mr on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.
0, 64-bit
(1 row)
omm_oracle=# SELECT nspname
omm_oracle-# FROM pg_catalog.pg_namespace
omm_oracle-# WHERE nspname LIKE 'utl%'
omm_oracle-# OR nspname LIKE 'dbms%';
nspname
--------------------------
dbms_metadata
dbms_random
dbms_output
dbms_lock
dbms_application_info
dbms_utility
dbms_job
utl_file
dbms_pipe
dbms_alert
dbms_assert
dbms_obfuscation_toolkit
dbms_lob
utl_url
dbms_snapshot
utl_encode
utl_raw
(17 rows)
六、如何做sql/plsql兼容
6.1 sql
语法涉及到内核，用户在没有源码的情况下，一般是不能改的。比如pivot/unpivot/keep，目前mogdb是不支持的。
但是pg系数据库可以自定义操作符（operator），也就是说，用户完全可以通过自己定义一个操作符，来实现一些数据库本身并不支持的sql功能
比如oracle中有的两个日期相减得一个数字，参考orafce中的写法
CREATE OR REPLACE FUNCTION oracle.subtract(oracle.date,oracle.date)
RETURNS double precision AS $$
SELECT date_part('epoch', ($1::timestamp - $2::timestamp)/3600/24);
$$ LANGUAGE SQL IMMUTABLE;
CREATE OPERATOR oracle.- (
LEFTARG = oracle.date,
RIGHTARG = oracle.date,
PROCEDURE = oracle.subtract
);
这个操作符甚至还可以用多个符号来组合定义 ，详见
https://docs.mogdb.io/zh/mogdb/v3.0/CREATE-OPERATOR
注意，对于 schema/操作符/左类型/右类型，这4个值的组合，必须保持唯一,遇到冲突时，可以换一个schema，并设置search_path的顺序来实现使用指定schema下的操作符
6.2 plsql
mogdb2.1起，已经开始支持package功能，从3.0.0版本又大大的增强了对oracle各种plsql语法的兼容性，但是的确还有部分代码不能直接移植，这里用表函数为例，来说明其是可以通过改写来支持的
6.2.1 表函数
在oracle中，可以创建一个return record type的函数，这样就可以在from后面使用这个函数；
而在MogDB中，常规的单行函数也可以在 from 后面，查出来的字段就是这个函数的out参数及return值（procedure的out参数也可以被select）
select * from dbms_utility.db_version();
另外也可以支持类似oracle表函数的方式，不过return的是setof typename/tablename
CREATE TABLE foo (fooid int, foosubid int, fooname text);
CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
6.2.2 pipeline函数
MogDB目前是不支持pipeline这个写法的，但是有类似的语法功能，我们以oracle官方文档的一个例子来进行改写
https://docs.oracle.com/cd/B10501_01/appdev.920/a96595/dci12tbl.htm
CREATE TABLE StockTable (
ticker VARCHAR(4),
open_price NUMBER,
close_price NUMBER
);
insert into StockTable values ('a',5,6);
insert into StockTable values ('b',7,8);
Commit;
-- Create the types for the table function's output collection
-- and collection elements
CREATE TYPE TickerType AS OBJECT
(
ticker VARCHAR2(4),
PriceType VARCHAR2(1),
price NUMBER
);
CREATE TYPE TickerTypeSet AS TABLE OF TickerType;
-- Define the ref cursor type
CREATE PACKAGE refcur_pkg IS
TYPE refcur_t IS REF CURSOR RETURN StockTable%ROWTYPE;
END refcur_pkg;
/
CREATE FUNCTION StockPivot(p refcur_pkg.refcur_t) RETURN TickerTypeSet
PIPELINED IS
out_rec TickerType := TickerType(NULL,NULL,NULL);
in_rec p%ROWTYPE;
BEGIN
LOOP
FETCH p INTO in_rec;
EXIT WHEN p%NOTFOUND;
-- first row
out_rec.ticker := in_rec.Ticker;
out_rec.PriceType := 'O';
out_rec.price := in_rec.Open_Price;
PIPE ROW(out_rec);
-- second row
out_rec.PriceType := 'C';
out_rec.Price := in_rec.Close_Price;
PIPE ROW(out_rec);
END LOOP;
CLOSE p;
RETURN;
END;
SELECT x.Ticker, x.Price
FROM TABLE(StockPivot( CURSOR(SELECT * FROM StockTable))) x
WHERE x.PriceType='C';
改写后
CREATE TABLE StockTable (
ticker VARCHAR(4),
open_price NUMBER,
close_price NUMBER
);
insert into StockTable values ('a',5,6);
insert into StockTable values ('b',7,8);
CREATE TYPE TickerType AS
(
ticker VARCHAR2(4),
PriceType VARCHAR2(1),
price NUMBER
);
CREATE or replace FUNCTION StockPivot(p refcursor) RETURNs --TickerTypeSet
setof TickerType
language plpgsql
as
$$
declare
out_rec TickerType := TickerType(NULL,NULL,NULL);
in_rec p%ROWTYPE;
BEGIN
LOOP
FETCH p INTO in_rec;
EXIT WHEN p%NOTFOUND;
-- first row
out_rec.ticker := in_rec.Ticker;
out_rec.PriceType := 'O';
out_rec.price := in_rec.Open_Price;
return next out_rec;
-- second row
out_rec.PriceType := 'C';
out_rec.Price := in_rec.Close_Price;
return next out_rec;
END LOOP;
CLOSE p;
RETURN;
end;
$$;
declare
cursor ccc for select * from StockTable;
begin
open ccc;
for rec in (select * from StockPivot(ccc) where PriceType='C' ) loop
raise notice '%,%',rec.Ticker, rec.Price;
end loop;
end;
可以看到此例中，我们把一个查询sql作为一个游标，传进了pipeline的函数，1行数据变2行，并通过返回字段作为一个where条件，得到了2条记录，和oracle中的逻辑完全一致。
- “PIPE ROW (record);” 对应的是 “return next record；”
- “RETURN TickerTypeSet（游标类型/集合） PIPELINED” 对应的是 “RETURNs setof TickerType（单行类型/单行复合类型）”
需要注意的两点
- MogDB3.0.1游标只能在plpgsql块中使用,不能在sql语句中使用
- MogDB3.0.1不支持定义 “TYPE refcur_t IS REF CURSOR RETURN StockTable%ROWTYPE;”
七、库内执行自定义C语言函数
oracle其实也是支持自定义c语言函数的，不过用得比较少，但没准有些特殊的用户就用了。mogdb也是支持c语言函数的,我们可以通过c语言函数，极大的去扩展数据库的功能。
7.1 一个简单的例子
- 安装依赖库
yum -y install gcc gcc-c++ kernel-devel
- 写一个c语言函数源码文件
cd /opt
cat > testfunc.c
#include "postgres.h"
#include "fmgr.h"
PG_MODULE_MAGIC;
extern "C" Datum add_ab(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(add_ab);
Datum
add_ab(PG_FUNCTION_ARGS)
{
int32 arg_a = PG_GETARG_INT32(0);
int32 arg_b = PG_GETARG_INT32(1);
PG_RETURN_INT32(arg_a + arg_b);
}
按 ctrl+D 结束
- 编译刚刚创建的c文件
（注意mogdb软件的目录）
g++ -std=c11 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -fPIC -I. -I./ -I/opt/mogdb/app/include/postgresql/server -I/opt/mogdb/app/include/postgresql/internal -D_GNU_SOURCE -c -o add_func.o testfunc.c
g++ -std=c11 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -fPIC add_func.o -L/opt/mogdb/app/lib -Wl,-rpath,'/opt/mogdb/app/lib' -shared -o add_func.so
- 复制编译好的文件到指定目录
cp /opt/add_func.so /opt/mogdb/app/lib/postgresql/proc_srclib/
- 登录数据库，创建函数
CREATE FUNCTION add_ab(a int ,b int ) RETURNS integer
AS 'add_func.so', 'add_ab'
LANGUAGE C STRICT;
- 使用函数
select add_ab(1,2);
7.2 库内执行操作系统命令
有了上面这个例子，就有角度可以写个自定义函数，来让数据库执行操作系统命令了，下面再写个简单的例子
- 创建源码文件
cd /opt
cat > execcmd.cpp
#include "postgres.h"
#include "fmgr.h"
#include "utils/builtins.h"
PG_MODULE_MAGIC;
extern "C" Datum execcmd(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(execcmd);
Datum
execcmd(PG_FUNCTION_ARGS)
{
char *cmd = text_to_cstring(PG_GETARG_TEXT_P(0));
system(cmd);
PG_RETURN_INT32(1);
}
- 编译（这里用c++的原因是utils/builtins.h用了c++的东西）
g++ -std=c++0x -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -fPIC -I. -I./ -I/opt/mogdb/app/include/postgresql/server -I/opt/mogdb/app/include/postgresql/internal -D_GNU_SOURCE -c -o execcmd.o execcmd.cpp
g++ -std=c11 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -O2 -fPIC execcmd.o -L/opt/mogdb/app/lib -Wl,-rpath,'/opt/mogdb/app/lib' -shared -o execcmd.so
- 复制文件
cp /opt/execcmd.so /opt/mogdb/app/lib/postgresql/proc_srclib/
- 在数据库内创建函数
CREATE FUNCTION execcmd(a text ) RETURNS integer
AS 'execcmd.so', 'execcmd'
LANGUAGE C STRICT;
- 测试
select execcmd('echo 098 > /tmp/pp123.log');
执行后可以发现已经生成了"/tmp/pp123.log"这个文件了。
当然还有更多用法，比如获取操作系统执行命令后返回的结果，就不再细说了，会C的自然知道怎么写，不建议初学者使用，因为涉及到操作系统，风险还是很大的。
对于Oracle很多貌似与数据库本身无关的功能，如果一定要支持，而plpgsql语言无法实现的或实现起来很难的，有了c语言的扩展，就开辟了一条新的道路
总结
得益于openGauss/PG强大的可自定义能力及扩展能力，用户可以在MogDB上发挥自己的创造性，不仅仅局限于兼容oracle，只要想象力丰富，甚至可以写出一些超越oracle的功能。
参考资料
https://docs.mogdb.io/zh/mogdb/v3.0/overview
https://gitee.com/enmotech/compat-tools
http://postgres.cn/docs/13/
https://gitee.com/opengauss/Plugin/tree/master/contrib/orafce
