oracle pl/sql与lightdb plorasql面向对象支持

虽说现在新系统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;

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

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

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

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