DM8存储过程

一、 DM存储过程介绍

存储过程是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。
存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户通过指定存储过程的名字并给定参数来调用执行。

处理模式	执行流程	核心问题	适用场景
应用层 SQL	应用层拼接 SQL → 网络传输 → 数据库解析 SQL → 执行 → 返回结果	1. 网络 IO 开销大（尤其批量处理）；<br>2. SQL 重复解析，性能损耗；<br>3. 业务逻辑分散在应用层，维护成本高	简单查询、单条数据操作
DM 存储过程	应用层调用存储过程 → 数据库直接执行预编译 SQL → 返回结果	1. 需提前定义，灵活性略低；<br>2. 调试难度高于普通 SQL	批量数据处理、复杂业务逻辑（如报表生成、数据同步）、权限管控严格的场景

优点：
• 存储过程可封装，并隐藏复杂的商业逻辑。
• 存储过程可以回传值，并可以接收参数。
• 存储过程无法使用SELECT指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
• 存储过程可以用在数据校验，实现商业逻辑等。
缺点：
• 存储过程，往往定制化于特定的数据库上，当切换到其他厂商的数据库系统时，需要重写原有存储过程。（DM数据库数据迁移工具DTS已经可以实现大多数主流数据库的存储过程适配，但有些存储过程仍然需要人为进行修改，此时我们就需要学习DM数据库的存储过程）
存储过程最大的优点就是可以通过“数据处理本地化”来提升性能：假设需批量更新 10 万条数据：若用应用层循环执行 UPDATE，需 10 万次网络传输 + 10 万次 SQL 解析；而用 DM 存储过程，仅需 1 次调用（网络传输）+1 次预编译（存储过程创建时已完成），执行效率可提升 10 倍以上。这也是金融、电商等 “大数据量处理场景” 优先选择存储过程的核心原因。

二、准备测试数据

创建用户表

CREATE TABLE USER_INFO (
    USER_ID INT PRIMARY KEY,          -- 用户ID（主键）
    USER_NAME VARCHAR(50) NOT NULL,   -- 用户名
    PHONE VARCHAR(20) UNIQUE,         -- 手机号（唯一）
    REGISTER_TIME DATETIME NOT NULL,  -- 注册时间
    STATUS CHAR(1) DEFAULT '1'        -- 状态：1-正常，0-禁用
);

创建订单表

CREATE TABLE ORDER_INFO (
    ORDER_ID INT PRIMARY KEY,         -- 订单ID（主键）
    USER_ID INT NOT NULL,             -- 关联用户ID
    ORDER_AMOUNT DECIMAL(10,2) NOT NULL, -- 订单金额
    ORDER_TIME DATETIME NOT NULL,     -- 下单时间
    PAY_STATUS CHAR(1) DEFAULT '0',   -- 支付状态：0-未支付，1-已支付，2-退款
    FOREIGN KEY (USER_ID) REFERENCES USER_INFO(USER_ID) -- 外键关联用户表
);

插入测试数据


--插入3个用户
INSERT INTO USER_INFO (USER_ID, USER_NAME, PHONE, REGISTER_TIME, STATUS)
VALUES 
(1, '张三', '13800138000', '2026-01-01 10:00:00', '1'),
(2, '李四', '13900139000', '2026-02-15 14:30:00', '1'),
(3, '王五', '13700137000', '2026-03-20 09:15:00', '0');

--插入5个订单
INSERT INTO ORDER_INFO (ORDER_ID, USER_ID, ORDER_AMOUNT, ORDER_TIME, PAY_STATUS)
VALUES 
(1001, 1, 99.90, '2026-01-02 08:30:00', '1'),
(1002, 1, 199.50, '2026-01-10 15:20:00', '1'),
(1003, 2, 2999.00, '2026-02-16 11:00:00', '0'),
(1004, 2, 499.00, '2026-03-01 16:40:00', '1'),
(1005, 3, 89.00, '2026-03-21 10:10:00', '0');

-- 提交事务
COMMIT;

三、 DM存储过程核心语法基础

