oracle 在 PL/SQL 中，我可以通过存储过程参数传递游标 FROM 子句的表模式吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/278882/
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
In PL/SQL, can I pass the table schema of a cursor FROM clause via a stored procedure parameter?
提问by Jordan Parmer
In PL/SQL, I would like to pass in a "source" schema as a parameter to a stored procedure. For instance:
在 PL/SQL 中，我想将“源”模式作为参数传递给存储过程。例如：
BEGIN
CURSOR my_cursor IS
SELECT my_field FROM <schema>.my_table
...
I want the 'schema' value to come from an input parameter into the stored procedure. Does anyone know how I could do that?
我希望“模式”值来自存储过程的输入参数。有谁知道我怎么能做到这一点？
P.S. Sorry if this is a stupid simple question, but I'm new to PL/SQL and must get some functions written quickly.
PS 对不起，如果这是一个愚蠢的简单问题，但我是 PL/SQL 的新手，必须快速编写一些函数。
回答by Dave Costa
In addition to what Mark Brady said, another dynamic SQL option is to use a REF CURSOR. Since your sample code includes a cursor this would be the most relevant.
除了 Mark Brady 所说的，另一个动态 SQL 选项是使用 REF CURSOR。由于您的示例代码包含一个游标，这将是最相关的。
PROCEDURE select_from_schema( the_schema VARCHAR2)
IS
TYPE my_cursor_type IS REF CURSOR;
my_cursor my_cursor_type;
BEGIN
OPEN my_cursor FOR 'SELECT my_field FROM '||the_schema||'.my_table';
-- Do your FETCHes just as with a normal cursor
CLOSE my_cursor;
END;
回答by Jordan Parmer
This has to be done with dynamic sql.
这必须使用动态 sql 来完成。
Either the DBMS_SQL package or the Execute Immediate statement.
DBMS_SQL 包或执行立即语句。
You can't use variables in the FROM clause.
不能在 FROM 子句中使用变量。
A potential solution may be to
一个潜在的解决方案可能是
ALTER SESSION SET Current_Schema = '' <-- the schema you want.
ALTER SESSION SET Current_Schema = '' <-- 你想要的模式。
That command changes the default schema. SO if you have a bunch of identically named tables you can save yourself dynamic SQL and make a Dynamic Alter Session.
该命令更改默认架构。所以如果你有一堆同名的表，你可以保存自己的动态 SQL 并创建一个动态更改会话。
