在 Oracle 中生成动态 SQL

我的plsql代码有问题，几乎尝试了所有方法。现在我失去了解决问题的理智和能力:)

情况是我想在所有表架构中搜索分配给变量v_ss的特定字符串并将其打印到DBMS_OUTPUT。我知道对于这种情况有现成的解决方案，但我想自己编码。下面的代码给了我一个错误，即我的v_stmt中"表不存在"。我假设这个选择不能识别rec.column_name，但为什么呢？

这是我的代码：

DECLARE
 v_stmt VARCHAR2(1000);
 v_ss VARCHAR2(30) := 'Argentina';
 v_own ALL_TAB_COLUMNS.OWNER%TYPE;
 v_tab_nam ALL_TAB_COLUMNS.TABLE_NAME%TYPE;
 v_col_nam ALL_TAB_COLUMNS.COLUMN_NAME%TYPE;
 CURSOR cur_asc IS
   SELECT t.owner, t.table_name, t.column_name
   FROM SYS.ALL_TAB_COLUMNS t
   WHERE t.OWNER LIKE 'HR'
     AND t.DATA_TYPE LIKE 'VARCHAR2';

BEGIN
  FOR rec IN cur_asc LOOP
    v_stmt := 'SELECT rec.owner, rec.table_name, rec.column_name FROM rec.table_name WHERE rec.column_name LIKE :1';
    EXECUTE IMMEDIATE v_stmt INTO v_own, v_tab_nam, v_col_nam USING v_ss;
    DBMS_OUTPUT.put_line(v_own || ':' || v_tab_nam || ':' || v_col_nam);
  END LOOP;

END;
/
Error report -
ORA-00942: table or view does not exist
ORA-06512: at line 19
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

你能给我一个关于我的错误以及如何解决它的解释吗？提前致谢

动态语句是在看不到PL/SQL变量的上下文中执行的，因此当它运行时rec.table_name等被解释为SQL级对象 - 不存在。

您必须将变量值连接到 from 和 where 子句的动态语句中;您可以在选择列表中执行相同的操作（尽管它们需要用转义的单引号括起来，因为它们是字符串），或者在那里使用绑定变量：

v_stmt := 'SELECT :owner, :table_name, :column_name FROM '
  || rec.table_name || ' WHERE ' || rec.column_name || ' LIKE :ss';
EXECUTE IMMEDIATE v_stmt INTO v_own, v_tab_nam, v_col_nam
USING rec.owner, rec.table_name, rec.column_name, v_ss;

不能对对象标识符使用绑定变量，因此需要对这些部分进行串联。

除非您以 HR 用户身份运行它，在这种情况下，您可以使用 user_tables 而不是 all_tables ，您还需要在查询中指定架构（如 Tony 和 Lalit 所述）;硬编码 HR 或使用查询的所有者：

v_stmt := 'SELECT :owner, :table_name, :column_name FROM '
  || rec.owner || '.' || rec.table_name
  || ' WHERE ' || rec.column_name || ' LIKE :ss';

对于所有不包含一个匹配值的表，这将导致错误 - 如果动态选择获得零行或多行。但这是一个单独的问题。

v_stmt ：= '从rec.table_name中选择 rec.owner， rec.table_name， rec.column_name rec.column_name 喜欢：1';

动态 sql 语句格式不正确。如果将变量括在单引号之间，则它们将被视为文字而不是变量。
必须在架构前面加上架构前缀table_name否则，您必须在以用户身份连接时运行脚本HR。

v_stmt ：= "选择"||rec.owner||'''， '''||rec.table_name||'''， '                     ||rec.column_name||'从 '||rec.owner||'."                     ||rec.table_name||'其中 '||rec.column_name||'喜欢 ：1';

永远记住，每当使用动态语句时，请始终使用 DBMS_OUTPUT 首先验证正在生成的实际 SQL。这是调试动态查询的最佳方式。

您需要处理NO_DATA_FOUND异常，因为它会为所有与您正在使用的过滤器不匹配的表抛出错误。

SQL>声明  2 v_stmt VARCHAR2（1000）;  3 v_ss VARCHAR2（30） ：= '阿根廷';  4 v_own ALL_TAB_COLUMNS。所有者%类型;  5 v_tab_nam ALL_TAB_COLUMNS。TABLE_NAME%类型;  6 v_col_nam ALL_TAB_COLUMNS。COLUMN_NAME%类型;  7 光标cur_asc  8 是  9 选择所有者， 10 t.table_name， 11 t.column_name 12 来自系统。ALL_TAB_COLUMNS 吨 13 T.所有者喜欢"HR"的地方 14 t.DATA_TYPE像"VARCHAR2"; 15 开始 16 表示 REC IN cur_asc 17 循环 18 v_stmt ：= '选择 '''||rec.owner||'''， '''||rec.table_name||'''， ' 19 ||rec.column_name||'从 '||rec.owner||'." 20 ||rec.table_name||'其中 '||rec.column_name||'喜欢 ：1'; 21 开始 22 立即执行v_stmt v_own， 23 v_tab_nam， 24 v_col_nam使用v_ss; 25 DBMS_OUTPUT.put_line（v_own ||':' ||v_tab_nam ||':' ||v_col_nam）; 26 例外 27 当NO_DATA_FOUND那时 28 空; 29 结束; 30端环; 31 完; 32   /人力资源：国家：阿根廷PL/SQL 过程已成功完成。

这里有两个问题：

您需要将表名和列名连接到动态 SQL 中，因为"SELECT rec.owner..."正在尝试从别名为 rec 的表中选择名为 owner 的列，它不引用您的 for 循环记录。
由于表中可能没有匹配的行或许多匹配的行，因此不能使用 select into ，需要使用游标。

试试这个：

DECLARE
 v_stmt VARCHAR2(1000);
 v_ss VARCHAR2(30) := 'Argentina';
 v_own ALL_TAB_COLUMNS.OWNER%TYPE;
 v_tab_nam ALL_TAB_COLUMNS.TABLE_NAME%TYPE;
 v_col_nam ALL_TAB_COLUMNS.COLUMN_NAME%TYPE;
 CURSOR cur_asc IS
   SELECT t.owner, t.table_name, t.column_name
   FROM ALL_TAB_COLUMNS t
   WHERE t.DATA_TYPE LIKE 'VARCHAR2'
   AND ROWNUM < 10;
 c SYS_REFCURSOR;
BEGIN
  FOR rec IN cur_asc LOOP
    v_stmt := 'SELECT ''' || rec.owner || ''',''' || rec.table_name || ''', ''' || rec.column_name || ''' FROM ' || rec.table_name || ' WHERE ' || rec.column_name || ' LIKE :1';
    OPEN c FOR v_stmt USING v_ss;
    LOOP
      FETCH c INTO v_own, v_tab_nam, v_col_nam;
      EXIT WHEN c%NOTFOUND;
      DBMS_OUTPUT.put_line(v_own || ':' || v_tab_nam || ':' || v_col_nam);
    END LOOP;
    CLOSE c;
  END LOOP;
END;
/

相关内容

最新更新

热门标签：