數(shù)據(jù)校驗(yàn)和清洗

• 例如，檢查客戶的年齡是否在合理范圍內(nèi)，貸款金額是否符合規(guī)定的上下限等。

• 對(duì)于不符合規(guī)則的數(shù)據(jù)，可以進(jìn)行清洗和修正。比如，將空值替換為默認(rèn)值，或者對(duì)錯(cuò)誤的數(shù)據(jù)進(jìn)行糾正。

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

計(jì)算客戶的信用評(píng)分

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;-- 根據(jù)客戶收入計(jì)算部分信用評(píng)分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;-- 根據(jù)貸款金額和期限計(jì)算部分信用評(píng)分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;-- 根據(jù)還款歷史計(jì)算部分信用評(píng)分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íng)分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;                  -- 信用評(píng)分v_base_rate NUMBER := 0.05;             -- 基準(zhǔn)利率（假設(shè)為5%）v_risk_factor NUMBER := 0;              -- 風(fēng)險(xiǎn)調(diào)整因子BEGIN-- 獲取借款人信用評(píng)分（假設(shè)存儲(chǔ)在customer_info表中）SELECT credit_scoreINTO v_credit_scoreFROM customer_infoWHERE customer_id = p_customer_id;-- 根據(jù)信用評(píng)分調(diào)整風(fēng)險(xiǎn)因子IF v_credit_score >= 750 THENv_risk_factor := 0.01;  -- 信用評(píng)分高，風(fēng)險(xiǎn)低，利率調(diào)整因子小ELSIF v_credit_score BETWEEN 600 AND 749 THENv_risk_factor := 0.03;  -- 信用評(píng)分中等，風(fēng)險(xiǎn)中等，利率調(diào)整因子中等ELSEv_risk_factor := 0.05;  -- 信用評(píng)分低，風(fēng)險(xiǎn)高，利率調(diào)整因子大END IF;-- 根據(jù)貸款類型調(diào)整利率IF p_loan_type = '個(gè)人貸款' THENv_risk_factor := v_risk_factor + 0.02;ELSIF p_loan_type = '房貸' THENv_risk_factor := v_risk_factor + 0.01;ELSIF p_loan_type = '消費(fèi)貸款' THENv_risk_factor := v_risk_factor + 0.03;ELSEv_risk_factor := v_risk_factor + 0.04;  -- 其他貸款類型END IF;-- 根據(jù)貸款期限調(diào)整利率IF p_loan_term > 60 THENv_risk_factor := v_risk_factor + 0.02;  -- 貸款期限長(zhǎng)，風(fēng)險(xiǎn)增加END IF;-- 計(jì)算最終貸款利率p_loan_rate := v_base_rate + v_risk_factor;EXCEPTIONWHEN NO_DATA_FOUND THENp_loan_rate := NULL;DBMS_OUTPUT.PUT_LINE('未找到借款人信息，請(qǐng)檢查客戶ID是否正確。');WHEN OTHERS THENp_loan_rate := NULL;DBMS_OUTPUT.PUT_LINE('計(jì)算過(guò)程中發(fā)生錯(cuò)誤：' || SQLERRM);
END calculate_loan_rate;

?調(diào)用存儲(chǔ)過(guò)程

DECLAREv_customer_id NUMBER := 12345;          -- 借款人IDv_loan_amount NUMBER := 50000;         -- 貸款金額v_loan_term NUMBER := 60;              -- 貸款期限（月）v_loan_type VARCHAR2(20) := '個(gè)人貸款'; -- 貸款類型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('計(jì)算出的貸款利率為：' || TO_CHAR(v_loan_rate * 100) || '%');ELSEDBMS_OUTPUT.PUT_LINE('未能計(jì)算貸款利率，請(qǐng)檢查輸入?yún)?shù)。');END IF;
END;

生成月度貸款發(fā)放報(bào)表

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-- 打開(kāi)報(bào)表文件UTL_FILE.FOPEN('REPORT_DIR', 'monthly_loan_report.txt', 'W');-- 寫(xiě)入報(bào)表標(biāo)題UTL_FILE.PUT_LINE(UTL_FILE.FILE_TYPE, 'Monthly Loan Report');UTL_FILE.PUT_LINE(UTL_FILE.FILE_TYPE, '-------------------');-- 遍歷游標(biāo)，寫(xiě)入報(bào)表內(nèi)容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;-- 關(guān)閉報(bào)表文件UTL_FILE.FCLOSE(UTL_FILE.FILE_TYPE);
END;

