Oracle 中是否可以将表名作为参数传递？

Question

我想创建一个像这样的存储过程：

PROCEDURE P_CUSTOMER_UPDATE
  (
      pADSLTable IN Table,
      pAccountname IN NVARCHAR2,
      pStatus IN NUMBER,
      pNote IN NVARCHAR2,
      pEmail IN NVARCHAR2,
      pMobi IN NVARCHAR2,
      pServiceTypeID IN NUMBER,
      pDate IN DATE
  )
  IS
  BEGIN
      UPDATE pADSLTable
      SET STATUS = pStatus, NOTE = pNote, EMAIL = pEmail, MOBI = pMobi, SERVICETYPE_ID = pServiceTypeID, ACTIVATION_DATE = pDate
      WHERE ACCOUNT_NAME = pAccountname;
  END;

当然，Oracle 不允许我这样做。有办法解决这个问题吗？非常感谢。

Answer 1

您有几个不同的表，它们具有完全相同的列名和数据类型？闻起来像是一个狡猾的设计。

无论如何，我们不能像这样在简单的 SQL 中使用变量作为数据库对象。我们必须使用动态SQL。

PROCEDURE P_CUSTOMER_UPDATE
  (
      pADSLTable IN USER_TABLES.table_name%type,
      pAccountname IN NVARCHAR2,
      pStatus IN NUMBER,
      pNote IN NVARCHAR2,
      pEmail IN NVARCHAR2,
      pMobi IN NVARCHAR2,
      pServiceTypeID IN NUMBER,
      pDate IN DATE
  )
  IS
  BEGIN
      execute immediate 
          'UPDATE '||pADSLTable
          ||' SET STATUS = :1, NOTE = :2, EMAIL = :3, MOBI = :4, SERVICETYPE_ID = :5, ACTIVATION_DATE = :6'
          ||' WHERE ACCOUNT_NAME = :7'
      using pStatus, pNote, pEmail, pMobi, pServiceTypeID, pDate, pAccountname;
  END;

避免使用动态 SQL 的原因之一是它很容易被滥用。恶意者可以使用这些参数来尝试绕过我们的安全措施。这称为 SQL 注入。我认为人们高估了 SQL 注入的重要性。这不会自动成为威胁。例如，如果该过程是包中的私有过程（即未在规范中声明），则任何人都不太可能劫持它。

但采取预防措施是明智的。 DBMS_ASSERT 是 Oracle 10g 中引入的一个包，用于捕获尝试的 SQL 注入攻击。在这种情况下，值得使用它来验证传递的表名。

....
'UPDATE '|| DBMS_ASSERT.simple_sql_name(pADSLTable)
....  

这将阻止任何人传递 'pay_table set salary = salary * 10 where id = 1234 --' 作为表名参数。

避免动态 SQL 的另一个原因是它更难正确执行并且更难调试。实际语句的语法仅在运行时检查。最好有一套完整的单元测试来验证所有传递的输入，以确保该过程不会引发语法异常。

最后，此类动态 SQL 不会显示在 ALL_DEPENDENCIES 等视图中。这使得进行影响分析和查找使用给定表或列的所有程序变得更加困难。

Answer 2

是的，有原生动态 SQL：

EXECUTE IMMEDIATE 'UPDATE ' || pADSLTable || 
  'SET STATUS = :1, NOTE = :2, EMAIL = :3, MOBI = :4, SERVICETYPE_ID = :5, ACTIVATION_DATE = :6 WHERE ACCOUNT_NAME = :7 '  
  USING pStatus, pNote, pEmail, pMobi, pServiceTypeId, pDate, pAccountname;

性能和错误检查不太好（没有编译时语法和架构验证）。
谨防 SQL 注入。

因此，如果您只有几个表可供选择，请考虑使用带有所有选项的 if/then/else 结构。

Answer 3

您可以使用动态 SQL 来使用各种 DDL 语句。您可以将不同数据库对象的名称作为参数传递或在变量中进行操作。