1. 创建存储过程：

最简单的存储过程：Hello DM!
学习DM存储过程首先先从最简单的存储过程开始。我们创建一个存储过程，实现调用时输出"Hello DM!"
创建存储过程SQL

-- 创建存储过程PROC_HELLO_DM
CREATE OR REPLACE PROCEDURE PROC_HELLO_DM
AS
-- 声明部分：变量、游标等（此案例无变量，故为空）
BEGIN
    -- 执行部分：业务逻辑
    DBMS_OUTPUT.PUT_LINE('Hello DM!'); -- 输出内容
END;

关键语法解析
• CREATE OR REPLACE：若存储过程已存在，则覆盖（避免 “存储过程已存在” 报错）；
• PROCEDURE PROC_HELLO_DM：PROCEDURE是关键字，PROC_HELLO_DM是存储过程名（命名规范：前缀 PROC_+ 业务含义）；
• AS：分隔 “存储过程名” 与 “声明部分”（也可使用IS，DM 中AS与IS等价）；
• BEGIN…END：执行部分，包含具体的业务逻辑；
• DBMS_OUTPUT.PUT_LINE：DM 提供的输出函数，类似 Java 的System.out.println，需开启输出才能看到结果。
调用存储过程
在DM中，我们通过call语句调用存储过程

CALL PROC_HELLO_DM();

2. 变量的声明和使用

变量是存储过程中存储临时数据的 “容器”，DM 支持多种变量类型（如INT、VARCHAR、DATETIME、DECIMAL），且支持 % TYPE % ROWTYPE”等动态类型，提升代码灵活性。

1) 基本变量声明和赋值

创建存储过程，查询用户ID=1的用户名和注册时间，输出
创建存储过程SQL

CREATE OR REPLACE PROCEDURE PROC_QUERY_USER
AS
    -- 声明变量：变量名 类型 [默认值]
    v_user_id INT := 1; -- 用户ID，默认值1
    v_user_name VARCHAR(50); -- 用户名
    v_register_time DATETIME; -- 注册时间
BEGIN
    -- 1. 从USER_INFO表查询数据，赋值给变量
    SELECT USER_NAME, REGISTER_TIME
    INTO v_user_name, v_register_time -- 注意：INTO后变量顺序需与SELECT字段顺序一致
    FROM USER_INFO
    WHERE USER_ID = v_user_id;

    -- 2. 输出结果
    DBMS_OUTPUT.PUT_LINE('用户ID：' || v_user_id); -- ||是字符串拼接符
    DBMS_OUTPUT.PUT_LINE('用户名：' || v_user_name);
    DBMS_OUTPUT.PUT_LINE('注册时间：' || TO_CHAR(v_register_time, 'YYYY-MM-DD HH24:MI:SS')); -- 格式化日期
END;

关键语法解析
• 变量声明格式：变量名类型 [:= 默认值]，如v_user_id INT := 1；
• INTO子句：用于将SELECT查询结果赋值给变量，要求查询结果只能有1行
• 字符串拼接：DM用||拼接字符串，如：‘用户ID：’ || v_user_id
• 日期格式化：TO_CHAR(v_register_time, ‘YYYY-MM-DD HH24:MI:SS’)将DATETIME类型转换为字符串，避免默认格式。
调用存储结果

2) 动态类型：%TYPE与%ROWTYPE

如果当表结构发生变化时，若变量声明时用固定类型，则原本的存储过程会报错，需要手动修改存储过程；而使用%TYPE(引用列类型)或%ROWTYPE引用表行类型，可以自动适配表结构变化，减少维护成本。
%TYPE：引用列类型
使用%TYPE声明变量，查询用户 ID=2 的手机号。
存储过程SQL

CREATE OR REPLACE PROCEDURE PROC_QUERY_USER_PHONE
AS
    -- v_phone的类型与USER_INFO表的PHONE列一致
    v_phone USER_INFO.PHONE%TYPE;
    v_user_id USER_INFO.USER_ID%TYPE := 2; -- v_user_id的类型与USER_ID列一致
