oracle - PL/SQL : How to pass a tablename to a dynamic SQL?

Question

我是 PL/SQL 新手，我尝试通过参数动态设置 SELECT 的表名。

这工作正常。

DECLARE 
  FUNCTION foo (pat VARCHAR) RETURN NUMBER IS
    tabname VARCHAR (100) := 'my_table';
    n NUMBER := -1;
    sqlcmd VARCHAR (100) := 'SELECT COUNT(*) FROM ' || tabname || ' WHERE bezeichnung LIKE :1';
  BEGIN
    EXECUTE IMMEDIATE sqlcmd INTO n USING pat;
    RETURN n;
  END foo;
BEGIN
    dbms_output.put_line (foo ('bla%'));
END;

如果我尝试通过参数设置 tabname 就像使用 pat 一样，那么它会失败并出现错误:

invalid table name

DECLARE

  FUNCTION defval (pat VARCHAR, offs NUMBER) RETURN NUMBER IS
    tabname VARCHAR (100) := 'A_KGL_EIGENSCHAFTEN';
    n NUMBER := -1;
    sqlcmd VARCHAR (100) := 'SELECT COUNT(*) FROM :1 WHERE bezeichnung LIKE :2';
  BEGIN     
    EXECUTE IMMEDIATE sqlcmd INTO n USING tabname, pat;
    dbms_output.put_line ('tabname: ' || tabname);
    dbms_output.put_line ('n: ' || n);
    RETURN n;
  END defval;
BEGIN
    dbms_output.put_line (defval ('LPG.GAX.%.DBE', 2));
END;

如何通过这个绑定(bind)参数设置表名？

Answer 1

如果您希望使用连接来防止 sql 注入(inject)，那么您可以使用 sys.DBMS_ASSERT.SQL_OBJECT_NAME(p_table_name)

所以你的字符串变量应该是这样的:

sqlcmd VARCHAR (100) := 'SELECT COUNT(*) FROM ' 
                        || sys.DBMS_ASSERT.SQL_OBJECT_NAME(tabname) 
                        || ' WHERE bezeichnung LIKE :1';

您可以从 oracle documentation 了解有关 DBMS_ASSERT 的更多信息.