当前位置： 首页 > article >正文

存储过程和自定义函数在银行信贷业务中的应用(oracle)

article 2025/3/13 0:51:13

数据校验和清洗

  • 例如，检查客户的年龄是否在合理范围内，贷款金额是否符合规定的上下限等。

  • 对于不符合规则的数据，可以进行清洗和修正。比如，将空值替换为默认值，或者对错误的数据进行纠正。

CREATE OR REPLACE PROCEDURE ValidateAndCleanCreditData
AS
BEGIN
    -- 检查客户年龄是否在合理范围内（18 到 100 岁）
    UPDATE credit_data
    SET customer_age = NULL
    WHERE customer_age < 18 OR customer_age > 100;

    -- 将空值的贷款金额替换为默认值 0
    UPDATE credit_data
    SET loan_amount = 0
    WHERE loan_amount IS NULL;

    -- 检查贷款利率是否在合理范围内（0 到 100%）
    DELETE FROM credit_data
    WHERE interest_rate < 0 OR interest_rate > 100;
END;

计算客户的信用评分

CREATE OR REPLACE PROCEDURE CalculateCreditScore
    (p_customer_id IN NUMBER, p_credit_score OUT NUMBER)
AS
    v_customer_income NUMBER;
    v_loan_amount NUMBER;
    v_loan_term NUMBER;
    v_payment_history VARCHAR2(50);
    v_score NUMBER := 0;
BEGIN
    -- 获取客户的基本信息和贷款记录
    SELECT customer_income, loan_amount, loan_term, payment_history
    INTO v_customer_income, v_loan_amount, v_loan_term, v_payment_history
    FROM credit_data
    WHERE customer_id = p_customer_id;

    -- 根据客户收入计算部分信用评分
    IF v_customer_income > 50000 THEN
        v_score := v_score + 30;
    ELSIF v_customer_income > 30000 THEN
        v_score := v_score + 20;
    ELSE
        v_score := v_score + 10;
    END IF;

    -- 根据贷款金额和期限计算部分信用评分
    IF v_loan_amount / v_loan_term < 1000 THEN
        v_score := v_score + 20;
    ELSIF v_loan_amount / v_loan_term < 2000 THEN
        v_score := v_score + 15;
    ELSE
        v_score := v_score + 10;
    END IF;

    -- 根据还款历史计算部分信用评分
    IF v_payment_history = 'Good' THEN
        v_score := v_score + 40;
    ELSIF v_payment_history = 'Fair' THEN
        v_score := v_score + 20;
    ELSE
        v_score := v_score + 10;
    END IF;

    -- 返回信用评分
    p_credit_score := v_score;
END;

确定贷款利率

CREATE OR REPLACE PROCEDURE calculate_loan_rate (
    p_customer_id IN NUMBER,                -- 借款人ID
    p_loan_amount IN NUMBER,                -- 贷款金额
    p_loan_term IN NUMBER,                  -- 贷款期限（月）
    p_loan_type IN VARCHAR2,                -- 贷款类型
    p_loan_rate OUT NUMBER                  -- 贷款利率
) AS
    -- 借款人信息变量
    v_credit_score NUMBER;                  -- 信用评分
    v_base_rate NUMBER := 0.05;             -- 基准利率（假设为5%）
    v_risk_factor NUMBER := 0;              -- 风险调整因子

BEGIN
    -- 获取借款人信用评分（假设存储在customer_info表中）
    SELECT credit_score
    INTO v_credit_score
    FROM customer_info
    WHERE customer_id = p_customer_id;

    -- 根据信用评分调整风险因子
    IF v_credit_score >= 750 THEN
        v_risk_factor := 0.01;  -- 信用评分高，风险低，利率调整因子小
    ELSIF v_credit_score BETWEEN 600 AND 749 THEN
        v_risk_factor := 0.03;  -- 信用评分中等，风险中等，利率调整因子中等
    ELSE
        v_risk_factor := 0.05;  -- 信用评分低，风险高，利率调整因子大
    END IF;

    -- 根据贷款类型调整利率
    IF p_loan_type = '个人贷款' THEN
        v_risk_factor := v_risk_factor + 0.02;
    ELSIF p_loan_type = '房贷' THEN
        v_risk_factor := v_risk_factor + 0.01;
    ELSIF p_loan_type = '消费贷款' THEN
        v_risk_factor := v_risk_factor + 0.03;
    ELSE
        v_risk_factor := v_risk_factor + 0.04;  -- 其他贷款类型
    END IF;

    -- 根据贷款期限调整利率
    IF p_loan_term > 60 THEN
        v_risk_factor := v_risk_factor + 0.02;  -- 贷款期限长，风险增加
    END IF;

    -- 计算最终贷款利率
    p_loan_rate := v_base_rate + v_risk_factor;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_loan_rate := NULL;
        DBMS_OUTPUT.PUT_LINE('未找到借款人信息，请检查客户ID是否正确。');
    WHEN OTHERS THEN
        p_loan_rate := NULL;
        DBMS_OUTPUT.PUT_LINE('计算过程中发生错误：' || SQLERRM);
