DELIMITER $$
CREATE OR REPLACE PROCEDURE CHECKANDUPDATESALARIES(p_dept_id IN NUMBER, p_result OUT CLOB, p_percent IN NUMBER, p_min_dept IN NUMBER DEFAULT NULL, p_max_dept IN NUMBER DEFAULT NULL)
IS
  TYPE stats_rec IS RECORD (
    total_emp NUMBER,
    avg_salary NUMBER,
    max_salary NUMBER
  );
  TYPE p_result_rec IS RECORD (
    employee_id NUMBER,
    last_name VARCHAR2(50),
    salary NUMBER(8,2)
  );
  v_stats stats_rec;
  v_result p_result_rec;
  v_cursor SYS_REFCURSOR;
  v_sql VARCHAR2(1000);
  v_dept_name VARCHAR2(50);
  v_rows_updated NUMBER := 0;
  v_total_budget NUMBER := 0;
  CURSOR dept_cur IS
    SELECT department_id, budget FROM DEPARTMENTS
    WHERE (department_id >= p_min_dept OR p_min_dept IS NULL)
      AND (department_id <= p_max_dept OR p_max_dept IS NULL);
BEGIN
  -- 部门验证
  SELECT department_name INTO v_dept_name FROM DEPARTMENTS WHERE department_id = p_dept_id;

  -- 基础统计
  SELECT COUNT(*), AVG(salary), MAX(salary)
  INTO v_stats.total_emp, v_stats.avg_salary, v_stats.max_salary
  FROM EMPLOYEES WHERE department_id = p_dept_id;

  -- 动态SQL生成
  v_sql := 'SELECT * FROM (SELECT employee_id, last_name, salary FROM employees '
        || 'WHERE department_id = :1 ORDER BY salary DESC) WHERE ROWNUM <= 5';

  OPEN v_cursor FOR v_sql USING p_dept_id;

  -- 构建JSON输出
  p_result := '{
  "department": "' || v_dept_name || '",
  "total_employees": ' || v_stats.total_emp || ',
  "salary_stats": {
    "average": ' || v_stats.avg_salary || ',
    "max": ' || v_stats.max_salary || '
  },
  "top_earners": [';

  LOOP
    FETCH v_cursor INTO v_result;
    EXIT WHEN v_cursor%NOTFOUND;
    p_result := p_result || '
    {"id": ' || v_result.employee_id || ', "name": "' || v_result.last_name || '", "salary": ' || v_result.salary || '},';
  END LOOP;

  p_result := RTRIM(p_result, ',') || '
  ]
}';

  CLOSE v_cursor;

  FOR dept_rec IN dept_cur LOOP
    -- 薪资更新处理
    UPDATE EMPLOYEES
    SET salary = NVL(salary, 3000) * (1 + p_percent/100)
    WHERE department_id = dept_rec.department_id;

    v_rows_updated := v_rows_updated + SQL%ROWCOUNT;

    -- 预算验证
    SELECT SUM(salary) INTO v_total_budget
    FROM EMPLOYEES WHERE department_id = dept_rec.department_id;

    IF v_total_budget > dept_rec.budget THEN
      RAISE_APPLICATION_ERROR(-20001, 'Budget exceeded in department ' || dept_rec.department_id);
    END IF;
  END LOOP;

  -- 输出处理结果
  DBMS_OUTPUT.PUT_LINE('Updated ' || v_rows_updated || ' records, Time ' || SYSDATE);

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    p_result := '{"error": "Department not found"}';
  WHEN OTHERS THEN
    p_result := '{"error": "' || SQLERRM || '"}';
END;
$$