sql脚本

下面我将以“你是一个只熟悉 MySQL 基本 DML 的开发人员，要负责 Oracle 相关工作”为前提，系统性讲解 Oracle PL/SQL 的常用语法（变量、列表、循环、判断、异常、日志等），并给出一套可直接用于生产、具有高健壮性的示例脚本模板。

内容分成两部分：

一、PL/SQL 最常用语法讲解（上手即可用）

Oracle 的 PL/SQL 是一种过程化 SQL 语言，语法类似于：

SQL（DML/DQL）
Java（变量、循环、异常等结构）

1. PL/SQL 代码的基本结构

DECLARE
    -- 变量声明区
BEGIN
    -- 执行区
EXCEPTION
    -- 异常捕获区
END;
/

注意：最后要加 / 来执行整个块。

2. 变量声明（DECLARE）

2.1 基本类型

DECLARE
    v_name VARCHAR2(50);
    v_age  NUMBER := 18;      -- 直接赋初值
    v_now  DATE := SYSDATE;
BEGIN
    NULL;
END;
/

2.2 使用表字段的类型（推荐）

避免字段类型变化产生脚本报错：

v_emp_name employees.last_name%TYPE;

表示“变量的类型由 employees 表中 last_name 的类型自动决定”。

3. 常用数据结构 LIST/ARRAY（集合）

Oracle 提供三种集合：

集合类型	是否需要 SQL 域支持	是否可作为表列类型	是否可遍历
VARRAY	否	是	是
Nested Table	否	是	是
Associative Array（最常用）	否	否	是

最常用的是 Associative Array（Key-Value 类似 Map/List）：

DECLARE
    TYPE t_ids IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    v_ids t_ids;

BEGIN
    v_ids(1) := 10;
    v_ids(2) := 20;
    v_ids(3) := 30;
END;
/

遍历：

FOR i IN v_ids.FIRST .. v_ids.LAST LOOP
    IF v_ids.EXISTS(i) THEN
        DBMS_OUTPUT.PUT_LINE('Value=' || v_ids(i));
    END IF;
END LOOP;

4. 赋值方式

4.1 直接赋值

v_name := 'Tom';

4.2 SQL 查询赋值（SELECT INTO）

PL/SQL 必须使用 INTO：

SELECT last_name INTO v_name
FROM employees
WHERE employee_id = 100;

（注意：如返回 0 或多行会报错，异常捕获详见后面）

5. 条件判断（IF / CASE）

5.1 IF 语句

IF v_age > 18 THEN
    v_status := 'adult';
ELSIF v_age = 18 THEN
    v_status := 'just adult';
ELSE
    v_status := 'child';
END IF;

5.2 CASE

v_type := CASE 
             WHEN v_age >= 60 THEN 'senior'
             WHEN v_age >= 18 THEN 'adult'
             ELSE 'child'
          END;

6. 循环（FOR/WHILE/LOOP）

6.1 FOR 循环

FOR i IN 1..10 LOOP
    DBMS_OUTPUT.PUT_LINE(i);
END LOOP;

6.2 WHILE 循环

WHILE v_count < 10 LOOP
    v_count := v_count + 1;
END LOOP;

6.3 LOOP + EXIT

LOOP
    EXIT WHEN v_flag = 1;
END LOOP;

7. 异常捕获（EXCEPTION）

Oracle PL/SQL 的异常比较强大，常用如下：

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('查询不到数据');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('返回多行');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('未知错误: ' || SQLERRM);
END;
/

SQLERRM：返回错误消息
SQLCODE：返回错误代码

8. 打印日志（DBMS_OUTPUT）

DBMS_OUTPUT.PUT_LINE('Hello');

注意：某些 IDE（如 SQLPlus 需要开启）

SET SERVEROUTPUT ON;

在 PL/SQL 块中生产环境通常不使用 DBMS_OUTPUT，而是：

插入日志表
使用 PRAGMA AUTONOMOUS_TRANSACTION 独立提交（稍后讲）

9. 事务控制（COMMIT/ROLLBACK）

在 PL/SQL 中：

默认需要 你显式 COMMIT
异常情况下需要 rollback

