表名称为参数时,Oracle使用动态SQL [英] Oracle using dynamic sql when table name is a parameter

查看:53
本文介绍了表名称为参数时,Oracle使用动态SQL的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个要转换为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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