如何从包调用函数

Question

如何通过创建在包中使用的相同集合类型的自己的集合来调用函数?可以使用自己创建的集合从包调用函数吗?调用函数时出现错误

create or replace package pk
is
type e_list is table of emp%rowtype index by pls_integer;
function pro1 return e_list;
end;

create or replace package body pk
is
x number;
function pro1 return e_list
is
v_emp e_list;
begin
for x in 100..110 loop
select * into v_emp(x) from employees where employee_id=x;
end loop;
return v_emp;
end;
end;

调用函数检查下面的代码plzz.

declare
type p_list is table of emp%rowtype index by pls_integer;
r_cur p_list;
x number;
begin
r_cur:=pk.pro1;
x:=r_cur.first;
for i in 1..10 loop
dbms_output.put_line(r_cur(x).salary);
x:=r_cur.next(x);
end loop;
end;

Answer 1

如果要在包外使用该类型，请在SQL级别创建它。像这样:

样本数据:

SQL> SELECT * FROM employees;
EMPLOYEE_ID ENAME          SALARY
----------- ---------- ----------
100 SMITH             800
101 ALLEN            1600
102 WARD             1250
103 JONES            2975

类型:

SQL> CREATE OR REPLACE TYPE e_list_t IS OBJECT
2  (
3     employee_id NUMBER,
4     ename VARCHAR2 (20),
5     salary NUMBER
6  );
7  /
Type created.
SQL> CREATE OR REPLACE TYPE e_list IS TABLE OF e_list_t;
2  /
Type created.

包规范及其正文:

SQL> CREATE OR REPLACE PACKAGE pk
2  IS
3     FUNCTION pro1
4        RETURN e_list;
5  END;
6  /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY pk
2  IS
3     FUNCTION pro1
4        RETURN e_list
5     IS
6        v_emp  e_list := e_list ();
7     BEGIN
8        SELECT e_list_t (employee_id, ename, salary)
9          BULK COLLECT INTO v_emp
10          FROM employees;
11
12        RETURN v_emp;
13     END;
14  END;
15  /
Package body created.

测试:

SQL> DECLARE
2     r_cur  e_list;
3  BEGIN
4     r_cur := pk.pro1;
5
6     FOR x IN 1 .. r_cur.LAST
7     LOOP
8        DBMS_OUTPUT.put_line (r_cur (x).ename || ': ' || r_cur (x).salary);
9     END LOOP;
10  END;
11  /
SMITH: 800
ALLEN: 1600
WARD: 1250
JONES: 2975
PL/SQL procedure successfully completed.
SQL>