oracle pl/sql面向对象支持及lightdb兼容

虽说现在新系统pl/sql已经很少用了，但是在信创迁移过程中，仍然有很多oracle pl/sql过程、函数、包需要能够无缝的移植到信创数据库，比如在很多风控系统移植lightdb的过程中，就遇到了很多，甚至还有pro*c。所以才有了本文。

-- 最常用，用于集合处理
DECLARE
CURSOR allrows_cur IS SELECT * FROM emp;
TYPE employee_aat IS TABLE OF emp%ROWTYPE INDEX BY PLS_INTEGER;  -- 带index by，关联数组，Associative array
l_employees employee_aat;

TYPE employee_aat2 IS TABLE OF emp%ROWTYPE;   -- 不带index by，嵌套表
l_employees2 employee_aat2;

BEGIN
OPEN allrows_cur;
LOOP
FETCH allrows_cur BULK COLLECT INTO l_employees LIMIT 100;
/* Process the data by scanning through the collection. */
FOR l_row IN 1 .. l_employees.COUNT
LOOP
  dbms_output.put_line (l_employees(l_row).ename || '-' || l_employees(l_row).EMPNO);
END LOOP;
EXIT WHEN allrows_cur%NOTFOUND;
END LOOP;
CLOSE allrows_cur;

SELECT * BULK COLLECT INTO l_employees2 FROM emp;
FOR l_row IN 1 .. l_employees2.COUNT
LOOP
  dbms_output.put_line ('l_employees2==' || l_employees2(l_row).ename || '-' || l_employees2(l_row).EMPNO);
END LOOP;
END;

SMITH-7369
ALLEN-7499
WARD-7521
JONES-7566
MARTIN-7654
BLAKE-7698
CLARK-7782
SCOTT-7788
KING-7839
TURNER-7844
ADAMS-7876
JAMES-7900
FORD-7902
MILLER-7934
l_employees2==SMITH-7369
l_employees2==ALLEN-7499
l_employees2==WARD-7521
l_employees2==JONES-7566
l_employees2==MARTIN-7654
l_employees2==BLAKE-7698
l_employees2==CLARK-7782
l_employees2==SCOTT-7788
l_employees2==KING-7839
l_employees2==TURNER-7844
l_employees2==ADAMS-7876
l_employees2==JAMES-7900
l_employees2==FORD-7902
l_employees2==MILLER-7934

注：我不喜欢使用可变长度数组，所以本文不包含它。

Table 5-1 PL/SQL Collection Types

Collection Type	Number of Elements	Index Type	Dense or Sparse	Uninitialized Status	Where Defined	Can Be ADT Attribute Data Type
Associative array (or index-by table)	Unspecified	String or PLS_INTEGER	Either	Empty	In PL/SQL block or package	No
VARRAY (variable-size array)	Specified	Integer	Always dense	Null	In PL/SQL block or package or at schema level	Only if defined at schema level
Nested table	Unspecified	Integer	Starts dense, can become sparse	Null	In PL/SQL block or package or at schema level	Only if defined at schema level

Number of Elements

If the number of elements is specified, it is the maximum number of elements in the collection. If the number of elements is unspecified, the maximum number of elements in the collection is the upper limit of the index type.

Dense or Sparse

A dense collection has no gaps between elements—every element between the first and last element is defined and has a value (the value can be NULL unless the element has a NOT NULL constraint). A sparse collection has gaps between elements.

Uninitialized Status

An empty collection exists but has no elements. To add elements to an empty collection, invoke the EXTEND method (described in "EXTEND Collection Method").

A null collection (also called an atomically null collection) does not exist. To change a null collection to an existing collection, you must initialize it, either by making it empty or by assigning a non-NULL value to it (for details, see "Collection Constructors" and "Assigning Values to Collection Variables"). You cannot use the EXTEND method to initialize a null collection.

自定义记录类型

CREATE TABLE Book_table (
Booknum NUMBER,
Section VARCHAR2(20),
Title VARCHAR2(20),
Author VARCHAR2(20),
Available CHAR(1)
);
INSERT INTO Book_table (
Booknum, Section, Title, Author, Available
)
VALUES (
121001, 'Classic', 'Iliad', 'Homer', 'Y'
);
INSERT INTO Book_table (
Booknum, Section, Title, Author, Available
)
VALUES (
121002, 'Novel', 'Gone with the Wind', 'Mitchell M', 'N'
);
SELECT * FROM Book_table ORDER BY Booknum;