BEGIN
    SELECT PHONE
    INTO v_phone
    FROM USER_INFO
    WHERE USER_ID = v_user_id;

    DBMS_OUTPUT.PUT_LINE('用户ID=' || v_user_id || '的手机号：' || v_phone);
END;

调用结果

%ROWTYPE：引用表行类型
用%ROWTYPE声明变量，存储用户 ID=3 的整行数据，并输出用户名和状态
存储过程SQL:

CREATE OR REPLACE PROCEDURE PROC_QUERY_USER_ROW
AS
    -- v_user_row的类型与USER_INFO表的“一行数据”一致，包含所有列
    v_user_row USER_INFO%ROWTYPE;
BEGIN
    SELECT *
    INTO v_user_row
    FROM USER_INFO
    WHERE USER_ID = 3;

    DBMS_OUTPUT.PUT_LINE('用户名：' || v_user_row.USER_NAME);
    DBMS_OUTPUT.PUT_LINE('状态：' || CASE v_user_row.STATUS WHEN '1' THEN '正常' WHEN '0' THEN '禁用' END);
END;

调用结果

使用%ROWTYPE比单独声明多个变量更简洁。例如，若需访问用户的USER_NAME、PHONE、REGISTER_TIME三个列，用%ROWTYPE只需 1 个变量，而单独声明需 3 个变量。

3. 存储过程流程控制

存储过程的核心用法就是实现复杂业务逻辑，流程控制（if else,LOOP循环等）是实现逻辑分支的关键。

1) IF条件判断

创建存储过程，输入用户ID，判断用户状态
• 若状态为 1，输出 “用户 [用户名] 正常”；
• 若状态为 0，输出 “用户 [用户名] 已禁用”；
• 若状态为其他值，输出 “用户状态异常”
创建存储过程SQL

CREATE OR REPLACE PROCEDURE PROC_JUDGE_USER_STATUS(
    -- 输入参数：IN表示“仅传入值，不返回”
    p_user_id IN INT
)
AS
    v_user_name USER_INFO.USER_NAME%TYPE;
    v_status USER_INFO.STATUS%TYPE;
BEGIN
    -- 查询用户状态和用户名
    SELECT USER_NAME, STATUS
    INTO v_user_name, v_status
    FROM USER_INFO
    WHERE USER_ID = p_user_id;

    -- IF条件判断
    IF v_status = '1' THEN
        DBMS_OUTPUT.PUT_LINE('用户' || v_user_name || '（ID=' || p_user_id || '）正常');
    ELSIF v_status = '0' THEN -- 注意：DM中是ELSIF，不是ELSE IF
        DBMS_OUTPUT.PUT_LINE('用户' || v_user_name || '（ID=' || p_user_id || '）已禁用');
    ELSE
        DBMS_OUTPUT.PUT_LINE('用户' || v_user_name || '（ID=' || p_user_id || '）状态异常');
    END IF;
END;

关键语法解析
• 输入参数：IN p_user_id INT表示p_user_id是输入参数，调用时必须传入值；
• IF…ELSIF…ELSE：多分支判断，注意ELSIF的拼写（无空格，非ELSE IF）；
• END IF：必须闭合IF块，否则会报语法错误。
调用存储过程

2) 循环语句

DMSQL 程序支持五种类型的循环语句：LOOP 语句、WHILE 语句、FOR 语句、REPEAT 语句和 FORALL 语句。其中前四种为基本类型的循环语句：LOOP 语句循环重复执行一系列语句，直到 EXIT 语句终止循环为止；WHILE 语句循环检测一个条件表达式，当表达式的值为 TRUE 时就执行循环体的语句序列；FOR 语句对一系列的语句重复执行指定次数的循环；REPEAT 语句重复执行一系列语句直至达到条件表达式的限制要求。FORALL 语句对一条 DML 语句执行多次，当 DML 语句中使用数组或嵌套表时可进行优化处理，能大幅提升性能。
在这里重点讲解最常用的LOOP和FOR
LOOP：无限循环
创建存储过程，批量插入5条用户测试数据,使用LOOP实现
存储过程SQL

