如何從存儲過程返回多行？ (Oracle PL / SQL)

我想用一個(gè)參數(shù)創(chuàng)建一個(gè)存儲過程，該存儲過程將根據(jù)參數(shù)返回不同的記錄集。 這是怎么做的？ 我可以從普通SQL中調(diào)用它嗎？

5個(gè)解決方案

65 votes

這是如何構(gòu)建一個(gè)函數(shù)，該函數(shù)返回可以像表一樣查詢的結(jié)果集：

SQL> create type emp_obj is object (empno number, ename varchar2(10));

2 /

Type created.

SQL> create type emp_tab is table of emp_obj;

2 /

Type created.

SQL> create or replace function all_emps return emp_tab

2 is

3 l_emp_tab emp_tab := emp_tab();

4 n integer := 0;

5 begin

6 for r in (select empno, ename from emp)

7 loop

8 l_emp_tab.extend;

9 n := n + 1;

10 l_emp_tab(n) := emp_obj(r.empno, r.ename);

11 end loop;

12 return l_emp_tab;

13 end;

14 /

Function created.

SQL> select * from table (all_emps);

EMPNO ENAME

---------- ----------

7369 SMITH

7499 ALLEN

7521 WARD

7566 JONES

7654 MARTIN

7698 BLAKE

7782 CLARK

7788 SCOTT

7839 KING

7844 TURNER

7902 FORD

7934 MILLER

Tony Andrews answered 2020-06-30T04:27:23Z

22 votes

我認(rèn)為您想返回一個(gè)REFCURSOR：

create function test_cursor

return sys_refcursor

is

c_result sys_refcursor;

begin

open c_result for

select * from dual;

return c_result;

end;

更新：如果需要從SQL調(diào)用此函數(shù)，請使用建議使用的表函數(shù)，例如@Tony Andrews。

Thilo answered 2020-06-30T04:27:48Z

8 votes

您可以使用Oracle流水線函數(shù)

基本上，當(dāng)您希望將PLSQL(或Java或C)例程作為“源”時(shí) 數(shù)據(jù)-而不是表-您將使用流水線函數(shù)。

簡單示例-生成一些隨機(jī)數(shù)據(jù)

如何根據(jù)輸入?yún)?shù)創(chuàng)建N個(gè)唯一的隨機(jī)數(shù)？

create type array

as table of number;

create function gen_numbers(n in number default null)

return array

PIPELINED

as

begin

for i in 1 .. nvl(n,999999999)

loop

pipe row(i);

end loop;

return;

end;

假設(shè)我們需要三行內(nèi)容。 現(xiàn)在，我們可以通過以下兩種方式之一進(jìn)行操作：

select * from TABLE(gen_numbers(3));

COLUMN_VALUE

1

2

3

要么

select * from TABLE(gen_numbers)

where rownum <= 3;

COLUMN_VALUE

1

2

3

管道功能1管道功能2

Mohsen Heydari answered 2020-06-30T04:28:45Z

3 votes

如果要在普通SQL中使用它，我將讓存儲過程使用結(jié)果行填充表或臨時(shí)表(或使用@Tony Andrews方法)。

如果要使用@Thilo的解決方案，則必須使用PL / SQL循環(huán)游標(biāo)。這是一個(gè)示例：(我使用了過程而不是函數(shù)，就像@Thilo一樣)

create or replace procedure myprocedure(retval in out sys_refcursor) is

begin

open retval for

select TABLE_NAME from user_tables;

end myprocedure;

declare

myrefcur sys_refcursor;

tablename user_tables.TABLE_NAME%type;

begin

myprocedure(myrefcur);

loop

fetch myrefcur into tablename;

exit when myrefcur%notfound;

dbms_output.put_line(tablename);

end loop;

close myrefcur;

end;

John Smithers answered 2020-06-30T04:29:10Z

1 votes

create procedure (p_cur out sys_refcursor) as begin open p_cur for select * from end;

S. Mayol answered 2020-06-30T04:29:26Z

總結(jié)

以上是生活随笔為你收集整理的oracle存储过程多分支怎样写,如何从存储过程返回多行？ （Oracle PL / SQL）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。