DECLARE
TYPE Book_list_record IS RECORD (
Booknum NUMBER,
Section VARCHAR2(20),
Title VARCHAR2(20),
Author VARCHAR2(20),
Available CHAR(1)
);
TYPE Book_list_t1 IS TABLE OF Book_list_record;
v_Book_list Book_list_t1;  -- 嵌套表
BEGIN
SELECT Booknum, Section, Title, Author, Available BULK COLLECT INTO v_Book_list FROM Book_table;
FOR l_row IN 1 .. v_Book_list.COUNT
LOOP
  dbms_output.put_line ('v_Book_list==' || v_Book_list(l_row).Booknum || '-' || v_Book_list(l_row).Title);
END LOOP;
END;

v_Book_list==121001-Iliad
v_Book_list==121002-Gone with the Wind

　　记录类型和对象类型的差异，需要特别注意，对象需要通过构造器设置值，记录类型和ROWTYPE类型可以赋值。直接将SELECT Booknum, Section, Title, Author, Available BULK COLLECT INTO v_Book_list赋值给对象嵌套表，将报值不足。

预定义基于对象的嵌套表类型

CREATE OR REPLACE TYPE Book_t1 AS OBJECT (
Booknum NUMBER,
Section VARCHAR2(20),
Title VARCHAR2(20),
Author VARCHAR2(20),
Available CHAR(1)
);

CREATE OR REPLACE TYPE Book_list_t AS TABLE OF Book_t1;

DECLARE
v_Book_list Book_list_t;  -- 嵌套表
BEGIN
SELECT Book_t1(Booknum, Section, Title, Author, Available)/* 对象类型特殊的地方 */ BULK COLLECT INTO v_Book_list FROM Book_table;
FOR l_row IN 1 .. v_Book_list.COUNT
LOOP
  dbms_output.put_line ('v_Book_list==' || v_Book_list(l_row).Booknum || '-' || v_Book_list(l_row).Title);
END LOOP;
END;

v_Book_list==121001-Iliad
v_Book_list==121002-Gone with the Wind

is table of 和 as table of

在创建类型的时候是一样的，都可以生效。

CREATE OR REPLACE TYPE Book_list_t IS TABLE OF Book_t1;

和

CREATE OR REPLACE TYPE Book_list_t AS TABLE OF Book_t1;

都能生效。

但是在过程内定义嵌套表的时候，只能用IS TABLE OF，不能用AS TABLE OF，会报错。

DECLARE
TYPE employee_aat2 AS TABLE OF emp%ROWTYPE;   -- 不带index by，嵌套表
l_employees2 employee_aat2;

BEGIN

SELECT * BULK COLLECT INTO l_employees2 FROM emp;
FOR l_row IN 1 .. l_employees2.COUNT
LOOP
  dbms_output.put_line ('l_employees2==' || l_employees2(l_row).ename || '-' || l_employees2(l_row).EMPNO);
END LOOP;
END;

SQL 错误 [6550] [65000]: ORA-06550: 第 2 行, 第 23 列: 
PLS-00103: 出现符号 "TABLE"在需要下列之一时：
 object
   opaque

object和record的区别

https://sqlpey.com/question/oracle-object-vs-record-type-whats-difference

https://forums.oracle.com/ords/apexds/post/object-type-vs-record-type-2620

对象嵌套表的构造与使用

CREATE TYPE address_typ AS OBJECT (
 street VARCHAR2(30),
 city VARCHAR2(20),
 state CHAR(2),
 postal_code VARCHAR2(6) );

CREATE OR REPLACE TYPE employee_typ AS OBJECT (
 employee_id NUMBER(6),
 first_name VARCHAR2(20),
 last_name VARCHAR2(25),
 email VARCHAR2(25),
 phone_number VARCHAR2(20),
 hire_date DATE,
 job_id VARCHAR2(10),
 salary NUMBER(8,2),
 commission_pct NUMBER(2,2),
 manager_id NUMBER(6),
 department_id NUMBER(4),
 address address_typ,
 MAP MEMBER FUNCTION get_idno RETURN NUMBER,
 MEMBER PROCEDURE display_address ( SELF IN OUT NOCOPY employee_typ ) );
 

CREATE OR REPLACE TYPE BODY employee_typ AS
 MAP MEMBER FUNCTION get_idno RETURN NUMBER IS
 BEGIN
 RETURN employee_id;
 END;
 MEMBER PROCEDURE display_address ( SELF IN OUT NOCOPY employee_typ ) IS
 BEGIN
 DBMS_OUTPUT.PUT_LINE(first_name || ' ' || last_name);
 DBMS_OUTPUT.PUT_LINE(address.street);
 DBMS_OUTPUT.PUT_LINE(address.city || ', ' || address.state || ' ' ||
 address.postal_code);
 END;
END;


DECLARE
 TYPE emps IS TABLE OF employee_typ;
 emp_list emps := emps(); -- emp is atomically null