END calculate_loan_rate;

 调用存储过程

DECLARE
    v_customer_id NUMBER := 12345;          -- 借款人ID
    v_loan_amount NUMBER := 50000;         -- 贷款金额
    v_loan_term NUMBER := 60;              -- 贷款期限（月）
    v_loan_type VARCHAR2(20) := '个人贷款'; -- 贷款类型
    v_loan_rate NUMBER;                    -- 贷款利率
BEGIN
    calculate_loan_rate(
        p_customer_id => v_customer_id,
        p_loan_amount => v_loan_amount,
        p_loan_term => v_loan_term,
        p_loan_type => v_loan_type,
        p_loan_rate => v_loan_rate
    );

    IF v_loan_rate IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE('计算出的贷款利率为：' || TO_CHAR(v_loan_rate * 100) || '%');
    ELSE
        DBMS_OUTPUT.PUT_LINE('未能计算贷款利率，请检查输入参数。');
    END IF;
END;

生成月度贷款发放报表

CREATE OR REPLACE PROCEDURE GenerateMonthlyLoanReport
AS
    CURSOR loan_cursor IS
        SELECT loan_type, 
               SUM(loan_amount) AS total_loan_amount, 
               COUNT(*) AS loan_count
        FROM credit_data
        WHERE loan_date BETWEEN TRUNC(SYSDATE, 'MONTH') AND LAST_DAY(SYSDATE)
        GROUP BY loan_type;
BEGIN
    -- 打开报表文件
    UTL_FILE.FOPEN('REPORT_DIR', 'monthly_loan_report.txt', 'W');

    -- 写入报表标题
    UTL_FILE.PUT_LINE(UTL_FILE.FILE_TYPE, 'Monthly Loan Report');
    UTL_FILE.PUT_LINE(UTL_FILE.FILE_TYPE, '-------------------');

    -- 遍历游标，写入报表内容
    FOR loan_record IN loan_cursor LOOP
        UTL_FILE.PUT_LINE(UTL_FILE.FILE_TYPE, 'Loan Type: '
        || loan_record.loan_type || ', Total Loan Amount: ' || 
        loan_record.total_loan_amount || ', Loan Count: ' || loan_record.loan_count);
    END LOOP;

    -- 关闭报表文件
    UTL_FILE.FCLOSE(UTL_FILE.FILE_TYPE);
END;

sql解析(oracle):

TRUNC(SYSDATE, 'MONTH')：

这个函数将当前日期 SYSDATE 截断到月份的开始，即返回当前月份的第一天。

LAST_DAY(SYSDATE)：

这个函数返回当前月份的最后一天

SYSDATE 返回的是数据库服务器的当前日期和时间

 -- 打开报表文件
UTL_FILE.FOPEN('REPORT_DIR', 'monthly_loan_report.txt', 'W');

UTL_FILE 包

用于在 PL/SQL 程序中读取和写入操作系统文件。它允许数据库程序与文件系统交互，例如创建文件、写入数据、读取文件内容等

其中 UTL 是 Utility 的缩写，表示这是一个工具包

UTL_FILE.FOPEN 函数

用于打开一个文件, 语法如下

UTL_FILE.FOPEN(location => 'DIRECTORY_ALIAS', 
               filename => 'FILE_NAME', 
               open_mode => 'OPEN_MODE', 
               max_linesize => MAX_LINESIZE);

  • location：指定文件所在的目录，必须是数据库中定义的目录别名（DIRECTORY 对象）。目录别名是通过 CREATE DIRECTORY 语句创建的，指向操作系统中的一个实际目录。

  • filename：指定要打开的文件名。

  • open_mode：指定文件的打开模式，可以是：

    • 'R'：以只读模式打开文件。 Read（读取）

    • 'W'：以写入模式打开文件（如果文件已存在，内容会被清空）。 Write（写入）

    • 'A'：以追加模式打开文件（写入内容会追加到文件末尾）。 Append（ 追加 )

  • max_linesize：可选参数，指定文件的最大行长度，默认为 32767 字节

