oracle 如何在存储过程中使用 DROP TABLE 等命令
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12801533/
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
How to use commands like DROP TABLE etc. within a stored procedure
提问by Craig C
I have written a script which is intended to be scheduled to run overnight to copy yesterday's tables, drop yesterday's tables, create new tables, then compare changes. This is working fine as a scheduled task, but I am really struggling to find a way of allowing the users to execute it on the fly for testing purposes.
我编写了一个脚本，计划在夜间运行以复制昨天的表、删除昨天的表、创建新表，然后比较更改。这作为计划任务运行良好，但我真的很难找到一种允许用户出于测试目的而即时执行它的方法。
What I really want to do is to pack it all into a stored procedure, as I have an eForm tool which will easily allow the user (very non-technical) to be able to execute the procedure with a click.
我真正想要做的是将它全部打包到一个存储过程中，因为我有一个 eForm 工具，它可以轻松地让用户（非常非技术）能够通过单击来执行该过程。
Here is an example of the start of the script. Can anyone help me shoehorn this into a stored procedure as it clearly won't accept commands like DROP TABLE as standard.
这是脚本开始的示例。谁能帮我把它硬塞进一个存储过程中，因为它显然不会接受像 DROP TABLE 这样的命令作为标准。
Thanks.
谢谢。
DROP TABLE SQL2005TEST.ABSENCEFULLDATADIFF_YESTERDAY;
DROP TABLE SQL2005TEST.ABSENCELATESTSTART_YESTERDAY;
DROP TABLE SQL2005TEST.ABSENCELATESTEND_YESTERDAY;
CREATE TABLE SQL2005TEST.ABSENCEFULLDATADIFF_YESTERDAY
AS SELECT * FROM SQL2005TEST.ABSENCEFULLDATADIFF;
CREATE TABLE SQL2005TEST.ABSENCELATESTSTART_YESTERDAY
AS SELECT * FROM SQL2005TEST.ABSENCELATESTSTART;
CREATE TABLE SQL2005TEST.ABSENCELATESTEND_YESTERDAY
AS SELECT * FROM SQL2005TEST.ABSENCELATESTEND;
回答by AnBisw
CREATE OR REPLACE PROCEDURE proc_name AS
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE SQL2005TEST.ABSENCEFULLDATADIFF_YESTERDAY';
.....
EXECUTE IMMEDIATE 'CREATE TABLE SQL2005TEST.ABSENCELATESTEND_YESTERDAY
AS SELECT * FROM SQL2005TEST.ABSENCELATESTEND';
....
....
EXCEPTION
....
....
END;
The EXECUTE IMMEDIATEstatement executes a dynamic SQL statement or anonymous PL/SQL block, within a PL/SQL block, or Stored Procedure, or Package. This is more specifically used if you need to run DDL statements like DROP, CREATE TABLEetc. You cannot execute DDL commands from PL/SQL as DML statements, so the only way is dynamic SQL. More info hereand here.
该EXECUTE IMMEDIATE语句在 PL/SQL 块、存储过程或包中执行动态 SQL 语句或匿名 PL/SQL 块。如果您需要运行诸如等的DDL 语句DROP，则更具体地使用它CREATE TABLE。您不能将 PL/SQL 中的 DDL 命令作为 DML 语句执行，因此唯一的方法是动态 SQL。更多信息在这里和这里。
