oracle 包package的使用

drop table emp;

create table emp
(
    empno nvarchar2(100),
    ename nvarchar2(200)
)

insert into emp(empno,ename)values('111','aaa');
insert into emp(empno,ename)values('222','bbb');
insert into emp(empno,ename)values('333','ccc');

commit;

delete from emp;

commit;

--创建包说明
CREATE OR REPLACE PACKAGE EMP_PKG 
IS
  TYPE emp_table_type IS TABLE OF emp%ROWTYPE  INDEX BY BINARY_INTEGER;
  
  PROCEDURE read_emp_table (p_emp_table OUT emp_table_type);
END EMP_PKG;

--创建包体
CREATE OR REPLACE PACKAGE BODY EMP_PKG 
IS
PROCEDURE read_emp_table 
(p_emp_table OUT emp_table_type) 
IS
I BINARY_INTEGER := 0;
BEGIN
   FOR emp_record IN ( SELECT * FROM emp ) LOOP
      P_emp_table(i) := emp_record;
      I := I + 1;
    END LOOP;
  END read_emp_table;
END EMP_PKG;

--执行
DECLARE 
  E_table EMP_PKG.emp_table_type;
BEGIN
  EMP_PKG.read_emp_table(e_table);

if e_table.FIRST >= 0 then
     FOR rd IN e_table.FIRST ..e_table.LAST 
   LOOP
    DBMS_OUTPUT.PUT_LINE(e_table(rd).ename);
   END LOOP;
else 
     DBMS_OUTPUT.PUT_LINE('变量结果集为空!');
end if;
END;