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

2021-12-20 00:00:00 sql oracle stored-procedures plsql

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

I want to create a stored procedure like this:

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 不允许我这样做.有没有办法解决这个问题?非常感谢.

Of course, Oracle does not let me do that. Is there a way to work around this problem? Thank you very much.

推荐答案

您有几个具有完全相同的列名和数据类型的不同表?闻起来像一个狡猾的设计.

You have several different tables with exactly the same column names and data types? Smells like a dodgy design.

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

Anyway, we cannot use variables as database objects in straightforward SQL like that. We have to use dynamic 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 注入的重要性.它不会自动构成威胁.例如，如果该过程是包中的私有过程(即未在规范中声明)，那么任何人都不太可能劫持它.

One reason to avoid the use of dynamic SQL is that it is open to abuse. Malicious people can use the parameters to attempt to bypass our security. This is called SQL injection. I think people over estimate the significance of SQL injection. It's not automatically a threat. For instance if the procedure is a private procedure in a package (i.e. not declared in the specification) it is unlikely that anybody will hijack it.

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

But it is sensible to take precautions. DBMS_ASSERT is a package introduced in Oracle 10g to trap attempted SQL injection attacks. It this case it would be worth using it to validate the passed table name 

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

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

This would prevent anybody passing 'pay_table set salary = salary * 10 where id = 1234 --' as the table name parameter.

避免使用动态 SQL 的另一个原因是更难获得正确和更难调试.实际语句的语法仅在运行时检查.最好有一套完整的单元测试来验证所有传递的输入，以确保过程不会抛出语法异常.

Another reason to avoid dynamic SQL is that it is harder to get right and harder to debug. The syntax of the actual statement is only checked at run time. It is good to have a complete suite of unit tests which validate all the passed inputs, to ensure that the procedure doesn't hurl a syntax exception.

最后，这样的动态 SQL 不会出现在诸如 ALL_DEPENDENCIES 之类的视图中.这使得进行影响分析和定位使用给定表或列的所有程序变得更加困难.

Finally, such dynamic SQL doesn't show up in views such as ALL_DEPENDENCIES. This makes it harder to undertake impact analysis and locate all the programs which use a given table or column.

相关文章