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;