-- 写入报表标题
UTL_FILE.FOPEN('REPORT_DIR', 'monthly_loan_report.txt', 'W');

 'REPORT_DIR'：这是目录别名，指向一个已经通过 CREATE DIRECTORY 创建的目录对象。例如：

 

返回值

UTL_FILE.FOPEN 返回一个文件句柄（FILE_TYPE），这个句柄用于后续的文件操作，例如写入内容或关闭文件。

UTL_FILE.PUT_LINE(UTL_FILE.FILE_TYPE, 'Monthly Loan Report');

UTL_FILE.PUT_LINE函数

用于将一行文本写入到指定的文件中。

UTL_FILE.FILE_TYPE

一个文件类型对象，它在前面的代码中通过UTL_FILE.FOPEN函数打开了一个文件，并将其赋值给UTL_FILE.FILE_TYPE。这个对象代表了要写入的文件。

'Monthly Loan Report'

这是要写入文件的文本内容，即报表的标题

UTL_FILE.PUT_LINE(UTL_FILE.FILE_TYPE, '-------------------');

也是使用UTL_FILE.PUT_LINE函数将一行文本写入到文件中

'-------------------'：这是要写入文件的文本内容，通常用于在标题下方添加一条分隔线，以增强报表的可读性。

-- 遍历游标，写入报表内容
FOR loan_record IN loan_cursor LOOP
        UTL_FILE.PUT_LINE(UTL_FILE.FILE_TYPE, 'Loan Type: ' 
        || loan_record.loan_type || ', Total Loan Amount: ' 
        || loan_record.total_loan_amount || ', Loan Count: ' 
        || loan_record.loan_count);
    END LOOP;

FOR  ...  IN  ...   LOOP循环

从游标（loan_cursor）中逐条读取数据，并将每条记录的信息写入到一个文件中。

自动从游标中逐条取出数据，并将每条记录赋值给 loan_record, 直到游标中的所有记录都被处理完毕。

UTL_FILE.PUT_LINE

是 UTL_FILE 包中的一个函数，用于将一行文本写入到文件中

UTL_FILE.FILE_TYPE 

这是要写入的目标文件

'Loan Type: ' || loan_record.loan_type || ', Total Loan Amount: ' || loan_record.total_loan_amount || ', Loan Count: ' || loan_record.loan_count

这是要写入的字符串内容

   -- 关闭报表文件
    UTL_FILE.FCLOSE(UTL_FILE.FILE_TYPE);
END;

UTL_FILE.FCLOSE函数

用于关闭一个通过 UTL_FILE.FOPEN 打开的文件。它的作用是释放与文件句柄相关的资源，并确保文件正确关闭

UTL_FILE.FILE_TYPE

这是要关闭的文件, 也是之前UTL_FILE.FOPEN的返回值 

逾期贷款统计报表

-- 创建存储过程
CREATE OR REPLACE PROCEDURE sp_OverdueLoanReport (
    p_StartDate DATE,  -- 统计开始日期
    p_EndDate DATE,    -- 统计结束日期
    p_Cursor OUT SYS_REFCURSOR  -- 输出游标
) IS
BEGIN
    -- 打开游标，查询逾期贷款数据
    OPEN p_Cursor FOR
    SELECT
        l.loan_id AS "LoanID",
        c.customer_name AS "CustomerName",
        l.loan_amount AS "LoanAmount",
        l.due_date AS "DueDate",
        TRUNC(SYSDATE - l.due_date) AS "OverdueDays",  -- 计算逾期天数
        (l.loan_amount - NVL(SUM(p.payment_amount), 0)) AS "OverdueAmount" 
         -- 计算逾期金额
    FROM
        loans l
    INNER JOIN
        customers c ON l.customer_id = c.customer_id
    LEFT JOIN
        payments p ON l.loan_id = p.loan_id
    WHERE
        l.due_date <= SYSDATE  -- 筛选出逾期的贷款
        AND p.payment_date BETWEEN p_StartDate AND p_EndDate
    GROUP BY
        l.loan_id, c.customer_name, l.loan_amount, l.due_date
    HAVING
        l.loan_amount > NVL(SUM(p.payment_amount), 0)  -- 筛选出贷款未完全偿还的
    ORDER BY
        "OverdueDays" DESC;

END sp_OverdueLoanReport;

解析:

