存儲過程和自定義函數在銀行信貸業務中的應用(oracle)

數據校驗和清洗

例如，檢查客戶的年齡是否在合理范圍內，貸款金額是否符合規定的上下限等。
對于不符合規則的數據，可以進行清洗和修正。比如，將空值替換為默認值，或者對錯誤的數據進行糾正。

CREATE OR REPLACE PROCEDURE ValidateAndCleanCreditData
AS
BEGIN-- 檢查客戶年齡是否在合理范圍內（18 到 100 歲）UPDATE credit_dataSET customer_age = NULLWHERE customer_age < 18 OR customer_age > 100;-- 將空值的貸款金額替換為默認值 0UPDATE credit_dataSET loan_amount = 0WHERE loan_amount IS NULL;-- 檢查貸款利率是否在合理范圍內（0 到 100%）DELETE FROM credit_dataWHERE interest_rate < 0 OR interest_rate > 100;
END;

計算客戶的信用評分

CREATE OR REPLACE PROCEDURE CalculateCreditScore(p_customer_id IN NUMBER, p_credit_score OUT NUMBER)
ASv_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_historyINTO v_customer_income, v_loan_amount, v_loan_term, v_payment_historyFROM credit_dataWHERE customer_id = p_customer_id;-- 根據客戶收入計算部分信用評分IF v_customer_income > 50000 THENv_score := v_score + 30;ELSIF v_customer_income > 30000 THENv_score := v_score + 20;ELSEv_score := v_score + 10;END IF;-- 根據貸款金額和期限計算部分信用評分IF v_loan_amount / v_loan_term < 1000 THENv_score := v_score + 20;ELSIF v_loan_amount / v_loan_term < 2000 THENv_score := v_score + 15;ELSEv_score := v_score + 10;END IF;-- 根據還款歷史計算部分信用評分IF v_payment_history = 'Good' THENv_score := v_score + 40;ELSIF v_payment_history = 'Fair' THENv_score := v_score + 20;ELSEv_score := v_score + 10;END IF;-- 返回信用評分p_credit_score := v_score;
END;

確定貸款利率

