你可以用“结果集”搜索一下,会有很多很多的相关贴子。
http://www.csdn.net/expert/topic/764/764635.xml?temp=.4557764
http://www.csdn.net/expert/topic/764/764635.xml?temp=.4557764
解决方案 »
- oralce 新建用户grant dba 然后revoke DBA后为何还能登陆?
- 求购OCA: Oracle 10g 管理 I 学习指南(1Z0-042)
- 下面是一段自己修改的一个oracle存储过程,里面错误应该很多,大家找。按错误个数及难度给分.
- 一存储过程调试编译无反应,而其他的则一切正常,盼解决之
- OCI怎么比OO4O还慢?(VC)
- 带条件的触发器
- 指定回滚段问题????????
- oracle设置问题,非高手莫入????在线等待,急 急 急 急
- 求大神指教 SQL连接查询问题
- ORACLE sql Developer 解析XML
- ORACLE9下的java存储过程与8i下使用方法有神莫区别?
- 这个错误是什么意思??
有点问题-????
Oracle存储存过程 记录集可返回 游标能返回吗?这问题太强大 www.Oracle.com================================================================ok?
declare
type c_type is ref cursor;
c_data c_type;
c_null tab.row_id%type;
begin
open :c_data for
select row_id from tab order by row_id;
while :c_data%rowcount < :c_init loop
fetch :c_data into c_null;
end loop;
end;
"; $stmt=OCIParse($conn, $sql);
$curs=OCINewCursor($conn); $offset=100; // dont fetch the first 100 rows
$limit=10; // we want exactly 10 rows OCIBindByName($stmt,":c_init",$offset,32);
OCIBindByName($stmt,":c_data",$curs,-1,OCI_B_CURSOR); OCIExecute($stmt);
OCIExecute($curs); for ($i=0;$i<$limit&&OCIFetchinto($curs,$cols,OCI_ASSOC);$i++)
print $cols[ROW_ID]."\n"; OCIFreeStatement($stmt);
OCIFreeCursor($curs);
OCILogoff($conn);
$conn = OCILogon( "system", "manager", "grief.world");
$sql= "
declare
type c_type is ref cursor;
c_data c_type;
c_null tab.row_id%type;
begin
open :c_data for
select row_id from tab order by row_id;
while :c_data%rowcount < :c_init loop
fetch :c_data into c_null;
end loop;
end;
";
$stmt=OCIParse($conn, $sql);
$curs=OCINewCursor($conn);
$offset=100; // dont fetch the first 100 rows
$limit=10; // we want exactly 10 rows
OCIBindByName($stmt, ":c_init",$offset,32);
OCIBindByName($stmt, ":c_data",$curs,-1,OCI_B_CURSOR);
OCIExecute($stmt);
OCIExecute($curs);
for($i=0;$i<$limit&&OCIFetchinto($curs,$cols,OCI_ASSOC);$i++)
print $cols[ROW_ID]."\n";
OCIFreeStatement($stmt);
OCIFreeCursor($curs);
OCILogoff($conn);
?>我自已写的是这样
//存储过程
CREATE OR REPLACE PACKAGE MyType
AS
type c_type is ref cursor;
END MyType;
CREATE OR REPLACE PROCEDURE TEST
(
ParaIn varchar2,
CursorOut OUT MyType.c_type
) ISBegin
Open CursorOut For
Select * from wstate2;
End TEST;
//PHP
<?
$conn=OCILogon("estar","star","estar");$sql="
declare
type c_type is ref cursor;
CursorOut c_type;
Begin
Test('luo',:CursorOut);
End;";$stmt=OCIParse($conn,$sql);OCIBindByName($stmt,":CursorOut",$cursor,-1,OCI_B_CURSOR);
OCIExecute($stmt);for($i=0;$i<$limit&&OCIFetchinto($cursor,$cols,OCI_ASSOC);$i++)
print $cols[ID]."\n";OCIFreeStatement($stmt);
OCILogoff($conn);
?>//错误
Warning: Supplied argument is not a valid OCI8-Statement resource in c:\estar\test.php on line 14Warning: OCIStmtExecute: ORA-06550: 行1、列1: PLS-00103: 出现符号""在需要下列之一时: begindeclareexitforfunctiongoto ifloopmodnullpackagepragmaprocedureraisereturnselectseparate typeupdatewhilewith << formtableclosecurrentdefinedeletefetchlockinsertopenrollback savepointsetsqlcommitlibrary in c:\estar\test.php on line 15
看看这个吧,很长,需要仔细看,是可以的
http://www.csdn.net/expert/topic/903/903782.xml?temp=.762356
测试过程:
1、建立测试表
CREATE TABLE student
(
id NUMBER,
name VARCHAR2(30),
sex VARCHAR2(10),
address VARCHAR2(100),
postcode VARCHAR2(10),
birthday DATE,
photo LONG RAW
)
/2、建立带ref cursor定义的包和包体及函数:
CREATE OR REPLACE
package pkg_test as
/* 定义ref cursor类型
不加return类型,为弱类型,允许动态sql查询,
否则为强类型,无法使用动态sql查询;
*/
type myrctype is ref cursor; --函数申明
function get(intID number) return myrctype;
end pkg_test;
/CREATE OR REPLACE
package body pkg_test as
--函数体
function get(intID number) return myrctype is
rc myrctype; --定义ref cursor变量
sqlstr varchar2(500);
begin
if intID=0 then
--静态测试,直接用select语句直接返回结果
open rc for select id,name,sex,address,postcode,birthday from student;
else
--动态sql赋值,用:w_id来申明该变量从外部获得
sqlstr := 'select id,name,sex,address,postcode,birthday from student where id=:w_id';
--动态测试,用sqlstr字符串返回结果,用using关键词传递参数
open rc for sqlstr using intid;
end if; return rc;
end get;end pkg_test;
/3、用pl/sql块进行测试:
declare
w_rc pkg_test.myrctype; --定义ref cursor型变量 --定义临时变量,用于显示结果
w_id student.id%type;
w_name student.name%type;
w_sex student.sex%type;
w_address student.address%type;
w_postcode student.postcode%type;
w_birthday student.birthday%type;begin
--调用函数,获得记录集
w_rc := pkg_test.get(1); --fetch结果并显示
fetch w_rc into w_id,w_name,w_sex,w_address,w_postcode,w_birthday;
dbms_output.put_line(w_name);
end;4、测试结果:
通过。
第二个是我自己写的,测试已经通过:
--创建测试表:
SQL> create table a (id number,name varchar2(50),doctime date);Table created.--插入六条测试数据:
SQL> insert into a values (1,'aaa',to_date('2002-07-01','yyyy-mm-dd'));1 row created.SQL> insert into a values (2,'bbb',to_date('2002-07-02','yyyy-mm-dd'));1 row created.SQL> insert into a values (3,'ccc',to_date('2002-07-03','yyyy-mm-dd'));1 row created.SQL> insert into a values (4,'ddd',to_date('2002-07-04','yyyy-mm-dd'));1 row created.SQL> insert into a values (5,'eee',to_date('2002-07-05','yyyy-mm-dd'));1 row created.SQL> insert into a values (6,'fff',to_date('2002-07-06','yyyy-mm-dd'));1 row created.SQL> commit;Commit complete.--创建两个type
SQL> create or replace type myobjectype as object (x int,y date,z varchar2(50));
2 /Type created.SQL> create or replace type mytabletype as table of myobjectype
2 /Type created.--创建可以返回纪录集的函数(不传入表名参数)
SQL> create or replace function testrerecordnotabname (tableid in number)
2 return mytabletype
3 as
4 l_data mytabletype :=mytabletype();
5 begin
6 for i in (select * from a where id>=tableid) loop
7 l_data.extend;
8 l_data(l_data.count) := myobjectype(i.id,i.doctime,i.name);
9 exit when i.id = 62;
10 end loop;
11 return l_data;
12 end;
13 /Function created.SQL> commit;Commit complete.--创建可以返回纪录集的函数(可以传入表名参数)
SQL> create or replace function testrerecordtabname (tablename in varchar2,tableid in number)
2 return mytabletype
3 as
4 l_data mytabletype :=mytabletype();
5 strsql varchar2(50);
6 type v_cursor is ref cursor;
7 v_tempcursor v_cursor;
8 i1 number;
9 i2 varchar2(50);
10 i3 date;
11 begin
12 strsql := 'select * from ' || tablename || ' where id>=' || tableid;
13 open v_tempcursor for strsql;
14 loop
15 fetch v_tempcursor into i1,i2,i3;
16 l_data.extend;
17 l_data(l_data.count) := myobjectype(i1,i3,i2);
18 exit when v_tempcursor%NOTFOUND;
19 end loop;
20 return l_data;
21 end;
22 /Function created.SQL> commit;Commit complete.--测试不传表名参数的function(testrerecorenotabname)
SQL> set serveroutput on
SQL> declare
2 testre mytabletype :=mytabletype();
3 i number :=0;
4 begin
5 testre := testrerecordnotabname(1);
6 loop
7 i := i+1;
8 dbms_output.put_line(';' || testre(i).x || ';' || testre(i).y || ';' || testre(i).z || ';');
9 exit when i = testre.count;
10 end loop;
11 end;
12 /
;1;01-7?? -02;aaa;
;2;02-7?? -02;bbb;
;3;03-7?? -02;ccc;
;4;04-7?? -02;ddd;
;5;05-7?? -02;eee;
;6;06-7?? -02;fff;PL/SQL procedure successfully completed.--测试传表名参数的function(testrerecoretabname)
SQL> set serveroutput on
SQL> declare
2 testre mytabletype :=mytabletype();
3 i number :=0;
4 begin
5 testre := testrerecordtabname('a',1);
6 loop
7 i := i+1;
8 dbms_output.put_line(';' || testre(i).x || ';' || testre(i).y || ';' || testre(i).z || ';');
9 exit when i = testre.count;
10 end loop;
11 end;
12 /
;1;01-7?? -02;aaa;
;2;02-7?? -02;bbb;
;3;03-7?? -02;ccc;
;4;04-7?? -02;ddd;
;5;05-7?? -02;eee;
;6;06-7?? -02;fff;
;6;06-7?? -02;fff;PL/SQL procedure successfully completed.