【Oracle】存储过程

个人主页：Guiat
归属专栏：Oracle

正文

1. 存储过程基础概述

存储过程是预编译的SQL和PL/SQL代码块，存储在数据库中，可以重复调用执行。它是Oracle数据库中实现复杂业务逻辑的重要工具。

1.1 存储过程的概念与特点

1.2 存储过程的组成结构

1.3 存储过程的优势

• 性能优化：预编译，执行效率高
• 网络优化：减少客户端与服务器间的通信
• 安全性：封装数据访问，防止SQL注入
• 维护性：集中管理业务逻辑
• 可重用性：一次编写，多处调用

2. 基础存储过程

2.1 简单存储过程

2.1.1 创建第一个存储过程

-- 创建简单的存储过程
CREATE OR REPLACE PROCEDURE hello_world
AS
BEGINDBMS_OUTPUT.PUT_LINE('Hello, Oracle World!');DBMS_OUTPUT.PUT_LINE('当前时间: ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END hello_world;
/-- 调用存储过程
SET SERVEROUTPUT ON
EXEC hello_world;-- 或者使用CALL语句
CALL hello_world();-- 在PL/SQL块中调用
BEGINhello_world;
END;
/

2.1.2 带变量的存储过程

-- 创建包含变量的存储过程
CREATE OR REPLACE PROCEDURE employee_count_info
ASv_total_count NUMBER;v_active_count NUMBER;v_avg_salary NUMBER;v_max_salary NUMBER;v_min_salary NUMBER;
BEGIN-- 获取员工统计信息SELECT COUNT(*),COUNT(CASE WHEN hire_date >= ADD_MONTHS(SYSDATE, -12) THEN 1 END),ROUND(AVG(salary), 2),MAX(salary),MIN(salary)INTO v_total_count, v_active_count, v_avg_salary, v_max_salary, v_min_salaryFROM employees;-- 输出统计信息DBMS_OUTPUT.PUT_LINE('=== 员工统计信息 ===');DBMS_OUTPUT.PUT_LINE('总员工数: ' || v_total_count);DBMS_OUTPUT.PUT_LINE('近一年入职: ' || v_active_count);DBMS_OUTPUT.PUT_LINE('平均工资: $' || v_avg_salary);DBMS_OUTPUT.PUT_LINE('最高工资: $' || v_max_salary);DBMS_OUTPUT.PUT_LINE('最低工资: $' || v_min_salary);-- 工资分析IF v_avg_salary > 8000 THENDBMS_OUTPUT.PUT_LINE('工资水平: 较高');ELSIF v_avg_salary > 5000 THENDBMS_OUTPUT.PUT_LINE('工资水平: 中等');ELSEDBMS_OUTPUT.PUT_LINE('工资水平: 偏低');END IF;END employee_count_info;
/-- 执行存储过程
EXEC employee_count_info;

2.2 带参数的存储过程

2.2.1 输入参数 (IN)

-- 创建带输入参数的存储过程
CREATE OR REPLACE PROCEDURE get_employee_info(p_employee_id IN NUMBER
)
ASv_first_name VARCHAR2(50);v_last_name VARCHAR2(50);v_email VARCHAR2(100);v_salary NUMBER;v_hire_date DATE;v_department_name VARCHAR2(50);v_job_title VARCHAR2(50);v_manager_name VARCHAR2(100);
BEGIN-- 查询员工详细信息SELECT e.first_name,e.last_name,e.email,e.salary,e.hire_date,d.department_name,j.job_title,m.first_name || ' ' || m.last_nameINTO v_first_name, v_last_name, v_email, v_salary, v_hire_date,v_department_name, v_job_title, v_manager_nameFROM employees eLEFT JOIN departments d ON e.department_id = d.department_idLEFT JOIN jobs j ON e.job_id = j.job_idLEFT JOIN employees m ON e.manager_id = m.employee_idWHERE e.employee_id = p_employee_id;-- 显示员工信息DBMS_OUTPUT.PUT_LINE('=== 员工信息 ===');DBMS_OUTPUT.PUT_LINE('姓名: ' || v_first_name || ' ' || v_last_name);DBMS_OUTPUT.PUT_LINE('邮箱: ' || v_email);DBMS_OUTPUT.PUT_LINE('工资: $' || v_salary);DBMS_OUTPUT.PUT_LINE('入职日期: ' || TO_CHAR(v_hire_date, 'YYYY-MM-DD'));DBMS_OUTPUT.PUT_LINE('部门: ' || NVL(v_department_name, '未分配'));DBMS_OUTPUT.PUT_LINE('职位: ' || NVL(v_job_title, '未知'));DBMS_OUTPUT.PUT_LINE('经理: ' || NVL(v_manager_name, '无'));-- 计算工作年限DBMS_OUTPUT.PUT_LINE('工作年限: ' || ROUND(MONTHS_BETWEEN(SYSDATE, v_hire_date) / 12, 1) || ' 年');EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('错误: 员工ID ' || p_employee_id || ' 不存在');WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('发生错误: ' || SQLERRM);
END get_employee_info;
/-- 调用带参数的存储过程
EXEC get_employee_info(100);
EXEC get_employee_info(999); -- 测试不存在的员工

2.2.2 输出参数 (OUT)

-- 创建带输出参数的存储过程
CREATE OR REPLACE PROCEDURE calculate_employee_stats(p_department_id IN NUMBER,p_emp_count OUT NUMBER,p_avg_salary OUT NUMBER,p_total_salary OUT NUMBER,p_max_salary OUT NUMBER,p_min_salary OUT NUMBER
)
AS
BEGINSELECT COUNT(*),ROUND(AVG(salary), 2),SUM(salary),MAX(salary),MIN(salary)INTO p_emp_count, p_avg_salary, p_total_salary, p_max_salary, p_min_salaryFROM employeesWHERE department_id = p_department_id;-- 如果没有找到员工，设置默认值IF p_emp_count = 0 THENp_avg_salary := 0;p_total_salary := 0;p_max_salary := 0;p_min_salary := 0;END IF;EXCEPTIONWHEN OTHERS THENp_emp_count := -1;p_avg_salary := 0;p_total_salary := 0;p_max_salary := 0;p_min_salary := 0;
END calculate_employee_stats;
/-- 调用带输出参数的存储过程
DECLAREv_count NUMBER;v_avg_salary NUMBER;v_total_salary NUMBER;v_max_salary NUMBER;v_min_salary NUMBER;v_dept_id NUMBER := 60; -- IT部门
BEGINcalculate_employee_stats(p_department_id => v_dept_id,p_emp_count => v_count,p_avg_salary => v_avg_salary,p_total_salary => v_total_salary,p_max_salary => v_max_salary,p_min_salary => v_min_salary);DBMS_OUTPUT.PUT_LINE('=== 部门 ' || v_dept_id || ' 统计信息 ===');DBMS_OUTPUT.PUT_LINE('员工数量: ' || v_count);DBMS_OUTPUT.PUT_LINE('平均工资: $' || v_avg_salary);DBMS_OUTPUT.PUT_LINE('工资总额: $' || v_total_salary);DBMS_OUTPUT.PUT_LINE('最高工资: $' || v_max_salary);DBMS_OUTPUT.PUT_LINE('最低工资: $' || v_min_salary);
END;
/

2.2.3 输入输出参数 (IN OUT)

-- 创建带IN OUT参数的存储过程
CREATE OR REPLACE PROCEDURE adjust_salary(p_employee_id IN NUMBER,p_salary_change IN OUT NUMBER,p_result_message OUT VARCHAR2
)
ASv_current_salary NUMBER;v_new_salary NUMBER;v_min_salary NUMBER;v_max_salary NUMBER;v_job_id VARCHAR2(10);
BEGIN-- 获取员工当前信息SELECT salary, job_idINTO v_current_salary, v_job_idFROM employeesWHERE employee_id = p_employee_id;-- 获取职位工资范围SELECT min_salary, max_salaryINTO v_min_salary, v_max_salaryFROM jobsWHERE job_id = v_job_id;-- 如果输入的是百分比（小于1），转换为实际金额IF p_salary_change < 1 AND p_salary_change > -1 THENp_salary_change := v_current_salary * p_salary_change;END IF;-- 计算新工资v_new_salary := v_current_salary + p_salary_change;-- 检查工资范围IF v_new_salary < v_min_salary THENv_new_salary := v_min_salary;p_salary_change := v_new_salary - v_current_salary;p_result_message := '工资已调整到职位最低标准: $' || v_min_salary;ELSIF v_new_salary > v_max_salary THENv_new_salary := v_max_salary;p_salary_change := v_new_salary - v_current_salary;p_result_message := '工资已调整到职位最高标准: $' || v_max_salary;ELSEp_result_message := '工资调整成功: $' || v_current_salary || ' -> $' || v_new_salary;END IF;-- 更新员工工资UPDATE employeesSET salary = v_new_salary,last_updated = SYSDATEWHERE employee_id = p_employee_id;-- 返回实际调整金额p_salary_change := v_new_salary - v_current_salary;EXCEPTIONWHEN NO_DATA_FOUND THENp_result_message := '错误: 员工ID ' || p_employee_id || ' 不存在';p_salary_change := 0;WHEN OTHERS THENp_result_message := '错误: ' || SQLERRM;p_salary_change := 0;ROLLBACK;
END adjust_salary;
/-- 测试IN OUT参数
DECLAREv_salary_change NUMBER := 0.1; -- 10%增长v_message VARCHAR2(200);v_emp_id NUMBER := 107;
BEGINDBMS_OUTPUT.PUT_LINE('调整前工资变化: ' || v_salary_change);adjust_salary(p_employee_id => v_emp_id,p_salary_change => v_salary_change,p_result_message => v_message);DBMS_OUTPUT.PUT_LINE('实际工资变化: $' || v_salary_change);DBMS_OUTPUT.PUT_LINE('结果: ' || v_message);COMMIT;
END;
/

3. 高级存储过程特性

3.1 游标的使用

3.1.1 显式游标

-- 使用显式游标的存储过程
CREATE OR REPLACE PROCEDURE process_department_salaries(p_department_id IN NUMBER,p_increase_percent IN NUMBER DEFAULT 5
)
AS-- 声明游标CURSOR emp_cursor ISSELECT employee_id, first_name, last_name, salaryFROM employeesWHERE department_id = p_department_idORDER BY salary;-- 游标记录类型emp_rec emp_cursor%ROWTYPE;-- 其他变量v_counter NUMBER := 0;v_total_increase NUMBER := 0;v_old_salary NUMBER;v_new_salary NUMBER;BEGINDBMS_OUTPUT.PUT_LINE('=== 部门 ' || p_department_id || ' 工资调整 ===');DBMS_OUTPUT.PUT_LINE('调整比例: ' || p_increase_percent || '%');DBMS_OUTPUT.PUT_LINE('');-- 打开游标OPEN emp_cursor;LOOP-- 提取数据FETCH emp_cursor INTO emp_rec;-- 检查是否还有数据EXIT WHEN emp_cursor%NOTFOUND;-- 处理当前员工v_counter := v_counter + 1;v_old_salary := emp_rec.salary;v_new_salary := ROUND(v_old_salary * (1 + p_increase_percent / 100), 2);-- 更新工资UPDATE employeesSET salary = v_new_salary,last_updated = SYSDATEWHERE employee_id = emp_rec.employee_id;-- 累计增长金额v_total_increase := v_total_increase + (v_new_salary - v_old_salary);-- 显示调整信息DBMS_OUTPUT.PUT_LINE(v_counter || '. ' || emp_rec.first_name || ' ' || emp_rec.last_name || ': $' || v_old_salary || ' -> $' || v_new_salary || ' (+$' || (v_new_salary - v_old_salary) || ')');END LOOP;-- 关闭游标CLOSE emp_cursor;-- 显示汇总信息DBMS_OUTPUT.PUT_LINE('');DBMS_OUTPUT.PUT_LINE('=== 调整汇总 ===');DBMS_OUTPUT.PUT_LINE('处理员工数: ' || v_counter);DBMS_OUTPUT.PUT_LINE('总增长金额: $' || ROUND(v_total_increase, 2));IF v_counter > 0 THENDBMS_OUTPUT.PUT_LINE('平均增长: $' || ROUND(v_total_increase / v_counter, 2));COMMIT;DBMS_OUTPUT.PUT_LINE('工资调整已提交');ELSEDBMS_OUTPUT.PUT_LINE('未找到符合条件的员工');END IF;EXCEPTIONWHEN OTHERS THENIF emp_cursor%ISOPEN THENCLOSE emp_cursor;END IF;ROLLBACK;DBMS_OUTPUT.PUT_LINE('错误: ' || SQLERRM);
END process_department_salaries;
/-- 测试游标存储过程
EXEC process_department_salaries(20, 8); -- IT部门加薪8%

3.1.2 游标FOR循环

-- 使用游标FOR循环的存储过程
CREATE OR REPLACE PROCEDURE generate_employee_report(p_department_id IN NUMBER DEFAULT NULL
)
ASv_total_employees NUMBER := 0;v_total_salary NUMBER := 0;v_dept_name VARCHAR2(50);
BEGIN-- 获取部门名称IF p_department_id IS NOT NULL THENSELECT department_name INTO v_dept_nameFROM departments WHERE department_id = p_department_id;DBMS_OUTPUT.PUT_LINE('=== ' || v_dept_name || ' 部门员工报告 ===');ELSEDBMS_OUTPUT.PUT_LINE('=== 全公司员工报告 ===');END IF;DBMS_OUTPUT.PUT_LINE('');DBMS_OUTPUT.PUT_LINE(RPAD('员工姓名', 20) || RPAD('职位', 15) || RPAD('工资', 10) || RPAD('入职日期', 12) || '工作年限');DBMS_OUTPUT.PUT_LINE(RPAD('-', 70, '-'));-- 游标FOR循环FOR emp_rec IN (SELECT e.first_name || ' ' || e.last_name AS full_name,j.job_title,e.salary,e.hire_date,ROUND(MONTHS_BETWEEN(SYSDATE, e.hire_date) / 12, 1) AS years_serviceFROM employees eLEFT JOIN jobs j ON e.job_id = j.job_idWHERE (p_department_id IS NULL OR e.department_id = p_department_id)ORDER BY e.salary DESC) LOOP-- 处理每个员工记录v_total_employees := v_total_employees + 1;v_total_salary := v_total_salary + emp_rec.salary;DBMS_OUTPUT.PUT_LINE(RPAD(emp_rec.full_name, 20) ||RPAD(NVL(emp_rec.job_title, 'N/A'), 15) ||RPAD('$' || emp_rec.salary, 10) ||RPAD(TO_CHAR(emp_rec.hire_date, 'YYYY-MM-DD'), 12) ||emp_rec.years_service || '年');END LOOP;-- 显示汇总信息DBMS_OUTPUT.PUT_LINE(RPAD('-', 70, '-'));DBMS_OUTPUT.PUT_LINE('总员工数: ' || v_total_employees);DBMS_OUTPUT.PUT_LINE('工资总额: $' || v_total_salary);IF v_total_employees > 0 THENDBMS_OUTPUT.PUT_LINE('平均工资: $' || ROUND(v_total_salary / v_total_employees, 2));END IF;EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('未找到指定部门或部门无员工');WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('生成报告时发生错误: ' || SQLERRM);
END generate_employee_report;
/-- 测试游标FOR循环
EXEC generate_employee_report(60); -- IT部门报告
EXEC generate_employee_report; -- 全公司报告

3.2 异常处理

3.2.1 预定义异常处理

-- 完善的异常处理存储过程
CREATE OR REPLACE PROCEDURE safe_employee_update(p_employee_id IN NUMBER,p_first_name IN VARCHAR2 DEFAULT NULL,p_last_name IN VARCHAR2 DEFAULT NULL,p_email IN VARCHAR2 DEFAULT NULL,p_salary IN NUMBER DEFAULT NULL,p_department_id IN NUMBER DEFAULT NULL
)
ASv_current_email VARCHAR2(100);v_update_count NUMBER := 0;v_sql_stmt VARCHAR2(4000) := 'UPDATE employees SET last_updated = SYSDATE';v_where_clause VARCHAR2(100) := ' WHERE employee_id = :emp_id';
BEGIN-- 验证员工是否存在SELECT email INTO v_current_emailFROM employees WHERE employee_id = p_employee_id;-- 构建动态更新语句IF p_first_name IS NOT NULL THENv_sql_stmt := v_sql_stmt || ', first_name = :first_name';END IF;IF p_last_name IS NOT NULL THENv_sql_stmt := v_sql_stmt || ', last_name = :last_name';END IF;IF p_email IS NOT NULL THEN-- 检查邮箱唯一性SELECT COUNT(*) INTO v_update_countFROM employees WHERE email = p_email AND employee_id != p_employee_id;IF v_update_count > 0 THENRAISE_APPLICATION_ERROR(-20001, '邮箱地址已被其他员工使用');END IF;v_sql_stmt := v_sql_stmt || ', email = :email';END IF;IF p_salary IS NOT NULL THENIF p_salary <= 0 THENRAISE_APPLICATION_ERROR(-20002, '工资必须大于0');END IF;v_sql_stmt := v_sql_stmt || ', salary = :salary';END IF;IF p_department_id IS NOT NULL THEN-- 验证部门是否存在SELECT COUNT(*) INTO v_update_countFROM departments WHERE department_id = p_department_id;IF v_update_count = 0 THENRAISE_APPLICATION_ERROR(-20003, '指定的部门不存在');END IF;v_sql_stmt := v_sql_stmt || ', department_id = :dept_id';END IF;-- 执行更新（这里简化处理，实际应用中可使用动态SQL）UPDATE employees SET first_name = NVL(p_first_name, first_name),last_name = NVL(p_last_name, last_name),email = NVL(p_email, email),salary = NVL(p_salary, salary),department_id = NVL(p_department_id, department_id),last_updated = SYSDATEWHERE employee_id = p_employee_id;v_update_count := SQL%ROWCOUNT;IF v_update_count > 0 THENCOMMIT;DBMS_OUTPUT.PUT_LINE('员工信息更新成功');DBMS_OUTPUT.PUT_LINE('员工ID: ' || p_employee_id);DBMS_OUTPUT.PUT_LINE('更新行数: ' || v_update_count);ELSEDBMS_OUTPUT.PUT_LINE('没有记录被更新');END IF;EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('错误: 员工ID ' || p_employee_id || ' 不存在');WHEN DUP_VAL_ON_INDEX THENROLLBACK;DBMS_OUTPUT.PUT_LINE('错误: 违反唯一性约束，可能是邮箱重复');WHEN VALUE_ERROR THENROLLBACK;DBMS_OUTPUT.PUT_LINE('错误: 数据类型或长度错误');WHEN INVALID_NUMBER THENROLLBACK;DBMS_OUTPUT.PUT_LINE('错误: 无效的数字格式');WHEN OTHERS THENROLLBACK;IF SQLCODE BETWEEN -20999 AND -20000 THEN-- 用户定义的应用错误DBMS_OUTPUT.PUT_LINE('业务错误: ' || SQLERRM);ELSE-- 其他系统错误DBMS_OUTPUT.PUT_LINE('系统错误: ' || SQLCODE || ' - ' || SQLERRM);END IF;
END safe_employee_update;
/-- 测试异常处理
BEGIN-- 正常更新safe_employee_update(p_employee_id => 107,p_first_name => 'Diana',p_salary => 5200);-- 测试各种异常情况safe_employee_update(999, 'Test', 'User'); -- 员工不存在safe_employee_update(107, p_salary => -1000); -- 无效工资safe_employee_update(107, p_department_id => 999); -- 部门不存在
END;
/

3.2.2 用户定义异常

-- 创建带用户定义异常的存储过程
CREATE OR REPLACE PROCEDURE transfer_employee(p_employee_id IN NUMBER,p_new_department_id IN NUMBER,p_effective_date IN DATE DEFAULT SYSDATE
)
AS-- 用户定义异常employee_not_found EXCEPTION;department_not_found EXCEPTION;invalid_transfer_date EXCEPTION;same_department EXCEPTION;employee_is_manager EXCEPTION;-- 变量声明v_current_dept_id NUMBER;v_dept_count NUMBER;v_manager_count NUMBER;v_employee_name VARCHAR2(100);v_old_dept_name VARCHAR2(50);v_new_dept_name VARCHAR2(50);BEGIN-- 验证转移日期IF p_effective_date < TRUNC(SYSDATE) THENRAISE invalid_transfer_date;END IF;-- 获取员工当前信息BEGINSELECT e.department_id, e.first_name || ' ' || e.last_name,d.department_nameINTO v_current_dept_id, v_employee_name, v_old_dept_nameFROM employees eLEFT JOIN departments d ON e.department_id = d.department_idWHERE e.employee_id = p_employee_id;EXCEPTIONWHEN NO_DATA_FOUND THENRAISE employee_not_found;END;-- 检查是否转移到相同部门IF v_current_dept_id = p_new_department_id THENRAISE same_department;END IF;-- 验证新部门是否存在SELECT COUNT(*), MAX(department_name)INTO v_dept_count, v_new_dept_nameFROM departments WHERE department_id = p_new_department_id;IF v_dept_count = 0 THENRAISE department_not_found;END IF;-- 检查员工是否是部门经理SELECT COUNT(*)INTO v_manager_countFROM departmentsWHERE manager_id = p_employee_id;IF v_manager_count > 0 THENRAISE employee_is_manager;END IF;-- 记录转移历史INSERT INTO employee_transfer_history (transfer_id,employee_id,old_department_id,new_department_id,transfer_date,created_by,created_date) VALUES (employee_transfer_seq.NEXTVAL,p_employee_id,v_current_dept_id,p_new_department_id,p_effective_date,USER,SYSDATE);-- 执行转移UPDATE employeesSET department_id = p_new_department_id,last_updated = SYSDATEWHERE employee_id = p_employee_id;COMMIT;-- 输出成功信息DBMS_OUTPUT.PUT_LINE('=== 员工转移成功 ===');DBMS_OUTPUT.PUT_LINE('员工: ' || v_employee_name);DBMS_OUTPUT.PUT_LINE('从: ' || NVL(v_old_dept_name, '未分配部门'));DBMS_OUTPUT.PUT_LINE('到: ' || v_new_dept_name);DBMS_OUTPUT.PUT_LINE('生效日期: ' || TO_CHAR(p_effective_date, 'YYYY-MM-DD'));EXCEPTIONWHEN employee_not_found THENDBMS_OUTPUT.PUT_LINE('错误: 员工ID ' || p_employee_id || ' 不存在');WHEN department_not_found THENDBMS_OUTPUT.PUT_LINE('错误: 目标部门ID ' || p_new_department_id || ' 不存在');WHEN invalid_transfer_date THENDBMS_OUTPUT.PUT_LINE('错误: 转移日期不能早于今天');WHEN same_department THENDBMS_OUTPUT.PUT_LINE('错误: 员工已经在目标部门中');WHEN employee_is_manager THENDBMS_OUTPUT.PUT_LINE('错误: 无法转移部门经理，请先指定新经理');WHEN OTHERS THENROLLBACK;DBMS_OUTPUT.PUT_LINE('转移失败: ' || SQLERRM);
END transfer_employee;
/-- 创建转移历史表（如果不存在）
CREATE TABLE employee_transfer_history (transfer_id NUMBER PRIMARY KEY,employee_id NUMBER,old_department_id NUMBER,new_department_id NUMBER,transfer_date DATE,created_by VARCHAR2(30),created_date DATE
);CREATE SEQUENCE employee_transfer_seq START WITH 1 INCREMENT BY 1;-- 测试用户定义异常
EXEC transfer_employee(107, 20); -- 正常转移
EXEC transfer_employee(999, 20); -- 员工不存在
EXEC transfer_employee(107, 999); -- 部门不存在
EXEC transfer_employee(107, 20); -- 相同部门

4. 存储过程的高级应用

4.1 动态SQL

4.1.1 使用EXECUTE IMMEDIATE

-- 动态SQL存储过程
CREATE OR REPLACE PROCEDURE dynamic_table_stats(p_table_name IN VARCHAR2,p_owner IN VARCHAR2 DEFAULT USER
)
ASv_sql VARCHAR2(4000);v_count NUMBER;v_avg_value NUMBER;v_max_value NUMBER;v_min_value NUMBER;v_column_name VARCHAR2(128);v_data_type VARCHAR2(128);-- 游标：获取数值列CURSOR num_columns_cur ISSELECT column_name, data_typeFROM all_tab_columnsWHERE table_name = UPPER(p_table_name)AND owner = UPPER(p_owner)AND data_type IN ('NUMBER', 'INTEGER', 'FLOAT')ORDER BY column_id;BEGINDBMS_OUTPUT.PUT_LINE('=== 表 ' || p_owner || '.' || p_table_name || ' 统计信息 ===');-- 检查表是否存在v_sql := 'SELECT COUNT(*) FROM ' || p_owner || '.' || p_table_name;BEGINEXECUTE IMMEDIATE v_sql INTO v_count;DBMS_OUTPUT.PUT_LINE('总记录数: ' || v_count);EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('错误: 表不存在或无访问权限');RETURN;END;IF v_count = 0 THENDBMS_OUTPUT.PUT_LINE('表为空，无统计数据');RETURN;END IF;DBMS_OUTPUT.PUT_LINE('');DBMS_OUTPUT.PUT_LINE('数值列统计:');DBMS_OUTPUT.PUT_LINE(RPAD('列名', 20) || RPAD('数据类型', 15) || RPAD('平均值', 12) || RPAD('最大值', 12) || '最小值');DBMS_OUTPUT.PUT_LINE(RPAD('-', 70, '-'));-- 遍历数值列FOR col_rec IN num_columns_cur LOOPBEGIN-- 构建动态SQLv_sql := 'SELECT ROUND(AVG(' || col_rec.column_name || '), 2), ' ||'MAX(' || col_rec.column_name || '), ' ||'MIN(' || col_rec.column_name || ') ' ||'FROM ' || p_owner || '.' || p_table_name ||' WHERE ' || col_rec.column_name || ' IS NOT NULL';EXECUTE IMMEDIATE v_sql INTO v_avg_value, v_max_value, v_min_value;DBMS_OUTPUT.PUT_LINE(RPAD(col_rec.column_name, 20) ||RPAD(col_rec.data_type, 15) ||RPAD(NVL(TO_CHAR(v_avg_value), 'N/A'), 12) ||RPAD(NVL(TO_CHAR(v_max_value), 'N/A'), 12) ||NVL(TO_CHAR(v_min_value), 'N/A'));EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE(RPAD(col_rec.column_name, 20) ||RPAD(col_rec.data_type, 15) ||'计算错误: ' || SUBSTR(SQLERRM, 1, 30));END;END LOOP;EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('执行错误: ' || SQLERRM);
END dynamic_table_stats;
/-- 测试动态SQL
EXEC dynamic_table_stats('EMPLOYEES');
EXEC dynamic_table_stats('DEPARTMENTS');

4.1.2 动态查询构建器

-- 通用查询构建器存储过程
CREATE OR REPLACE PROCEDURE flexible_employee_search(p_department_id IN NUMBER DEFAULT NULL,p_job_id IN VARCHAR2 DEFAULT NULL,p_min_salary IN NUMBER DEFAULT NULL,p_max_salary IN NUMBER DEFAULT NULL,p_hire_date_from IN DATE DEFAULT NULL,p_hire_date_to IN DATE DEFAULT NULL,p_order_by IN VARCHAR2 DEFAULT 'employee_id',p_order_direction IN VARCHAR2 DEFAULT 'ASC'
)
ASv_sql VARCHAR2(4000);v_where_clause VARCHAR2(2000) := '';v_conditions NUMBER := 0;-- 定义REF CURSOR类型TYPE emp_cursor_type IS REF CURSOR;emp_cursor emp_cursor_type;-- 记录类型v_employee_id NUMBER;v_full_name VARCHAR2(100);v_email VARCHAR2(100);v_salary NUMBER;v_hire_date DATE;v_department_name VARCHAR2(50);v_job_title VARCHAR2(50);BEGIN-- 构建基本查询v_sql := 'SELECT e.employee_id, ' ||'e.first_name || '' '' || e.last_name AS full_name, ' ||'e.email, e.salary, e.hire_date, ' ||'d.department_name, j.job_title ' ||'FROM employees e ' ||'LEFT JOIN departments d ON e.department_id = d.department_id ' ||'LEFT JOIN jobs j ON e.job_id = j.job_id';-- 构建WHERE条件IF p_department_id IS NOT NULL THENv_where_clause := v_where_clause || CASE WHEN v_conditions > 0 THEN ' AND ' ELSE ' WHERE ' END ||'e.department_id = ' || p_department_id;v_conditions := v_conditions + 1;END IF;IF p_job_id IS NOT NULL THENv_where_clause := v_where_clause || CASE WHEN v_conditions > 0 THEN ' AND ' ELSE ' WHERE ' END ||'e.job_id = ''' || p_job_id || '''';v_conditions := v_conditions + 1;END IF;IF p_min_salary IS NOT NULL THENv_where_clause := v_where_clause || CASE WHEN v_conditions > 0 THEN ' AND ' ELSE ' WHERE ' END ||'e.salary >= ' || p_min_salary;v_conditions := v_conditions + 1;END IF;IF p_max_salary IS NOT NULL THENv_where_clause := v_where_clause || CASE WHEN v_conditions > 0 THEN ' AND ' ELSE ' WHERE ' END ||'e.salary <= ' || p_max_salary;v_conditions := v_conditions + 1;END IF;IF p_hire_date_from IS NOT NULL THENv_where_clause := v_where_clause || CASE WHEN v_conditions > 0 THEN ' AND ' ELSE ' WHERE ' END ||'e.hire_date >= DATE ''' || TO_CHAR(p_hire_date_from, 'YYYY-MM-DD') || '''';v_conditions := v_conditions + 1;END IF;IF p_hire_date_to IS NOT NULL THENv_where_clause := v_where_clause || CASE WHEN v_conditions > 0 THEN ' AND ' ELSE ' WHERE ' END ||'e.hire_date <= DATE ''' || TO_CHAR(p_hire_date_to, 'YYYY-MM-DD') || '''';v_conditions := v_conditions + 1;END IF;-- 组合完整SQLv_sql := v_sql || v_where_clause || ' ORDER BY ' || p_order_by || ' ' || p_order_direction;DBMS_OUTPUT.PUT_LINE('=== 员工搜索结果 ===');DBMS_OUTPUT.PUT_LINE('查询SQL: ' || v_sql);DBMS_OUTPUT.PUT_LINE('');DBMS_OUTPUT.PUT_LINE(RPAD('ID', 6) || RPAD('姓名', 20) || RPAD('邮箱', 25) || RPAD('工资', 10) || RPAD('入职日期', 12) || RPAD('部门', 15) || '职位');DBMS_OUTPUT.PUT_LINE(RPAD('-', 100, '-'));-- 执行动态查询OPEN emp_cursor FOR v_sql;LOOPFETCH emp_cursor INTO v_employee_id, v_full_name, v_email, v_salary, v_hire_date, v_department_name, v_job_title;EXIT WHEN emp_cursor%NOTFOUND;DBMS_OUTPUT.PUT_LINE(RPAD(v_employee_id, 6) ||RPAD(SUBSTR(v_full_name, 1, 19), 20) ||RPAD(SUBSTR(v_email, 1, 24), 25) ||RPAD('$' || v_salary, 10) ||RPAD(TO_CHAR(v_hire_date, 'YYYY-MM-DD'), 12) ||RPAD(SUBSTR(NVL(v_department_name, 'N/A'), 1, 14), 15) ||SUBSTR(NVL(v_job_title, 'N/A'), 1, 20));END LOOP;DBMS_OUTPUT.PUT_LINE(RPAD('-', 100, '-'));DBMS_OUTPUT.PUT_LINE('找到 ' || emp_cursor%ROWCOUNT || ' 条记录');CLOSE emp_cursor;EXCEPTIONWHEN OTHERS THENIF emp_cursor%ISOPEN THENCLOSE emp_cursor;END IF;DBMS_OUTPUT.PUT_LINE('查询执行错误: ' || SQLERRM);
END flexible_employee_search;
/-- 测试动态查询构建器
-- 搜索IT部门，工资在5000-10000之间的员工
EXEC flexible_employee_search(p_department_id => 60, p_min_salary => 5000, p_max_salary => 10000);-- 搜索2005年后入职的员工，按工资降序排列
EXEC flexible_employee_search(p_hire_date_from => DATE '2005-01-01', p_order_by => 'salary', p_order_direction => 'DESC');

4.2 批量处理

4.2.1 BULK COLLECT和FORALL

-- 高性能批量处理存储过程
CREATE OR REPLACE PROCEDURE bulk_salary_adjustment(p_department_id IN NUMBER,p_adjustment_type IN VARCHAR2, -- 'PCT' for percentage, 'AMT' for amountp_adjustment_value IN NUMBER,p_batch_size IN NUMBER DEFAULT 1000
)
AS-- 声明集合类型TYPE emp_id_array IS TABLE OF employees.employee_id%TYPE;TYPE salary_array IS TABLE OF employees.salary%TYPE;TYPE name_array IS TABLE OF VARCHAR2(100);-- 声明集合变量v_emp_ids emp_id_array;v_old_salaries salary_array;v_new_salaries salary_array;v_emp_names name_array;-- 其他变量v_total_processed NUMBER := 0;v_total_adjustment NUMBER := 0;v_batch_count NUMBER := 0;-- 游标声明CURSOR emp_cursor ISSELECT employee_id, salary, first_name || ' ' || last_nameFROM employeesWHERE department_id = p_department_idORDER BY employee_id;BEGINDBMS_OUTPUT.PUT_LINE('=== 批量工资调整开始 ===');DBMS_OUTPUT.PUT_LINE('部门ID: ' || p_department_id);DBMS_OUTPUT.PUT_LINE('调整类型: ' || CASE p_adjustment_type WHEN 'PCT' THEN '百分比' WHEN 'AMT' THEN '固定金额' ELSE '未知' END);DBMS_OUTPUT.PUT_LINE('调整值: ' || p_adjustment_value);DBMS_OUTPUT.PUT_LINE('批次大小: ' || p_batch_size);DBMS_OUTPUT.PUT_LINE('');-- 验证调整类型IF p_adjustment_type NOT IN ('PCT', 'AMT') THENRAISE_APPLICATION_ERROR(-20001, '无效的调整类型，必须是PCT或AMT');END IF;-- 打开游标并批量处理OPEN emp_cursor;LOOP-- 批量获取数据FETCH emp_cursor BULK COLLECT INTO v_emp_ids, v_old_salaries, v_emp_namesLIMIT p_batch_size;-- 退出条件EXIT WHEN v_emp_ids.COUNT = 0;v_batch_count := v_batch_count + 1;DBMS_OUTPUT.PUT_LINE('处理第 ' || v_batch_count || ' 批，记录数: ' || v_emp_ids.COUNT);-- 初始化新工资数组v_new_salaries := salary_array();v_new_salaries.EXTEND(v_emp_ids.COUNT);-- 计算新工资FOR i IN 1..v_emp_ids.COUNT LOOPIF p_adjustment_type = 'PCT' THENv_new_salaries(i) := ROUND(v_old_salaries(i) * (1 + p_adjustment_value / 100), 2);ELSE -- AMTv_new_salaries(i) := v_old_salaries(i) + p_adjustment_value;END IF;-- 确保工资不为负数IF v_new_salaries(i) < 0 THENv_new_salaries(i) := 0;END IF;v_total_adjustment := v_total_adjustment + (v_new_salaries(i) - v_old_salaries(i));END LOOP;-- 批量更新FORALL i IN 1..v_emp_ids.COUNTUPDATE employeesSET salary = v_new_salaries(i),last_updated = SYSDATEWHERE employee_id = v_emp_ids(i);-- 记录处理的员工信息FOR i IN 1..v_emp_ids.COUNT LOOPDBMS_OUTPUT.PUT_LINE('  ' || v_emp_names(i) || ': $' || v_old_salaries(i) || ' -> $' || v_new_salaries(i) || ' (变化: $' || (v_new_salaries(i) - v_old_salaries(i)) || ')');END LOOP;v_total_processed := v_total_processed + v_emp_ids.COUNT;-- 提交当前批次COMMIT;DBMS_OUTPUT.PUT_LINE('第 ' || v_batch_count || ' 批处理完成并提交');DBMS_OUTPUT.PUT_LINE('');END LOOP;CLOSE emp_cursor;-- 显示汇总信息DBMS_OUTPUT.PUT_LINE('=== 批量调整完成 ===');DBMS_OUTPUT.PUT_LINE('总处理员工数: ' || v_total_processed);DBMS_OUTPUT.PUT_LINE('总调整金额: $' || ROUND(v_total_adjustment, 2));DBMS_OUTPUT.PUT_LINE('处理批次数: ' || v_batch_count);IF v_total_processed > 0 THENDBMS_OUTPUT.PUT_LINE('平均调整: $' || ROUND(v_total_adjustment / v_total_processed, 2));END IF;EXCEPTIONWHEN OTHERS THENIF emp_cursor%ISOPEN THENCLOSE emp_cursor;END IF;ROLLBACK;DBMS_OUTPUT.PUT_LINE('批量处理失败: ' || SQLERRM);DBMS_OUTPUT.PUT_LINE('已回滚所有更改');
END bulk_salary_adjustment;
/-- 测试批量处理
EXEC bulk_salary_adjustment(60, 'PCT', 10, 5); -- IT部门加薪10%，每批5人
EXEC bulk_salary_adjustment(20, 'AMT', 500, 3); -- 市场部每人加薪$500，每批3人

4.2.2 错误处理的批量操作

-- 带错误处理的批量数据导入存储过程
CREATE OR REPLACE PROCEDURE bulk_import_employees(p_batch_size IN NUMBER DEFAULT 100
)
AS-- 集合类型定义TYPE emp_record_type IS RECORD (employee_id NUMBER,first_name VARCHAR2(50),last_name VARCHAR2(50),email VARCHAR2(100),hire_date DATE,job_id VARCHAR2(10),salary NUMBER,department_id NUMBER);TYPE emp_array_type IS TABLE OF emp_record_type;v_employees emp_array_type;-- 错误处理相关TYPE error_array_type IS TABLE OF NUMBER;v_error_indexes error_array_type;v_success_count NUMBER := 0;v_error_count NUMBER := 0;v_total_processed NUMBER := 0;-- 游标：从临时导入表读取数据CURSOR import_cursor ISSELECT employee_id, first_name, last_name, email, hire_date,job_id, salary, department_idFROM temp_employee_importWHERE processed_flag IS NULLORDER BY employee_id;BEGINDBMS_OUTPUT.PUT_LINE('=== 开始批量导入员工数据 ===');DBMS_OUTPUT.PUT_LINE('批次大小: ' || p_batch_size);DBMS_OUTPUT.PUT_LINE('');-- 创建临时导入表（如果不存在）BEGINEXECUTE IMMEDIATE 'CREATE TABLE temp_employee_import AS SELECT * FROM employees WHERE 1=0';EXECUTE IMMEDIATE 'ALTER TABLE temp_employee_import ADD processed_flag VARCHAR2(1)';EXECUTE IMMEDIATE 'ALTER TABLE temp_employee_import ADD error_message VARCHAR2(500)';DBMS_OUTPUT.PUT_LINE('创建临时导入表');EXCEPTIONWHEN OTHERS THENIF SQLCODE != -955 THEN -- 表已存在RAISE;END IF;END;OPEN import_cursor;LOOP-- 批量获取数据FETCH import_cursor BULK COLLECT INTO v_employees LIMIT p_batch_size;EXIT WHEN v_employees.COUNT = 0;DBMS_OUTPUT.PUT_LINE('处理批次，记录数: ' || v_employees.COUNT);-- 使用FORALL进行批量插入，并收集错误BEGINFORALL i IN 1..v_employees.COUNT SAVE EXCEPTIONSINSERT INTO employees (employee_id, first_name, last_name, email, hire_date,job_id, salary, department_id, last_updated) VALUES (v_employees(i).employee_id,v_employees(i).first_name,v_employees(i).last_name,v_employees(i).email,v_employees(i).hire_date,v_employees(i).job_id,v_employees(i).salary,v_employees(i).department_id,SYSDATE);-- 所有记录都成功插入v_success_count := v_success_count + v_employees.COUNT;-- 标记为已处理FORALL i IN 1..v_employees.COUNTUPDATE temp_employee_importSET processed_flag = 'S'WHERE employee_id = v_employees(i).employee_id;EXCEPTIONWHEN OTHERS THEN-- 处理批量操作中的错误IF SQLCODE = -24381 THEN -- FORALL with SAVE EXCEPTIONSv_error_count := v_error_count + SQL%BULK_EXCEPTIONS.COUNT;v_success_count := v_success_count + (v_employees.COUNT - SQL%BULK_EXCEPTIONS.COUNT);DBMS_OUTPUT.PUT_LINE('批次中有 ' || SQL%BULK_EXCEPTIONS.COUNT || ' 个错误');-- 处理每个错误FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOPDECLAREv_error_index NUMBER := SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;v_error_code NUMBER := SQL%BULK_EXCEPTIONS(i).ERROR_CODE;v_error_msg VARCHAR2(500) := SQLERRM(-v_error_code);BEGINDBMS_OUTPUT.PUT_LINE('  错误 ' || i || ': 员工ID ' || v_employees(v_error_index).employee_id ||' - ' || v_error_msg);-- 更新错误信息UPDATE temp_employee_importSET processed_flag = 'E',error_message = v_error_msgWHERE employee_id = v_employees(v_error_index).employee_id;END;END LOOP;-- 标记成功的记录FOR i IN 1..v_employees.COUNT LOOPDECLAREv_is_error BOOLEAN := FALSE;BEGIN-- 检查是否是错误记录FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOPIF SQL%BULK_EXCEPTIONS(j).ERROR_INDEX = i THENv_is_error := TRUE;EXIT;END IF;END LOOP;-- 如果不是错误记录，标记为成功IF NOT v_is_error THENUPDATE temp_employee_importSET processed_flag = 'S'WHERE employee_id = v_employees(i).employee_id;END IF;END;END LOOP;ELSE-- 其他错误RAISE;END IF;END;v_total_processed := v_total_processed + v_employees.COUNT;COMMIT;DBMS_OUTPUT.PUT_LINE('批次处理完成');DBMS_OUTPUT.PUT_LINE('');END LOOP;CLOSE import_cursor;-- 显示最终统计DBMS_OUTPUT.PUT_LINE('=== 导入完成 ===');DBMS_OUTPUT.PUT_LINE('总处理记录: ' || v_total_processed);DBMS_OUTPUT.PUT_LINE('成功导入: ' || v_success_count);DBMS_OUTPUT.PUT_LINE('失败记录: ' || v_error_count);DBMS_OUTPUT.PUT_LINE('成功率: ' || ROUND(v_success_count / v_total_processed * 100, 2) || '%');EXCEPTIONWHEN OTHERS THENIF import_cursor%ISOPEN THENCLOSE import_cursor;END IF;ROLLBACK;DBMS_OUTPUT.PUT_LINE('导入过程发生严重错误: ' || SQLERRM);
END bulk_import_employees;
/-- 创建错误报告存储过程
CREATE OR REPLACE PROCEDURE show_import_errors
AS
BEGINDBMS_OUTPUT.PUT_LINE('=== 导入错误报告 ===');FOR rec IN (SELECT employee_id, first_name, last_name, error_messageFROM temp_employee_importWHERE processed_flag = 'E'ORDER BY employee_id) LOOPDBMS_OUTPUT.PUT_LINE('员工ID: ' || rec.employee_id ||', 姓名: ' || rec.first_name || ' ' || rec.last_name ||', 错误: ' || rec.error_message);END LOOP;
END;
/

5. 存储过程的调试与优化

5.1 调试技术

5.1.1 使用DBMS_OUTPUT进行调试

-- 带调试信息的存储过程
CREATE OR REPLACE PROCEDURE debug_salary_calculation(p_employee_id IN NUMBER,p_debug_mode IN BOOLEAN DEFAULT FALSE
)
ASv_base_salary NUMBER;v_bonus_pct NUMBER;v_commission_pct NUMBER;v_total_compensation NUMBER;v_tax_rate NUMBER := 0.25; -- 25%税率v_net_salary NUMBER;PROCEDURE debug_print(p_message VARCHAR2) ISBEGINIF p_debug_mode THENDBMS_OUTPUT.PUT_LINE('[DEBUG] ' || TO_CHAR(SYSDATE, 'HH24:MI:SS') || ' - ' || p_message);END IF;END debug_print;BEGINdebug_print('开始计算员工 ' || p_employee_id || ' 的薪资');-- 获取基本工资SELECT salary, NVL(commission_pct, 0)INTO v_base_salary, v_commission_pctFROM employeesWHERE employee_id = p_employee_id;debug_print('基本工资: $' || v_base_salary);debug_print('佣金比例: ' || (v_commission_pct * 100) || '%');-- 计算奖金比例（基于工作年限）SELECT CASE WHEN MONTHS_BETWEEN(SYSDATE, hire_date) >= 60 THEN 0.15  -- 5年以上15%WHEN MONTHS_BETWEEN(SYSDATE, hire_date) >= 36 THEN 0.10  -- 3年以上10%WHEN MONTHS_BETWEEN(SYSDATE, hire_date) >= 12 THEN 0.05  -- 1年以上5%ELSE 0ENDINTO v_bonus_pctFROM employeesWHERE employee_id = p_employee_id;debug_print('奖金比例: ' || (v_bonus_pct * 100) || '%');-- 计算总薪酬v_total_compensation := v_base_salary * (1 + v_bonus_pct + v_commission_pct);debug_print('税前总薪酬: $' || ROUND(v_total_compensation, 2));-- 计算税后薪资v_net_salary := v_total_compensation * (1 - v_tax_rate);debug_print('税后薪资: $' || ROUND(v_net_salary, 2));debug_print('税额: $' || ROUND(v_total_compensation - v_net_salary, 2));-- 更新员工薪资信息（假设有扩展表）BEGINUPDATE employee_compensationSET base_salary = v_base_salary,bonus_amount = v_base_salary * v_bonus_pct,commission_amount = v_base_salary * v_commission_pct,gross_salary = v_total_compensation,tax_amount = v_total_compensation - v_net_salary,net_salary = v_net_salary,calculation_date = SYSDATEWHERE employee_id = p_employee_id;IF SQL%ROWCOUNT = 0 THENdebug_print('员工薪资记录不存在，插入新记录');INSERT INTO employee_compensation (employee_id, base_salary, bonus_amount, commission_amount,gross_salary, tax_amount, net_salary, calculation_date) VALUES (p_employee_id, v_base_salary, v_base_salary * v_bonus_pct,v_base_salary * v_commission_pct, v_total_compensation,v_total_compensation - v_net_salary, v_net_salary, SYSDATE);END IF;debug_print('薪资记录更新成功');EXCEPTIONWHEN OTHERS THENdebug_print('更新薪资记录时出错: ' || SQLERRM);-- 创建表（如果不存在）EXECUTE IMMEDIATE 'CREATE TABLE employee_compensation (employee_id NUMBER PRIMARY KEY,base_salary NUMBER,bonus_amount NUMBER,commission_amount NUMBER,gross_salary NUMBER,tax_amount NUMBER,net_salary NUMBER,calculation_date DATE)';debug_print('创建薪资表并重试');INSERT INTO employee_compensation (employee_id, base_salary, bonus_amount, commission_amount,gross_salary, tax_amount, net_salary, calculation_date) VALUES (p_employee_id, v_base_salary, v_base_salary * v_bonus_pct,v_base_salary * v_commission_pct, v_total_compensation,v_total_compensation - v_net_salary, v_net_salary, SYSDATE);END;COMMIT;debug_print('薪资计算完成');-- 输出结果DBMS_OUTPUT.PUT_LINE('=== 员工 ' || p_employee_id || ' 薪资计算结果 ===');DBMS_OUTPUT.PUT_LINE('基本工资: $' || v_base_salary);DBMS_OUTPUT.PUT_LINE('奖金: $' || ROUND(v_base_salary * v_bonus_pct, 2));DBMS_OUTPUT.PUT_LINE('佣金: $' || ROUND(v_base_salary * v_commission_pct, 2));DBMS_OUTPUT.PUT_LINE('税前总额: $' || ROUND(v_total_compensation, 2));DBMS_OUTPUT.PUT_LINE('税后薪资: $' || ROUND(v_net_salary, 2));EXCEPTIONWHEN NO_DATA_FOUND THENdebug_print('员工不存在: ' || p_employee_id);DBMS_OUTPUT.PUT_LINE('错误: 员工ID ' || p_employee_id || ' 不存在');WHEN OTHERS THENdebug_print('发生错误: ' || SQLERRM);ROLLBACK;DBMS_OUTPUT.PUT_LINE('计算失败: ' || SQLERRM);
END debug_salary_calculation;
/-- 测试调试功能
EXEC debug_salary_calculation(100, TRUE);  -- 开启调试模式
EXEC debug_salary_calculation(101, FALSE); -- 关闭调试模式

5.1.2 性能监控和分析

-- 性能监控存储过程
CREATE OR REPLACE PROCEDURE performance_test_procedure
ASv_start_time TIMESTAMP;v_end_time TIMESTAMP;v_elapsed_time INTERVAL DAY TO SECOND;v_cpu_time NUMBER;v_logical_reads NUMBER;v_physical_reads NUMBER;-- 获取会话统计信息FUNCTION get_session_stat(p_stat_name VARCHAR2) RETURN NUMBER ISv_value NUMBER;BEGINSELECT value INTO v_valueFROM v$mystat m, v$statname nWHERE m.statistic# = n.statistic#AND n.name = p_stat_name;RETURN v_value;EXCEPTIONWHEN NO_DATA_FOUND THENRETURN 0;END;v_start_cpu NUMBER;v_start_logical_reads NUMBER;v_start_physical_reads NUMBER;BEGINDBMS_OUTPUT.PUT_LINE('=== 性能测试开始 ===');-- 记录开始时间和统计信息v_start_time := SYSTIMESTAMP;v_start_cpu := get_session_stat('CPU used by this session');v_start_logical_reads := get_session_stat('session logical reads');v_start_physical_reads := get_session_stat('physical reads');-- 执行测试操作DBMS_OUTPUT.PUT_LINE('执行测试操作...');-- 模拟复杂查询FOR i IN 1..1000 LOOPDECLAREv_count NUMBER;v_avg_salary NUMBER;BEGINSELECT COUNT(*), AVG(salary)INTO v_count, v_avg_salaryFROM employees eJOIN departments d ON e.department_id = d.department_idWHERE e.salary > 5000;END;END LOOP;-- 记录结束时间和统计信息v_end_time := SYSTIMESTAMP;v_cpu_time := get_session_stat('CPU used by this session') - v_start_cpu;v_logical_reads := get_session_stat('session logical reads') - v_start_logical_reads;v_physical_reads := get_session_stat('physical reads') - v_start_physical_reads;-- 计算执行时间v_elapsed_time := v_end_time - v_start_time;-- 输出性能统计DBMS_OUTPUT.PUT_LINE('=== 性能统计结果 ===');DBMS_OUTPUT.PUT_LINE('执行时间: ' || EXTRACT(SECOND FROM v_elapsed_time) + EXTRACT(MINUTE FROM v_elapsed_time) * 60 + EXTRACT(HOUR FROM v_elapsed_time) * 3600 || ' 秒');DBMS_OUTPUT.PUT_LINE('CPU时间: ' || v_cpu_time || ' 厘秒');DBMS_OUTPUT.PUT_LINE('逻辑读: ' || v_logical_reads || ' 块');DBMS_OUTPUT.PUT_LINE('物理读: ' || v_physical_reads || ' 块');DBMS_OUTPUT.PUT_LINE('缓存命中率: ' || ROUND((v_logical_reads - v_physical_reads) / v_logical_reads * 100, 2) || '%');END performance_test_procedure;
/-- 执行性能测试
EXEC performance_test_procedure;

5.2 存储过程优化

5.2.1 SQL优化技巧

-- 优化前后对比的存储过程
CREATE OR REPLACE PROCEDURE optimized_employee_analysis(p_use_optimized IN BOOLEAN DEFAULT TRUE
)
ASv_start_time TIMESTAMP;v_end_time TIMESTAMP;v_count NUMBER;BEGINDBMS_OUTPUT.PUT_LINE('=== 员工分析性能对比 ===');IF p_use_optimized THENDBMS_OUTPUT.PUT_LINE('使用优化版本...');v_start_time := SYSTIMESTAMP;-- 优化版本：使用分析函数和单次查询FOR rec IN (SELECT department_name,employee_count,avg_salary,max_salary,min_salary,salary_rankFROM (SELECT d.department_name,COUNT(e.employee_id) OVER (PARTITION BY d.department_id) AS employee_count,ROUND(AVG(e.salary) OVER (PARTITION BY d.department_id), 2) AS avg_salary,MAX(e.salary) OVER (PARTITION BY d.department_id) AS max_salary,MIN(e.salary) OVER (PARTITION BY d.department_id) AS min_salary,RANK() OVER (ORDER BY AVG(e.salary) OVER (PARTITION BY d.department_id) DESC) AS salary_rank,ROW_NUMBER() OVER (PARTITION BY d.department_id ORDER BY e.employee_id) AS rnFROM departments dLEFT JOIN employees e ON d.department_id = e.department_id)WHERE rn = 1 AND employee_count > 0ORDER BY salary_rank) LOOPv_count := v_count + 1;END LOOP;ELSEDBMS_OUTPUT.PUT_LINE('使用未优化版本...');v_start_time := SYSTIMESTAMP;-- 未优化版本：多次查询FOR dept_rec IN (SELECT department_id, department_name FROM departments) LOOPDECLAREv_emp_count NUMBER;v_avg_salary NUMBER;v_max_salary NUMBER;v_min_salary NUMBER;BEGIN-- 每个部门都要执行一次查询SELECT COUNT(*), NVL(ROUND(AVG(salary), 2), 0),NVL(MAX(salary), 0),NVL(MIN(salary), 0)INTO v_emp_count, v_avg_salary, v_max_salary, v_min_salaryFROM employeesWHERE department_id = dept_rec.department_id;IF v_emp_count > 0 THENv_count := v_count + 1;END IF;END;END LOOP;END IF;v_end_time := SYSTIMESTAMP;DBMS_OUTPUT.PUT_LINE('处理部门数: ' || v_count);DBMS_OUTPUT.PUT_LINE('执行时间: ' || ROUND(EXTRACT(SECOND FROM (v_end_time - v_start_time)) * 1000, 2) || ' 毫秒');END optimized_employee_analysis;
/-- 性能对比测试
EXEC optimized_employee_analysis(FALSE); -- 未优化版本
EXEC optimized_employee_analysis(TRUE);  -- 优化版本

5.2.2 内存和资源优化

-- 资源优化的存储过程
CREATE OR REPLACE PROCEDURE resource_optimized_batch_process(p_table_name IN VARCHAR2,p_batch_size IN NUMBER DEFAULT 1000
)
ASTYPE id_array_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;v_ids id_array_type;v_sql VARCHAR2(4000);v_cursor_id INTEGER;v_rows_processed NUMBER;v_total_processed NUMBER := 0;v_batch_count NUMBER := 0;-- 使用REF CURSOR减少内存占用TYPE ref_cursor_type IS REF CURSOR;v_cursor ref_cursor_type;v_id NUMBER;BEGINDBMS_OUTPUT.PUT_LINE('=== 资源优化批处理 ===');DBMS_OUTPUT.PUT_LINE('表名: ' || p_table_name);DBMS_OUTPUT.PUT_LINE('批次大小: ' || p_batch_size);-- 构建查询SQLv_sql := 'SELECT id FROM ' || p_table_name || ' WHERE processed_flag IS NULL ORDER BY id';OPEN v_cursor FOR v_sql;LOOP-- 清空数组v_ids.DELETE;v_rows_processed := 0;-- 批量获取IDFOR i IN 1..p_batch_size LOOPFETCH v_cursor INTO v_id;EXIT WHEN v_cursor%NOTFOUND;v_ids(i) := v_id;v_rows_processed := v_rows_processed + 1;END LOOP;EXIT WHEN v_rows_processed = 0;v_batch_count := v_batch_count + 1;DBMS_OUTPUT.PUT_LINE('处理第 ' || v_batch_count || ' 批，记录数: ' || v_rows_processed);-- 批量处理BEGINFORALL i IN 1..v_rows_processedEXECUTE IMMEDIATE 'UPDATE ' || p_table_name || ' SET processed_flag = ''Y'', processed_date = SYSDATE WHERE id = :1'USING v_ids(i);v_total_processed := v_total_processed + v_rows_processed;-- 定期提交以释放锁和日志空间COMMIT;-- 可选：在批次间暂停以减少系统负载IF MOD(v_batch_count, 10) = 0 THENDBMS_OUTPUT.PUT_LINE('已处理 ' || v_batch_count || ' 批，暂停1秒...');DBMS_LOCK.SLEEP(1);END IF;EXCEPTIONWHEN OTHERS THENROLLBACK;DBMS_OUTPUT.PUT_LINE('批次 ' || v_batch_count || ' 处理失败: ' || SQLERRM);-- 继续处理下一批END;END LOOP;CLOSE v_cursor;DBMS_OUTPUT.PUT_LINE('=== 处理完成 ===');DBMS_OUTPUT.PUT_LINE('总处理记录: ' || v_total_processed);DBMS_OUTPUT.PUT_LINE('处理批次: ' || v_batch_count);EXCEPTIONWHEN OTHERS THENIF v_cursor%ISOPEN THENCLOSE v_cursor;END IF;ROLLBACK;DBMS_OUTPUT.PUT_LINE('处理过程发生错误: ' || SQLERRM);
END resource_optimized_batch_process;
/

6. 存储过程的安全性

6.1 权限管理

6.1.1 存储过程权限控制

-- 创建带权限检查的存储过程
CREATE OR REPLACE PROCEDURE secure_salary_update(p_employee_id IN NUMBER,p_new_salary IN NUMBER,p_reason IN VARCHAR2
)
ASv_current_user VARCHAR2(30);v_user_role VARCHAR2(30);v_current_salary NUMBER;v_max_allowed_salary NUMBER;v_is_authorized BOOLEAN := FALSE;-- 权限检查函数FUNCTION check_user_permission(p_operation VARCHAR2) RETURN BOOLEAN ISv_count NUMBER;BEGIN-- 检查用户是否有特定权限SELECT COUNT(*)INTO v_countFROM user_role_privsWHERE granted_role IN ('HR_MANAGER', 'SALARY_ADMIN')OR granted_role = 'DBA';RETURN v_count > 0;END;-- 审计日志记录PROCEDURE log_salary_change(p_emp_id NUMBER,p_old_salary NUMBER,p_new_salary NUMBER,p_changed_by VARCHAR2,p_reason VARCHAR2,p_status VARCHAR2) ISPRAGMA AUTONOMOUS_TRANSACTION;BEGININSERT INTO salary_audit_log (log_id, employee_id, old_salary, new_salary,changed_by, change_reason, change_status,change_date) VALUES (salary_audit_seq.NEXTVAL, p_emp_id, p_old_salary, p_new_salary,p_changed_by, p_reason, p_status, SYSDATE);COMMIT;END;BEGIN-- 获取当前用户信息v_current_user := USER;DBMS_OUTPUT.PUT_LINE('=== 安全工资更新 ===');DBMS_OUTPUT.PUT_LINE('操作用户: ' || v_current_user);DBMS_OUTPUT.PUT_LINE('员工ID: ' || p_employee_id);DBMS_OUTPUT.PUT_LINE('新工资: $' || p_new_salary);-- 权限检查IF NOT check_user_permission('SALARY_UPDATE') THENlog_salary_change(p_employee_id, NULL, p_new_salary, v_current_user, p_reason, 'PERMISSION_DENIED');RAISE_APPLICATION_ERROR(-20001, '权限不足：用户无工资更新权限');END IF;-- 获取当前工资SELECT salary INTO v_current_salaryFROM employeesWHERE employee_id = p_employee_id;-- 业务规则检查IF p_new_salary <= 0 THENlog_salary_change(p_employee_id, v_current_salary, p_new_salary, v_current_user, p_reason, 'INVALID_AMOUNT');RAISE_APPLICATION_ERROR(-20002, '工资金额必须大于0');END IF;-- 检查工资增长限制（不能超过50%）IF p_new_salary > v_current_salary * 1.5 THENlog_salary_change(p_employee_id, v_current_salary, p_new_salary, v_current_user, p_reason, 'EXCESSIVE_INCREASE');RAISE_APPLICATION_ERROR(-20003, '工资增长不能超过50%');END IF;-- 检查职位工资上限SELECT j.max_salary INTO v_max_allowed_salaryFROM employees eJOIN jobs j ON e.job_id = j.job_idWHERE e.employee_id = p_employee_id;IF p_new_salary > v_max_allowed_salary THENlog_salary_change(p_employee_id, v_current_salary, p_new_salary, v_current_user, p_reason, 'EXCEEDS_JOB_LIMIT');RAISE_APPLICATION_ERROR(-20004, '工资超过职位上限: $' || v_max_allowed_salary);END IF;-- 执行更新UPDATE employeesSET salary = p_new_salary,last_updated = SYSDATEWHERE employee_id = p_employee_id;-- 记录成功的更改log_salary_change(p_employee_id, v_current_salary, p_new_salary, v_current_user, p_reason, 'SUCCESS');COMMIT;DBMS_OUTPUT.PUT_LINE('工资更新成功');DBMS_OUTPUT.PUT_LINE('原工资: $' || v_current_salary);DBMS_OUTPUT.PUT_LINE('新工资: $' || p_new_salary);DBMS_OUTPUT.PUT_LINE('变化: $' || (p_new_salary - v_current_salary));EXCEPTIONWHEN NO_DATA_FOUND THENlog_salary_change(p_employee_id, NULL, p_new_salary, v_current_user, p_reason, 'EMPLOYEE_NOT_FOUND');DBMS_OUTPUT.PUT_LINE('错误: 员工不存在');WHEN OTHERS THENROLLBACK;DBMS_OUTPUT.PUT_LINE('更新失败: ' || SQLERRM);
END secure_salary_update;
/-- 创建审计日志表
CREATE TABLE salary_audit_log (log_id NUMBER PRIMARY KEY,employee_id NUMBER,old_salary NUMBER,new_salary NUMBER,changed_by VARCHAR2(30),change_reason VARCHAR2(500),change_status VARCHAR2(50),change_date DATE
);CREATE SEQUENCE salary_audit_seq START WITH 1 INCREMENT BY 1;-- 创建审计查询存储过程
CREATE OR REPLACE PROCEDURE show_salary_audit(p_employee_id IN NUMBER DEFAULT NULL,p_days_back IN NUMBER DEFAULT 30
)
AS
BEGINDBMS_OUTPUT.PUT_LINE('=== 工资变更审计报告 ===');DBMS_OUTPUT.PUT_LINE('查询范围: 最近 ' || p_days_back || ' 天');IF p_employee_id IS NOT NULL THENDBMS_OUTPUT.PUT_LINE('员工ID: ' || p_employee_id);END IF;DBMS_OUTPUT.PUT_LINE('');FOR rec IN (SELECT l.log_id, l.employee_id, e.first_name || ' ' || e.last_name AS employee_name,l.old_salary, l.new_salary, l.changed_by, l.change_reason,l.change_status, l.change_dateFROM salary_audit_log lLEFT JOIN employees e ON l.employee_id = e.employee_idWHERE (p_employee_id IS NULL OR l.employee_id = p_employee_id)AND l.change_date >= SYSDATE - p_days_backORDER BY l.change_date DESC) LOOPDBMS_OUTPUT.PUT_LINE('记录ID: ' || rec.log_id);DBMS_OUTPUT.PUT_LINE('员工: ' || NVL(rec.employee_name, '未知') || ' (ID: ' || rec.employee_id || ')');DBMS_OUTPUT.PUT_LINE('工资变化: $' || NVL(rec.old_salary, 0) || ' -> $' || rec.new_salary);DBMS_OUTPUT.PUT_LINE('操作人: ' || rec.changed_by);DBMS_OUTPUT.PUT_LINE('状态: ' || rec.change_status);DBMS_OUTPUT.PUT_LINE('时间: ' || TO_CHAR(rec.change_date, 'YYYY-MM-DD HH24:MI:SS'));DBMS_OUTPUT.PUT_LINE('原因: ' || NVL(rec.change_reason, '未提供'));DBMS_OUTPUT.PUT_LINE('---');END LOOP;
END show_salary_audit;
/-- 测试安全存储过程
EXEC secure_salary_update(100, 25000, '年度调薪');
EXEC show_salary_audit(100, 7); -- 查看员工100最近7天的工资变更

结语
感谢您的阅读！期待您的一键三连！欢迎指正！