如果Oracle用户不存在，如何创建该用户？

Question

我正在尝试创建一个脚本，如果用户不存在的话，则会创建用户。

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'
)

以下错误信息被给出：

SQL Error: ORA-00922: missing or invalid option

我在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

在Oracle中，是否有一种类似的方法可以在尝试创建新用户之前检查用户是否已经存在？

- Kyle Williamson

---

使用EXECUTE IMMEDIATE。 - 6ton

Answer 1

在Oracle 23c之前，SQL Server中可用的IF NOT EXISTS语法在Oracle中不可用。

一般来说，Oracle脚本只会执行CREATE语句，如果对象已经存在，你会收到一个错误提示，但你可以忽略它。这就是所有标准的Oracle部署脚本所做的事情。

然而，如果你真的想要检查对象是否存在，并且只有在对象不存在时才执行，从而避免错误，你可以编写一个PL/SQL块。编写一个检查用户是否存在的SQL语句，如果用户不存在，则使用EXECUTE IMMEDIATE从PL/SQL块中执行CREATE USER。

一个这样的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;
/

---

自从Oracle 23c版本以后，可以像这样创建用户：

create user if not exists smith identified by smith;

- Mark J. Bobak

Answer 2

您需要编写一个 PL/SQL 代码块。 可以查看这里的示例。

您可以使用一些类似以下的 PL/SQL 代码检查用户是否存在于 all_users 表中:

SELECT count(*) INTO v_count_user
FROM all_users
WHERE username = 'Kyle'

然后，在 IF 条件语句中使用 v_count_user，以有条件地执行“创建用户”语句。

- 6ton

Answer 3

从前面的答案中可以看出，if not exists在Oracle中不被支持。为了澄清当尝试创建已经存在的用户时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

上述语句是在 Oracle 数据库 11g 企业版 Release 11.2.0.1.0 - 64位 Production 上执行的。

- Emil G

Answer 4

另一种方法是在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;
/

- Nicholas Sushkin