如何在存储过程中使用 DROP TABLE 等命令 [英] How to use commands like DROP TABLE etc. within a stored procedure

问题描述

我编写了一个脚本，计划在夜间运行以复制昨天的表、删除昨天的表、创建新表，然后比较更改.这作为计划任务运行良好，但我真的很难找到一种方法，允许用户出于测试目的即时执行它.

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.

我真正想做的是将它全部打包到一个存储过程中，因为我有一个 eForm 工具，它可以轻松地让用户(非常非技术性的)能够通过单击来执行该过程.

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.

这是脚本开始的示例.谁能帮我把它硬塞到一个存储过程中，因为它显然不会接受像 DROP TABLE 这样的命令作为标准.

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 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;

推荐答案

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;

EXECUTE IMMEDIATE 语句在 PL/SQL 块、存储过程或包中执行动态 SQL 语句或匿名 PL/SQL 块.如果您需要运行 DROP、CREATE TABLE 等 DDL 语句，则更具体地使用它.您不能将 PL/SQL 中的 DDL 命令作为 DML 语句执行，因此唯一的方法是动态 SQL.更多信息这里和这里.

The EXECUTE IMMEDIATE statement 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 TABLE etc. You cannot execute DDL commands from PL/SQL as DML statements, so the only way is dynamic SQL. More info here and here.

这篇关于如何在存储过程中使用 DROP TABLE 等命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！