存储过程与函数

CREATE OR REPLACE PROCEDURE raise_salary (
  p_dept_id  IN     NUMBER,
  p_percent  IN     NUMBER,
  p_count    OUT    NUMBER,
  p_total    OUT    NUMBER
) IS
BEGIN
  IF p_percent <= 0 OR p_percent > 0.5 THEN
    RAISE_APPLICATION_ERROR(-20001, '调薪比例必须在 0 到 0.5 之间');
  END IF;

  UPDATE employees
  SET    salary = salary * (1 + p_percent)
  WHERE  department_id = p_dept_id;

  p_count := SQL%ROWCOUNT;

  SELECT SUM(salary) INTO p_total
  FROM   employees
  WHERE  department_id = p_dept_id;
END raise_salary;
/

只作示例阅读：5 行写入限制下只开放单独创建普通空表；CTAS、删表、清空表、改结构、触发器和存储程序不允许执行。请在独立学习数据库中练习。

-- 方式一：PL/SQL 块
DECLARE
  v_cnt NUMBER;
  v_sum NUMBER;
BEGIN
  raise_salary(60, 0.05, v_cnt, v_sum);
  DBMS_OUTPUT.PUT_LINE('调整 ' || v_cnt || ' 人，调整后部门工资总额 ' || v_sum);
  ROLLBACK;
END;
/

-- 方式二：SQL*Plus 绑定变量
VARIABLE b_cnt NUMBER
VARIABLE b_sum NUMBER
EXEC raise_salary(60, 0.05, :b_cnt, :b_sum)
PRINT b_cnt b_sum
ROLLBACK

CREATE OR REPLACE FUNCTION years_of_service (
  p_hire_date IN DATE
) RETURN NUMBER IS
  v_years NUMBER;
BEGIN
  IF p_hire_date IS NULL THEN
    RETURN NULL;
  END IF;

  v_years := TRUNC(MONTHS_BETWEEN(SYSDATE, p_hire_date) / 12);
  RETURN v_years;
END years_of_service;
/

只作示例阅读：5 行写入限制下只开放单独创建普通空表；CTAS、删表、清空表、改结构、触发器和存储程序不允许执行。请在独立学习数据库中练习。

SELECT employee_id,
       last_name,
       hire_date,
       years_of_service(hire_date) AS years
FROM   employees
WHERE  years_of_service(hire_date) >= 15
ORDER  BY years DESC;

注意：在 WHERE 里对列调用自定义函数会让该列上的索引失效（除非建函数索引）。数据量大时慎用。

只作示例阅读：受限模式不允许调用「YEARS_OF_SERVICE」：用户函数或程序可能在查询中写入数据。请在独立学习数据库中练习。

-- 包规范：对外接口
CREATE OR REPLACE PACKAGE pkg_emp AS
  g_min_salary CONSTANT NUMBER := 3000;       -- 公共常量

  FUNCTION  get_avg_salary (p_dept_id IN NUMBER) RETURN NUMBER;
  PROCEDURE adjust_salary  (p_dept_id IN NUMBER, p_percent IN NUMBER);
END pkg_emp;
/

-- 包体：具体实现
CREATE OR REPLACE PACKAGE BODY pkg_emp AS

  FUNCTION get_avg_salary (p_dept_id IN NUMBER) RETURN NUMBER IS
    v_avg NUMBER;
  BEGIN
    SELECT ROUND(AVG(salary), 2) INTO v_avg
    FROM   employees WHERE department_id = p_dept_id;
    RETURN NVL(v_avg, 0);
  END get_avg_salary;

  PROCEDURE adjust_salary (p_dept_id IN NUMBER, p_percent IN NUMBER) IS
  BEGIN
    UPDATE employees
    SET    salary = GREATEST(salary * (1 + p_percent), g_min_salary)
    WHERE  department_id = p_dept_id;
  END adjust_salary;

END pkg_emp;
/

-- 调用
SELECT pkg_emp.get_avg_salary(60) FROM dual;

只作示例阅读：5 行写入限制下只开放单独创建普通空表；CTAS、删表、清空表、改结构、触发器和存储程序不允许执行。请在独立学习数据库中练习。