银行数仓之存储过程小案例

简单的案例

从多个表获取客户交易数据，进行数据清洗、计算和汇总，并将结果存储到目标表中

CREATE OR REPLACE PROCEDURE bank_data_warehouse_processing
IS
    -- 定义游标获取源表数据
    CURSOR source_data_cur IS
        SELECT customer_id, transaction_amount, transaction_date
        FROM source_transactions;

    -- 定义变量存储计算和汇总结果
    total_amount NUMBER := 0;
    average_amount NUMBER := 0;
    transaction_count NUMBER := 0;

    -- 定义异常处理
    invalid_data EXCEPTION;

BEGIN
    FOR source_data_rec IN source_data_cur
    LOOP
        -- 数据清洗和验证
        IF source_data_rec.transaction_amount < 0 THEN
            RAISE invalid_data;
        END IF;

        -- 数据计算和汇总
        total_amount := total_amount + source_data_rec.transaction_amount;
        transaction_count := transaction_count + 1;

    END LOOP;

    -- 计算平均值
    average_amount := total_amount / transaction_count;

    -- 将结果插入到目标表
    INSERT INTO target_table (customer_id, total_amount, average_amount, transaction_count)
    VALUES (source_data_rec.customer_id, total_amount, average_amount, transaction_count);

    -- 提交事务
    COMMIT;

EXCEPTION
    WHEN invalid_data THEN
        -- 记录错误日志
        INSERT INTO error_table (error_message, error_time)
        VALUES ('Invalid transaction amount found.', SYSDATE);
        ROLLBACK;
    WHEN OTHERS THEN
        -- 记录其他错误日志
        INSERT INTO error_table (error_message, error_time)
        VALUES ('Unknown error occurred.', SYSDATE);
        ROLLBACK;
END;

在 Oracle 中，RAISE invalid_data; 是用于手动触发一个自定义的异常 invalid_data 。当满足特定的条件（在上述示例中，是当交易金额小于 0 时），通过 RAISE 语句来抛出这个自定义异常，从而执行异常处理部分的代码

VALUES ('Invalid transaction amount found.', SYSDATE) 这里指定了要插入的值，'Invalid transaction amount found.' 是错误消息的文本内容，SYSDATE 是 Oracle 中的内置函数，会获取当前的系统日期和时间作为插入的值。ROLLBACK 用于回滚当前事务，撤销在此事务中进行的所有未提交的更改，将数据库状态恢复到事务开始之前。综合起来，当触发了自定义的 invalid_data 异常时，会将错误消息和当前时间插入到错误表中，并回滚当前事务。

触发器和存储器连用的案例

存储过程：

CREATE OR REPLACE PROCEDURE calculate_customer_transaction_summary
IS
    -- 定义游标获取客户信息及交易数据
    CURSOR customer_transaction_cur IS
        SELECT c.customer_id, c.name, SUM(t.amount) AS total_amount, AVG(t.amount) AS average_amount
        FROM customers c
        JOIN transactions t ON c.customer_id = t.customer_id
        GROUP BY c.customer_id, c.name;

    -- 定义变量存储操作结果
    v_update_count NUMBER := 0;
BEGIN
    FOR cur_rec IN customer_transaction_cur
    LOOP
        -- 更新客户余额表
        UPDATE customer_balances cb
        SET total_transaction_amount = cur_rec.total_amount,
            average_transaction_amount = cur_rec.average_amount
        WHERE cb.customer_id = cur_rec.customer_id;

        v_update_count := v_update_count + SQL%ROWCOUNT;
    END LOOP;

    -- 输出操作结果
    DBMS_OUTPUT.PUT_LINE('Updated '| v_update_count ||' customer balance records.');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error occurred: '| SQLERRM);
END;

触发器：

CREATE OR REPLACE TRIGGER give_vip_discount
AFTER INSERT ON transactions
FOR EACH ROW
DECLARE
    v_customer_status VARCHAR2(20);
BEGIN
    -- 获取客户状态
    SELECT status INTO v_customer_status
    FROM customers
    WHERE customer_id = :NEW.customer_id;

    -- 如果是 VIP 客户，给予优惠
    IF v_customer_status = 'VIP' THEN
        :NEW.amount := :NEW.amount * 0.9;  -- 给予 10% 的优惠
    END IF;
END;