SQL 是否可以在 Oracle 中将表名作为参数传递？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3690038/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is it possible to pass table name as a parameter in Oracle?
提问by Quan Mai
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;
Of course, Oracle does not let me do that. Is there a way to work around this problem? Thank you very much.
当然，Oracle 不允许我这样做。有没有办法解决这个问题？非常感谢。
回答by APC
You have several different tables with exactly the same column names and data types? Smells like a dodgy design.
您有几个具有完全相同列名和数据类型的不同表？闻起来像一个狡猾的设计。
Anyway, we cannot use variables as database objects in straightforward SQL like that. We have to use dynamic SQL.
无论如何，我们不能像这样在简单的 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;
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 hiHyman it.
避免使用动态 SQL 的原因之一是它容易被滥用。恶意人员可以使用这些参数来试图绕过我们的安全措施。这称为 SQL 注入。我认为人们高估了 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
但采取预防措施是明智的。DBMS_ASSERT 是 Oracle 10g 中引入的用于捕获 SQL 注入攻击的包。在这种情况下，值得使用它来验证传递的表名
....
'UPDATE '|| DBMS_ASSERT.simple_sql_name(pADSLTable)
....
This would prevent anybody passing 'pay_table set salary = salary * 10 where id = 1234 --'as the table name parameter.
这将阻止任何人'pay_table set salary = salary * 10 where id = 1234 --'作为表名参数传递。
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 的另一个原因是更难获得正确和更难调试。实际语句的语法仅在运行时检查。最好有一套完整的单元测试来验证所有传递的输入，以确保过程不会抛出语法异常。
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.
最后，此类动态 SQL 不会出现在诸如 ALL_DEPENDENCIES 之类的视图中。这使得进行影响分析和定位使用给定表或列的所有程序变得更加困难。
回答by Thilo
Yes, there is Native Dynamic SQL:
是的，有本地动态 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;
Performance and error checking is not as good (no compile-time syntax and schema validation). Beware of SQL injection.
性能和错误检查不太好（没有编译时语法和模式验证）。谨防 SQL 注入。
So, if you have only a couple of tables to choose from, consider using an if/then/else construct with all options instead.
因此，如果您只有几个表可供选择，请考虑使用带有所有选项的 if/then/else 结构。
回答by Vishnu Gupta
You can use all sort of DDL statements using dynamic SQL. You can pass names of different database objects as parameters or manipulate in variables.
您可以使用动态 SQL 使用各种 DDL 语句。您可以将不同数据库对象的名称作为参数传递或在变量中进行操作。
