一个例子,供参考:create or replace package pkg_test
as
type cur_test is ref cursor; -- 定義一個cursor的type
/*
*返回结果集
*/
function getResult(input1 in number,input2 in varchar2)
return cur_test;
end pkg_test;
/
create or replace package body pkg_test is
/*
*返回结果集
*/
function getResult(input1 in number,input2 in varchar2)
return cur_test
is
cur_return cur_test;
begin
open cur_return for
SELECT *
FROM TEST
WHERE AA=INPUT1
AND BB LIKE '%'||INPUT2||'%';
return cur_return;
exception
when others then
if cur_return%isopen
then
close cur_return;
end if;
end getResult;end pkg_test;
as
type cur_test is ref cursor; -- 定義一個cursor的type
/*
*返回结果集
*/
function getResult(input1 in number,input2 in varchar2)
return cur_test;
end pkg_test;
/
create or replace package body pkg_test is
/*
*返回结果集
*/
function getResult(input1 in number,input2 in varchar2)
return cur_test
is
cur_return cur_test;
begin
open cur_return for
SELECT *
FROM TEST
WHERE AA=INPUT1
AND BB LIKE '%'||INPUT2||'%';
return cur_return;
exception
when others then
if cur_return%isopen
then
close cur_return;
end if;
end getResult;end pkg_test;
解决方案 »
- oracle 10g express edition启动问题
- 求cnoug的邀请码
- sql怎么写
- 如何安装 oracle8.05i 才能在客户端运行exp80.exe 已完成对数据库的导出
- 求一条查询语句!!!
- 关于ORACLE里边的NVL语句的问题,请教大家,谢谢
- 请问用PL/SQL语句能不能遍历一个目录下的文件啊
- 紧急求救,两数据数据改变问题
- 超菜的几个问题,请大侠帮帮忙
- SQL语句转为ORACLE语句问题?进来帮下忙!!
- union ,inner join 与(left,right)outer join 的区别
- 各位大虾,ORACLE中上一月的函数是什么,怎么自动用函数表示当前日期的上一月?深表感谢!
(
vchno char
)as
aaaaa char
bbbbb char
begin
select 字段1,字段2 into aaaa,bbbb from gdrecv where vchno=vchno;
end
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;
/
sqlstr :='select id,name,sex,address,postcode,birthday
from student where id='||p_id;
OPEN p_rc FOR sqlstr;