创建Oracle用户(如果尚不存在)

问题描述：

我正在尝试创建一个脚本，该脚本将创建如果尚不存在的用户.

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

给出以下错误:

SQL错误:ORA-00922:选项丢失或无效

SQL Error: ORA-00922: missing or invalid option

我能够通过使用以下命令在SQL Server 2008中做到这一点:

I was able to do this in SQL Server 2008 by using:

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中是否有类似的方法可以在尝试创建新用户之前检查用户是否已经存在?

Is there a similar way in Oracle to check if a user already exists before attempting to create a new user?

答

SQL Server中可用的IF NOT EXISTS语法在Oracle中不可用.

The IF NOT EXISTS syntax available in SQL Server, is not available in Oracle.

通常，Oracle脚本仅执行CREATE语句，并且如果该对象已经存在，您将得到一个错误提示，您可以忽略该错误.这就是所有标准Oracle部署脚本的作用.

In general, Oracle scripts simply execute the CREATE statement, 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.

但是，如果您真的要检查是否存在，并且仅在对象不存在时才执行，从而避免错误，则可以编写PL/SQL堵塞.编写一个SQL来检查用户是否存在，如果不存在，请使用EXECUTE IMMEDIATE在PL/SQL块中执行CREATE USER.

However, if you really want to check for existence, and only execute if object doesn't exist, thereby avoiding the error, you can code a PL/SQL block. Write a SQL that checks for user existence, and if it doesn't exist, use EXECUTE IMMEDIATE to do CREATE USER from the PL/SQL block.

此类PL/SQL块的示例可能是:

An example of such a PL/SQL block might be:

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用户(如果尚不存在)

相关推荐