Oracle 存储过程返回结果集
1. Oracle 存储过程返回结果集
2.
3. 过程返回记录集：
4. CREATE OR REPLACE
5. AS
6. IS REF CURSOR;
7.
8. PROCEDURE get (p_id NUMBER, p_rc OUT
9. END
10. /
11.
12. CREATE OR REPLACE
13. AS
14. PROCEDURE get (p_id NUMBER, p_rc OUT
15. IS
16. sqlstr VARCHAR2 (500);
17. BEGIN
18. THEN
19. OPEN p_rc FOR
20. SELECT ID, NAME, sex, address, postcode, birthday
21. FROM
22. ELSE
23. sqlstr :=
24. select id,name,sex,address,postcode,birthday
25. from student where
26. OPEN p_rc FOR
27. END
28. END
29. END
30. /
31.
32. 函数返回记录集:
33. 建立带ref cursor定义的包和包体及函数：
34. CREATE OR REPLACE
35. package pkg_test as
36. /* 定义ref cursor类型
37. return类型，为弱类型，允许动态sql查询，
38. 否则为强类型，无法使用动态sql查询;
39. */
40. is ref cursor;
41.
42. --函数申明
43. function get(intID number) return
44. end
45. /
46.
47. CREATE OR REPLACE
48. package body pkg_test as
49. --函数体
50. function get(intID number) return myrctype is
51. --定义ref cursor变量
52. sqlstr varchar2(500);
53. begin
54. then
55. --静态测试，直接用select语句直接返回结果
56. open rc for select id,name,sex,address,postcode,birthday from
57. else
58. --动态sql赋值，用:w_id来申明该变量从外部获得
59. 'select id,name,sex,address,postcode,birthday from student where id=:w_id';
60. --动态测试，用sqlstr字符串返回结果，用using关键词传递参数
61. open rc for
62. end
63.
64. return
65. end
66.
67. end
68. /
69.
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
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
;
(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 ;
在PL/SQL developer中可以直接调用
cursor strcur is
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
'
));
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝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; 之后就不会显示两次了
