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 TABLE in pl/sql doesn't exist. So I come up with this script :
我需要一个创建表的脚本,或者如果已经存在则删除它,以及重新创建表时。经过一些研究后我发现pl / sql中的CREATE OR REPLACE TABLE不存在。所以我想出了这个脚本:
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?
有没有正确的方法来实现这个功能?
2 个解决方案
#1
5
Using a global temporary table would 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.
分享和享受。
#2
9
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 tables first. Personally, I'd reassess whether it's necessary at all.
你真的不应该在PL / SQL中这样做,在运行时创建的表将指示数据模型中的缺陷。如果你真的相信你绝对必须这样做,那么先调查临时表。就个人而言,我会重新评估是否有必要。
You seem to be going for the EAFP as opposed to LBYL approach, 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_TABLES to 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;
/
#1
5
Using a global temporary table would 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.
分享和享受。
#2
9
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 tables first. Personally, I'd reassess whether it's necessary at all.
你真的不应该在PL / SQL中这样做,在运行时创建的表将指示数据模型中的缺陷。如果你真的相信你绝对必须这样做,那么先调查临时表。就个人而言,我会重新评估是否有必要。
You seem to be going for the EAFP as opposed to LBYL approach, 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_TABLES to 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;
/