二、一套可直接用于生产的“高健壮性 Oracle 脚本模板”

下面是一个银行/金融行业常用的模板，适用于：

✔ 表下线 ✔ 数据修复脚本 ✔ 数据迁移脚本 ✔ 批处理脚本 ✔ 任何需要可恢复性的 SQL

📌生产级 PL/SQL 脚本模板（含日志、异常、事务、恢复方案）

SET SERVEROUTPUT ON;
DECLARE
    -- 日志变量
    v_step   VARCHAR2(100);
    v_msg    VARCHAR2(4000);

    -- 实际业务变量
    v_count  NUMBER := 0;

    -- 自治事务用于写日志
    PRAGMA AUTONOMOUS_TRANSACTION;

    -- 日志函数
    PROCEDURE write_log(p_step VARCHAR2, p_msg VARCHAR2) IS
    BEGIN
        INSERT INTO system_script_log(run_time, step, message)
        VALUES (SYSDATE, p_step, p_msg);
        COMMIT;    -- 独立事务，记录日志不影响主流程
    END;

BEGIN
    v_step := '1. 开始执行脚本';
    write_log(v_step, '开始时间=' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));

    ---------------------------------------------------------------------
    -- 第一步：校验数据
    ---------------------------------------------------------------------
    v_step := '2. 数据校验';
    SELECT COUNT(*) INTO v_count FROM employees WHERE status IS NULL;

    write_log(v_step, '空状态记录数=' || v_count);

    ---------------------------------------------------------------------
    -- 第二步：实际修改数据
    ---------------------------------------------------------------------
    v_step := '3. 更新状态';

    UPDATE employees
       SET status = 'ACTIVE'
     WHERE status IS NULL;

    write_log(v_step, '更新行数=' || SQL%ROWCOUNT);

    ---------------------------------------------------------------------
    -- 第三步：提交事务
    ---------------------------------------------------------------------
    v_step := '4. 完成提交';
    COMMIT;

    write_log(v_step, '脚本执行完成');

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        write_log('异常', 'NO DATA FOUND');
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('异常：未找到数据');

    WHEN OTHERS THEN
        write_log('异常', SQLERRM);
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('未知错误: ' || SQLERRM);

END;
/

这套模板的特点

特性	说明
可恢复性强	所有关键操作前都写日志
异常安全	任意报错立刻 rollback
独立日志事务	日志不会因为 rollback 丢失
信息透明	明确每一步执行情况
可审计	日志表可永久保存 audit 信息

三、如何写一个健壮性高的 Oracle 脚本（总结）

✔ 1. 必须记录日志（使用自治事务）

✔ 2. 必须有异常捕获

✔ 3. 关键步骤前必须写日志（方便恢复）

✔ 4. 永远不要使用隐式提交（如 DDL）

✔ 5. 检查影响行数，避免误伤（SQL%ROWCOUNT）

✔ 6. 使用 %TYPE,%ROWTYPE 来兼容字段变更

✔ 7. 要有“干跑”模式（DRY RUN），只统计不更新

✔ 8. 避免一次更新百万行，使用分批处理（LIMIT 1000）

如果你需要，我可以给你：

✅ 生产级分批处理脚本模板（例如每次处理 1000 条） ✅ 表下线专业脚本模板（非常适合你现在的银行项目） ✅ 触发器/视图依赖查询脚本 ✅ Oracle 常用工具脚本合集

如果你愿意，我可以根据你实际要下线的具体表，给你写一份完整可执行、带日志、可回滚的专业脚本。

一、PL/SQL 最常用语法讲解（上手即可用）

1. PL/SQL 代码的基本结构

2. 变量声明（DECLARE）

2.1 基本类型​

2.2 使用表字段的类型（推荐）​

3. 常用数据结构 LIST/ARRAY（集合）

4. 赋值方式

4.1 直接赋值​

4.2 SQL 查询赋值（SELECT INTO）​

5. 条件判断（IF / CASE）

5.1 IF 语句​

5.2 CASE​

6. 循环（FOR/WHILE/LOOP）

6.1 FOR 循环​

6.2 WHILE 循环​

6.3 LOOP + EXIT​