Создание пользователя Oracle, если он еще не существует

Я пытаюсь создать script, который создаст пользователей , если они еще не существуют.

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: ORA-00922: отсутствует или недействительный параметр

Я смог сделать это в 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 проверить, существует ли пользователь, прежде чем пытаться создать нового пользователя?

Ответ 1

Синтаксис IF NOT EXISTS, доступный в SQL Server, недоступен в Oracle.

В общем, сценарии Oracle просто выполняют оператор CREATE, и если объект уже существует, вы получите сообщение об ошибке, которое вы можете игнорировать. Это то, что делают все стандартные сценарии развертывания Oracle.

Однако, если вы действительно хотите проверить существование и выполнять только в том случае, если объект не существует, что позволяет избежать ошибки, вы можете закодировать блок PL/SQL. Напишите SQL, который проверяет существование пользователя, и, если он не существует, используйте EXECUTE IMMEDIATE для выполнения CREATE USER из блока PL/SQL.

Примером такого блока 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;
/

Надеюсь, это поможет.

Ответ 2

Вам нужно написать блок pl/sql. Смотрите пример здесь

Вы можете проверить, существует ли пользователь в таблице all_users, используя некоторый код pl/sql, например:

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

а затем используйте v_count_user в условии IF для условного выполнения инструкции create user.

Ответ 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 Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

Ответ 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;
/