sql解析(oracle):

TRUNC(SYSDATE, 'MONTH')：

這個(gè)函數(shù)將當(dāng)前日期 SYSDATE 截?cái)嗟皆路莸拈_(kāi)始，即返回當(dāng)前月份的第一天。

LAST_DAY(SYSDATE)：

這個(gè)函數(shù)返回當(dāng)前月份的最后一天

SYSDATE 返回的是數(shù)據(jù)庫(kù)服務(wù)器的當(dāng)前日期和時(shí)間

 -- 打開(kāi)報(bào)表文件
UTL_FILE.FOPEN('REPORT_DIR', 'monthly_loan_report.txt', 'W');

UTL_FILE 包

用于在 PL/SQL 程序中讀取和寫(xiě)入操作系統(tǒng)文件。它允許數(shù)據(jù)庫(kù)程序與文件系統(tǒng)交互，例如創(chuàng)建文件、寫(xiě)入數(shù)據(jù)、讀取文件內(nèi)容等

其中?UTL 是 Utility 的縮寫(xiě)，表示這是一個(gè)工具包

UTL_FILE.FOPEN?函數(shù)

用于打開(kāi)一個(gè)文件, 語(yǔ)法如下

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

• location：指定文件所在的目錄，必須是數(shù)據(jù)庫(kù)中定義的目錄別名（DIRECTORY 對(duì)象）。目錄別名是通過(guò) CREATE DIRECTORY 語(yǔ)句創(chuàng)建的，指向操作系統(tǒng)中的一個(gè)實(shí)際目錄。

• filename：指定要打開(kāi)的文件名。

• open_mode：指定文件的打開(kāi)模式，可以是：

  • 'R'：以只讀模式打開(kāi)文件。 Read（讀取）

  • 'W'：以寫(xiě)入模式打開(kāi)文件（如果文件已存在，內(nèi)容會(huì)被清空）。 Write（寫(xiě)入）

  • 'A'：以追加模式打開(kāi)文件（寫(xiě)入內(nèi)容會(huì)追加到文件末尾）。 Append（ 追加 )

• max_linesize：可選參數(shù)，指定文件的最大行長(zhǎng)度，默認(rèn)為 32767 字節(jié)

-- 寫(xiě)入報(bào)表標(biāo)題
UTL_FILE.FOPEN('REPORT_DIR', 'monthly_loan_report.txt', 'W');

?'REPORT_DIR'：這是目錄別名，指向一個(gè)已經(jīng)通過(guò) CREATE DIRECTORY 創(chuàng)建的目錄對(duì)象。例如：

?

返回值

UTL_FILE.FOPEN 返回一個(gè)文件句柄（FILE_TYPE），這個(gè)句柄用于后續(xù)的文件操作，例如寫(xiě)入內(nèi)容或關(guān)閉文件。

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

UTL_FILE.PUT_LINE函數(shù)

用于將一行文本寫(xiě)入到指定的文件中。

UTL_FILE.FILE_TYPE

一個(gè)文件類型對(duì)象，它在前面的代碼中通過(guò)UTL_FILE.FOPEN函數(shù)打開(kāi)了一個(gè)文件，并將其賦值給UTL_FILE.FILE_TYPE。這個(gè)對(duì)象代表了要寫(xiě)入的文件。

'Monthly Loan Report'

這是要寫(xiě)入文件的文本內(nèi)容，即報(bào)表的標(biāo)題

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

也是使用UTL_FILE.PUT_LINE函數(shù)將一行文本寫(xiě)入到文件中

'-------------------'：這是要寫(xiě)入文件的文本內(nèi)容，通常用于在標(biāo)題下方添加一條分隔線，以增強(qiáng)報(bào)表的可讀性。

-- 遍歷游標(biāo)，寫(xiě)入報(bào)表內(nèi)容
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循環(huán)

從游標(biāo)（loan_cursor）中逐條讀取數(shù)據(jù)，并將每條記錄的信息寫(xiě)入到一個(gè)文件中。

自動(dòng)從游標(biāo)中逐條取出數(shù)據(jù)，并將每條記錄賦值給 loan_record,?直到游標(biāo)中的所有記錄都被處理完畢。

