我正在为 Oracle 数据库编写一些迁移脚本,并希望 Oracle 有类似于 MySQL 的 IF EXISTS
构造的东西。
具体来说,每当我想在 MySQL 中删除一个表时,我都会做类似的事情
DROP TABLE IF EXISTS `table_name`;
这样,如果表不存在,DROP
不会产生错误,脚本可以继续。
Oracle 是否有类似的机制?我意识到我可以使用以下查询来检查表是否存在
SELECT * FROM dba_tables where table_name = 'table_name';
但是将其与 DROP
绑定在一起的语法让我无法理解。
最好和最有效的方法是捕获“table not found”异常:这样可以避免检查表是否存在两次的开销;并且不会遇到这样的问题,即如果 DROP 由于其他原因(可能很重要)失败,异常仍然会向调用者提出:
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
附录作为参考,以下是其他对象类型的等效块:
序列
BEGIN
EXECUTE IMMEDIATE 'DROP SEQUENCE ' || sequence_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -2289 THEN
RAISE;
END IF;
END;
看法
BEGIN
EXECUTE IMMEDIATE 'DROP VIEW ' || view_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
扳机
BEGIN
EXECUTE IMMEDIATE 'DROP TRIGGER ' || trigger_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -4080 THEN
RAISE;
END IF;
END;
指数
BEGIN
EXECUTE IMMEDIATE 'DROP INDEX ' || index_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1418 THEN
RAISE;
END IF;
END;
柱子
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE ' || table_name
|| ' DROP COLUMN ' || column_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -904 AND SQLCODE != -942 THEN
RAISE;
END IF;
END;
数据库链接
BEGIN
EXECUTE IMMEDIATE 'DROP DATABASE LINK ' || dblink_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -2024 THEN
RAISE;
END IF;
END;
物化视图
BEGIN
EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW ' || mview_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -12003 THEN
RAISE;
END IF;
END;
类型
BEGIN
EXECUTE IMMEDIATE 'DROP TYPE ' || type_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -4043 THEN
RAISE;
END IF;
END;
约束
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE ' || table_name
|| ' DROP CONSTRAINT ' || constraint_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -2443 AND SQLCODE != -942 THEN
RAISE;
END IF;
END;
调度程序作业
BEGIN
DBMS_SCHEDULER.drop_job(job_name);
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -27475 THEN
RAISE;
END IF;
END;
用户/架构
BEGIN
EXECUTE IMMEDIATE 'DROP USER ' || user_name;
/* you may or may not want to add CASCADE */
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1918 THEN
RAISE;
END IF;
END;
包裹
BEGIN
EXECUTE IMMEDIATE 'DROP PACKAGE ' || package_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -4043 THEN
RAISE;
END IF;
END;
程序
BEGIN
EXECUTE IMMEDIATE 'DROP PROCEDURE ' || procedure_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -4043 THEN
RAISE;
END IF;
END;
功能
BEGIN
EXECUTE IMMEDIATE 'DROP FUNCTION ' || function_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -4043 THEN
RAISE;
END IF;
END;
表空间
BEGIN
EXECUTE IMMEDIATE 'DROP TABLESPACE ' || tablespace_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -959 THEN
RAISE;
END IF;
END;
代名词
BEGIN
EXECUTE IMMEDIATE 'DROP SYNONYM ' || synonym_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1434 THEN
RAISE;
END IF;
END;
declare
c int;
begin
select count(*) into c from user_tables where table_name = upper('table_name');
if c = 1 then
execute immediate 'drop table table_name';
end if;
end;
那是为了检查当前模式中的表是否存在。要检查给定表是否已存在于不同的架构中,您必须使用 all_tables
而不是 user_tables
并添加条件 all_tables.owner = upper('schema_name')
我一直在寻找相同的东西,但最终我编写了一个程序来帮助我:
CREATE OR REPLACE PROCEDURE DelObject(ObjName varchar2,ObjType varchar2)
IS
v_counter number := 0;
begin
if ObjType = 'TABLE' then
select count(*) into v_counter from user_tables where table_name = upper(ObjName);
if v_counter > 0 then
execute immediate 'drop table ' || ObjName || ' cascade constraints';
end if;
end if;
if ObjType = 'PROCEDURE' then
select count(*) into v_counter from User_Objects where object_type = 'PROCEDURE' and OBJECT_NAME = upper(ObjName);
if v_counter > 0 then
execute immediate 'DROP PROCEDURE ' || ObjName;
end if;
end if;
if ObjType = 'FUNCTION' then
select count(*) into v_counter from User_Objects where object_type = 'FUNCTION' and OBJECT_NAME = upper(ObjName);
if v_counter > 0 then
execute immediate 'DROP FUNCTION ' || ObjName;
end if;
end if;
if ObjType = 'TRIGGER' then
select count(*) into v_counter from User_Triggers where TRIGGER_NAME = upper(ObjName);
if v_counter > 0 then
execute immediate 'DROP TRIGGER ' || ObjName;
end if;
end if;
if ObjType = 'VIEW' then
select count(*) into v_counter from User_Views where VIEW_NAME = upper(ObjName);
if v_counter > 0 then
execute immediate 'DROP VIEW ' || ObjName;
end if;
end if;
if ObjType = 'SEQUENCE' then
select count(*) into v_counter from user_sequences where sequence_name = upper(ObjName);
if v_counter > 0 then
execute immediate 'DROP SEQUENCE ' || ObjName;
end if;
end if;
end;
希望这可以帮助
只是想发布一个完整的代码,该代码将使用 Jeffrey 的代码创建一个表并删除它(向他致敬,而不是我!)。
BEGIN
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE tablename';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
EXECUTE IMMEDIATE 'CREATE TABLE tablename AS SELECT * FROM sourcetable WHERE 1=0';
END;
使用 SQL*PLUS,您还可以使用 WHENEVER SQLERROR 命令:
WHENEVER SQLERROR CONTINUE NONE
DROP TABLE TABLE_NAME;
WHENEVER SQLERROR EXIT SQL.SQLCODE
DROP TABLE TABLE_NAME;
使用 CONTINUE NONE
会报告错误,但脚本将继续。使用 EXIT SQL.SQLCODE
,脚本将在出现错误时终止。
我更喜欢以下经济解决方案
BEGIN
FOR i IN (SELECT NULL FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND OBJECT_NAME = 'TABLE_NAME') LOOP
EXECUTE IMMEDIATE 'DROP TABLE TABLE_NAME';
END LOOP;
END;
另一种方法是定义一个异常,然后只捕获该异常,让所有其他异常传播。
Declare
eTableDoesNotExist Exception;
PRAGMA EXCEPTION_INIT(eTableDoesNotExist, -942);
Begin
EXECUTE IMMEDIATE ('DROP TABLE myschema.mytable');
Exception
When eTableDoesNotExist Then
DBMS_Output.Put_Line('Table already does not exist.');
End;
一种方法是使用 DBMS_ASSERT.SQL_OBJECT_NAME :
此函数验证输入参数字符串是否为现有 SQL 对象的限定 SQL 标识符。
DECLARE
V_OBJECT_NAME VARCHAR2(30);
BEGIN
BEGIN
V_OBJECT_NAME := DBMS_ASSERT.SQL_OBJECT_NAME('tab1');
EXECUTE IMMEDIATE 'DROP TABLE tab1';
EXCEPTION WHEN OTHERS THEN NULL;
END;
END;
/
oracle 中没有“DROP TABLE IF EXISTS”,您必须执行 select 语句。
试试这个(我不了解 oracle 语法,所以如果我的变量是 ify,请原谅我):
declare @count int
select @count=count(*) from all_tables where table_name='Table_name';
if @count>0
BEGIN
DROP TABLE tableName;
END
如果您想让它可重新输入并最小化删除/创建周期,您可以使用 dbms_metadata.get_ddl 缓存 DDL 并使用如下构造重新创建所有内容: declare v_ddl varchar2(4000); begin select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') into v_ddl from dual; [COMPARE CACHED DDL AND EXECUTE IF NO MATCH] exception when others then if sqlcode = -31603 then [GET AND EXECUTE CACHED DDL] else raise; end if; end;
这只是一个示例,应该有一个循环内部,DDL 类型、名称和所有者是变量。
像这样的块可能对您有用。
DECLARE
table_exist INT;
BEGIN
SELECT Count(*)
INTO table_exist
FROM dba_tables
WHERE owner = 'SCHEMA_NAME'
AND table_name = 'EMPLOYEE_TABLE';
IF table_exist = 1 THEN
EXECUTE IMMEDIATE 'drop table EMPLOYEE_TABLE';
END IF;
END;
可悲的是,不存在如果存在则丢弃或不存在则创建之类的东西
您可以编写一个 plsql 脚本来包含那里的逻辑。
http://download.oracle.com/docs/cd/B12037_01/server.101/b10759/statements_9003.htm
我不太喜欢 Oracle 语法,但我认为 @Erich 的脚本会是这样的。
declare
cant integer
begin
select into cant count(*) from dba_tables where table_name='Table_name';
if count>0 then
BEGIN
DROP TABLE tableName;
END IF;
END;
你总是可以自己捕捉到错误。
begin
execute immediate 'drop table mytable';
exception when others then null;
end;
过度使用它被认为是不好的做法,类似于其他语言中的空 catch()'es。
问候 K
我更喜欢指定表和架构所有者。
还要注意区分大小写。 (见下文“上”条款)。
我扔了几个不同的对象,以表明它们可以在除 TABLEs 之外的地方使用。
......
declare
v_counter int;
begin
select count(*) into v_counter from dba_users where upper(username)=upper('UserSchema01');
if v_counter > 0 then
execute immediate 'DROP USER UserSchema01 CASCADE';
end if;
end;
/
CREATE USER UserSchema01 IDENTIFIED BY pa$$word
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;
grant create session to UserSchema01;
还有一个 TABLE 示例:
declare
v_counter int;
begin
select count(*) into v_counter from all_tables where upper(TABLE_NAME)=upper('ORDERS') and upper(OWNER)=upper('UserSchema01');
if v_counter > 0 then
execute immediate 'DROP TABLE UserSchema01.ORDERS';
end if;
end;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE "IMS"."MAX" ';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
EXECUTE IMMEDIATE '
CREATE TABLE "IMS"."MAX"
( "ID" NUMBER NOT NULL ENABLE,
"NAME" VARCHAR2(20 BYTE),
CONSTRAINT "MAX_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSAUX" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSAUX" ';
END;
// 执行此代码,检查表是否存在,然后创建表 max.这仅适用于单个编译
EXECUTE IMMEDIATE 'DROP TABLE mytable';
句子(脚本中的每个表一个),我是否必须为每个句子放置一个异常处理程序,还是将所有句子包装在一个BEGIN ... EXCEPTION ... END;
块中就足够了?IF OBJECT_ID('TblName') IS NOT NULL DROP TABLE TblName
。似乎 SQL 语言的冗长程度与价格成正比。