使用PL/SQL过程获取多行

Question

程序：

create or replace PROCEDURE employee_project1 (
emp_id   in  EMPLOYEES.EMPLOYEE_ID%TYPE,
cur      out SYS_REFCURSOR
)
IS
BEGIN
OPEN cur FOR
SELECT p.project_id,
p.project_name
FROM   employees e
INNER JOIN departments d
ON ( e.department_id = d.department_id )
INNER JOIN projects p
ON ( p.department_id = e.department_id)
WHERE  e.employee_id = emp_id;
END;

这个程序对我有效。我正在执行以下代码的程序：

执行：

declare
cur SYS_REFCURSOR;
pro_id int;
pro_name projects.project_name%TYPE;
begin
employee_project1(43, cur);
loop
fetch cur into pro_id, pro_name;
exit when cur%NOTFOUND;
dbms_output.put_line(pro_id||' '||pro_name);
end loop;
end;

我必须用JDBC在java中调用该过程。那么，如何通过上面的执行代码来调用java中的过程呢？

Answer 1

使用集合(可以使用CREATE TYPE ... AS TABLE OF ...创建(或VARRAY(类似于内置的VARRAY的SYS.ODCI*LIST(和BULK COLLECT INTO:

CREATE PROCEDURE employee_project (
emp_id    in  EMPLOYEES.EMPLOYEE_ID%TYPE,
pro_ids   out SYS.ODCINUMBERLIST,
pro_names out SYS.ODCIVARCHAR2LIST
)
IS
BEGIN
SELECT p.project_id,
p.project_name
BULK COLLECT INTO
pro_ids,
pro_names
FROM   employees e
INNER JOIN departments d
ON ( e.department_id= d.department_id )
INNER JOIN projects p
ON ( p.department_id = d.department_id)
WHERE  e.employee_id = emp_id;
END;
/

或者只返回光标(不带INTO子句(：

CREATE PROCEDURE employee_project (
emp_id   in  EMPLOYEES.EMPLOYEE_ID%TYPE,
cur      out SYS_REFCURSOR
)
IS
BEGIN
OPEN cur FOR
SELECT p.project_id,
p.project_name
FROM   employees e
INNER JOIN departments d
ON ( e.department_id= d.department_id )
INNER JOIN projects p
ON ( p.department_id = d.department_id)
WHERE  e.employee_id = emp_id;
END;
/