给你个例子:
/*包头:定义参考游标,定义函数,返回*/
create or replace package dinya_pkg_test
as
type myCursor is ref cursor;
function get(p_id number) return myCursor;
end dinya_pkg_test;
--======================================================
create or replace package body dinya_pkg_test
as
--*****************************************************************************
--输入ID 返回记录集的函数
function get(p_id number) return myCursor is
rc myCursor;
strsql varchar2(200);
begin
if p_id=0 then
open rc for select a.user_name from fnd_user a ;
else
strsql:='select a.user_name from fnd_user a where a.user_id=:p_id';
open rc for strsql;
end if;
return rc;
end get;
end dinya_pkg_test;*****************************************************
--调用:
set serverout on
declare
w_rc dinya_pkg_test.myCursor;
w_name varchar2(100);
begin
w_rc:=dinya_pkg_test.get(0);
loop
fetch w_rc into w_name;
exit when w_rc%notfound;
dbms_output.put_line(w_name);
end loop;
end;
/
/*包头:定义参考游标,定义函数,返回*/
create or replace package dinya_pkg_test
as
type myCursor is ref cursor;
function get(p_id number) return myCursor;
end dinya_pkg_test;
--======================================================
create or replace package body dinya_pkg_test
as
--*****************************************************************************
--输入ID 返回记录集的函数
function get(p_id number) return myCursor is
rc myCursor;
strsql varchar2(200);
begin
if p_id=0 then
open rc for select a.user_name from fnd_user a ;
else
strsql:='select a.user_name from fnd_user a where a.user_id=:p_id';
open rc for strsql;
end if;
return rc;
end get;
end dinya_pkg_test;*****************************************************
--调用:
set serverout on
declare
w_rc dinya_pkg_test.myCursor;
w_name varchar2(100);
begin
w_rc:=dinya_pkg_test.get(0);
loop
fetch w_rc into w_name;
exit when w_rc%notfound;
dbms_output.put_line(w_name);
end loop;
end;
/
解决方案 »
- ubuntu 12.04 已经安装好了oracle 10G xe版,启动sqlplus遇到一下问题
- 不同用户同一张表,查询结果竟然不一样
- 关于同学录系统的数据库设计
- TNS: 监听程序无法启动专用服务器进程。请专家给出具体解决方案,先谢谢!
- 高手急救:安装Oracle Data Provider For .net出错。
- 急求一条sql语句,在线等,高手请进
- 这句话对不对啊:select * from table where rownum<1000 and rwonum>100,如果不对该怎么改啊
- 那个更快
- 帮我写一个 一次生成100条数据的oracle 的 语句 谢谢!!!
- pl/sql数据库怎么向查出来的表中的列添加数据?求解!急!
- 急急急!调用存储过程后,数据库发出ORA-3127错误,说还有活动操作存在?
- 关于Oracle中的MD5加密问题...
is
type ty is ref cursor;
vstr varchar2(100);
vemp emp%rowtype;
ta ty;
begin
vstr:='select * from emp';open ta for vstr;
loop
fetch ta into vemp;
exit when ta%notfound;
---操作
end loop;
close ta;
end pro;
AS
TYPE myrctype IS REF CURSOR; PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);
END pkg_test;
/CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE get (p_id NUMBER, p_rc OUT myrctype)
IS
sqlstr VARCHAR2 (500);
BEGIN
IF p_id = 0 THEN
OPEN p_rc FOR
SELECT ID, NAME, sex, address, postcode, birthday
FROM student;
ELSE
sqlstr :=
'select id,name,sex,address,postcode,birthday
from student where id=:w_id';
OPEN p_rc FOR sqlstr USING p_id;
END IF;
END get;
END pkg_test;
/
IF @resultNum = 1
BEGIN
select @tempIndexType = (select distinct dataType from dbo.CAF_PM_IndexDef_Table where indexId = @tempIndexId)
SELECT @tempIndexId,@tempIndexName,@tempIndexType,@tempIndexMaxThreshold,@tempIndexMinThreshold,@tempIsAlarm
CLOSE cusor_records
DEALLOCATE cusor_records
return
END