这里没有定义游标, 因为在 Oracle 数据库中，使用 SYS_REFCURSOR 作为存储过程的输出参数时，不需要显式定义游标。SYS_REFCURSOR 是一个系统定义的游标类型，用于返回查询结果集。在存储过程中，直接通过 OPEN p_Cursor FOR 语句将查询结果集绑定到游标即可。

SYS  即 system 系统

REF  即Reference 引用

OPEN p_Cursor FOR 后面的 select 语句

多表查询语句: 

  • loans l：主表，存储贷款信息。

  • INNER JOIN customers c ON l.customer_id = c.customer_id：通过客户编号将 loans 表与 customers 表连接，获取客户名称。

  • LEFT JOIN payments p ON l.loan_id = p.loan_id：通过贷款编号将 loans 表与 payments 表连接，获取还款信息。使用 LEFT JOIN 是为了确保即使没有还款记录的贷款也能被查询到。

END sp_OverdueLoanReport;

oracle存储过程的结束需要再写一次存储过程名称

将查询结果直接插入到表中

方法 1：直接在存储过程中插入到目标表

前提: 已经存在一个目标表 

CREATE OR REPLACE PROCEDURE sp_OverdueLoanReport (
    p_StartDate DATE,  -- 统计开始日期
    p_EndDate DATE,    -- 统计结束日期
    p_Cursor OUT SYS_REFCURSOR  -- 输出游标
) IS
BEGIN
    -- 清空目标表（如果需要）
    DELETE FROM overdue_loan_report;

    -- 插入数据到目标表
    INSERT INTO overdue_loan_report (
        LoanID,
        CustomerName,
        LoanAmount,
        DueDate,
        OverdueDays,
        OverdueAmount
    )
    SELECT
        l.loan_id AS LoanID,
        c.customer_name AS CustomerName,
        l.loan_amount AS LoanAmount,
        l.due_date AS DueDate,
        TRUNC(SYSDATE - l.due_date) AS OverdueDays,  -- 计算逾期天数
        (l.loan_amount - NVL(SUM(p.payment_amount), 0)) AS OverdueAmount  
        -- 计算逾期金额
    FROM
        loans l
    INNER JOIN
        customers c ON l.customer_id = c.customer_id
    LEFT JOIN
        payments p ON l.loan_id = p.loan_id
    WHERE
        l.due_date <= SYSDATE  -- 筛选出逾期的贷款
        AND p.payment_date BETWEEN p_StartDate AND p_EndDate
    GROUP BY
        l.loan_id, c.customer_name, l.loan_amount, l.due_date
    HAVING
        l.loan_amount > NVL(SUM(p.payment_amount), 0)  -- 筛选出贷款未完全偿还的
    ORDER BY
        OverdueDays DESC;

    -- 提交事务
    COMMIT;

    -- 打开游标，返回查询结果
    OPEN p_Cursor FOR
    SELECT * FROM overdue_loan_report ORDER BY OverdueDays DESC;

END sp_OverdueLoanReport;

方法 2：使用临时表存储结果

CREATE OR REPLACE PROCEDURE sp_OverdueLoanReport (
    p_StartDate DATE,  -- 统计开始日期
    p_EndDate DATE,    -- 统计结束日期
    p_Cursor OUT SYS_REFCURSOR  -- 输出游标
) IS
BEGIN
    -- 创建临时表（如果尚未创建）
    EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE temp_overdue_loan_report (
        LoanID NUMBER,
        CustomerName VARCHAR2(100),
        LoanAmount NUMBER,
        DueDate DATE,
        OverdueDays NUMBER,
        OverdueAmount NUMBER
    ) ON COMMIT DELETE ROWS';

    -- 清空临时表（如果需要）
    EXECUTE IMMEDIATE 'TRUNCATE TABLE temp_overdue_loan_report';

    -- 插入数据到临时表
    INSERT INTO temp_overdue_loan_report (
        LoanID,
        CustomerName,
        LoanAmount,
        DueDate,
        OverdueDays,
        OverdueAmount
    )
    SELECT
        l.loan_id AS LoanID,
        c.customer_name AS CustomerName,
        l.loan_amount AS LoanAmount,
        l.due_date AS DueDate,
        TRUNC(SYSDATE - l.due_date) AS OverdueDays,  -- 计算逾期天数
        (l.loan_amount - NVL(SUM(p.payment_amount), 0)) AS OverdueAmount  
         -- 计算逾期金额
    FROM
        loans l
    INNER JOIN
        customers c ON l.customer_id = c.customer_id
    LEFT JOIN
        payments p ON l.loan_id = p.loan_id
    WHERE
        l.due_date <= SYSDATE  -- 筛选出逾期的贷款
        AND p.payment_date BETWEEN p_StartDate AND p_EndDate
    GROUP BY
        l.loan_id, c.customer_name, l.loan_amount, l.due_date
    HAVING
        l.loan_amount > NVL(SUM(p.payment_amount), 0)  -- 筛选出贷款未完全偿还的
    ORDER BY
        OverdueDays DESC;

    -- 提交事务
    COMMIT;

    -- 打开游标，返回查询结果
    OPEN p_Cursor FOR
    SELECT * FROM temp_overdue_loan_report ORDER BY OverdueDays DESC;

