SQL 語句是動態生成的﹐比如
i := 0;
iCount := 12;
--Sql的構造很復雜,這里只是舉例
LOOP
strSql = strSql ¦ ¦ 'FieldName' ¦ ¦ TO_CHAR(i);
if i
1<icount '="" :="SELECT " \---------------------------------------------------------------="" \--创建测试表：="" builder用="" cursor來被oracle="" end="" exit;="" from="" if="" loop;="" mytable';="" sql="" strsql="" then="" ¦="" 例子1：="" 應該怎么做啊﹐求救﹗="" 我要返回一個ref="" 的develope的6i里面的report="" 看看下面两个例子，会对你有帮助的。=""> create table a (id number,name varchar2(50),doctime date);
2
3Table created.
4
5\--插入六条测试数据：
6SQL> insert into a values (1,'aaa',to_date('2002-07-01','yyyy-mm-dd'));
7
81 row created.
9
10SQL> insert into a values (2,'bbb',to_date('2002-07-02','yyyy-mm-dd'));
11
121 row created.
13
14SQL> insert into a values (3,'ccc',to_date('2002-07-03','yyyy-mm-dd'));
15
161 row created.
17
18SQL> insert into a values (4,'ddd',to_date('2002-07-04','yyyy-mm-dd'));
19
201 row created.
21
22SQL> insert into a values (5,'eee',to_date('2002-07-05','yyyy-mm-dd'));
23
241 row created.
25
26SQL> insert into a values (6,'fff',to_date('2002-07-06','yyyy-mm-dd'));
27
281 row created.
29
30SQL> commit;
31
32Commit complete.
33
34\--创建两个type
35SQL> create or replace type myobjectype as object (x int,y date,z varchar2(50));
362 /
37
38Type created.
39
40SQL> create or replace type mytabletype as table of myobjectype
412 /
42
43Type created.
44
45\--创建可以返回纪录集的函数(不传入表名参数)
46SQL> create or replace function testrerecordnotabname (tableid in number)
472 return mytabletype
483 as
494 l_data mytabletype :=mytabletype();
505 begin
516 for i in (select * from a where id>=tableid) loop
527 l_data.extend;
538 l_data(l_data.count) := myobjectype(i.id,i.doctime,i.name);
549 exit when i.id = 62;
5510 end loop;
5611 return l_data;
5712 end;
5813 /
59
60Function created.
61
62SQL> commit;
63
64Commit complete.
65
66\--创建可以返回纪录集的函数(可以传入表名参数)
67SQL> create or replace function testrerecordtabname (tablename in varchar2,tableid in number)
682 return mytabletype
693 as
704 l_data mytabletype :=mytabletype();
715 strsql varchar2(50);
726 type v_cursor is ref cursor;
737 v_tempcursor v_cursor;
748 i1 number;
759 i2 varchar2(50);
7610 i3 date;
7711 begin
7812 strsql := 'select * from ' ¦ ¦ tablename ¦ ¦ ' where id>=' ¦ ¦ tableid;
7913 open v_tempcursor for strsql;
8014 loop
8115 fetch v_tempcursor into i1,i2,i3;
8216 l_data.extend;
8317 l_data(l_data.count) := myobjectype(i1,i3,i2);
8418 exit when v_tempcursor%NOTFOUND;
8519 end loop;
8620 return l_data;
8721 end;
8822 /
89
90Function created.
91
92SQL> commit;
93
94Commit complete.
95
96\--测试不传表名参数的function(testrerecorenotabname)
97SQL> set serveroutput on
98SQL> declare
992 testre mytabletype :=mytabletype();
1003 i number :=0;
1014 begin
1025 testre := testrerecordnotabname(1);
1036 loop
1047 i := i+1;
1058 dbms_output.put_line(';' ¦ ¦ testre(i).x ¦ ¦ ';' ¦ ¦ testre(i).y ¦ ¦ ';' ¦ ¦ testre(i).z ¦ ¦ ';');
1069 exit when i = testre.count;
10710 end loop;
10811 end;
10912 /
110;1;01-7?? -02;aaa;
111;2;02-7?? -02;bbb;
112;3;03-7?? -02;ccc;
113;4;04-7?? -02;ddd;
114;5;05-7?? -02;eee;
115;6;06-7?? -02;fff;
116
117PL/SQL procedure successfully completed.
118
119\--测试传表名参数的function(testrerecoretabname)
120SQL> set serveroutput on
121SQL> declare
1222 testre mytabletype :=mytabletype();
1233 i number :=0;
1244 begin
1255 testre := testrerecordtabname('a',1);
1266 loop
1277 i := i+1;
1288 dbms_output.put_line(';' ¦ ¦ testre(i).x ¦ ¦ ';' ¦ ¦ testre(i).y ¦ ¦ ';' ¦ ¦ testre(i).z ¦ ¦ ';');
1299 exit when i = testre.count;
13010 end loop;
13111 end;
13212 /
133;1;01-7?? -02;aaa;
134;2;02-7?? -02;bbb;
135;3;03-7?? -02;ccc;
136;4;04-7?? -02;ddd;
137;5;05-7?? -02;eee;
138;6;06-7?? -02;fff;
139;6;06-7?? -02;fff;
140
141PL/SQL procedure successfully completed.
142
143例子2：
144
145测试过程：
1461、建立测试表
147CREATE TABLE student
148(
149id NUMBER,
150name VARCHAR2(30),
151sex VARCHAR2(10),
152address VARCHAR2(100),
153postcode VARCHAR2(10),
154birthday DATE,
155photo LONG RAW
156)
157/
158
1592、建立带ref cursor定义的包和包体及函数：
160CREATE OR REPLACE
161package pkg_test as
162/* 定义ref cursor类型
163不加return类型，为弱类型，允许动态sql查询，
164否则为强类型，无法使用动态sql查询;
165*/
166type myrctype is ref cursor;
167
168\--函数申明
169function get(intID number) return myrctype;
170end pkg_test;
171/
172
173CREATE OR REPLACE
174package body pkg_test as
175\--函数体
176function get(intID number) return myrctype is
177rc myrctype; --定义ref cursor变量
178sqlstr varchar2(500);
179begin
180if intID=0 then
181\--静态测试，直接用select语句直接返回结果
182open rc for select id,name,sex,address,postcode,birthday from student;
183else
184\--动态sql赋值，用:w_id来申明该变量从外部获得
185sqlstr := 'select id,name,sex,address,postcode,birthday from student where id=:w_id';
186\--动态测试，用sqlstr字符串返回结果，用using关键词传递参数
187open rc for sqlstr using intid;
188end if;
189
190return rc;
191end get;
192
193end pkg_test;
194/
195
1963、用pl/sql块进行测试：
197declare
198w_rc pkg_test.myrctype; --定义ref cursor型变量
199
200\--定义临时变量，用于显示结果
201w_id student.id%type;
202w_name student.name%type;
203w_sex student.sex%type;
204w_address student.address%type;
205w_postcode student.postcode%type;
206w_birthday student.birthday%type;
207
208begin
209\--调用函数，获得记录集
210w_rc := pkg_test.get(1);
211
212\--fetch结果并显示
213fetch w_rc into w_id,w_name,w_sex,w_address,w_postcode,w_birthday;
214dbms_output.put_line(w_name);
215end;
216
2174、测试结果：
218通过。
219\---------------------------------------------------------------
220
221search:ref cursor.
222\---------------------------------------------------------------
223
224\--包的定义
225
226TYPE t_CurList IS REF CURSOR ;
227
228\--包体的过程
229/************************************************************/
230PROCEDURE sp_Get_Cur(……，
231io_cursor IN OUT t_CurList)
232IS
233BEGIN
234……
235strSql := 'SELECT ' ¦ ¦ strSql ¦ ¦ ' FROM myTable';
236OPEN io_cursor FOR strSql ;
237EXCEPTION
238WHEN OTHERS THEN
239END sp_Get_Cur;
240
241\---------------------------------------------------------------
242
243向这样的Procedure，我的VB,VC或JAVA可以取到吗？
244\---------------------------------------------------------------
245
246当然可以了。</icount>