如果 Oracle 用户尚不存在，则创建该用户
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30710990/
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
Creating an Oracle User if it doesn't already exist
提问by Kyle Williamson
I am trying to create a script that will create users if they do not already exist.
我正在尝试创建一个脚本，如果用户不存在，它将创建用户。
CREATE USER "Kyle" PROFILE "DEFAULT" IDENTIFIED BY "password" ACCOUNT UNLOCK
WHERE NOT IN //Also tried 'WHERE NOT EXISTS'
(
SELECT username FROM all_users WHERE username = 'Kyle'
)
The following error is given:
给出以下错误：
SQL Error: ORA-00922: missing or invalid option
SQL 错误：ORA-00922：缺少或无效的选项
I was able to do this in SQL Server 2008 by using:
我能够通过使用以下方法在 SQL Server 2008 中做到这一点：
IF NOT EXISTS
(SELECT name FROM master.sys.server_principals
WHERE name = 'Kyle')
BEGIN
CREATE LOGIN Kyle WITH PASSWORD = 'temppassword' MUST_CHANGE, CHECK_EXPIRATION=ON, CHECK_POLICY=ON
END
Is there a similar way in Oracle to check if a user already exists before attempting to create a new user?
在尝试创建新用户之前，Oracle 中是否有类似的方法来检查用户是否已存在？
回答by Mark J. Bobak
The IF NOT EXISTSsyntax available in SQL Server, is not available in Oracle.
IF NOT EXISTSSQL Server 中可用的语法在 Oracle 中不可用。
In general, Oracle scripts simply execute the CREATEstatement, and if the object already exist, you'll get an error indicating that, which you can ignore. This is what all the standard Oracle deployment scripts do.
通常，Oracle 脚本只是执行该CREATE语句，如果该对象已经存在，您将收到一个错误指示，您可以忽略该错误。这是所有标准 Oracle 部署脚本所做的。
However, if you reallywant to check for existence, and only execute if object doesn't exist, thereby avoiding the error, you can code a PL/SQLblock. Write a SQLthat checks for user existence, and if it doesn't exist, use EXECUTE IMMEDIATEto do CREATE USERfrom the PL/SQLblock.
但是，如果您真的想检查是否存在，并且仅在对象不存在时才执行，从而避免错误，您可以编写一个PL/SQL块。编写一个SQL检查用户存在的代码，如果它不存在，则从块中使用EXECUTE IMMEDIATEto do 。CREATE USERPL/SQL
An example of such a PL/SQL block might be:
此类 PL/SQL 块的示例可能是：
declare
userexist integer;
begin
select count(*) into userexist from dba_users where username='SMITH';
if (userexist = 0) then
execute immediate 'create user smith identified by smith';
end if;
end;
/
Hope that helps.
希望有帮助。
回答by 6ton
You need to write a pl/sql block. See an example here
您需要编写一个 pl/sql 块。在此处查看示例
You can check if the user exists in the all_users table using some pl/sql code like:
您可以使用一些 pl/sql 代码检查用户是否存在于 all_users 表中，例如：
SELECT count(*) INTO v_count_user
FROM all_users
WHERE username = 'Kyle'
and then use v_count_user in an IF condition to conditionally execute the create user statement.
然后在 IF 条件中使用 v_count_user 有条件地执行 create user 语句。
回答by Emil G
From the previous answers, it is clear that if not existsis not supported in Oracle. To clarify which error(s) are thrown by Oracle when attempting to create an already existing user (and as a bonus, when attempting to drop a non existing user):
从前面的答案中可以明显看出，if not existsOracle 不支持。澄清 Oracle 在尝试创建现有用户时（以及尝试删除非现有用户时）抛出的错误：
drop user foo;
ORA-01918: user 'foo' does not exist
create user existing_user IDENTIFIED BY existing_user;
ORA-01920: user name 'existing_user' conflicts with another user or role name
The statements above were executed on Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
以上语句是在 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production 上执行的
回答by Nicholas Sushkin
Another way to do it is to create the user in a PL/SQL block and catch the ORA-01920 error. That way, the block does not throw any errors when the user exists.
另一种方法是在 PL/SQL 块中创建用户并捕获 ORA-01920 错误。这样，当用户存在时，块不会抛出任何错误。
DECLARE
sqlStatement varchar2(512);
user_exists EXCEPTION;
PRAGMA EXCEPTION_INIT(user_exists, -1920);
BEGIN
sqlStatement := 'CREATE USER "Kyle" ' ||
'IDENTIFIED BY "password" ' ||
'PROFILE "Default" ' ||
'ACCOUNT UNLOCK';
EXECUTE IMMEDIATE sqlStatement;
dbms_output.put_line(' OK: ' || sqlStatement);
EXCEPTION
WHEN user_exists THEN
dbms_output.put_line('WARN: ' || sqlStatement);
dbms_output.put_line('Already exists');
WHEN OTHERS THEN
dbms_output.put_line('FAIL: ' || sqlStatement);
RAISE;
END;
/
