oracle 如何使用创建或替换？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1008248/
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 do I use CREATE OR REPLACE?
提问by Jason Baker
Am I correct in understanding that CREATE OR REPLACE basically means "if the object exists, drop it, then create it either way?"
我是否正确理解 CREATE OR REPLACE 基本上意味着“如果对象存在，则删除它，然后以任何一种方式创建它？”
If so, what am I doing wrong? This works:
如果是这样，我做错了什么？这有效：
CREATE TABLE foo (id NUMBER,
title VARCHAR2(4000) DEFAULT 'Default Title')
And this doesn't (ORA-00922: missing or invalid option):
而这不会（ORA-00922：缺少或无效的选项）：
CREATE OR REPLACE TABLE foo (id NUMBER,
title VARCHAR2(4000) DEFAULT 'Default Title')
Am I doing something stupid? I don't seem to be able to find much documentation about this syntax.
我在做蠢事吗？我似乎无法找到有关此语法的大量文档。
回答by Quassnoi
This works on functions, procedures, packages, types, synonyms, trigger and views.
这适用于函数、过程、包、类型、同义词、触发器和视图。
Update:
更新：
After updating the post for the third time, I'll reformulate this:
第三次更新帖子后，我将重新表述：
This does not work on tables :)
这不适用于表:)
And yes, there is documentationon this syntax, and there are no REPLACEoption for CREATE TABLE.
是的，有文件在这句法，并且没有REPLACE供选择CREATE TABLE。
回答by Jeffrey Kemp
One of the nice things about the syntax is that you can be sure that a CREATE OR REPLACEwill never cause you to lose data (the most you will lose is code, which hopefully you'll have stored in source control somewhere).
语法的好处之一是您可以确定 aCREATE OR REPLACE永远不会导致您丢失数据（您丢失的最多的是代码，希望您将其存储在源代码控制中的某处）。
The equivalent syntax for tables is ALTER, which means you have to explicitly enumerate the exact changes that are required.
表的等效语法是 ALTER，这意味着您必须显式枚举所需的确切更改。
EDIT:By the way, if you need to do a DROP + CREATE in a script, and you don't care for the spurious "object does not exist" errors (when the DROP doesn't find the table), you can do this:
编辑：顺便说一句，如果您需要在脚本中执行 DROP + CREATE，并且您不关心虚假的“对象不存在”错误（当 DROP 找不到表时），您可以这样做这个：
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE owner.mytable';
EXCEPTION
WHEN OTHERS THEN
IF sqlcode != -0942 THEN RAISE; END IF;
END;
/
回答by RC.
There is no create or replace table in Oracle.
Oracle 中没有创建或替换表。
You must:
你必须：
DROP TABLE foo; CREATE TABLE foo (....);
回答by Andy Mikula
CREATE OR REPLACEcan only be used on functions, procedures, types, views, or packages - it will not work on tables.
CREATE OR REPLACE只能用于函数、过程、类型、视图或包 - 它不适用于表。
回答by Kavan
Following script should do the trick on Oracle:
以下脚本应该在 Oracle 上起作用：
BEGIN
EXECUTE IMMEDIATE 'drop TABLE tablename';
EXCEPTION
WHEN OTHERS THEN
IF sqlcode != -0942 THEN RAISE;
END IF;
END;
回答by northpole
回答by XorNegative
A usefull procedure for oracle databases without using exeptions (under circumstances you have to replace user_tables with dba_tables and/or constrain the tablespace in the query):
一个有用的 oracle 数据库过程，不使用例外（在某些情况下，您必须用 dba_tables 替换 user_tables 和/或限制查询中的表空间）：
create or replace procedure NG_DROP_TABLE(tableName varchar2)
is
c int;
begin
select count(*) into c from user_tables where table_name = upper(tableName);
if c = 1 then
execute immediate 'drop table '||tableName;
end if;
end;
回答by grokster
-- To Create or Replace a Table we must first silently Drop a Table that may not exist
DECLARE
table_not_exist EXCEPTION;
PRAGMA EXCEPTION_INIT (table_not_exist , -00942);
BEGIN
EXECUTE IMMEDIATE('DROP TABLE <SCHEMA>.<TABLE NAME> CASCADE CONSTRAINTS');
EXCEPTION WHEN table_not_exist THEN NULL;
END;
/
回答by BGDev
So I've been using this and it has worked very well: - it works more like a DROP IF EXISTS but gets the job done
所以我一直在使用它并且它运行得非常好： - 它更像一个 DROP IF EXISTS 但完成工作
DECLARE
VE_TABLENOTEXISTS EXCEPTION;
PRAGMA EXCEPTION_INIT(VE_TABLENOTEXISTS, -942);
PROCEDURE DROPTABLE(PIS_TABLENAME IN VARCHAR2) IS
VS_DYNAMICDROPTABLESQL VARCHAR2(1024);
BEGIN
VS_DYNAMICDROPTABLESQL := 'DROP TABLE ' || PIS_TABLENAME;
EXECUTE IMMEDIATE VS_DYNAMICDROPTABLESQL;
EXCEPTION
WHEN VE_TABLENOTEXISTS THEN
DBMS_OUTPUT.PUT_LINE(PIS_TABLENAME || ' NOT EXIST, SKIPPING....');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RAISE;
END DROPTABLE;
BEGIN
DROPTABLE('YOUR_TABLE_HERE');
END DROPTABLE;
/
Hope this helps Also reference: PLS-00103 Error in PL/SQL Developer
希望这有帮助 还参考： PL/SQL Developer 中的 PLS-00103 错误
回答by cybork
'Create or replace table' is not possible. As others stated, you can write a procedure and/or use begin execute immediately (...). Because I don't see an answer with how to (re)create the table, I putted a script as an answer.
“创建或替换表”是不可能的。正如其他人所说，您可以编写一个过程和/或使用开始立即执行（...）。因为我没有看到有关如何（重新）创建表格的答案，所以我放了一个脚本作为答案。
PS: in line of what jeffrey-kemp mentioned: this beneath script will NOT save data that is already present in the table you are going to drop. Because of the risk of loosing data, at our company it is only allowed to alter existing tables on the production environment, and it is not allowed to drop tables. By using the drop table statement, sooner or later you will get the company police standing at your desk.
PS：根据 jeffrey-kemp 提到的：这个脚本下面不会保存你要删除的表中已经存在的数据。由于存在丢失数据的风险，我们公司只允许修改生产环境中已有的表，不允许删除表。通过使用 drop table 语句，迟早你会让公司警察站在你的办公桌前。
--Create the table 'A_TABLE_X', and drop the table in case it already is present
BEGIN
EXECUTE IMMEDIATE
'
CREATE TABLE A_TABLE_X
(
COLUMN1 NUMBER(15,0),
COLUMN2 VARCHAR2(255 CHAR),
COLUMN3 VARCHAR2(255 CHAR)
)';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -955 THEN -- ORA-00955: object name already used
EXECUTE IMMEDIATE 'DROP TABLE A_TABLE_X';
END IF;
END;
