使用选择查询在另一个存储过程中执行存储过程 [英] Executing a stored procedure inside another stored procedure using Select Query

问题描述

我想在自己的存储过程内的表的每一行上调用存储过程.在SQL Developer工作区中，我可以像这样简单地调用它:

I want to call a stored procedure on every row of a table inside my own stored procedure. In SQL Developer workspace I can simple call it like this:

SELECT my_stored_proc(...)
FROM my_table

但是在我的存储过程中，这似乎没有执行:

However in my stored procedure this doesn't seem to execute:

stmt := 'SELECT my_stored_proc(...) FROM my_table';
EXECUTE IMMEDIATE stmt;

这确实有效:

DECLARE
l_cursor sys_refcursor;

BEGIN
OPEN l_cursor FOR stmt;
LOOP
  FETCH l_cursor INTO ...;
  EXIT WHEN l_cursor%NOTFOUND;
  my_stored_proc(...);
END LOOP;
...

我的问题是为什么会这样.

My question is why is this happening.

推荐答案

第二个示例中的行为已明确涵盖

The behaviour in your second example is explicitly covered in a prominent note in the documentation:

如果 dynamic_sql_statement 是SELECT语句，而您同时省略了 into_clause 和 bulk_collect_into_clause ，则 execute_immediate_statement 永远不会执行.例如，此语句从不增加序列:

If dynamic_sql_statement is a SELECT statement, and you omit both into_clause and bulk_collect_into_clause, then execute_immediate_statement never executes. For example, this statement never increments the sequence:

EXECUTE IMMEDIATE 'SELECT S.NEXTVAL FROM DUAL'

本质上是您在做什么.因为您没有选择过程的结果(或者实际上是您的函数)放入一个变量，所以不会执行查询，因此不会调用该函数.

Which is essentially what you are doing. Because you don't select the result of your procedure (or, in fact your function) into a variable, the query is not executed, so the function is not called.

由于表中有多行，因此您无法选择标量变量，尽管您可以将collect批量收集为一个collection.或像在第三个示例中一样使用光标. (尽管，如果用同一条语句打开游标，您似乎在每行两次调用该函数，一次是在执行查询时，另一次是在PL/SQL循环中.我可能读得太多了进入您人为的示例...)

As you have multiple rows in your table you can't select into a scalar variable, though you could bulk collect into a collection; or use a cursor as you do in your third example. (Although, if the cursor is being opened with the same statement, you seem to be calling the function twice for each row - once when the query is executed, and then again inside the PL/SQL loop. I'm probably reading too much into your contrived example...)

这篇关于使用选择查询在另一个存储过程中执行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！