/wufajiaru/archive//04/28/56796.html
Oracle 存储过程返回结果集
关键字: 转载
Sql代码 Oracle存储过程返回结果集 过程返回记录集：CREATEORREPLACEPACKAGEpkg_testASTYPEmyrctypeISREFCURSOR;PROCEDUREget(p_idNUMBER,p_rcOUTmyrctype);ENDpkg_test; /CREATEORREPLACEPACKAGEBODYpkg_testASPROCEDUREget(p_idNUMBER,p_rcOUTmyrctype)ISsqlstrVARCHAR2(500);BEGINIFp_id=0THENOPENp_rcFORSELECTID,NAME,sex,address,postcode,birthdayFROMstudent;ELSEsqlstr:= 'selectid,name,sex,address,postcode,birthdayfromstudentwhereid=:w_id';OPENp_rcFORsqlstrUSINGp_id;ENDIF;ENDget;ENDpkg_test; / 函数返回记录集: 建立带refcursor定义的包和包体及函数：CREATEORREPLACE packagepkg_testas/*定义refcursor类型 不加return类型，为弱类型，允许动态sql查询， 否则为强类型，无法使用动态sql查询; */ typemyrctypeisrefcursor; --函数申明functionget(intIDnumber)returnmyrctype;endpkg_test; /CREATEORREPLACE packagebodypkg_testas--函数体functionget(intIDnumber)returnmyrctypeisrcmyrctype;--定义refcursor变量 sqlstrvarchar2(500);beginifintID=0then--静态测试，直接用select语句直接返回结果openrcforselectid,name,sex,address,postcode,birthdayfromstudent;else--动态sql赋值，用:w_id来申明该变量从外部获得 sqlstr:='selectid,name,sex,address,postcode,birthdayfromstudentwhereid=:w_id'; --动态测试，用sqlstr字符串返回结果，用using关键词传递参数openrcforsqlstrusingintid;endif;returnrc;endget;endpkg_test; / ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
/feiliu010/article/details/1538822
Oracle中函数/过程返回结果集的几种方式:
以函数return为例,存储过程只需改为out参数即可,在oracle 10g测试通过.
(1) 返回游标:
return的类型为:SYS_REFCURSOR
之后在IS里面定义变量:curr SYS_REFCURSOR;
最后在函数体中写:
open cur for
select ......;
return cur;
例:
CREATEORREPLACEFUNCTIONA_Test(
orTypevarchar2
)RETURNSYS_REFCURSOR
is
type_curSYS_REFCURSOR;
BEGIN
OPENtype_curFOR
selectcol1,col2,col3fromtestTable;
RETURNtype_cur;
END;
(2)返回table类型的结果集:
首先定义一个行类型:
CREATEORREPLACETYPE"SPLIT_ARR"ASOBJECT(nowStrvarchar2(18))
其次以此行类型定义一个表类型:
CREATEORREPLACETYPE"SPLIT_TAB"ASTABLEofsplit_arr;
定义函数(此函数完成字符串拆分功能):
CREATEORREPLACEFUNCTIONGetSubStr(
strinvarchar2,--待分割的字符串
splitcharinvarchar2--分割标志
)
returnsplit_tab
IS
restStrvarchar2(2000)defaultGetSubStr.str;--剩余的字符串
thisStrvarchar2(18);--取得的当前字符串
indexStrint;--临时存放分隔符在字符串中的位置
vsplit_tab:=split_tab();--返回结果
begin
dbms_output.put_line(restStr);
whilelength(restStr)!=0
LOOP
<<top>>
indexStr:=instr(restStr,splitchar);--从子串中取分隔符的第一个位置
ifindexStr=0andlength(restStr)!=0then--在剩余的串中找不到分隔符
begin
v.extend;
v(v.count):=split_arr(Reststr);
returnv;
end;
endif;
ifindexStr=1then---第一个字符便为分隔符,此时去掉分隔符
begin
restStr:=substr(restStr,2);
gototop;
end;
endif;
iflength(restStr)=0orrestStrisnullthen
returnv;
endif;
v.extend;
thisStr:=substr(restStr,1,indexStr-1);--取得当前的字符串
restStr:=substr(restStr,indexStr+1);---取剩余的字符串
v(v.count):=split_arr(thisStr);
ENDLOOP;
returnv;
end;
在PL/SQL developer中可以直接调用
cursorstrcuris
selectnowStrfromTable(GetSubStr('111,222,333,,,',','));
(3)以管道形式输出:
createtyperow_typeasobject(avarchar2(10),vvarchar2(10));--定义行对象
createtypetable_typeastableofrow_type;--定义表对象
createorreplacefunctiontest_fun(
ainvarchar2,binvarchar2
)
returntable_typepipelined
is
vrow_type;--定义v为行对象类型
begin
forthisrowin(selecta,bfrommytablewherecol1=aandcol2=b)loop
v:=row_type(thisrow.a,thisrow.b);
piperow(v);
endloop;
return;
end;
select*fromtable(test_fun('123','456'));
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝fetch into 显示两次
open v_cursor for
select 1from dual;
loop
fetch v_cursor into v_num;
DBMS_OUTPUT.PUT_LINE(v_num);
exit when v_cursor%notfound;
end loop;
close v_cursor;
将DBMS_OUTPUT.PUT_LINE(v_num); 放到exit when v_cursor%notfound;之后就不会显示两次了
