转自:http://blog.csdn.net/feiliu010/article/details/1538822
Oracle中函数/过程返回结果集的几种方式:
以函数return为例,存储过程只需改为out参数即可,在oracle 10g测试通过.(1) 返回游标:
return的类型为:SYS_REFCURSOR
之后在IS里面定义变量:curr SYS_REFCURSOR;
最后在函数体中写:
open cur for
select ......;
return cur;
例:
CREATE
OR
REPLACE
FUNCTION
A_Test(
orType varchar2
) RETURN SYS_REFCURSOR
is
type_cur SYS_REFCURSOR;
BEGIN
OPEN type_cur FOR
select col1,col2,col3 from testTable ;
RETURN type_cur;
END ;
orType varchar2
) RETURN SYS_REFCURSOR
is
type_cur SYS_REFCURSOR;
BEGIN
OPEN type_cur FOR
select col1,col2,col3 from testTable ;
RETURN type_cur;
END ;
(2)返回table类型的结果集:
首先定义一个行类型:
CREATE
OR
REPLACE
TYPE "SPLIT_ARR"
AS
OBJECT(nowStr
varchar2
(
18
))
其次以此行类型定义一个表类型:
CREATE
OR
REPLACE
TYPE "SPLIT_TAB"
AS
TABLE
of
split_arr;
定义函数(此函数完成字符串拆分功能):
CREATE
OR
REPLACE
FUNCTION
GetSubStr(
str in varchar2 , -- 待分割的字符串
splitchar in varchar2 -- 分割标志
)
return split_tab
IS
restStr varchar2 ( 2000 ) default GetSubStr. str ; -- 剩余的字符串
thisStr varchar2 ( 18 ); -- 取得的当前字符串
indexStr int ; -- 临时存放分隔符在字符串中的位置
v split_tab : = split_tab(); -- 返回结果
begin
dbms_output.put_line(restStr);
while length(restStr) != 0
LOOP
<< top >>
indexStr : = instr(restStr,splitchar); -- 从子串中取分隔符的第一个位置
if indexStr = 0 and length(restStr) != 0 then -- 在剩余的串中找不到分隔符
begin
v.extend;
v(v. count ) : = split_arr(Reststr);
return v;
end ;
end if ;
if indexStr = 1 then -- -第一个字符便为分隔符,此时去掉分隔符
begin
restStr : = substr(restStr, 2 );
goto top ;
end ;
end if ;
if length(restStr) = 0 or restStr is null then
return v;
end if ;
v.extend;
thisStr : = substr(restStr, 1 ,indexStr - 1 ); -- 取得当前的字符串
restStr : = substr(restStr,indexStr + 1 ); -- -取剩余的字符串
v(v. count ) : = split_arr(thisStr);
END LOOP;
return v;
end ;
str in varchar2 , -- 待分割的字符串
splitchar in varchar2 -- 分割标志
)
return split_tab
IS
restStr varchar2 ( 2000 ) default GetSubStr. str ; -- 剩余的字符串
thisStr varchar2 ( 18 ); -- 取得的当前字符串
indexStr int ; -- 临时存放分隔符在字符串中的位置
v split_tab : = split_tab(); -- 返回结果
begin
dbms_output.put_line(restStr);
while length(restStr) != 0
LOOP
<< top >>
indexStr : = instr(restStr,splitchar); -- 从子串中取分隔符的第一个位置
if indexStr = 0 and length(restStr) != 0 then -- 在剩余的串中找不到分隔符
begin
v.extend;
v(v. count ) : = split_arr(Reststr);
return v;
end ;
end if ;
if indexStr = 1 then -- -第一个字符便为分隔符,此时去掉分隔符
begin
restStr : = substr(restStr, 2 );
goto top ;
end ;
end if ;
if length(restStr) = 0 or restStr is null then
return v;
end if ;
v.extend;
thisStr : = substr(restStr, 1 ,indexStr - 1 ); -- 取得当前的字符串
restStr : = substr(restStr,indexStr + 1 ); -- -取剩余的字符串
v(v. count ) : = split_arr(thisStr);
END LOOP;
return v;
end ;
在PL/SQL developer中可以直接调用
cursor
strcur
is
select nowStr from Table (GetSubStr( ' 111,222,333,,, ' , ' , ' ));
select nowStr from Table (GetSubStr( ' 111,222,333,,, ' , ' , ' ));
(3)以管道形式输出:
create
type row_type
as
object(a
varchar2
(
10
), v
varchar2
(
10
));
--
定义行对象
create type table_type as table of row_type; -- 定义表对象
create or replace function test_fun(
a in varchar2 ,b in varchar2
)
return table_type pipelined
is
v row_type; -- 定义v为行对象类型
begin
for thisrow in ( select a, b from mytable where col1 = a and col2 = b) loop
v : = row_type(thisrow.a, thisrow.b);
pipe row (v);
end loop;
return ;
end ;
select * from table (test_fun( ' 123 ' , ' 456 ' ));
create type table_type as table of row_type; -- 定义表对象
create or replace function test_fun(
a in varchar2 ,b in varchar2
)
return table_type pipelined
is
v row_type; -- 定义v为行对象类型
begin
for thisrow in ( select a, b from mytable where col1 = a and col2 = b) loop
v : = row_type(thisrow.a, thisrow.b);
pipe row (v);
end loop;
return ;
end ;
select * from table (test_fun( ' 123 ' , ' 456 ' ));