征文连载丨在不修改数据库源码的情况下，如何提高 MogDB 对 Oracle 的兼容性？...

云和恩墨

于 2022-12-06 17:48:55 发布

阅读量227

点赞数

文章标签：数据库 oracle postgresql

本文链接：https://blog.csdn.net/weixin_54551388/article/details/128213393

版权

：

2022年8月4日至9月9日，墨天轮社区联合云和恩墨发起了首届「MogDB 主题征文活动」，邀请各位技术从业者学习、使用 MogDB 数据库，分享使用心得与实战案例，一起探索这款融合了众多创新特性的商业版企业级数据库。活动期间共有93篇稿件通过评审，主题涵盖数据库安装部署、主从搭建、工具测试、源码解析、新特性解读，以及备份恢复、迁移实践等，并由专家评选出一等奖1名、二等奖3名、三等奖5名。本篇是三等奖获得者王勇昱(DarkAthena)的文章。

当前信创环境下，由于国家相关政策，很多企业被要求必须弃用 Oracle。很多企业本身并不具备数据库内核研发的能力。而使用国产数据库或者开源数据库，对 Oracle 数据库的兼容性始终是个避不开的问题。

那么，除了等待数据库厂商提供增强 Oracle 兼容能力的数据库版本，使用数据库的企业自身还能通过何种方式来提高开源或国产数据库对 Oracle 的兼容性？

openGauss 基于PostgreSQL 9.2，而PG的底层框架设计，很多地方是和 Oracle 类似的，而且 openGauss 的SQL及PLpgSQL语法，能高度兼容 Oracle，所以本文以 openGauss 的商业发行版之一“MogDB” 为例，来说一些增强对 Oracle 兼容性的手段。

注意，本文 MogDB 版本为 3.0.1，其他版本可能会有部分区别，暂不区分讨论。

另外，兼容性良好的表现，至少分两层，一是不需要改写代码即可支持，二是可以通过改写代码来支持。

01.

已有的插件或兼容工具包

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 extenion whale;
create extenion 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时，就相当舒适了。

02.

如何兼容内置表或视图

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管理，还涉及到权限，建议根据实际情况对视图进行符合业务代码规则的修改。

03.

如何兼容数据类型

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 这些隐藏类型进行对比。

3.2

PL/SQL 数据类型

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

从 MogDB 3.0.0开始，支持创建table of的type，比如：

create type test_type is table of number;

如果是varrayof的type，目前暂不支持创建，但是也可以改写成tableof，因为这两种类型在 MogDB 的PL/SQL中，使用方法基本是一样的。另外，还可以用创建domain的方式，比如：

create domain test_type is number[];

04.

如何兼容内置函数/过程

4.1

单行函数

4.1.1 不带关键字的函数

这种很简单，只要知道 Oracle 里是怎么算的，就能模拟出来，可参考compat-tools，这里举几个例子：

例一：cosh （数学函数）

公式为：

cosh\ x=\frac{e^x+e^{-x}}{2}cosh x=2ex+e−x

则创建的自定义函数为

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标准里的分析函数，从 MogDB 3.0.0开始已经支持了。

另外，可以用下面这个SQL，查出所有支持分析函数语法的函数(所有聚合函数均可over)

select distinct proname from pg_proc p where  p.proiswindow or p.proisagg

05.

如何兼容内置包

我把内置包分这么几类：

数据库管理（比如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)

06.

如何做SQL或PL/SQL兼容

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

PL/SQL

MogDB 2.1起，已经开始支持package功能，从3.0.0版本又大大的增强了对 Oracle 各种PL/SQL语法的兼容性，但是的确还有部分代码不能直接移植，这里用表函数为例，来说明其是可以通过改写来支持的。

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”对应的是“ return next；”；
“RETURN TickerTypeSet（游标类型） PIPELINED”对应的是“RETURNssetofTickerType（单行复合类型）”。

需要注意的两点:

MogDB 3.0.1游标只能在PLpgSQL块中使用，不能在SQL语句中使用；
MogDB 3.0.1不支持定义“TYPE refcur_t IS REF CURSOR RETURN StockTable%ROWTYPE;”。

07.

库内执行自定义C语言函数

Oracle 其实也是支持自定义c语言函数的，不过用得比较少，但没准有些特殊的用户就用了。MogDB 也是支持c语言函数的，我们可以通过c语言函数，极大的去扩展数据库的功能。

7.1

一个简单的例子

1. 安装依赖库

yum -y install gcc gcc-c++ kernel-devel

2. 写一个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 结束。

3. 编译刚刚创建的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

4. 复制编译好的文件到指定目录

cp /opt/add_func.so /opt/mogdb/app/lib/postgresql/proc_srclib/

5. 登录数据库，创建函数

CREATE FUNCTION add_ab(a int ,b int ) RETURNS integer
AS 'add_func.so', 'add_ab'
LANGUAGE C STRICT;

6. 使用函数

select add_ab(1,2);

7.2

库内执行操作系统命令

有了上面这个例子，就有角度可以写个自定义函数，来让数据库执行操作系统命令了，下面再写个简单的例子：

1. 创建源码文件

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);
}

2. 编译（这里用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

3. 复制文件

cp /opt/execcmd.so /opt/mogdb/app/lib/postgresql/proc_srclib/

4. 在数据库内创建函数

CREATE FUNCTION execcmd(a text ) RETURNS integer
AS 'execcmd.so', 'execcmd'
LANGUAGE C STRICT;

5. 测试

select execcmd('echo 098 > /tmp/pp123.log');

执行后可以发现已经生成了"/tmp/pp123.log"这个文件了。

当然还有更多用法，比如获取操作系统执行命令后返回的结果，就不再细说了，会C的自然知道怎么写，不建议初学者使用，因为涉及到操作系统，风险还是很大的。

对于 Oracle 很多貌似与数据库本身无关的功能，如果一定要支持，而PLpgSQL语言无法实现的或实现起来很难的，有了c语言的扩展，就开辟了一条新的道路。

08.

总结

得益于 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

往期回顾

数据驱动，成就未来，云和恩墨，不负所托！

云和恩墨创立于2011年，以“数据驱动，成就未来”为使命，是智能的数据技术提供商。我们致力于将数据技术带给每个行业、每个组织、每个人，构建数据驱动的智能未来。

云和恩墨在数据承载（分布式存储、数据持续保护）、管理（数据库软件、数据库云管平台、数据技术服务）、加工（应用开发质量管控、数据模型管控、数字化转型咨询）和应用（数据服务化管理平台、数据智能、隐私计算数据联邦平台）等领域为各个组织提供可信赖的产品、服务和解决方案，围绕用户需求，持续为客户创造价值，激发数据潜能，为成就未来敏捷高效的数字世界而不懈努力。

云和恩墨

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
征文连载丨在不修改数据库源码的情况下，如何提高 MogDB 对 Oracle 的兼容性？...

：2022年8月4日至9月9日，墨天轮社区联合云和恩墨发起了首届「MogDB 主题征文活动」，邀请各位技术从业者学习、使用 MogDB 数据库，分享使用心得与实战案例，一起探索这款融合了众多创新特性的商业版企业级数据库。活动期间共有93篇稿件通过评审，主题涵盖数据库安装部署、主从搭建、工具测试、源码解析、新特性解读，以及备份恢复、迁移实践等，并由专家评选出一等奖1名、二等奖3名、三等奖5名。本篇...
复制链接

扫一扫