在Oracle pl / sql中创建或替换表 [英] Create or replace table in Oracle pl/sql
问题描述
我需要一个脚本创建表或如果它已经存在drop它,当重新创建表。经过一些研究,我发现在pl / sql中的 CREATE OR REPLACE TABLE
不存在。所以我想出了这个脚本:
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 :
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?
推荐答案
使用全局临时表似乎是一个更好的选择。然而,如果你坚持在运行时删除和重新添加表,你可以查询一个_TABLES视图(即USER_TABLES,DBA_TABLES,ALL_TABLES)来确定表是否存在,如果它存在,则删除它,然后创建它:
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:
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(...)';
分享并享受。
这篇关于在Oracle pl / sql中创建或替换表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!