-- 创建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;
/

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

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之前的版本不支持关联数组作为参数

1.返回类型为pipelined table。　　

create or REPLACE type dept_obj is OBJECT( DEPTNO NUMBER(2,0), DNAME VARCHAR2(14 BYTE) ); -- is object从23.3开始支持

create or REPLACE 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)
);

type dept_ref_type is REF CURSOR;

function getDept(in_loc IN VARCHAR2) return dept_obj_type pipelined;
/*return a pipelined demo end*/

/*return a cursor demo start*/
FUNCTION getDeptInfo(in_deptno IN dept.deptno%TYPE) 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 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);
    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 dept.deptno%TYPE) 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;