pl/sql怎么返回数据集?
CREATE OR REPLACE PACKAGE pkg_test
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;
/
create or replace package pkg_test
as
type myCursor is ref cursor;
function get(p_id number) return myCursor;
end pkg_test;create or replace package body 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 using p_id;
end if;
return rc;
end get;
end pkg_test;
--上面是一个返回结果集的函数,下面调用.
create or replace procedure pro_test as
v_out pkg_test%myCursor;
v_name varchar2(100);
begin
v_out:=pkg_test.get(0); --得到结果集
loop
fetch v_out into v_name;
exit when v_out%notfound;
--这里进行处理,想要处理哪一行或进行什么处理在这里进行
end loop;
......
end;
CREATE OR REPLACE PACKAGE pkg_test
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;
/
create or replace package pkg_test
as
type myCursor is ref cursor;
function get(p_id number) return myCursor;
end pkg_test;create or replace package body 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 using p_id;
end if;
return rc;
end get;
end pkg_test;
--上面是一个返回结果集的函数,下面调用.
create or replace procedure pro_test as
v_out pkg_test%myCursor;
v_name varchar2(100);
begin
v_out:=pkg_test.get(0); --得到结果集
loop
fetch v_out into v_name;
exit when v_out%notfound;
--这里进行处理,想要处理哪一行或进行什么处理在这里进行
end loop;
......
end;
解决方案 »
- 想去Oracle官网下载一个 9i 的安装文件,有账号密码
- 百分贴,求 ORACLE 存储过程中,保证每次调用该存储过程时都是一个新的会话SESSION
- 只想多insert一条数据,却把先前的100W条数据全部清光..oracle是怎么回事..嗨..
- 单元测试Cannot load JDBC driver class 'oracle.jdbc.driver.OracleDriver'
- oracle过程错误 恳求指教
- 安装Oracle817为什么老是出错?
- 关闭用户连接
- OEM中作业调度的设置:能否有每天的固定一个时间点
- MySQL中什么函数的作用与Oracle中的exception作用相同
- SQL分组查询语句问题
- oracle不能安装在win2000profession上吗?
- 求一句sql
pk_sc2.fs2('select * from TParameterMedicare');
End;
/
试试
pk_sc2.fs2('select * from TParameterMedicare');
End;我怎么才能得到返回值呢?
open c_cursor for sqlStr
loop
fetch c_cursor into ...
........
exit when ....
end loop
create package test_age
as
type myrc is ref cursor;
end;
/
create or replace PROCEDURE Display(p_rc out test_age.myrc)
is
begin
open p_rc for 'select * from pwm';
end Display;
/
type c_Type is ref cursor;
procedure fs2(sqlStr varchar2,rs out c_Type);
end pk_sc2;create or replace package body pk_sc2 as
procedure fs2(sqlStr varchar2,rs out c_Type) is
begin
open rs for sqlStr;
end fs2;
end pk_sc2;