3. SQL*Plus与命令行自动化基础

SQL*Plus 是 Oracle 数据库最经典、最稳定的命令行客户端。它看起来朴素，但在自动化运维场景中价值极高：无需图形界面、可嵌入 Shell 脚本、可通过返回码判断执行结果、可批量执行 SQL 文件。掌握 SQL*Plus 的连接管理、脚本变量、格式化输出、错误处理和批量执行机制，是构建 Oracle 自动化运维体系的第一步。

连接管理：三种身份与两种连接方式

SQL*Plus 连接数据库时，需要明确“以什么身份连接”和“连接到哪里”。身份上分为三类：普通用户（如 scott/tiger）、特权用户（如 sysdba、sysoper）和操作系统认证用户。连接方式上分为本地连接和网络连接。

本地操作系统认证常用于服务器本机运维，例如以 oracle 操作系统用户执行：

sqlplus / as sysdba
sqlplus /nolog
SQL> connect / as sysdba

网络连接则通过 Easy Connect 或 TNS 别名指定目标实例：

sqlplus system/Oracle_123@//192.168.10.21:1521/orclpdb
sqlplus system/Oracle_123@orclpdb

在自动化脚本中，推荐使用 /nolog 先启动 SQL*Plus，再用 CONNECT 命令连接。这样可以把连接串、用户名、密码从命令行参数中分离，便于统一管理和避免密码出现在进程列表中。密码可通过 DEFINE 变量或外部文件传入，但要注意文件权限控制。

脚本变量：让 SQL 文件可复用

SQL*Plus 支持两种变量：替换变量（substitution variable）和绑定变量（bind variable）。替换变量在 SQL 文本被解析前完成文本替换，适合控制表名、日期、路径等；绑定变量在 SQL 执行时传值，适合传数据值，能减少硬解析。

替换变量以 & 或 && 开头。& 每次出现都会提示输入，&& 只在第一次提示，之后复用。例如：

SELECT COUNT(*) FROM &tab_name
WHERE create_date >= TO_DATE('&&start_date','YYYY-MM-DD');

DEFINE start_date = '2024-01-01'
DEFINE owner_name = 'HR'
SELECT table_name FROM dba_tables WHERE owner = '&owner_name';

在自动化脚本中，通常用 DEFINE 在脚本开头集中定义变量，或通过命令行传入：

sqlplus -s system/Oracle_123@orclpdb @check.sql HR 2024-01-01

脚本内用 &1、&2 接收位置参数。这样同一个 SQL 文件可以针对不同 schema、不同日期反复执行，是批量运维的基础。

格式化输出：让结果可读、可解析

SQL*Plus 默认输出对人工阅读不友好，列宽、页大小、行数都可能影响结果。自动化场景下，格式化输出有两个目标：一是人看得懂，二是脚本能解析。常用命令包括：

• SET LINESIZE 200：设置行宽，避免结果折行。
• SET PAGESIZE 100：设置每页行数，PAGESIZE 0 可取消分页和标题。
• SET FEEDBACK OFF：关闭“已选择 N 行”提示。
• SET HEADING OFF：关闭列标题，适合输出纯数据。
• SET TRIMSPOOL ON：去除 spool 文件行尾空格。
• COLUMN：控制列格式，如 COLUMN table_name FORMAT A30。

例如，导出某用户所有表名到文件：

SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON
SPOOL /tmp/hr_tables.lst
SELECT table_name FROM dba_tables WHERE owner = 'HR';
SPOOL OFF

如果希望输出 CSV 格式，可以用 SET COLSEP ',' 并配合 HEADING 控制。注意：SQL*Plus 不是专业导出工具，复杂格式建议用 SQLcl 或外部程序处理。

错误处理：让脚本知道“失败了”

自动化脚本最怕“看起来执行了，其实失败了”。SQL*Plus 提供了几个关键机制：

• WHENEVER SQLERROR EXIT SQL.SQLCODE：SQL 出错时以 SQLCODE 作为退出码退出。
• WHENEVER OSERROR EXIT FAILURE：操作系统错误时退出。
• WHENEVER SQLERROR CONTINUE：出错继续执行，适合收集多个错误。
• SET ERRORLOGGING ON：将错误记录到表，便于事后分析。

典型脚本头部写法：

WHENEVER OSERROR EXIT 1
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET ECHO OFF
SET FEEDBACK ON

Shell 中通过 $? 判断：

sqlplus -s system/Oracle_123@orclpdb @batch.sql
rc=$?
if [ $rc -ne 0 ]; then
  echo "SQL*Plus failed with code $rc"
  exit $rc
fi

这样上层调度系统就能准确感知失败，触发告警或重试。

批量执行 SQL 文件

批量执行通常有三种方式：

1. 在 SQL*Plus 内用 @file.sql 或 @@file.sql 调用子脚本。
2. 用 Shell 循环调用多个 SQL 文件。
3. 用 SPOOL 汇总输出，配合日志归档。

例如，批量执行一个目录下所有 SQL 文件：

for f in /opt/dba/sql/*.sql; do
  echo "Running $f ..."
  sqlplus -s system/Oracle_123@orclpdb @"$f"
  if [ $? -ne 0 ]; then
    echo "FAILED: $f"
    exit 1
  fi
done

如果 SQL 文件之间有依赖顺序，建议用主脚本显式控制：

@01_create_table.sql
@02_load_data.sql
@03_create_index.sql

下面这张图概括了 SQL*Plus 自动化脚本从连接、变量替换、执行、错误判断到批量调度的核心链路，帮助理解各环节的输入输出关系。

一个可复用的批量检查脚本示例

下面给出一个较完整的示例：检查多个用户下的无效对象，输出到日志，并根据结果返回退出码。示例中的用户名、密码、服务名均为演示用，实际执行时需替换。

#!/bin/bash
# check_invalid.sh
export ORACLE_SID=orcl
LOG=/tmp/invalid_$(date +%Y%m%d).log

sqlplus -s system/Oracle_123@orclpdb <<EOF
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET PAGESIZE 0
SET FEEDBACK OFF
SET HEADING OFF
SET TRIMSPOOL ON
SPOOL $LOG
SELECT owner || '.' || object_name || ' [' || object_type || ']'
FROM dba_objects
WHERE status = 'INVALID'
  AND owner IN ('HR','SCOTT','APP');
SPOOL OFF
EXIT 0
EOF

rc=$?
if [ $rc -ne 0 ]; then
  echo "Check failed, rc=$rc"
  exit $rc
fi

if [ -s "$LOG" ]; then
  echo "Found invalid objects:"
  cat "$LOG"
else
  echo "No invalid objects."
fi

这个脚本体现了几个关键点：用 WHENEVER SQLERROR 控制退出码；用 SPOOL 收集结果；用 -s 静默模式减少无关输出；Shell 根据 $? 和日志内容做二次判断。实际运维中，可以把这类脚本注册到 crontab 或调度平台，实现每日自动巡检。

小结与常见注意点

SQL*Plus 自动化的核心可以归纳为四条：连接要安全可复用，变量要参数化，输出要可解析，错误要可感知。实际落地时还需注意：

• 脚本文件建议使用 UTF-8 或与数据库字符集匹配的编码，避免中文乱码。
• 密码不要硬编码在脚本中，使用 Wallet 或权限受控的凭据文件。
• 批量执行前先做 dry-run 或输出 SQL 预览，避免误操作生产数据。
• 日志文件要带时间戳和任务名，便于事后追溯。
• 对于复杂逻辑，SQL*Plus 能力有限，可考虑 SQLcl、Python cx_Oracle 或 Shell+SQL 组合。