CREATE OR REPLACE PROCEDURE PROC_INSERT_USER_LOOP
AS
    v_user_id INT := 10; -- 起始用户ID
    v_count INT := 0; -- 计数器，控制插入次数
BEGIN
    -- 无限循环
    LOOP
        -- 插入数据
        INSERT INTO USER_INFO (USER_ID, USER_NAME, PHONE, REGISTER_TIME, STATUS)
        VALUES (v_user_id, '测试用户' || v_user_id, '13800' || v_user_id || '0000', SYSDATE, '1');

        -- 计数器+1
        v_count := v_count + 1;

        -- 退出条件：插入5次后退出
        EXIT WHEN v_count >= 5;

        -- 用户ID+1
        v_user_id := v_user_id + 1;
    END LOOP;

    -- 提交事务（批量插入需手动提交）
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('成功插入' || v_count || '条用户数据');
END;

调用结果与验证

FOR循环：固定次数循环
创建存储过程，用FOR循环查询用户 ID 从 1 到 5 的用户名，输出结果。
存储过程SQL

CREATE OR REPLACE PROCEDURE PROC_QUERY_USER_FOR
AS
    v_user_name USER_INFO.USER_NAME%TYPE;
BEGIN
    -- FOR循环：从1到5（包含5），每次i+1
    FOR i IN 1..5 LOOP
        -- 捕获“用户ID不存在”的错误（避免循环中断）
        BEGIN
            SELECT USER_NAME
            INTO v_user_name
            FROM USER_INFO
            WHERE USER_ID = i;

            DBMS_OUTPUT.PUT_LINE('用户ID=' || i || '：' || v_user_name);
        EXCEPTION
            WHEN NO_DATA_FOUND THEN -- 未找到数据时触发
                DBMS_OUTPUT.PUT_LINE('用户ID=' || i || '：不存在');
        END;
    END LOOP;
END;

调用结果

为什么在FOR循环中加EXCEPTION？
若用户 ID=4 不存在，SELECT…INTO会报NO_DATA_FOUND错误，若不捕获，整个存储过程会中断，后续的用户 ID=5 查询也无法执行。在循环内部加局部EXCEPTION，可确保即使某一次循环出错，后续循环仍能正常执行。

4. 游标

当存储过程需要处理SELECT返回的多行数据时（如批量更新订单状态、批量计算用户积分），变量无法满足需求，此时需用游标（CURSOR）。游标本质是 “指向多行结果集的指针”，可逐行读取数据并处理。
静态游标是只读游标，它总是按照打开游标时的原样显示结果集，在编译时就能确定静态游标使用的查询。
静态游标又分为两种：隐式游标和显式游标。
隐式游标：隐式游标使用 FOR … IN 结构，由系统自动管理游标的打开、读取和关闭，代码简洁，推荐在 DM8 中使用。
显示游标：显式游标需要手动管理 OPEN、FETCH、CLOSE，在 DM8 中也支持。更适合需要精细控制游标行为的场景；
创建存储过程，查询所有"已支付"（PAY_STATUS=1）的订单，输出订单ID、用户ID、订单金额，并计算总金额。
存储过程SQL
隐式游标

CREATE OR REPLACE PROCEDURE PROC_QUERY_PAID_ORDERS 
AS
    v_total_amount DECIMAL(12, 2) := 0; -- 总金额
BEGIN
    -- 隐式游标遍历订单数据
    FOR rec IN (
        SELECT ORDER_ID, USER_ID, ORDER_AMOUNT 
        FROM ORDER_INFO 
        WHERE PAY_STATUS = '1'
    ) LOOP
        -- 输出订单信息
        DBMS_OUTPUT.PUT_LINE('订单ID：' || rec.ORDER_ID || '，用户ID：' || rec.USER_ID || '，金额：' || rec.ORDER_AMOUNT);
        -- 累加总金额
        v_total_amount := v_total_amount + rec.ORDER_AMOUNT;
    END LOOP;

    -- 输出总金额
    DBMS_OUTPUT.PUT_LINE('------------------------');
    DBMS_OUTPUT.PUT_LINE('已支付订单总金额：' || v_total_amount);
