oracle 在过程中删除表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14564641/
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
Drop a table in a procedure
提问by Alejandro Bastidas
Possible Duplicate:
Oracle: If Table Exists
Drop table if it exists
可能的重复项：
Oracle：如果表存在则
删除表（如果存在）
I'm trying to create this procedure but I get an error.
我正在尝试创建此过程，但出现错误。
CREATE OR REPLACE PROCEDURE SP_VEXISTABLA(NOMBRE IN VARCHAR2)
IS
CANTIDAD NUMBER(3);
BEGIN
SELECT COUNT(*) INTO CANTIDAD FROM ALL_OBJECTS WHERE OBJECT_NAME = NOMBRE;
IF (CANTIDAD >0) THEN
DROP TABLE NOMBRE;
END IF;
END;
The error is:
错误是：
Error(8,1): PLS-00103: Encountered the symbol "END" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge.
错误（8,1）：PLS-00103：在期望以下之一时遇到符号“END”：（如果循环 mod null pragma raise return select update while with << continue close current delete fetch lock插入打开回滚保存点集 sql 为所有合并管道清除执行提交。
Do you know what am I doing wrong?
你知道我做错了什么吗？
回答by DazzaL
you'd need to change your procedure to:
您需要将程序更改为：
CREATE OR REPLACE PROCEDURE SP_VEXISTABLA(NOMBRE IN VARCHAR2)
IS
CANTIDAD NUMBER(3);
BEGIN
SELECT COUNT(*) INTO CANTIDAD FROM USER_TABLES WHERE TABLE_NAME = NOMBRE;
IF (CANTIDAD >0) THEN
execute immediate 'DROP TABLE ' || NOMBRE;
END IF;
END;
回答by Art
You cannot DROP tables in procedure using the DROP command. You need to use EXECUTE IMMEDIATE to run DDL commands in PL/SQL.
您不能使用 DROP 命令在过程中删除表。您需要使用 EXECUTE IMMEDIATE 在 PL/SQL 中运行 DDL 命令。
回答by user2001117
It will not allow you use directly DDL statament inside the PLSQL Procedure.
You need to use Execute Immediatestatement in order to execute the DDL.
它不允许您在 PLSQL 过程中直接使用 DDL 语句。您需要使用Execute Immediate语句来执行 DDL。
Use the code below:
使用下面的代码：
CREATE OR REPLACE PROCEDURE SP_VEXISTABLA(Table_nameIN VARCHAR2)
IS
CANTIDAD integer;
BEGIN
SELECT COUNT(*) INTO CANTIDAD FROM USER_TABLES WHERE TABLE_NAME = Table_name;
DBMS_OUTPUT.PUT_LINE(CANTIDAD);
IF (CANTIDAD >0) THEN
DBMS_OUTPUT.PUT_LINE(Table_name);
execute immediate 'DROP TABLE ' || Table_name;
END IF;
END;
