Shell脚本中通过SQLPlus检查Oracle表是否存在并创建

看起来你的脚本在表不存在时会输出冗余的错误信息，而且没有实现表不存在则创建的核心功能。咱们来修复这个问题，同时优化整体逻辑：

原脚本的核心问题 #

• 用SELECT COUNT(*) FROM table_name来判断表存在性，当表不存在时，即使PL/SQL捕获了异常，sqlplus仍会把编译错误输出到标准输出，导致后续变量tabcount包含错误内容。
• 缺少创建表的逻辑分支，只做了存在性提示。

改进后的脚本 #

这个脚本会先通过Oracle数据字典检查表是否存在，避免抛出异常，然后根据结果自动创建表：

#!/bin/bash
# 配置数据库连接信息和目标表名
DB_USER="user"
DB_PASS="pass"
TARGET_TABLE="your_table_name"
# 临时文件存储sqlplus输出（也可以用管道直接处理）
TEMP_OUTPUT="/tmp/table_check.tmp"

# 执行sqlplus逻辑，检查表是否存在并创建
sqlplus -s "${DB_USER}/${DB_PASS}" << EOF > ${TEMP_OUTPUT}
SET SERVEROUTPUT ON
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
DECLARE
    tab_exists NUMBER;
BEGIN
    -- 查询当前用户下的表是否存在（如果是其他用户的表，换成ALL_TABLES并加上OWNER条件）
    SELECT COUNT(1) INTO tab_exists 
    FROM USER_TABLES 
    WHERE TABLE_NAME = UPPER('${TARGET_TABLE}');
    
    IF tab_exists = 0 THEN
        -- 表不存在则创建，这里替换成你的建表语句
        EXECUTE IMMEDIATE 'CREATE TABLE ${TARGET_TABLE} (
            id NUMBER,
            name VARCHAR2(50),
            create_date DATE
        )';
        DBMS_OUTPUT.PUT_LINE('Table ${TARGET_TABLE} created successfully.');
    ELSE
        -- 表存在时，可选输出表行数
        DECLARE
            tab_count NUMBER;
        BEGIN
            SELECT COUNT(1) INTO tab_count FROM ${TARGET_TABLE};
            DBMS_OUTPUT.PUT_LINE('Table ${TARGET_TABLE} exists, row count: ' || tab_count);
        EXCEPTION
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('Table ${TARGET_TABLE} exists, but cannot get row count: ' || SQLERRM);
        END;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
END;
/
EXIT
EOF

# 读取并输出结果
cat ${TEMP_OUTPUT}
# 清理临时文件
rm -f ${TEMP_OUTPUT}

关键优化点说明 #

• 用数据字典查询替代直接查询表：通过USER_TABLES（当前用户的表）检查表存在性，不会抛出ORA-00942错误，逻辑更稳定。如果要检查其他用户的表，改用ALL_TABLES并添加OWNER = 'OTHER_USER'条件。
• 动态执行建表语句：用EXECUTE IMMEDIATE执行DDL语句，因为PL/SQL块中不能直接写CREATE TABLE。
• 静默模式与输出控制：sqlplus -s开启静默模式，配合SET FEEDBACK OFF等命令，避免输出多余的sqlplus提示信息。
• 错误处理：添加全局异常捕获，输出具体错误信息，方便排查问题。

测试场景验证 #

1. 表存在且为空：输出Table your_table_name exists, row count: 0
2. 表存在且非空：输出Table your_table_name exists, row count: N（N为实际行数）
3. 表不存在：输出Table your_table_name created successfully.

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