END;

显示游标

CREATE OR REPLACE PROCEDURE PROC_QUERY_PAID_ORDERS 
AS
    -- 声明显式游标
    CURSOR cur_paid_orders IS 
        SELECT ORDER_ID,USER_ID,ORDER_AMOUNT 
        FROM ORDER_INFO 
        WHERE PAY_STATUS = '1';

    -- 声明变量用于接收游标数据
    v_order_id ORDER_INFO.ORDER_ID%TYPE;
    v_user_id ORDER_INFO.USER_ID%TYPE;
    v_order_amount ORDER_INFO.ORDER_AMOUNT%TYPE;

    v_total_amount DECIMAL(12, 2) := 0; -- 总金额
BEGIN
    -- 打开游标
    OPEN cur_paid_orders;

    -- 循环读取数据
    LOOP
        FETCH cur_paid_orders INTO v_order_id, v_user_id, v_order_amount;
        EXIT WHEN cur_paid_orders%NOTFOUND;

        -- 输出订单信息
        DBMS_OUTPUT.PUT_LINE('订单ID：' || v_order_id || '，用户ID：' || v_user_id || '，金额：' || v_order_amount);
        -- 累加总金额
        v_total_amount := v_total_amount + v_order_amount;
    END LOOP;

    -- 关闭游标
    CLOSE cur_paid_orders;

    -- 输出总金额
    DBMS_OUTPUT.PUT_LINE('------------------------');
    DBMS_OUTPUT.PUT_LINE('已支付订单总金额：' || v_total_amount);
END;

关键语法解析
显示游标
• 游标声明：CURSOR 游标名 IS SELECT语句，定义要处理的多行结果集
• 游标操作三步骤：OPEN（打开游标，执行 SELECT）→ FETCH（逐行读取数据）→ CLOSE（关闭游标）；
• 游标属性：%NOTFOUND（游标无数据时为 TRUE），用于判断是否退出循环；
• 注意事项：游标必须关闭，否则会占用数据库连接资源（若存储过程异常终止，游标会自动关闭，但推荐手动关闭）。
调用结果

动态游标：可变查询条件
普通游标的查询条件是固定的（如PAY_STATUS=‘1’），若需根据传入参数动态调整查询条件（如查询指定用户的订单），需用动态游标。
与静态游标不同，动态游标在声明部分只是先声明一个游标类型的变量，并不指定其关联的查询语句，在执行部分打开游标时才指定查询语句。动态游标的使用主要在定义和打开时与显式游标不同，下面进行详细介绍。
存储过程SQL

CREATE OR REPLACE PROCEDURE PROC_QUERY_ORDERS_DYNAMIC(
    p_user_id IN INT,          -- 输入：用户ID
    p_pay_status IN CHAR(1)    -- 输入：支付状态
)
AS
    -- 1. 声明动态游标类型
    TYPE ref_cursor_type IS REF CURSOR RETURN ORDER_INFO%ROWTYPE;

    -- 2. 声明动态游标变量
    cur_dynamic_orders ref_cursor_type;

    -- 3. 声明变量存储游标数据
    v_order_row ORDER_INFO%ROWTYPE;
BEGIN
    -- 4. 打开动态游标：根据输入参数调整查询条件
    OPEN cur_dynamic_orders FOR
        SELECT *
        FROM ORDER_INFO
        WHERE USER_ID = p_user_id
          AND PAY_STATUS = p_pay_status;

    -- 5. 逐行读取并输出
    DBMS_OUTPUT.PUT_LINE('用户ID=' || p_user_id || '，支付状态=' || p_pay_status || '的订单：');
    LOOP
        FETCH cur_dynamic_orders INTO v_order_row;
        EXIT WHEN cur_dynamic_orders%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE('订单ID：' || v_order_row.ORDER_ID || '，金额：' || v_order_row.ORDER_AMOUNT || '，时间：' || TO_CHAR(v_order_row.ORDER_TIME, 'YYYY-MM-DD'));
    END LOOP;

    -- 6. 关闭游标
    CLOSE cur_dynamic_orders;