UTL_FILE.PUT_LINE

是 UTL_FILE 包中的一個(gè)函數(shù)，用于將一行文本寫(xiě)入到文件中

UTL_FILE.FILE_TYPE?

這是要寫(xiě)入的目標(biāo)文件

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

這是要寫(xiě)入的字符串內(nèi)容

   -- 關(guān)閉報(bào)表文件UTL_FILE.FCLOSE(UTL_FILE.FILE_TYPE);
END;

UTL_FILE.FCLOSE函數(shù)

用于關(guān)閉一個(gè)通過(guò) UTL_FILE.FOPEN 打開(kāi)的文件。它的作用是釋放與文件句柄相關(guān)的資源，并確保文件正確關(guān)閉

UTL_FILE.FILE_TYPE

這是要關(guān)閉的文件, 也是之前UTL_FILE.FOPEN的返回值?

逾期貸款統(tǒng)計(jì)報(bào)表

-- 創(chuàng)建存儲(chǔ)過(guò)程
CREATE OR REPLACE PROCEDURE sp_OverdueLoanReport (p_StartDate DATE,  -- 統(tǒng)計(jì)開(kāi)始日期p_EndDate DATE,    -- 統(tǒng)計(jì)結(jié)束日期p_Cursor OUT SYS_REFCURSOR  -- 輸出游標(biāo)
) IS
BEGIN-- 打開(kāi)游標(biāo)，查詢逾期貸款數(shù)據(jù)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",  -- 計(jì)算逾期天數(shù)(l.loan_amount - NVL(SUM(p.payment_amount), 0)) AS "OverdueAmount" -- 計(jì)算逾期金額FROMloans lINNER JOINcustomers c ON l.customer_id = c.customer_idLEFT JOINpayments p ON l.loan_id = p.loan_idWHEREl.due_date <= SYSDATE  -- 篩選出逾期的貸款A(yù)ND 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;

解析:

這里沒(méi)有定義游標(biāo), 因?yàn)樵?Oracle 數(shù)據(jù)庫(kù)中，使用 SYS_REFCURSOR 作為存儲(chǔ)過(guò)程的輸出參數(shù)時(shí)，不需要顯式定義游標(biāo)。SYS_REFCURSOR 是一個(gè)系統(tǒng)定義的游標(biāo)類型，用于返回查詢結(jié)果集。在存儲(chǔ)過(guò)程中，直接通過(guò) OPEN p_Cursor FOR 語(yǔ)句將查詢結(jié)果集綁定到游標(biāo)即可。

SYS? 即 system 系統(tǒng)

REF? 即Reference 引用

OPEN p_Cursor FOR 后面的 select 語(yǔ)句

多表查詢語(yǔ)句:?

• loans l：主表，存儲(chǔ)貸款信息。

• INNER JOIN customers c ON l.customer_id = c.customer_id：通過(guò)客戶編號(hào)將 loans 表與 customers 表連接，獲取客戶名稱。

• LEFT JOIN payments p ON l.loan_id = p.loan_id：通過(guò)貸款編號(hào)將 loans 表與 payments 表連接，獲取還款信息。使用 LEFT JOIN 是為了確保即使沒(méi)有還款記錄的貸款也能被查詢到。

END sp_OverdueLoanReport;

oracle存儲(chǔ)過(guò)程的結(jié)束需要再寫(xiě)一次存儲(chǔ)過(guò)程名稱

將查詢結(jié)果直接插入到表中

方法 1：直接在存儲(chǔ)過(guò)程中插入到目標(biāo)表

前提: 已經(jīng)存在一個(gè)目標(biāo)表?

CREATE OR REPLACE PROCEDURE sp_OverdueLoanReport (p_StartDate DATE,  -- 統(tǒng)計(jì)開(kāi)始日期p_EndDate DATE,    -- 統(tǒng)計(jì)結(jié)束日期p_Cursor OUT SYS_REFCURSOR  -- 輸出游標(biāo)
) IS
BEGIN-- 清空目標(biāo)表（如果需要）DELETE FROM overdue_loan_report;-- 插入數(shù)據(jù)到目標(biāo)表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,  -- 計(jì)算逾期天數(shù)(l.loan_amount - NVL(SUM(p.payment_amount), 0)) AS OverdueAmount  -- 計(jì)算逾期金額FROMloans lINNER JOINcustomers c ON l.customer_id = c.customer_idLEFT JOINpayments p ON l.loan_id = p.loan_idWHEREl.due_date <= SYSDATE  -- 篩選出逾期的貸款A(yù)ND 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;-- 提交事務(wù)COMMIT;-- 打開(kāi)游標(biāo)，返回查詢結(jié)果OPEN p_Cursor FORSELECT * FROM overdue_loan_report ORDER BY OverdueDays DESC;END sp_OverdueLoanReport;

