Oracle下的動(dòng)態(tài)游標(biāo)實(shí)現(xiàn)（總結(jié)）
2024-08-29 13:31:42
供稿：網(wǎng)友
不少朋友在開發(fā)過程中，要使用到游標(biāo)進(jìn)行統(tǒng)計(jì)(不用臨時(shí)表)并返回統(tǒng)計(jì)結(jié)果，但游標(biāo)后面的sql卻是動(dòng)態(tài)的，如select * from tablename where ? order ?."?"代表?xiàng)l件，這樣該如何處理呢。在同事的幫助下我實(shí)踐了一下?？偨Y(jié)出來。
假設(shè)tablename表中有field如下：
field1 varchar2(50)
field2 varchar2(50)
field3 varchar(50)
field4 varchar2(50)
field5 varchar2(20)
field6 float,
field7 float
1.定義游標(biāo)
create or replace package refcursor is
-- author : ricky
-- created : 2003-9-1 14:08:45
-- purpose :
-- public type declarations
type t_refcursor is ref cursor;
end refcursor;
2.創(chuàng)建類型
創(chuàng)建的類型與tablename中表的fields一致，當(dāng)然也要看你實(shí)際是否要統(tǒng)計(jì)所有的fields.
create or replace type tabletype as object
(
-- author : ricky
-- created : 2003-8-25 9:12:08
-- purpose :
-- attributes
field1 varchar2(50),
field2 varchar2(50),
field3 varchar(50),
field4 varchar2(50),
field5 varchar2(20),
field6 float,
field7 float
);
3.創(chuàng)建表類型
create or replace type tabletypelist as table of tabletype;
4.在存儲(chǔ)過程或者函數(shù)中使用，下面在函數(shù)中使用(存儲(chǔ)過程中不能用return一個(gè)表結(jié)構(gòu)，要用到臨時(shí)表)
create or replace function "test" (
return tabletypelist pipelined as
begin
v_cur refcursor.t_refcursor;
v_sqlstatement string(10000);
v_table tablename%rowtype;
tmp1 tablename.field1%type;
tmp2 tablename.field2%type;
tmp3 tablename.field3%type;
tmp4 tablename.field4%type;
tmp5 tablename.field5%type;
tmp6 tablename.field6%type;
tmp7 tablename.field6%type;
v_sqlstatement := 'select * from tablename where field1='1' order by field1';
open v_cur for v_sqlstatement;
loop
--這里是循環(huán)過程
fetch v_cur into v_comm;
exit when v_commcur%notfound;
--這里是你要處理的統(tǒng)計(jì)過程,中間的過程我沒有做統(tǒng)計(jì)，各位在實(shí)踐中按需要自己添加。
field1 = v_cur.field1;
field2 = v_cur.field2;
field3 = v_cur.field3;
field4 = v_cur.field4;
field5 = v_cur.field5;
field6 = v_cur.field6;
field7 = v_cur.field7;
v_table = tabletype(field1,
field2,
field3,
field4,
field5,
field6,
field7)
pipe row(v_table);
end loop
end;
