oracle 不存在则创建表，创建后输入一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30525569/
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 table if it does not exist, and enter one row after creating
提问by PhoenixDev
I need to create a table if it does not exist, and when it is created add a single row to it.
如果它不存在，我需要创建一个表，并在创建时向其中添加一行。
I'm new to oracle and PL/SQL so I basically need an equivalent of the following T-SQL:
我是 oracle 和 PL/SQL 的新手，所以我基本上需要以下 T-SQL 的等效项：
IF OBJECT_ID('my_table', 'U') IS NULL
BEGIN
CREATE TABLE my_table(id numeric(38,0), date datetime)
INSERT INTO my_table
VALUES (NULL, 0)
END
回答by Moudiz
if you want to check table creation
如果你想检查表创建
DECLARE count NUMBER;
BEGIN
count := 0;
SELECT COUNT(1) INTO count from user_tables WHERE table_name= 'MY_TABLE';
IF COL_COUNT = 0 THEN
EXECUTE IMMEDIATE 'create table ....';
END IF;
END;
/
A checking for DML.please note you have to sepcify your pk columns and values.
检查 .DML请注意您必须分离您的 pk 列和值。
DECLARE count NUMBER;
BEGIN
count := 0;
SELECT COUNT(1) INTO count from MY_TABLE WHERE id= 0 and name='Something';
IF COL_COUNT = 0 THEN
EXECUTE IMMEDIATE 'insert into MY_TABLE (id,name) values(0,''something'') ';
END IF;
END;
/
also note I recomand to specify columns when you insert into a table
另请注意，我建议在插入表时指定列
回答by PhoenixDev
Another approach is to use exception logic. I changed field names and types according to Oracle rules
另一种方法是使用异常逻辑。我根据 Oracle 规则更改了字段名称和类型
declare
eAlreadyExists exception;
pragma exception_init(eAlreadyExists, -00955);
begin
execute immediate 'CREATE TABLE my_table(id number, dateof date)';
execute immediate 'INSERT INTO my_table VALUES (NULL, sysdate)';
exception when eAlreadyExists then
null;
end;
but may be it is not a good idea to create tables dynamically
但动态创建表可能不是一个好主意
回答by Lalit Kumar B
In my opinion, you should not be creating objects on the fly. You should think about your design before implementing it.
在我看来，您不应该即时创建对象。在实施之前，您应该考虑您的设计。
Anyway, if you really want to do it this way, then you need to do it programmatically in PL/SQL(ab)using EXECUTE IMMEDIATE.
无论如何，如果您真的想这样做，那么您需要在PL/SQL(ab) 中使用EXECUTE IMMEDIATE以编程方式进行。
However, I would prefer the CTASi.e. create table as selectif you want to create a table ta once with a single row. For example,
但是，我更喜欢CTAS，即create table as select如果您想使用单行创建一次表 ta。例如，
SQL> CREATE TABLE t AS SELECT 1 id, SYSDATE dt FROM DUAL;
Table created.
SQL> SELECT * FROM t;
ID DT
---------- ---------
1 29-MAY-15
SQL>
The table is created permanently.
该表是永久创建的。
If you are looking for a temporary table, which you could use to store session specific data, then look at creating Global temporary table.
如果您正在寻找可用于存储会话特定数据的临时表，请查看创建全局临时表。
From documentation,
从文档来看，
Use the CREATE GLOBAL TEMPORARY TABLE statement to create a temporary table. The ON COMMIT clause indicates if the data in the table is transaction-specific (the default) or session-specific
使用 CREATE GLOBAL TEMPORARY TABLE 语句创建临时表。ON COMMIT 子句指示表中的数据是特定于事务的（默认）还是特定于会话的
回答by Hawk
You can use NOT EXISTSwith select statement:
您可以将NOT EXISTS与 select 语句一起使用：
IF NOT EXISTS(SELECT 1 FROM my_table) THEN
CREATE TABLE my_table(id NUMBER, date date);
COMMIT;
INSERT INTO my_table(id, date) values (NULL, O);
COMMIT;
END IF;
UPDATE
更新
According to the comment, I cannot use Exist directly in PL/SQL. So this is another way to do it:
根据评论，我不能直接在 PL/SQL 中使用 Exist。所以这是另一种方法：
begin
select case
when exists(select 1
from my_table)
then 1
else 0
end into l_exists
from dual;
if (l_exists = 1)
then
-- anything
else
EXECUTE IMMEDIATE 'CREATE TABLE my_table(id NUMBER, date date)';
EXECUTE IMMEDIATE 'INSERT INTO my_table(id, date) values (NULL, O)';
end if;
end;
