How can I create and return a cursor from a plsql function?
I created a plsql function and I want to create a cursor and return this cursor from the function. Then I want to call this function in a Java class and retrieve data from the cursor. Note: the cursor returns one row. I wrote something like this,,
CREATE OR REPLACE
FUNCTION FUNCTION1 ( emp_id IN NUMBER)RETURN cursor AS
cursor newCursor(e_id number) is
sel开发者_Go百科ect * from table1 where employee_id = e_id;
type refCursor is ref cursor;
BEGIN
open newCursor(emp_id);
loop
exit when newCursor%notfound;
fetch newCursor into refCursor;
end loop;
RETURN refCursor;
END FUNCTION1;
What return type should I use if I want to return a cursor?
Model it after the following function which works
create or replace function getemps return sys_refcursor is
v_curs sys_refcursor;
begin
open v_curs for select ename from emp;
return v_curs;
end;
/
sys_refcursor is oracle's generic implicit cursor use EXPLICITE cursor instead
精彩评论