给同事写个一个Pro*C调用存储过程返回的游标的小例子。
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.C:\Documents and Settings\htaix>cd c:\
C:\>sqlplus test/test@remotedb
SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 2月 5 16:48:02 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> SELECT * FROM T;
ID NAME
---------- ----------------------------------------
1 a
2 b
3 c
4 d
SQL> CREATE OR REPLACE PROCEDURE GETCURSOR(MYCURSOR OUT SYS_REFCURSOR)
2 AS
3 BEGIN
4 OPEN MYCURSOR FOR SELECT * FROM T;
5 END;
6
7 /
过程已创建。
SQL> SET SERVEROUT ON
SQL> declare
2 mycursor sys_refcursor;
3 v_row t%rowtype;
4 begin
5 GETCURSOR(mycursor);
6 loop
7 fetch mycursor into v_row;
8 exit when mycursor%notfound;
9 dbms_output.put_line('id='||v_row.id);
10 end loop;
11 close mycursor;
12 end;
13 /
id=1
id=2
id=3
id=4
PL/SQL 过程已成功完成。
SQL>
创建了一个返回游标类型的存储过程。
Pro*C代码如下：
#include
#include
#include
exec sql include sqlca;
main()
{
exec sql begin declare section;
char *username="test/test@remotedb";
int id;
char name[20];
sql_cursor t_cursor;
exec sql end declare section;
exec sql connect :username;
exec sql allocate :t_cursor;
EXEC SQL EXECUTE
BEGIN
GETCURSOR(:t_cursor);
END;
END-EXEC;
exec sql whenever not found do break;
while(1)
{
exec sql fetch :t_cursor into :id,:name;
printf("id: %d,name: %s\n",id,name);
}
exec sql close :t_cursor;
exec sql free :t_cursor;
exec sql commit work release;
}
编程成可执行文件，执行结果如下：
/home/cpicsrv/yansp > ./test
id: 1,name: a
id: 2,name: b
id: 3,name: c
id: 4,name: d