Oracle Shell脚本创建用户：仅忽略用户已存在错误

要实现仅忽略用户已存在的错误（ORA-01920），遇到表空间不存在等其他错误时终止脚本的需求，推荐将create_users.sql中的创建用户逻辑改为PL/SQL块处理，这样可以精确捕获特定错误码，同时保留原shell脚本的全局错误终止规则。

方法一：用PL/SQL块逐个处理用户创建 #

修改后的create_users.sql内容如下：

SET SERVEROUTPUT ON;

-- 创建用户vijay
BEGIN
  EXECUTE IMMEDIATE 'CREATE USER vijay IDENTIFIED BY vijay';
  DBMS_OUTPUT.PUT_LINE('用户vijay创建成功');
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -1920 THEN -- 匹配ORA-01920: 用户名称冲突错误
      DBMS_OUTPUT.PUT_LINE('用户vijay已存在，跳过创建');
    ELSE
      DBMS_OUTPUT.PUT_LINE('创建用户vijay失败: ' || SQLERRM);
      RAISE; -- 抛出非预期错误，触发脚本终止
    END IF;
END;
/

-- 创建用户ramesh
BEGIN
  EXECUTE IMMEDIATE 'CREATE USER ramesh IDENTIFIED BY ramesh';
  DBMS_OUTPUT.PUT_LINE('用户ramesh创建成功');
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -1920 THEN
      DBMS_OUTPUT.PUT_LINE('用户ramesh已存在，跳过创建');
    ELSE
      DBMS_OUTPUT.PUT_LINE('创建用户ramesh失败: ' || SQLERRM);
      RAISE;
    END IF;
END;
/

-- 执行授权操作（用户存在则正常执行，用户不存在则触发错误终止脚本）
GRANT SELECT_CATALOG_ROLE TO vijay;
GRANT SELECT ON app1.table_name1 TO vijay;
GRANT SELECT_CATALOG_ROLE TO ramesh;
GRANT SELECT ON app1.table_name1 TO ramesh;

方法二：封装存储过程简化批量创建（推荐） #

如果需要创建大量用户，将逻辑封装为存储过程更简洁：

SET SERVEROUTPUT ON;

-- 创建通用的用户创建存储过程
CREATE OR REPLACE PROCEDURE create_user_if_not_exists(
  p_username IN VARCHAR2,
  p_password IN VARCHAR2
) IS
BEGIN
  EXECUTE IMMEDIATE 'CREATE USER ' || p_username || ' IDENTIFIED BY ' || p_password;
  DBMS_OUTPUT.PUT_LINE('用户 [' || p_username || '] 创建成功');
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE = -1920 THEN
      DBMS_OUTPUT.PUT_LINE('用户 [' || p_username || '] 已存在，跳过');
    ELSE
      DBMS_OUTPUT.PUT_LINE('创建用户 [' || p_username || '] 出错: ' || SQLERRM);
      RAISE; -- 抛出错误终止脚本
    END IF;
END;
/

-- 调用存储过程批量创建用户
BEGIN
  create_user_if_not_exists('VIJAY', 'VIJAY');
  create_user_if_not_exists('RAMESH', 'RAMESH');
  -- 可添加更多用户
END;
/

-- 授权语句
GRANT SELECT_CATALOG_ROLE TO vijay;
GRANT SELECT ON app1.table_name1 TO vijay;
GRANT SELECT_CATALOG_ROLE TO ramesh;
GRANT SELECT ON app1.table_name1 TO ramesh;

原shell脚本无需修改 #

保留原shell脚本中的WHENEVER SQLERROR EXIT 1;，当PL/SQL块抛出非预期错误（如ORA-01536：表空间配额超出、ORA-00959：表空间不存在等）时，SQL*Plus会立即退出并返回状态码1，终止整个自动化流程。

关键逻辑说明 #

• EXECUTE IMMEDIATE：因为CREATE USER是DDL语句，无法直接在PL/SQL块中执行，所以用动态SQL执行。
• SQLCODE = -1920：精准匹配用户已存在的错误码，仅忽略该类错误。
• RAISE：遇到其他错误时重新抛出，触发全局的WHENEVER SQLERROR EXIT 1规则，确保脚本终止。

内容的提问来源于stack exchange，提问作者vijaymec50