表名称为参数时,Oracle使用动态SQL [英] Oracle using dynamic sql when table name is a parameter
问题描述
我有一个要转换为PLSQL的SQL查询(存储过程)我已经转换了大多数存储过程,但是无法转换以下部分:
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 :
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
据我了解,当表名是参数时,我无法使用ORACLE动态sql(立即执行).
As i understand i can't use ORACLE dynamic sql (execute immediate) when the table name is a parameter.
此外,当我在SQL存储过程中执行此动态查询时,每个SELECT语句将相关的表行返回给我,这些结果在每个循环中都是不同的.
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?*我如何使用动态"光标,以便能够显示动态结果?
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 ?
感谢您的建议
推荐答案
如果您唯一不知道如何做的是动态使用表名,那么这就是您可以完成的操作
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
这篇关于表名称为参数时,Oracle使用动态SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!