在 Oracle pl/sql 中创建或替换表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16634699/
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
Create or replace table in Oracle pl/sql
提问by kuldarim
I need a script which creates table or if it already exist drops it, and when recreates table. After some research I have found out that CREATE OR REPLACE TABLEin pl/sql doesn't exist. So I come up with this script :
我需要一个创建表的脚本，或者如果它已经存在，则删除它，并在重新创建表时删除它。经过一番研究，我发现CREATE OR REPLACE TABLE在 pl/sql 中不存在。所以我想出了这个脚本：
DECLARE
does_not_exist EXCEPTION;
PRAGMA EXCEPTION_INIT (does_not_exist, -942);
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE foobar';
EXCEPTION
WHEN does_not_exist
THEN
NULL;
END;
/
CREATE TABLE foobar (c1 INT);
Is there any proper way to achieve this functionality?
有没有什么合适的方法来实现这个功能？
回答by Ben
You really shouldn't be doing this in PL/SQL, tables created at runtime would be indicative of a flaw in your data model. If you're really convinced you absolutely have to do this then investigate temporary tablesfirst. Personally, I'd reassess whether it's necessary at all.
您真的不应该在 PL/SQL 中这样做，在运行时创建的表将表明您的数据模型存在缺陷。如果你真的确信你绝对必须这样做，那么首先调查临时表。就个人而言，我会重新评估是否有必要。
You seem to be going for the EAFP as opposed to LBYLapproach, which is described in a few answers to this question. I would argue that this is unnecessary. A table is a fairly static beast, you can use the system view USER_TABLESto determine whether it exists before dropping it.
您似乎要使用EAFP，而不是 LBYL方法，这在此问题的一些答案中有所描述。我认为这是不必要的。表是一个相当静态的野兽，您可以在删除它之前使用系统视图USER_TABLES来确定它是否存在。
declare
l_ct number;
begin
-- Determine if the table exists.
select count(*) into l_ct
from user_tables
where table_name = 'THE_TABLE';
-- Drop the table if it exists.
if l_ct = 1 then
execute immediate 'drop table the_table';
end if;
-- Create the new table it either didn-t exist or
-- has been dropped so any exceptions are exceptional.
execute immediate 'create table the_table ( ... )';
end;
/
回答by Bob Jarvis - Reinstate Monica
Using a global temporary tablewould seem to be a better option. However, if you insist on dropping and re-adding tables at runtime you could query one of the _TABLES views (i.e. USER_TABLES, DBA_TABLES, ALL_TABLES) to determine if the table exists, drop it if it does, then create it:
使用全局临时表似乎是更好的选择。但是，如果您坚持在运行时删除和重新添加表，您可以查询 _TABLES 视图之一（即 USER_TABLES、DBA_TABLES、ALL_TABLES）以确定该表是否存在，如果存在则删除它，然后创建它：
SELECT COUNT(*)
INTO nCount
FROM USER_TABLES
WHERE TABLE_NAME = 'FOOBAR';
IF nCount <> 0 THEN
EXECUTE IMMEDIATE 'DROP TABLE FOOBAR';
END IF;
EXECUTE IMMEDIATE 'CREATE TABLE FOOBAR(...)';
Share and enjoy.
分享和享受。
