oracle oracle中如何从另一个存储过程调用一个存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4155836/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to call a stored procedure from another stored procedure in oracle
提问by Reeth
I have an existing stored procedure that takes 2 parameters and returns back an oracle cursor. The cursor contains about 30 to 60 rows of data.
我有一个现有的存储过程，它接受 2 个参数并返回一个 oracle 游标。游标包含大约 30 到 60 行数据。
I want to use the above pre-existing stored procedure in another stored procedure as a table...basically I want to call the pre-existing stored procedure and see if the rows returned back contain a particular value.
我想在另一个存储过程中使用上述预先存在的存储过程作为表......基本上我想调用预先存在的存储过程并查看返回的行是否包含特定值。
For example:
例如：
SP 1 = get_data_1 (returns oracle cursor)
SP 2 = get_data_2
in get_data_2
在 get_data_2 中
select count(*) from get_data_1 (pass_input_parms) A where A.ID = '12345'
Conceptually it seems like a trivial thing to do to me however, being new to the oracle world I do not know how to make use of preexisting stored procedures that return cursors.
从概念上讲，这对我来说似乎是一件微不足道的事情，但是作为 Oracle 世界的新手，我不知道如何利用预先存在的返回游标的存储过程。
How would I do this?
我该怎么做？
回答by Justin Cave
You cannot reuse a REF CURSOR from get_data_1 in a subsequent SQL statement because it's just a pointer to a statement handle. The cursor itself contains no data.
您不能在后续 SQL 语句中重用来自 get_data_1 的 REF CURSOR，因为它只是一个指向语句句柄的指针。游标本身不包含任何数据。
You could do something like
你可以做类似的事情
CREATE PROCEDURE get_data_2( p_cnt OUT NUMBER )
AS
l_rc <<your cursor type>>;
l_rec <<the type your cursor returns>>;
BEGIN
get_data_1(<<parameter 1>>, <<parameter 2>>, l_rc);
p_cnt := 0;
LOOP
FETCH l_rc INTO l_rec;
EXIT WHEN l_rc%NOTFOUND;
IF( l_rec.id = '12345' )
THEN
p_cnt := p_cnt + 1;
END IF;
END LOOP;
CLOSE l_rc;
END;
As you might imagine, though, this tends to get old relatively quickly. Given that, it tends not to be common in Oracle to have stored procedures that return REF CURSOR parameters except in cases where you are returning a finished view of the data to a client application. If there was a shared view, for example, that both GET_DATA_1 and GET_DATA_2 could query rather than having GET_DATA_2 call GET_DATA_1, that would simplify the program. If GET_DATA_1 was a pipelined table function rather than a procedure that returned a REF CURSOR, then it would be much easier to call GET_DATA_1 from GET_DATA_2.
但是，正如您可能想象的那样，这往往会相对较快地老化。鉴于此，在 Oracle 中具有返回 REF CURSOR 参数的存储过程并不常见，除非您将数据的完成视图返回给客户端应用程序。例如，如果有一个共享视图，GET_DATA_1 和 GET_DATA_2 都可以查询而不是让 GET_DATA_2 调用 GET_DATA_1，这将简化程序。如果 GET_DATA_1 是一个流水线表函数而不是一个返回 REF CURSOR 的过程，那么从 GET_DATA_2 调用 GET_DATA_1 会容易得多。
If you want to get started with pipelined table functions (using the SCOTT schema)
如果您想开始使用流水线表函数（使用 SCOTT 模式）
create or replace type emp_obj as object (
empno number,
ename varchar2(10),
job varchar2(9),
mgr number,
hiredate date );
/
create type emp_tbl
as
table of emp_obj;
/
create function emp_pipe( p_deptno IN NUMBER )
return emp_tbl pipelined
is
begin
FOR x IN (SELECT * FROM emp WHERE deptno = p_deptno)
LOOP
PIPE ROW( emp_obj( x.empno,
x.ename,
x.job,
x.mgr,
x.hiredate ) );
END LOOP;
END;
/
SQL> select * from table( emp_pipe( 10 ) );
EMPNO ENAME JOB MGR HIREDATE
---------- ---------- --------- ---------- ---------
7782 CLARK MANAGER 7839 09-JUN-81
7839 KING PRESIDENT 17-NOV-81
7934 MILLER CLERK 7782 23-JAN-82
回答by vaskin
At this point, you might as well define a new package and learn how to use cursor loops to deal with this, it will give you more programmatic control. PL/SQL is what you need to look up.
此时，你不妨定义一个新的包并学习如何使用游标循环来处理这个问题，它会给你更多的编程控制。PL/SQL 是您需要查找的内容。