BEGIN
 emp_list.extend(3);
-- call the constructor for employee_typ
 emp_list(1) := employee_typ(315, 'Francis', 'Logan', 'FLOGAN',
 '415.555.0100', sysdate,'SA_MAN', 11000, 0.15, 101, 110,
 address_typ('376 Mission', 'San Francisco', 'CA', '94222'));
 
 emp_list(2) := employee_typ(215, '2Franis', '2Loan', '2FLGAN',
 '245.555.0100', sysdate,'SA_MAN', 11000, 0.15, 101, 110,
 address_typ('237 Mission', '2Sn Francisco', '2C', '94222'));
 
 FOR i IN 1..emp_list.count loop
   DBMS_OUTPUT.PUT_LINE(emp_list(i).first_name || ' from dbms_output ' || emp_list(i).last_name); -- display details
   emp_list(i).display_address(); -- call object method to display details
 END LOOP;
END;
-- 输出
Francis from dbms_output Logan
Francis Logan
376 Mission
San Francisco, CA 94222
2Franis from dbms_output 2Loan
2Franis 2Loan
237 Mission
2Sn Francisco, 2C 94222
 from dbms_output 

对象类型不仅可以包含成员，也可以包含成员函数、构造器(只能通过java或c编写，不支持PL/SQL定义)、静态成员函数等，典型的面向对象。

集合类型上的默认支持函数。

表函数支持

嵌套表和关联数组都支持作为表函数的参数。

CREATE OR REPLACE PACKAGE pkg AS
TYPE rec IS RECORD(f1 NUMBER, f2 VARCHAR2(30));
-- TYPE mytab IS TABLE OF rec INDEX BY pls_integer; -- 默认空集合
TYPE mytab IS TABLE OF rec;  -- 默认未初始化，需要显示初始化
END;

DECLARE
v1 pkg.mytab; -- collection of records
v2 pkg.rec;
c1 SYS_REFCURSOR;
BEGIN
    v1 := pkg.mytab();  -- 嵌套表需要
    v1.extend(2);     -- 嵌套表需要
v1(1).f1 := 1;
v1(1).f2 := 'one';
OPEN c1 FOR SELECT * FROM TABLE(v1);
FETCH c1 INTO v2;
CLOSE c1;
DBMS_OUTPUT.PUT_LINE('Values in record are ' || v2.f1 || ' and ' || v2.f2);
END;

 

自定义对象类型及嵌套表在Oracle及EDB中的实现

　　自定义类型无论是嵌套表还是类型如create type as of object, create type is/as table of; 在edb中，基于对象的自定义类型是通过模拟schema来实现的，和package一样，即type_name就是schema_name，成员函数这些存储在其对应的schema下，这样也不会污染全局pg_proc。而自定义类型又可以作为package的成员之一，这就会比较复杂。package.type.method或pakcage.type.member和schema.table.attribute看起来冲突，type.member和table.attribute也冲突，这就有个serach_path优先级的问题。在PG中，默认一般先搜索current_schema、其次public、然后才是其他schema，当然也可以配置，但一般不会修改。

SELECT * FROM user_types;

BOOK_LIST_T R¡P<1àcÇ ï COLLECTION
BOOK_T1 R¡@<1àcÇ ï OBJECT
EMPLOYEE_TYP R¡u<1àcÇ ï OBJECT
ADDRESS_TYP R¡Z<1àcÇ ï OBJECT

SELECT * FROM USER_TYPE_ATTRS;  -- 存储属性
SELECT * FROM USER_TYPE_METHODS;  -- 存储成员函数

SELECT * FROM DBA_PROCEDURES WHERE object_name='PKG';  -- 尚未找到类型存储的位置

OWNER |OBJECT_NAME|PROCEDURE_NAME|OBJECT_ID|SUBPROGRAM_ID
-----------+-----------+--------------+---------+-------------
SYSTEM |PKG | | 183968| 0
　　这意味着，如果有相同的表名和类型名，就不知道找谁。所以，一种实现就是不允许类型名和表名相同以保证唯一性，lightdb 23.2及之前是允许的，通过search_path搜索路径顺序保证，23.3开始不再允许重名，和edb规则一样。

　　在oracle中是4级存储，schema.package.type.member。

lightdb兼容情况

　　截止23.2，lightdb支持标量类型的关联数组和嵌套表，不支持可变数组，不支持create table as/is object，不支持关联数组作为过程/函数参数类型/table()有一些限制。package级别不支持类型定义，匿名块不能使用package级别的类型定义。

　　23.3开始，上述特性基本上已经支持。参见lightdb oracle package兼容中的示例，都可无需修改支持。