谷歌官方建站服务/b站推广2023

数据校验和清洗

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

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

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;