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

数据校验和清洗

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

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

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_FOU