Oracle PLSQL - 在不存在的表上声明游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1942072/
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
Oracle PLSQL - Declare a cursor on a non-existing table
提问by Tom
I want to declare a cursor on a table that does not exist. Of course, my procedure doesnt compile.
我想在一个不存在的表上声明一个游标。当然，我的程序无法编译。
This table is a temporary table, and is created by a pre process. It will exist on runtime, but at compile time its another story.
该表是临时表，由预处理创建。它将在运行时存在，但在编译时则是另一回事。
For my select / updates an other DML operations, I've used
对于我的选择/更新其他 DML 操作，我使用了
EXECUTE IMMEDIATE 'operation from tmp_table'
EXECUTE IMMEDIATE 'operation from tmp_table'
but I can't find a workaround for cursors.
但我找不到游标的解决方法。
Is there a way?
有办法吗？
Basically, i want this to compile
基本上，我希望这个编译
drop table test;
/*from this on should compile*/
DECLARE
cursor c is select * from test;
BEGIN
for reg in c LOOP
/*...*/
END LOOP;
END;
update
更新
So far not compiling:
到目前为止还没有编译：
SQL> declare
2 c sys_refcursor;
3 BEGIN
4 open c for 'select * from pepito'; -- 'pepito' does not exist
5 close c;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 4
Should use CREATE PROCEDURE, thanks.
应该使用 CREATE PROCEDURE，谢谢。
Thanks in advance.
提前致谢。
采纳答案by Peter Lang
You should be able to define your cursor like this:
您应该能够像这样定义光标：
DECLARE
c SYS_REFCURSOR;
BEGIN
OPEN c FOR 'SELECT * FROM dual';
CLOSE c;
END;
You can also bind arguments:
您还可以绑定参数：
OPEN c FOR 'SELECT * FROM dual WHERE DUMMY = :1' USING 'X';
For further information see the Oracle documentation of the OPEN-FOR Statement.
有关更多信息，请参阅OPEN-FOR 语句的 Oracle 文档。
Example using a stored procedure
使用存储过程的示例
CREATE OR REPLACE PROCEDURE test IS
c SYS_REFCURSOR;
BEGIN
OPEN c FOR 'SELECT * FROM fdfdfdfdfd';
CLOSE c;
END;
/
回答by David Aldridge
Creating temporary tables as required is usually not considered good practice in Oracle, where Global Temporary Tables are better and would not cause this problem
根据需要创建临时表在 Oracle 中通常不被认为是好的做法，其中全局临时表更好，不会导致此问题
回答by Erich Kitzmueller
You can use DBMS_SQL to get even more flexibility than the ref cursor method described by Peter Lang. But it means more work, too.
您可以使用 DBMS_SQL 获得比 Peter Lang 描述的引用游标方法更多的灵活性。但这也意味着更多的工作。
