lightdb oracle package兼容
lightdb对oracle package的兼容性还是不错的，大部分都已经支持。如下：
-- 创建oracle模式数据库 zjh@postgres=# create database ora_db3 lightdb_syntax_compatible_typE=oracle; NOTICE: auto create user "ora_db3" success CREATE DATABASE \c ora_db3 --删除表 drop table appl_application_sbfp; drop table sys_user_info; --创建表和主键 CREATE TABLE appl_application_sbfp ( application_no varchar(32) NOT NULL , customer_id varchar(32) NOT NULL, customer_name varchar(32) NOT NULL, input_user_id varchar(32) NOT NULL, PRIMARY KEY (application_no) ); CREATE TABLE sys_user_info ( user_id varchar(32) NOT NULL , user_name varchar(32) NOT NULL, pass_word varchar(32),--default null org_id varchar(32), PRIMARY KEY (user_id) ); insert into appl_application_sbfp(application_no,customer_id,customer_name,input_user_id) values('APPL20190603','c20190603','王平','WANGLI'); insert into appl_application_sbfp(application_no,customer_id,customer_name,input_user_id) values('APPL20190604','c20190604','张平','WANGLI'); insert into sys_user_info(user_id,user_name) values('WANGLI','王丽'); -- 创建包、包体、存储函数 drop package body PA_GLOBAL; drop package PA_GLOBAL; create or replace package PA_GLOBAL is FUNCTION getUserName(user_id IN varchar2) RETURN varchar2; end PA_GLOBAL; / create or replace package body PA_GLOBAL is FUNCTION getUserName(user_id IN varchar2) RETURN varchar2 is userName varchar2(200); BEGIN SELECT user_name into userName from sys_user_info; return userName; END getUserName; BEGIN dbms_output.put_line('Control is now executing the package initialization part'); -- package初始化块必须在最后 end PA_GLOBAL; /
begin perform PA_GLOBAL.getUserName('abc'); end; /
package级别变量--存储在lt_variable中
CREATE OR REPLACE PACKAGE my_globals IS g_fav_language VARCHAR2 (6) := 'PL/SQL'; PROCEDURE show_fav_languages; END; / CREATE OR REPLACE PACKAGE BODY my_globals IS PROCEDURE show_fav_languages IS l_fav_language VARCHAR2 (6) := 'SQL'; BEGIN DBMS_OUTPUT.put_line ('* Local favorite language ' || l_fav_language); DBMS_OUTPUT.put_line ( '* Global favorite language ' || my_globals.g_fav_language); END; END; /
BEGIN DBMS_OUTPUT.PUT_LINE ('Initial values'); my_globals.show_fav_languages; DBMS_OUTPUT.PUT_LINE ('Global value changed'); my_globals.g_fav_language := 'SQL'; my_globals.show_fav_languages; END; / * Local favorite language SQL * Global favorite language PL/SQL * Local favorite language SQL * Global favorite language SQL DO zjh@ora_db=# BEGIN my_globals.show_fav_languages; my_globals.g_fav_language := 'SQL'; my_globals.show_fav_languages; END; / * Local favorite language SQL * Global favorite language SQL * Local favorite language SQL * Global favorite language SQL DO
包级全局变量是静态变量，不是实例变量。
package级别游标和%rowtype
CREATE TABLE test(x INT, y VARCHAR2(100)); create table rectype(a int,b varchar2(100)); INSERT INTO test VALUES (1, 'One'); INSERT INTO test VALUES (2, 'Two'); INSERT INTO test VALUES (3, 'Three'); CREATE OR REPLACE PACKAGE example AS -- Declare type, cursor: CURSOR curtype RETURN rectype%rowtype; rec rectype%rowtype; -- Declare subprograms: FUNCTION somefunc (v int) RETURN NUMBER; -- Overload preceding subprogram: PROCEDURE xfunc (emp_id NUMBER); PROCEDURE xfunc (emp_email VARCHAR2); END; / CREATE OR REPLACE PACKAGE BODY example AS nelems NUMBER; -- variable in this package -- Define cursor declared in package specification: CURSOR curtype RETURN rectype%rowtype IS SELECT x, y FROM test ORDER BY x; -- Define subprograms declared in package specification: FUNCTION somefunc (v int) RETURN NUMBER IS id NUMBER := 0; BEGIN OPEN curtype; FETCH curtype INTO rec; close curtype; RETURN rec.a; END; PROCEDURE xfunc (emp_id NUMBER) IS BEGIN NULL; END; PROCEDURE xfunc (emp_email VARCHAR2) IS BEGIN NULL; END; END; / select example.somefunc(1); somefunc ---------- 1 (1 row)
package级别自定义类型--23.3开始支持出参为自定义(内置、type/object或record)嵌套表（关联数组23.4支持）
zjh@ora_db2=# zjh@ora_db2=# CREATE OR REPLACE PACKAGE EMP_PKG zjh@ora_db2-# IS zjh@ora_db2$# TYPE emp_table_type IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER; -- binary_integer为lightdb不支持类型 zjh@ora_db2$# zjh@ora_db2$# PROCEDURE read_emp_table (p_emp_table OUT emp_table_type); zjh@ora_db2$# END EMP_PKG; zjh@ora_db2$# / ERROR: unsupported table index type zjh@ora_db2=# CREATE OR REPLACE PACKAGE EMP_PKG IS TYPE emp_table_type IS TABLE OF emp%ROWTYPE INDEX BY INTEGER; PROCEDURE read_emp_table (p_emp_table OUT emp_table_type); END EMP_PKG; / ERROR: SQL function cannot accept associative arrays type "emp_table_type" -- 23.2之前的版本不支持关联数组作为参数
package级别自定义类型--23.3开始支持create type xxx is/as object及其上的嵌套表
1.返回类型为嵌套表。 create /* or REPLACE 23.4支持 */ type dept_obj is OBJECT( DEPTNO NUMBER(2,0), DNAME VARCHAR2(14 BYTE) ); -- is object从23.3开始支持 create /* or REPLACE 23.4支持 */ type dept_obj_type AS table of dept_obj; 2.定义package 和package body。 create or replace package SPTest is /*return a pipelined demo start*/ type dept_data_rec_type is RECORD( DEPTNO NUMBER(2,0), DNAME VARCHAR2(14) );
-- ---------------- oracle不支持package内定义object，否则会报PLS-00540: object not supported in this context，下面是示例
-- type dept_data_rec_obj is OBJECT(
-- DEPTNO NUMBER(2,0),
-- DNAME VARCHAR2(14)
-- );
type dept_ref_type is REF CURSOR; function getDept(in_loc IN VARCHAR2) return dept_obj_type /* pipelined 23.4支持 */; /*return a pipelined demo end*/ /*return a cursor demo start*/ FUNCTION getDeptInfo(in_deptno IN int) RETURN dept_ref_type; /*return a cursor demo end*/ /* return a varchar value start */ function getName(in_deptno in number) RETURN VARCHAR2; /* return a varchar value end */ end SPTest; / ----------------------------------------------------------------------------------------------- create or replace package body SPTest is /*return a pipelined demo start*/ function getDept(in_loc IN VARCHAR2) return dept_obj_type /* pipelined 23.4支持 */ is l_dept_obj dept_obj :=dept_obj(null, null); dept_ref_type_cursor dept_ref_type; dept_data_rec dept_data_rec_type; begin open dept_ref_type_cursor for select deptno, dname from dept where loc = in_loc; loop fetch dept_ref_type_cursor into dept_data_rec; exit when dept_ref_type_cursor%NOTFOUND; l_dept_obj.DEPTNO := dept_data_rec.DEPTNO; l_dept_obj.DNAME := dept_data_rec.DNAME; pipe row(l_dept_obj); -- lightdb 23.3不支持，对应return next实现。24c支持 end loop; close dept_ref_type_cursor; RETURN ; end getDept; /*return a pipelined demo end*/ /*return a cursor demo start*/ FUNCTION getDeptInfo(in_deptno IN int) RETURN dept_ref_type AS dept_ref_type_cursor dept_ref_type; BEGIN OPEN dept_ref_type_cursor FOR SELECT deptno, dname, loc FROM dept where deptno = in_deptno; RETURN dept_ref_type_cursor; END getDeptInfo; /*return a cursor demo end*/ /* return a varchar value start */ function getName(in_deptno in number) RETURN VARCHAR2 as rtn_deptname VARCHAR2(100); begin select dname into rtn_deptname from dept where deptno = in_deptno; RETURN rtn_deptname; end getName; /* return a varchar value start */ end SPTest; / 最后，执行存储过程。 /*返回pipelined table 管道嵌套表 */ select deptno, dname from table(SPTest.getDept('NEW YORK')) order by deptno; /*返回cursor*/ select SPTest.getDeptInfo(10) from dual; /*返回具体值*/ select SPTest.getName(50) from dual;
http://www.dba-oracle.com/plsql/t_plsql_global_data.htm
http://www.light-pg.com/docs/lightdb/13.8-23.2/plorasql-packages.html lightdb还支持包级游标、类型。
