表为employees
字段为employee_id,class_id
CREATE OR REPLACE PROCEDURE get_emp ( e_ids OUT enum_t, c_ids OUT class_t, num_rows OUT int) IS
TYPE enum_t IS TABLE OF employees.employee_id%TYPE;
TYPE class_t IS TABLE OF employees.class_id%TYPE;
BEGIN
SELECT employee_id, class_id BULK COLLECT INTO e_ids,c_ids
FROM employees;
IF e_ids.COUNT = 0 THEN
num_rows := 0;
ELSE
num_rows := e_ids.LAST;
END get_emp
老是报错 哪位能帮我调一下啊
------解决方案--------------------
现在把对象enum_t定义在存储过程里面了，而存储过程的参数又用到这个enum_t对象
所以不行的吧
我是猜测。。。。
你写一个包，把对象定义到包头里，这样看看行不？
------解决方案--------------------
非得要写包吗?
CREATE OR REPLACE PROCEDURE get_emp ( e_ids OUT enum_t, c_ids OUT class_t, num_rows OUT int) IS
TYPE enum_t IS TABLE OF employees.employee_id%TYPE;
TYPE class_t IS TABLE OF employees.class_id%TYPE;
改成
CREATE OR REPLACE PROCEDURE get_emp ( e_ids OUT employees.employee_id%TYPE, c_ids OUT employees.class_id%TYPE, num_rows OUT int) IS
就行了
如果仅仅只是完成一个功能的话,写包没什么必要
------解决方案--------------------
create table employees(employee_id int,class_id int);
/
insert into employees values(1,1);
insert into employees values(2,2);
insert into employees values(3,3);
/
create or replace package aa
is
TYPE enum_t IS TABLE OF employees.employee_id%TYPE;
TYPE class_t IS TABLE OF employees.class_id%TYPE;
PROCEDURE get_emp ( e_ids OUT enum_t, c_ids OUT class_t, num_rows OUT int);
end;
/
create or replace package body aa
is
PROCEDURE get_emp( e_ids OUT enum_t, c_ids OUT class_t, num_rows OUT int)
is
begin
SELECT employee_id, class_id BULK COLLECT INTO e_ids,c_ids FROM employees;
for i in e_ids.first..e_ids.count loop
dbms_output.put_line(e_ids(i));
end loop;
num_rows:=e_ids.count;
end;
--测试过程
procedure test
is
a enum_t;
b class_t;
i int;
begin
get_emp(a,b,i);
end;
end;
/
--调用测试过程
declare
i int;
begin
aa.test;
end;
--测试结果
1
2
3