如果 Oracle 中不存在则创建一个表（使用 Java）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10771346/
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 a table if doesn't exists in the Oracle (using Java)
提问by arsenal
I need to create an oracle table if it doesn't exists in the Oracle Database and if the table exists then don't do anything, So for that I wrote an anonymous block that works in SQL Developer if I fire that query twice or thrice I don't get any exception in sql developer. This is the below query I wrote-
如果 Oracle 数据库中不存在，我需要创建一个 oracle 表，如果该表存在，则不要执行任何操作，因此，如果我两次或三次触发该查询，我编写了一个可在 SQL Developer 中工作的匿名块我在 sql developer 中没有任何异常。这是我写的以下查询-
public static final String DATABASE_TABLE = "LnPData";
public static final String CREATE_SQL = "DECLARE " +
"t_count INTEGER; " +
"v_sql VARCHAR2(1000) := 'create table " +DATABASE_TABLE +
"(ID number(10,0), " +
"CGUID VARCHAR(255), " +
"PGUID VARCHAR(255), " +
"SGUID VARCHAR(255), " +
"USERID VARCHAR(255), " +
"ULOC VARCHAR(255), " +
"SLOC VARCHAR(255), " +
"PLOC VARCHAR(255), " +
"ALOC VARCHAR(255), " +
"SITEID VARCHAR(255), " +
"PRIMARY KEY ( ID ))'; " +
"BEGIN " +
"SELECT COUNT(*) " +
"INTO t_count " +
"FROM user_tables " +
"WHERE table_name = '" +DATABASE_TABLE + "'; " +
"IF t_count = 0 THEN " +
"EXECUTE IMMEDIATE v_sql; " +
"END IF; " +
"END; ";
I execute the above sql query like this in my java code- First time when I run my program, table gets created, but the second time when I try to run the same program, I always get the below exception.
我在我的 java 代码中像这样执行上面的 sql 查询 - 第一次运行我的程序时，表被创建，但是当我第二次尝试运行相同的程序时，我总是得到以下异常。
// get the connection
LnPDataConstants.DB_CONNECTION = getDBConnection();
LnPDataConstants.STATEMENT = LnPDataConstants.DB_CONNECTION.createStatement();
LnPDataConstants.STATEMENT.executeUpdate(LnPDataConstants.CREATE_SQL);
And I always get sql exception as-
我总是得到 sql 异常 -
SQL Error: ORA-00955: name is already used by an existing object
00955. 00000 - "name is already used by an existing object"
Any suggestions why is it happening in the Java Code?
任何建议为什么它会发生在 Java 代码中？
P.S. I don't want to drop the table
PS我不想丢桌子
Updated Code:-
更新代码：-
public static final String CREATE_SQL = "CREATE TABLE IF NOT EXISTS " +DATABASE_TABLE +
"(ID number(10,0), " +
" CGUID VARCHAR(255), " +
" PGUID VARCHAR(255), " +
" SGUID VARCHAR(255), " +
" USERID VARCHAR(255), " +
" ULOC VARCHAR(255), " +
" SLOC VARCHAR(255), " +
" PLOC VARCHAR(255), " +
" ALOC VARCHAR(255), " +
" SITEID VARCHAR(255), " +
" PRIMARY KEY ( ID ))";
回答by óscar López
Use this script in PL/SQL:
在 PL/SQL 中使用这个脚本：
DECLARE cnt NUMBER;
BEGIN
SELECT count(*) INTO cnt FROM all_tables WHERE table_name = '<TABLE-NAME>';
IF cnt = 0 THEN
EXECUTE IMMEDIATE 'CREATE TABLE <TABLE-NAME>(<COLUMN-DEFINITIONS>)';
END IF;
END;
Replacing <TABLE-NAME>and <COLUMN-DEFINITIONS>with the appropriate values.
用适当的值替换<TABLE-NAME>和<COLUMN-DEFINITIONS>。
回答by Eggi
I also answered your other thread. Seems like that here you already figured out that you had one bracket to much in your query.
我也回答了你的另一个话题。似乎在这里您已经发现您的查询中有一个括号。
Your problem here is easy to solve:
您的问题很容易解决：
If you check user_tables you will see that tables which were not created by using double quotes are always upper case - if you want a table that is case sensitive use CREATE TABLE "tAbLeNamE"(test number). You select the table as "LnPData" and the table contains an entry which is LNPDATA.
如果您检查 user_tables，您将看到不是使用双引号创建的表始终是大写的 - 如果您想要一个区分大小写的表，请使用CREATE TABLE "tAbLeNamE"(test number)。您选择该表为“LnPData”，并且该表包含一个 LNPDATA 条目。
Solution would be to change LnPData into upper case or just change the where clause of your query:
解决方案是将 LnPData 更改为大写或仅更改查询的 where 子句：
"WHERE table_name = UPPER('" +DATABASE_TABLE + "'); " +
Answer also belongs to your other question java.sql.SQLException: ORA-00904.
答案也属于您的其他问题java.sql.SQLException: ORA-00904。
回答by Ben
Firstly why do you need to do this at all? Your code implies that you are creating multiple identical tables all with different names. This sounds like my worst nightmare.
首先，你为什么需要这样做？您的代码暗示您正在创建多个具有不同名称的相同表。这听起来像是我最糟糕的噩梦。
If I'm wrong and you're only creating one table then don't do it in code. Do it in the database first and then you're done. If I'm correct then normalise the table. Add an extra-column username(you already have userid?) or something like it and insert all your data that has this format into a single table. This table, once again, can already be created and there's no need to do it dynamically.
如果我错了并且您只创建了一张表，请不要在代码中进行操作。先在数据库中做，然后就大功告成了。如果我是对的，则对表格进行标准化。添加一个额外的列username（你已经有了userid？）或类似的东西，然后将所有具有这种格式的数据插入到一个表中。再一次，该表已经可以创建，无需动态创建。
You might need more indexes if you normalise but it appears as though you might be missing some anyway are you really always going to have idwhen you select from this table?
如果标准化，您可能需要更多索引，但看起来好像您可能会丢失一些索引，但是id当您从此表中选择时，您真的总是会拥有吗？
If you feel that you really haveto do this in the application code then, as Matt suggests, don't do this in your application code. You'll need to grant create table privileges to the schema you're operating in.
如果您觉得您确实必须在应用程序代码中执行此操作，那么正如Matt 建议的那样，不要在您的应用程序代码中执行此操作。您需要向正在操作的架构授予创建表权限。
Create a user that does have these privileges and add a stored procedure to do this to that user. Then grant your application schema execute privileges on that schema.
创建一个具有这些权限的用户并添加一个存储过程来为该用户执行此操作。然后授予您的应用程序架构对该架构的执行权限。
You should also be using the dbms_assertpackageto sanitize the input.
您还应该使用该dbms_assert包来清理输入。
Lastly, Eggi is correctyou need to uppercase object names, though dbms_assertdoes this by default.
最后，Eggi 是正确的，您需要大写对象名称，尽管dbms_assert默认情况下会这样做。
You should also note that calls to user_tablesor all_tableswill have a performance impact. Another reason not to do it in the application.
您还应该注意，对user_tables或 的调用all_tables会对性能产生影响。不在应用程序中这样做的另一个原因。
Putting this all together you get a stored procedure that looks something like this:
把这些放在一起，你会得到一个看起来像这样的存储过程：
create or replace procedure create_the_table
( pTableName varchar2 ) is
declare
-- Sanitize the input.
l_table_name := dbms_assert.simple_sql_name(pTableName);
l_exists number;
begin
select count(*)
into l_exists
from all_tables
where owner = 'MY_APPLICATION_SCHEMA'
and table_name = l_table_name
;
if l_exists = 0 then
execute immediate 'create table ' || l_table_name ||
' ( ID number(10,0),
CGUID VARCHAR(255),
PGUID VARCHAR(255),
SGUID VARCHAR(255),
USERID VARCHAR(255),
ULOC VARCHAR(255),
SLOC VARCHAR(255),
PLOC VARCHAR(255),
ALOC VARCHAR(255),
SITEID VARCHAR(255),
PRIMARY KEY ( ID )
)';
-- need to grant permissions so that we can check if this
-- is run again.
execute immediate 'grant select on ' || l_table_name ||
' to my_application_user';
end if;
end;
/
Then
然后
grant execute on create_the_table to my_application_user;
and to call...
并打电话...
begin
my_create_table_user.create_the_table(table_name);
end;
Basically my advice is:
基本上我的建议是：
- Don't do this at all
- If you have to do it do it in the database.
- 不要这样做
- 如果您必须这样做，请在数据库中进行。
回答by ebrima jimbara
in case anyone came with similar issue, this should help:
如果有人遇到类似问题，这应该会有所帮助：
Oracle version:
甲骨文版本：
DECLARE
NCOUNT NUMBER;
V_SQL LONG;
BEGIN
SELECT COUNT(*)
INTO NCOUNT
FROM USER_OBJECTS
WHERE OBJECT_NAME = 'tableName';
IF (NCOUNT <= 0) THEN
V_SQL := ' CREATE TABLE tableName(
column1,
column2,
column3,
column4,
column5,
column6 VARCHAR2(2))';
EXECUTE IMMEDIATE V_SQL;
END IF;
END;
Java version:
爪哇版：
String sqlStanging = "DECLARE "
+"NCOUNT NUMBER; "
+"V_SQL LONG; "
+"BEGIN "
+"SELECT COUNT(*) INTO NCOUNT FROM USER_OBJECTS WHERE OBJECT_NAME = 'tableName' ;"
+"IF(NCOUNT <= 0) "
+"THEN "
+"V_SQL:=' "
+"CREATE TABLE tableName"
+"("
+" column1, "
+" column2, "
+" column3, "
+" column4, "
+" column5, "
+" column6 VARCHAR2(2))';"
+"EXECUTE IMMEDIATE V_SQL ;"
+"END IF; "
+"END; ";
