从oracle存储过程返回多行 [英] returning multiple rows from an oracle stored procedure

问题描述

Hello All，



我创建了一个程序来显示表中的行，如下所示：

Hello All,

I have created a procedure to display rows from a table like following:

CREATE PROCEDURE SPGETDEPT
(P_CUR OUT SYS_REFCURSOR)
AS
BEGIN
OPEN P_CUR FOR
SELECT * FROM DEPT;
END;
/

并运行它：

and to run it:

var cur REFCURSOR
exec spgetdept(:cur);
print cur;

我的问题是如何俱乐部以上两部分，以便只通过执行一个程序就可以获得输出：

My question is how to club the above two parts so that output can be obtained by executing one procedure only:

execute newStoredProcedure;

非常感谢任何帮助或想法....

谢谢...... !!

Any help or idea is highly appreciated....
Thank you...!!

推荐答案

如果我正确理解你的问题，而不是使用存储过程，我会使用表函数，如果数据量较大，可能会流水线化。表函数可以返回一个也可以在查询中使用的结果集。



查看主题：

- 函数返回结果集

- 创建流水线功能

创建Oracle函数的示例 [ ^ ]

If I understand your question correctly, instead of using a stored procedure, I'd go with a table function, possibly pipelined if the amount of data is larger. A table function can return a result set that can be used also in a query.

Have a look at topics:
- Function Returning a Result Set
- Creating a Pipelined Function
in Examples for Creating Oracle Functions[^]

嘿..我尝试过这样的事情：

hey..i tried something like this:

CREATE OR REPLACE PROCEDURE sp AS
DEPT_REC     tbldept%TABLETYPE;
CURSOR p_cur IS SELECT * FROM tbldept;
BEGIN
OPEN p_cur;
DBMS_OUTPUT.PUT_LINE('HI');
LOOP
    FETCH p_cur INTO DEPT_REC;
    EXIT WHEN p_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(DEPT_REC);            
  END LOOP;
CLOSE p_cur; 
END;

输出正如预期的那样，但是列标题不会出现输出..我想这不符合我的目的......

output is coming as expected but the column headers are not coming in output..i guess this wont serve my purpose...

这篇关于从oracle存储过程返回多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！