CREATE OR REPLACE PROCEDURE calculate_loan_rate (p_customer_id IN NUMBER,                -- 借款人IDp_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_scoreINTO v_credit_scoreFROM customer_infoWHERE customer_id = p_customer_id;-- 根據信用評分調整風險因子IF v_credit_score >= 750 THENv_risk_factor := 0.01;  -- 信用評分高，風險低，利率調整因子小ELSIF v_credit_score BETWEEN 600 AND 749 THENv_risk_factor := 0.03;  -- 信用評分中等，風險中等，利率調整因子中等ELSEv_risk_factor := 0.05;  -- 信用評分低，風險高，利率調整因子大END IF;-- 根據貸款類型調整利率IF p_loan_type = '個人貸款' THENv_risk_factor := v_risk_factor + 0.02;ELSIF p_loan_type = '房貸' THENv_risk_factor := v_risk_factor + 0.01;ELSIF p_loan_type = '消費貸款' THENv_risk_factor := v_risk_factor + 0.03;ELSEv_risk_factor := v_risk_factor + 0.04;  -- 其他貸款類型END IF;-- 根據貸款期限調整利率IF p_loan_term > 60 THENv_risk_factor := v_risk_factor + 0.02;  -- 貸款期限長，風險增加END IF;-- 計算最終貸款利率p_loan_rate := v_base_rate + v_risk_factor;EXCEPTIONWHEN NO_DATA_FOUND THENp_loan_rate := NULL;DBMS_OUTPUT.PUT_LINE('未找到借款人信息，請檢查客戶ID是否正確。');WHEN OTHERS THENp_loan_rate := NULL;DBMS_OUTPUT.PUT_LINE('計算過程中發生錯誤：' || SQLERRM);
END calculate_loan_rate;

?調用存儲過程

DECLAREv_customer_id NUMBER := 12345;          -- 借款人IDv_loan_amount NUMBER := 50000;         -- 貸款金額v_loan_term NUMBER := 60;              -- 貸款期限（月）v_loan_type VARCHAR2(20) := '個人貸款'; -- 貸款類型v_loan_rate NUMBER;                    -- 貸款利率
BEGINcalculate_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 THENDBMS_OUTPUT.PUT_LINE('計算出的貸款利率為：' || TO_CHAR(v_loan_rate * 100) || '%');ELSEDBMS_OUTPUT.PUT_LINE('未能計算貸款利率，請檢查輸入參數。');END IF;
END;

生成月度貸款發放報表

CREATE OR REPLACE PROCEDURE GenerateMonthlyLoanReport
ASCURSOR loan_cursor ISSELECT loan_type, SUM(loan_amount) AS total_loan_amount, COUNT(*) AS loan_countFROM credit_dataWHERE 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 LOOPUTL_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 LOOPUTL_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 FORSELECTl.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" -- 計算逾期金額FROMloans lINNER JOINcustomers c ON l.customer_id = c.customer_idLEFT JOINpayments p ON l.loan_id = p.loan_idWHEREl.due_date <= SYSDATE  -- 篩選出逾期的貸款AND p.payment_date BETWEEN p_StartDate AND p_EndDateGROUP BYl.loan_id, c.customer_name, l.loan_amount, l.due_dateHAVINGl.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)SELECTl.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  -- 計算逾期金額FROMloans lINNER JOINcustomers c ON l.customer_id = c.customer_idLEFT JOINpayments p ON l.loan_id = p.loan_idWHEREl.due_date <= SYSDATE  -- 篩選出逾期的貸款AND p.payment_date BETWEEN p_StartDate AND p_EndDateGROUP BYl.loan_id, c.customer_name, l.loan_amount, l.due_dateHAVINGl.loan_amount > NVL(SUM(p.payment_amount), 0)  -- 篩選出貸款未完全償還的ORDER BYOverdueDays DESC;-- 提交事務COMMIT;-- 打開游標，返回查詢結果OPEN p_Cursor FORSELECT * 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)SELECTl.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  -- 計算逾期金額FROMloans lINNER JOINcustomers c ON l.customer_id = c.customer_idLEFT JOINpayments p ON l.loan_id = p.loan_idWHEREl.due_date <= SYSDATE  -- 篩選出逾期的貸款AND p.payment_date BETWEEN p_StartDate AND p_EndDateGROUP BYl.loan_id, c.customer_name, l.loan_amount, l.due_dateHAVINGl.loan_amount > NVL(SUM(p.payment_amount), 0)  -- 篩選出貸款未完全償還的ORDER BYOverdueDays DESC;-- 提交事務COMMIT;-- 打開游標，返回查詢結果OPEN p_Cursor FORSELECT * 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 ASrisk_index NUMBER;
BEGIN-- 根據收入、貸款金額和支付歷史計算風險指數IF customer_income > 50000 AND payment_history = 'Good' THENrisk_index := loan_amount / customer_income * 0.5;ELSIF customer_income > 30000 AND payment_history = 'Fair' THENrisk_index := loan_amount / customer_income * 0.7;ELSErisk_index := loan_amount / customer_income * 1;END IF;RETURN risk_index;
END;

創建一個存儲過程來調用這個自定義函數，并執行其他數據庫操作，比如更新客戶的信用評級

CREATE OR REPLACE PROCEDURE process_credit_approval(customer_id NUMBER
) AScustomer_income NUMBER;loan_amount NUMBER;payment_history VARCHAR2(50);risk_index NUMBER;credit_rating VARCHAR2(20);
BEGIN-- 獲取客戶的相關信息SELECT income, loan_amount, payment_historyINTO customer_income, loan_amount, payment_historyFROM customer_tableWHERE customer_id = customer_id;-- 調用自定義函數計算風險指數risk_index := calculate_risk_index(customer_income, loan_amount, payment_history);-- 根據風險指數確定信用評級IF risk_index < 0.3 THENcredit_rating := 'Excellent';ELSIF risk_index < 0.5 THENcredit_rating := 'Good';ELSIF risk_index < 0.7 THENcredit_rating := 'Fair';ELSEcredit_rating := 'Poor';END IF;-- 更新客戶的信用評級UPDATE customer_tableSET credit_rating = credit_ratingWHERE customer_id = customer_id;
END;