create or replace procedure procedure_name( o_cur out sys_refcursor )as begin open o_cur for select xxx from table_name; exception when others then null; end;
要返回值 应该是functioncreate or replace function function_name( o_cur out sys_refcursor )as begin open o_cur for select xxx from table_name; return o_cur; end;
CREATE OR REPLACE PACKAGE pkg_user AS --定義返回值(遊標類型) TYPE myrctype IS REF CURSOR; --定義查詢過程 PROCEDURE sp_userlist(cur_result OUT myrctype); END pkg_user;
2.
CREATE OR REPLACE PACKAGE BODY "PKG_USER" AS --查詢過程 PROCEDURE sp_userlist(cur_result OUT myrctype) IS sqlstr VARCHAR2(500); BEGIN OPEN cur_result FOR select * from sys_employee; END sp_userlist; END pkg_user;
create or replace procedure procedure_name(
o_cur out sys_refcursor
)as
begin
open o_cur for
select xxx
from table_name;
exception
when others then
null;
end;
o_cur out sys_refcursor
)as
begin
open o_cur for
select xxx
from table_name;
return o_cur;
end;
--定義返回值(遊標類型)
TYPE myrctype IS REF CURSOR;
--定義查詢過程
PROCEDURE sp_userlist(cur_result OUT myrctype);
END pkg_user;
2.
CREATE OR REPLACE PACKAGE BODY "PKG_USER" AS
--查詢過程
PROCEDURE sp_userlist(cur_result OUT myrctype) IS
sqlstr VARCHAR2(500);
BEGIN
OPEN cur_result FOR
select * from sys_employee;
END sp_userlist;
END pkg_user;