PostgreSQL Oracle 相容性 之 - PL/SQL record, table類型定義

oracle pl/sql是非常強大的一門sql程式設計語言，許多oracle使用者也使用它來處理一些要求延遲低且資料一緻性或可靠性要求很高的業務邏輯。

postgresql也有一門非常進階的内置sql程式設計語言，plpgsql。與oracle pl/sql文法極其類似，但是還是有一些不一樣的地方。

(ps：除了plpgsql，postgresql還支援c，java，python，perl等流行的語言作為資料庫的函數程式設計語言)

本文是針對有oracle使用者遇到的一些函數文法與postgresql不相容的地方，給出的修改建議。

涉及type xx is table of xxxx index by binary_integer文法、type xx is record文法。

在這個例子中，用到了oracle在pl/sql中支援的type定義，以及type table 的定義，這個在postgresql中用法不太一樣。

postgresql的type定義需要在資料庫中定義，而不是函數中定義。

以上pl/sql函數在plpgsql中需要作出如下調整：

.1.

修改為

函數外執行建立類型的sql

.2.

函數内對table的使用修改為數組的使用，數組的下标從1開始。

.3.

plpgsql目前不能直接修改複合數組對應的composite.element

需要修改為

或者請參考如下例子

nvl函數參考postgresql oracle相容包orafce。

.4.

array用法簡介

<a href="http://blog.163.com/digoal@126/blog/static/163877040201201275922529/">http://blog.163.com/digoal@126/blog/static/163877040201201275922529/</a>

<a href="https://www.postgresql.org/docs/9.5/static/arrays.html">https://www.postgresql.org/docs/9.5/static/arrays.html</a>

<a href="https://www.postgresql.org/docs/9.5/static/plpgsql-control-structures.html#plpgsql-foreach-array">https://www.postgresql.org/docs/9.5/static/plpgsql-control-structures.html#plpgsql-foreach-array</a>

循環

行列轉換

<a href="https://www.postgresql.org/docs/9.5/static/functions-array.html">https://www.postgresql.org/docs/9.5/static/functions-array.html</a>

使用composite type替代了pl/sql的type定義。

使用array替代了pl/sql的table定義。

複合類型的數組，不能直接修改複合類型的element，需要先用标量修改好後指派。

新增 create type [ if not exists ] 文法。這樣的話使用者就不需要将這個文法寫在函數外了，可以在函數内直接執行。

pl/sql的type是局部變量，而postgresql的type是全局的，這個也需要注意，如果多個pl/sql函數用到了同樣的type name但是結構不一樣，port到plpgsql時，需要建立多個type，在plpgsql中分别使用對應的type name。

plpgsql 暫時不支援composite數組直接設定element的值，需要加強plpgsql的文法功能。