注意，本文內容很豐富，建議收藏后慢慢閱讀。
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
