ORACLE 存储过程 函数 返回程序集合

--定义一个返回程序集的引用游标 

CREATE OR REPLACE PACKAGE BAF_QUERY_TABLE AS

  TYPE P_CURSOR IS ref CURSOR;

END BAF_QUERY_TABLE;

--创建存储过程，并返回游标的形式返回程序集 

create or replace procedure getList(p_taskID number, p_out_cursor out BAF_QUERY_TABLE.P_CURSOR) is

begin

  if p_taskID is null then 

      open p_out_cursor for select * from idm_task;

  else

      open p_out_cursor for select * from idm_task where task_id=p_taskID;

  end if;

end getList;

 

 

 

create or replace function sp_ListEmp return types.cursortype
as
    l_cursor    types.cursorType;
begin
    open l_cursor for select ename, empno from emp order by ename;
    return l_cursor;
end;

 

 

FUNCTION GETUSERNAME(P_USER_ID IN NUMBER) RETURN VARCHAR2 IS
    L_COUNT     NUMBER := 0;
    L_USER_NAME VARCHAR2(500) := '';
  BEGIN

    SELECT COUNT(*) INTO L_COUNT FROM SYS_USER WHERE USER_ID = P_USER_ID;
    IF L_COUNT = 1 THEN

      SELECT USER_NAME || '(' || USER_ACCOUNT || ')'
        INTO L_USER_NAME
        FROM SYS_USER
       WHERE USER_ID = P_USER_ID;

    END IF;

    RETURN l_user_name;

  END GetUserName;