在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不允许我这样做。有没有解决这个问题的方法？非常感谢。

- Quan Mai

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之类的视图中。这使得进行影响分析并定位使用给定表或列的所有程序更加困难。

- APC

---

DBMS_ASSERT虽然存在于10gR2中，但没有文档记录。感谢您指出这一点。 - Adam Musch

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结构并列出所有选项。

- Thilo

Answer 3

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

- Vishnu Gupta