当表名是参数时，Oracle 使用动态 sql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20839882/
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 using dynamic sql when table name is a parameter
提问by Refael Cohen
I have a SQL query (a store procedure ) that i want to convert to PLSQL I already conver most of the store procedure but i cant convert the following part :
我有一个 SQL 查询（一个存储过程），我想转换为 PLSQL 我已经转换了大部分存储过程，但我无法转换以下部分：
DECLARE lookupTableRow CURSOR FOR
SELECT TableName FROM SYS_LookUpTable
OPEN lookupTableRow
FETCH NEXT FROM lookupTableRow INTO @tableName
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql='SELECT * FROM '+@tableName
EXECUTE sp_executesql @sql
IF @counter=0
BEGIN
INSERT INTO T_TABLE_MAPPING VALUES('P_MAIN_METADATA', 'Table', @tableName)
END
ELSE
BEGIN
INSERT INTO T_TABLE_MAPPING
VALUES( 'P_MAIN_METADATA',
'Table' + CONVERT(NVARCHAR(10),@counter),
@tableName)
END
SET @counter=@counter+1
FETCH NEXT FROM lookupTableRow INTO @tableName
END
CLOSE lookupTableRow
DEALLOCATE lookupTableRow
As i understand i can't use ORACLE dynamic sql (execute immediate) when the table name is a parameter.
据我了解，当表名是参数时，我不能使用 ORACLE 动态 sql（立即执行）。
Furthermore when i execute this dynamic query in my SQL store procedure each SELECT statement return me as a result the relevant table rows , those result are different in each loop .
此外，当我在我的 SQL 存储过程中执行此动态查询时，每个 SELECT 语句都会返回相关表行，这些结果在每个循环中都不同。
Please advice for any solution * how can i use dynamic sql with table name as parameter ? * how can i use a "dynamic" cursor, in order to be able to display the dynamic results ?
请为任何解决方案提供建议 * 我如何使用表名作为参数的动态 sql？* 我如何使用“动态”光标，以便能够显示动态结果？
Thanks for the advice
感谢您的建议
回答by haki
If the only thing you don't know how to do here is using a table name dynamically then this is how you can accomplish that
如果您唯一不知道如何在这里做的是动态使用表名，那么这就是您可以做到的
1 declare
2 n number;
3 table_name varchar2(30) := 'dual';
4 begin
5 execute immediate 'select count(*) from ' || table_name into n;
6 dbms_output.put_line(n);
7* end;
SQL> /
1
回答by xQbert
If you're simply trying to populate a table that obfuscates table names to something else, why do it sequentially forcing the db to change contexts and slow down?
如果您只是想填充一个将表名混淆为其他内容的表，为什么要按顺序强制数据库更改上下文并减慢速度？
Create table t_table_mapping as
( Select 'P_MAIN_METADATA' "P_MAIN_METADATA",
'Table' || to_char(rownum) as "Table", table_name
from all_tables)
However this doesn't take into account schema names so what owner. So you need to add a where clause to limit to the specific schema you're wanting to do this for.
但是，这并没有考虑架构名称，因此所有者是什么。因此，您需要添加一个 where 子句来限制您要为其执行此操作的特定模式。
