表为employees
字段为employee_id,class_idCREATE 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老是报错 哪位能帮我调一下啊
字段为employee_id,class_idCREATE 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老是报错 哪位能帮我调一下啊
TYPE enum_t IS TABLE OF employees.employee_id%TYPE INDEX BY BINARY_INTEGER;
TYPE class_t IS TABLE OF employees.class_id%TYPE INDEX BY BINARY_INTEGER;
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 if;END get_emp
现在是最后一行的end报错了
现在报错是 必须说明标识符'ENUM_T'
TYPE class_t IS TABLE OF employees.class_id%TYPE;
这样定义可以么 语法有错么
所以不行的吧
我是猜测。
你写一个包,把对象定义到包头里,这样看看行不?
调试的话,用plsql developer怎么样?我也只是看过 没自己写过
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就行了如果仅仅只是完成一个功能的话,写包没什么必要
他要返回的不是一条数据啊 是一个结果集
要么用游标返回
要么只能定义对象了啊
/
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
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);
procedure test;//上面的少了一个测试的存储过程在包头中的定义
end;