是否可以在Oracle中将表名作为参数传递? [英] Is it possible to pass table name as a parameter in Oracle?

问题描述

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

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.

这篇关于是否可以在Oracle中将表名作为参数传递?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！