写一个较长的oracle存储过程sql带注释

<think>好的，用户让我写一个较长的Oracle存储过程SQL，并且要带注释。首先，我需要理解用户的需求。他们可能正在学习Oracle存储过程，或者在工作中需要编写一个复杂的例子，用来参考或者教学。用户提到“较长的”，所以这个存储过程需要包含多个部分，展示不同的功能，比如变量声明、游标使用、控制结构、异常处理等。 接下来，我要考虑用户可能的背景。可能是开发人员，或者数据库管理员，想要一个详尽的例子来理解存储过程的结构和语法。注释是必须的，所以需要每个部分都有清晰的解释，说明代码的作用和逻辑。 然后，我需要确定存储过程的主题。常见的例子包括数据迁移、报表生成、数据清理等。选择工资计算和年终奖发放作为例子，这样能涵盖较多的业务逻辑，比如计算奖金、插入日志、更新状态等，适合展示多个功能点。 现在，结构方面，存储过程通常包括声明部分、执行部分、异常处理。需要展示变量声明、游标的使用（显式和隐式）、条件判断（IF语句）、循环（LOOP），以及事务控制（COMMIT和ROLLBACK）。同时，异常处理部分要包括自定义异常和系统异常的处理。 注释方面，每个主要部分都要有注释，解释代码的作用。比如在声明变量时，说明每个变量的用途；在游标部分，说明查询的逻辑；在循环中，说明如何处理每条记录；在异常处理中，说明不同异常的处理方式。 需要注意的细节包括使用显式游标和隐式游标的区别，事务管理的正确位置（在适当的地方提交或回滚），以及日志记录的做法，比如插入日志表来跟踪操作历史。 还要考虑代码的规范性，比如缩进、命名规则，以及示例数据的合理性。比如使用员工表和日志表的结构，确保示例能够运行，尽管用户可能需要根据自己的表结构调整。 另外，用户可能希望看到一些高级功能，比如自治事务的日志记录，这样即使在主事务回滚时，日志仍然保留。或者使用动态SQL来展示灵活性。但为了不过于复杂，可能保持示例相对基础，但包含足够的细节展示关键点。 最后，确保代码没有语法错误，符合Oracle的语法规范，比如使用END LOOP标记循环结束，正确声明EXCEPTION部分等。同时，注释要清晰，不使用专业术语过多，让用户容易理解每一步的操作目的。</think>以下是一个包含完整业务逻辑、异常处理和详细注释的Oracle存储过程示例，演示了员工年终奖计算和发放流程： ```sql CREATE OR REPLACE PROCEDURE proc_calculate_bonus ( p_year IN NUMBER, -- 统计年度 p_dept_id IN NUMBER, -- 部门编号（可选） p_bonus_ratio IN NUMBER -- 奖金系数（0.1=10%） ) IS -- 声明显式游标：获取符合条件员工 CURSOR cur_emp IS SELECT e.emp_id, e.salary, NVL(SUM(o.order_amount), 0) total_sales, COUNT(a.attend_id) work_days FROM employees e LEFT JOIN sales_orders o ON e.emp_id = o.sales_emp_id AND EXTRACT(YEAR FROM o.order_date) = p_year LEFT JOIN attendance a ON e.emp_id = a.emp_id AND a.attendance_status = 'PRESENT' AND EXTRACT(YEAR FROM a.work_date) = p_year WHERE e.status = 'ACTIVE' AND (e.dept_id = p_dept_id OR p_dept_id IS NULL) GROUP BY e.emp_id, e.salary; -- 变量声明 v_bonus_amount NUMBER(10,2); -- 奖金金额 v_total_issued NUMBER(10,2) := 0; -- 总发放金额 v_error_message VARCHAR2(4000); -- 错误信息 v_processed_count NUMBER := 0; -- 处理人数 -- 自定义异常 invalid_ratio EXCEPTION; BEGIN -- 参数校验 IF p_bonus_ratio <= 0 OR p_bonus_ratio > 1 THEN RAISE invalid_ratio; END IF; -- 记录处理开始日志（自治事务） INSERT INTO proc_logs(job_name, start_time, status) VALUES ('BONUS_CALCULATION', SYSTIMESTAMP, 'STARTING') COMMIT; -- 打开游标 OPEN cur_emp; -- 循环处理每个员工 LOOP FETCH cur_emp INTO v_emp_id, v_salary, v_total_sales, v_work_days; EXIT WHEN cur_emp%NOTFOUND; BEGIN -- 奖金计算逻辑 v_bonus_amount := v_salary * p_bonus_ratio; -- 基础奖金 -- 销售提成（销售人员且销售额>10万） IF v_total_sales > 100000 THEN v_bonus_amount := v_bonus_amount + (v_total_sales * 0.05); END IF; -- 全勤奖（年工作天数>=250） IF v_work_days >= 250 THEN v_bonus_amount := v_bonus_amount + 2000; END IF; -- 更新员工奖金（带行级锁） UPDATE employees SET bonus = v_bonus_amount, last_updated = SYSTIMESTAMP WHERE emp_id = v_emp_id AND status = 'ACTIVE' FOR UPDATE; -- 防止并发修改 -- 插入奖金发放记录 INSERT INTO bonus_records (emp_id, calc_year, amount, issue_date) VALUES (v_emp_id, p_year, v_bonus_amount, SYSDATE); v_total_issued := v_total_issued + v_bonus_amount; v_processed_count := v_processed_count + 1; -- 每处理100人提交一次 IF MOD(v_processed_count, 100) = 0 THEN COMMIT; END IF; EXCEPTION WHEN OTHERS THEN v_error_message := '员工ID:' || v_emp_id || ' 错误:' || SQLERRM; -- 记录错误日志（自治事务） INSERT INTO error_logs (proc_name, error_msg, error_time) VALUES ('PROC_CALCULATE_BONUS', v_error_message, SYSTIMESTAMP); COMMIT; END; END LOOP; CLOSE cur_emp; -- 最终提交 COMMIT; -- 更新处理日志状态 UPDATE proc_logs SET end_time = SYSTIMESTAMP, status = 'COMPLETED', memo = '处理人数：' || v_processed_count || ' 总金额：' || v_total_issued WHERE job_name = 'BONUS_CALCULATION' AND start_time = (SELECT MAX(start_time) FROM proc_logs WHERE job_name = 'BONUS_CALCULATION'); EXCEPTION WHEN invalid_ratio THEN ROLLBACK; RAISE_APPLICATION_ERROR(-20001, '奖金系数必须在0-1之间'); WHEN NO_DATA_FOUND THEN ROLLBACK; RAISE_APPLICATION_ERROR(-20002, '未找到有效员工数据'); WHEN OTHERS THEN ROLLBACK; -- 记录未处理异常日志 INSERT INTO error_logs (proc_name, error_msg, error_time) VALUES ('PROC_CALCULATE_BONUS', SQLERRM, SYSTIMESTAMP); COMMIT; RAISE; END proc_calculate_bonus; / ``` ### **关键功能注释说明** 1. **参数处理** - 支持部门筛选（`p_dept_id`可为空） - 严格校验奖金系数范围（0-1之间） 2. **游标设计** - 使用显式游标`cur_emp`获取活跃员工 - 关联考勤、销售数据计算工作天数和销售额 - `NVL`函数处理空值 3. **事务控制** - 每处理100人提交一次（平衡性能与数据安全） - 最终提交保证数据完整性 - 异常时明确回滚范围 4. **锁机制** - `FOR UPDATE`子句防止并发修改 - 确保奖金更新操作的原子性 5. **日志记录** - 使用自治事务记录处理开始/结束状态 - 详细错误日志包含员工ID和错误信息 - 最终更新处理统计信息 6. **异常处理** - 自定义异常`invalid_ratio` - 捕获`NO_DATA_FOUND`等系统异常 - `WHEN OTHERS`兜底处理未知错误 7. **业务逻辑** - 基础奖金 = 月薪 × 系数 - 销售提成：销售额的5%（需超10万门槛） - 全勤奖：2000元（年出勤≥250天） ### **配套表结构建议** ```sql CREATE TABLE employees ( emp_id NUMBER PRIMARY KEY, salary NUMBER(10,2), dept_id NUMBER, status VARCHAR2(10) CHECK(status IN ('ACTIVE','INACTIVE')), bonus NUMBER(10,2), last_updated DATE ); CREATE TABLE bonus_records ( record_id NUMBER GENERATED ALWAYS AS IDENTITY, emp_id NUMBER, calc_year NUMBER, amount NUMBER(10,2), issue_date DATE ); CREATE TABLE proc_logs ( log_id NUMBER GENERATED ALWAYS AS IDENTITY, job_name VARCHAR2(50), start_time TIMESTAMP, end_time TIMESTAMP, status VARCHAR2(20), memo VARCHAR2(4000) ); ``` ### **调用示例** ```sql -- 执行全公司奖金计算（系数15%） BEGIN proc_calculate_bonus(p_year => 2023, p_dept_id => NULL, p_bonus_ratio => 0.15); END; -- 查看处理结果 SELECT * FROM proc_logs WHERE job_name = 'BONUS_CALCULATION'; ``` ### **优化方向** 1. 增加参数`p_batch_size`控制批量提交量 2. 使用`BULK COLLECT`和`FORALL`进行批量处理 3. 添加索引优化关联查询性能（如`employees.status`） 4. 实现断点续做功能（记录最后处理的emp_id）