END;

调式结果

5. 异常处理

存储过程执行过程中可能出现错误（如数据不存在、主键冲突等），若不处理，会直接中断执行并抛出错误信息；而异常处理可以捕获错误，执行自定义逻辑（如回滚事务、记录日志），让存储过程更加健壮。
常见异常类型与处理
DM 预定义了多种常见异常（如NO_DATA_FOUND、DUP_VAL_ON_INDEX），以下是企业级开发中最常用的异常处理案例。下面创建一个存储过程，，输入用户 ID 和新手机号，更新用户手机号；若用户不存在或手机号已存在，输出错误信息。
存储过程SQL

CREATE OR REPLACE PROCEDURE PROC_UPDATE_USER_PHONE(
    p_user_id IN INT,          -- 输入：用户ID
    p_new_phone IN VARCHAR(20) -- 输入：新手机号
)
AS
BEGIN
    -- 更新手机号
    UPDATE USER_INFO
    SET PHONE = p_new_phone
    WHERE USER_ID = p_user_id;

    -- 判断是否更新成功（影响行数为0表示用户不存在）
    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, '用户ID=' || p_user_id || '不存在'); -- 手动抛出异常
    END IF;

    -- 提交事务
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('用户ID=' || p_user_id || '的手机号已更新为：' || p_new_phone);

-- 异常处理块
EXCEPTION
    -- 1. 捕获“未找到数据”异常（虽已用SQL%ROWCOUNT判断，仍保留以防遗漏）
    WHEN NO_DATA_FOUND THEN
        ROLLBACK; -- 回滚事务
        DBMS_OUTPUT.PUT_LINE('错误：用户ID=' || p_user_id || '不存在');

    -- 2. 捕获“主键/唯一键冲突”异常（手机号是唯一键，重复会触发）
    WHEN DUP_VAL_ON_INDEX THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('错误：手机号' || p_new_phone || '已被占用');

    -- 3. 捕获“手动抛出的异常”（RAISE_APPLICATION_ERROR抛出的异常）
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('未知错误：' || SQLERRM); -- SQLERRM：获取错误信息
        DBMS_OUTPUT.PUT_LINE('错误代码：' || SQLCODE); -- SQLCODE：获取错误代码
END;

关键语法解析
• SQL%ROWCOUNT:返回上一条SQL语句影响的行数，用于判断更新/插入是否成功；
• RAISE_APPLICATION_ERROR:手动抛出异常，第一个参数是错误代码,第二个参数是错误信息；
• `EXCEPTION块:
• NO_DATA_FOUND：SELECT…INTO未找到数据时触发；
• DUP_VAL_ON_INDEX：插入 / 更新数据违反唯一键约束时触发；
• OTHERS：捕获所有未被明确处理的异常，是 “兜底” 处理；
• ROLLBACK：异常发生时回滚事务，避免数据不一致。
调用结果
**正常更新：**用户 ID=1，新手机号 = 13800138001（未被占用）

**用户不存在：**用户 ID=99，新手机号 = 13800138002

**手机号重复：**用户 ID=2，新手机号 = 13800138001（已被用户 1 占用）

四、总结

DM 存储过程的核心价值，正体现在对关键场景的精准适配：批量数据处理时，凭 “本地化执行” 减少网络 IO 与 SQL 解析开销，用 FORALL 等优化手段让效率倍增；复杂业务逻辑场景下，可封装如订单计算、报表生成等逻辑，避免业务分散于应用层，降低维护成本；权限管控严格的领域，能通过细粒度执行权限（而非表级权限）保障数据安全，还兼容 Oracle 语法降低迁移门槛。加之异常处理机制保障数据一致，使其成为国产数据库场景下高效、安全处理数据的实用工具。