END sp_OverdueLoanReport;

解析:

CREATE GLOBAL TEMPORARY TABLE  

创建一个全局临时表, 它允许会话（session）在事务中插入数据，并且在事务提交时不会删除这些数据，而是在会话结束时删除。 

ON COMMIT DELETE ROWS

全局临时表的一个选项

ON COMMIT：指定在事务提交时的行为。

DELETE ROWS：

  • 在事务提交时删除数据：当事务提交（COMMIT）或回滚（ROLLBACK）时，该临时表中插入的所有数据会被自动删除。

  • 表结构保留：虽然数据被删除，但临时表的结构仍然存在，不会被删除。

计算客户的信用风险指数

创建自定义函数

CREATE OR REPLACE FUNCTION calculate_risk_index(
    customer_income NUMBER,
    loan_amount NUMBER,
    payment_history VARCHAR2
) RETURN NUMBER AS
    risk_index NUMBER;
BEGIN
    -- 根据收入、贷款金额和支付历史计算风险指数
    IF customer_income > 50000 AND payment_history = 'Good' THEN
        risk_index := loan_amount / customer_income * 0.5;
    ELSIF customer_income > 30000 AND payment_history = 'Fair' THEN
        risk_index := loan_amount / customer_income * 0.7;
    ELSE
        risk_index := loan_amount / customer_income * 1;
    END IF;
    RETURN risk_index;
END;

创建一个存储过程来调用这个自定义函数，并执行其他数据库操作，比如更新客户的信用评级

CREATE OR REPLACE PROCEDURE process_credit_approval(
    customer_id NUMBER
) AS
    customer_income NUMBER;
    loan_amount NUMBER;
    payment_history VARCHAR2(50);
    risk_index NUMBER;
    credit_rating VARCHAR2(20);
BEGIN
    -- 获取客户的相关信息
    SELECT income, loan_amount, payment_history
    INTO customer_income, loan_amount, payment_history
    FROM customer_table
    WHERE customer_id = customer_id;

    -- 调用自定义函数计算风险指数
    risk_index := calculate_risk_index(customer_income, loan_amount, payment_history);

    -- 根据风险指数确定信用评级
    IF risk_index < 0.3 THEN
        credit_rating := 'Excellent';
    ELSIF risk_index < 0.5 THEN
        credit_rating := 'Good';
    ELSIF risk_index < 0.7 THEN
        credit_rating := 'Fair';
    ELSE
        credit_rating := 'Poor';
    END IF;

    -- 更新客户的信用评级
    UPDATE customer_table
    SET credit_rating = credit_rating
    WHERE customer_id = customer_id;
END;

http://www.kler.cn/a/582061.html

相关文章：

  • Spark数据倾斜深度解析与实战解决方案
  • 力扣-数组-367 有效的完全平方数
  • 【机器学习的主要类型】
  • HTML5 + Bootstrap5 网站底部代码实现与解析
  • Django系列教程（3）——Django的MVT设计模式
  • 大语言模型（LLM）的微调与应用
  • Elasticsearch 2025/3/7
  • 【漫话机器学习系列】125.普拉托变换（Platt Scaling）
  • 【UNIAPP】获取视频的第一帧作为封面（基于视频URL，Canvas）复制即用
  • git文件过大导致gitea仓库镜像推送失败问题解决（push failed: context deadline exceeded）
  • 在VMware Workstation Pro上轻松部署CentOS7 Linux虚拟机
  • 使用Java爬虫根据关键词获取衣联网商品列表：实战指南
  • 《2025年软件测试工程师面试》MySQL面试题
  • Linux 环境变量快速上手指南
  • 《UI 设计：点亮大数据可视化的智慧之光》
  • 告别手抖烦恼，重拾生活稳 “态”
  • 【物联网-WIFI】
  • 【MySQL】增删改查进阶
  • 【测试框架篇】单元测试框架pytest（5）：setup和teardown的详细使用
  • 2.4 基于Vitest的单元测试基础设施搭建