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的语法功能。