方法 2：使用臨時(shí)表存儲(chǔ)結(jié)果

CREATE OR REPLACE PROCEDURE sp_OverdueLoanReport (p_StartDate DATE,  -- 統(tǒng)計(jì)開(kāi)始日期p_EndDate DATE,    -- 統(tǒng)計(jì)結(jié)束日期p_Cursor OUT SYS_REFCURSOR  -- 輸出游標(biāo)
) IS
BEGIN-- 創(chuàng)建臨時(shí)表（如果尚未創(chuàng)建）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';-- 清空臨時(shí)表（如果需要）EXECUTE IMMEDIATE 'TRUNCATE TABLE temp_overdue_loan_report';-- 插入數(shù)據(jù)到臨時(shí)表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,  -- 計(jì)算逾期天數(shù)(l.loan_amount - NVL(SUM(p.payment_amount), 0)) AS OverdueAmount  -- 計(jì)算逾期金額FROMloans lINNER JOINcustomers c ON l.customer_id = c.customer_idLEFT JOINpayments p ON l.loan_id = p.loan_idWHEREl.due_date <= SYSDATE  -- 篩選出逾期的貸款A(yù)ND 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;-- 提交事務(wù)COMMIT;-- 打開(kāi)游標(biāo)，返回查詢結(jié)果OPEN p_Cursor FORSELECT * FROM temp_overdue_loan_report ORDER BY OverdueDays DESC;END sp_OverdueLoanReport;

解析:

CREATE GLOBAL TEMPORARY TABLE??

創(chuàng)建一個(gè)全局臨時(shí)表,?它允許會(huì)話（session）在事務(wù)中插入數(shù)據(jù)，并且在事務(wù)提交時(shí)不會(huì)刪除這些數(shù)據(jù)，而是在會(huì)話結(jié)束時(shí)刪除。?

ON COMMIT DELETE ROWS

全局臨時(shí)表的一個(gè)選項(xiàng)

ON COMMIT：指定在事務(wù)提交時(shí)的行為。

DELETE ROWS：

• 在事務(wù)提交時(shí)刪除數(shù)據(jù)：當(dāng)事務(wù)提交（COMMIT）或回滾（ROLLBACK）時(shí)，該臨時(shí)表中插入的所有數(shù)據(jù)會(huì)被自動(dòng)刪除。

• 表結(jié)構(gòu)保留：雖然數(shù)據(jù)被刪除，但臨時(shí)表的結(jié)構(gòu)仍然存在，不會(huì)被刪除。

計(jì)算客戶的信用風(fēng)險(xiǎn)指數(shù)

創(chuàng)建自定義函數(shù)

CREATE OR REPLACE FUNCTION calculate_risk_index(customer_income NUMBER,loan_amount NUMBER,payment_history VARCHAR2
) RETURN NUMBER ASrisk_index NUMBER;
BEGIN-- 根據(jù)收入、貸款金額和支付歷史計(jì)算風(fēng)險(xiǎn)指數(shù)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;

創(chuàng)建一個(gè)存儲(chǔ)過(guò)程來(lái)調(diào)用這個(gè)自定義函數(shù)，并執(zhí)行其他數(shù)據(jù)庫(kù)操作，比如更新客戶的信用評(píng)級(jí)

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-- 獲取客戶的相關(guān)信息SELECT income, loan_amount, payment_historyINTO customer_income, loan_amount, payment_historyFROM customer_tableWHERE customer_id = customer_id;-- 調(diào)用自定義函數(shù)計(jì)算風(fēng)險(xiǎn)指數(shù)risk_index := calculate_risk_index(customer_income, loan_amount, payment_history);-- 根據(jù)風(fēng)險(xiǎn)指數(shù)確定信用評(píng)級(jí)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;-- 更新客戶的信用評(píng)級(jí)UPDATE customer_tableSET credit_rating = credit_ratingWHERE customer_id = customer_id;
END;