过程函数

第12章 存储过程与函数

1. 什么是存储过程？存储过程和 PL/SQL 有什么区别？

存储过程（Stored Procedure）是一组预编译的 SQL 语句和 PL/SQL 代码，存储在数据库中，可以被多次调用。存储过程和 PL/SQL 的区别如下：

• PL/SQL：是一种过程化编程语言，用于编写存储过程、函数、触发器等。
• 存储过程：是 PL/SQL 的一种应用，用于封装复杂的业务逻辑，可以被其他程序或 SQL 语句调用。

2. 编写一个存储过程，输出 emp 表中各种工作的平均工资。

CREATE OR REPLACE PROCEDURE pro_avg_sal_by_job AS
    CURSOR cur IS SELECT job, AVG(sal) AS avg_sal FROM emp GROUP BY job;
BEGIN
    FOR i IN cur LOOP
        DBMS_OUTPUT.PUT_LINE(i.job || ' --- ' || i.avg_sal);
    END LOOP;
END;
/

3. 编写一个存储过程，根据输入的工作类型，输出该工作的平均工资。

CREATE OR REPLACE PROCEDURE pro_avg_sal_by_job(p_job VARCHAR2) AS
    v_avg_sal NUMBER;
BEGIN
    SELECT AVG(sal) INTO v_avg_sal FROM emp WHERE job = p_job GROUP BY job;
    DBMS_OUTPUT.PUT_LINE(p_job || ' --- ' || v_avg_sal);
END;
/

-- 调用存储过程
BEGIN
    pro_avg_sal_by_job('MANAGER');
END;
/

4. 什么是函数？函数和存储过程有什么区别？

函数（Function）是一段可重用的代码，用于执行特定任务并返回一个值。函数和存储过程的区别如下：

• 存储过程：可以执行多个操作，不一定要返回值。
• 函数：必须返回一个值，通常用于计算或转换数据。

5. 定义一个函数，根据员工编号返回员工姓名。

CREATE OR REPLACE FUNCTION fun_get_emp_name(p_empno NUMBER) RETURN VARCHAR2 AS
    v_ename VARCHAR2(10);
BEGIN
    SELECT ename INTO v_ename FROM emp WHERE empno = p_empno;
    RETURN v_ename;
END;
/

-- 调用函数
SELECT fun_get_emp_name(7788) FROM dual;

6. 给工资低于 1200 的员工增加工资 300。打印输出增加工资的员工编号，并显示一共给多少员工增加了工资。

CREATE OR REPLACE PROCEDURE pro_increase_sal AS
    CURSOR cur IS SELECT empno FROM emp WHERE sal < 1200;
    v_count NUMBER := 0;
BEGIN
    FOR i IN cur LOOP
        UPDATE emp SET sal = sal + 300 WHERE empno = i.empno;
        DBMS_OUTPUT.PUT_LINE('员工编号: ' || i.empno || ' 已加薪');
        v_count := v_count + 1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('共给 ' || v_count || ' 名员工加薪');
    COMMIT;
END;
/

7. 从 emp 表中查询某部门的员工情况（输入部门编号），将其工资最低定为 1500。

CREATE OR REPLACE PROCEDURE pro_set_min_sal(p_deptno NUMBER) AS
BEGIN
    UPDATE emp SET sal = 1500 WHERE sal < 1500 AND deptno = p_deptno;
    COMMIT;
END;
/

8. 建立本地过程，用于计算指定部门的工资总和，并统计其中的职工数量（打印）。

CREATE OR REPLACE PROCEDURE pro_dept_sal_sum(p_deptno NUMBER) AS
    v_total_sal NUMBER;
    v_emp_count NUMBER;
BEGIN
    SELECT SUM(sal), COUNT(empno) INTO v_total_sal, v_emp_count
    FROM emp WHERE deptno = p_deptno;
    DBMS_OUTPUT.PUT_LINE('部门编号: ' || p_deptno || ' 工资总和: ' || v_total_sal || ' 员工数量: ' || v_emp_count);
END;
/

9. 创建存储过程 p_employee，输入员工薪水范围，返回员工工号、姓名、薪水结果集，结果集按员工薪水升序排列。

CREATE OR REPLACE PROCEDURE pro_emp_by_sal_range(p_min_sal NUMBER, p_max_sal NUMBER) AS
    CURSOR cur IS SELECT empno, ename, sal FROM emp WHERE sal BETWEEN p_min_sal AND p_max_sal ORDER BY sal;
BEGIN
    FOR i IN cur LOOP
        DBMS_OUTPUT.PUT_LINE('工号: ' || i.empno || ' 姓名: ' || i.ename || ' 薪水: ' || i.sal);
    END LOOP;
END;
/

10. 使用存储过程，对部门表中部门编号为 10、30 的所有员工，更新奖金：

• 对奖金为 0 或空的员工，奖金变为工资的 40%。
• 对奖金不为空的员工，奖金增加 10%（原奖金的 1.1 倍）。
• 同时记录奖金变更的时间、原奖金、新奖金、员工编号、员工姓名。

-- 创建备份表
CREATE TABLE bak (
    bgdate   DATE,
    old_comm NUMBER,
    new_comm NUMBER,
    empno    NUMBER,
    name     VARCHAR2(10)
);

-- 创建存储过程
CREATE OR REPLACE PROCEDURE pro_update_comm AS
BEGIN
    -- 更新奖金
    UPDATE emp SET comm = comm * 1.1 WHERE comm > 0 AND deptno IN (10, 30);
    UPDATE emp SET comm = sal * 0.4 + NVL(comm, 0) WHERE (comm IS NULL OR comm = 0) AND deptno IN (10, 30);
    COMMIT;
END;
/

-- 创建触发器
CREATE OR REPLACE TRIGGER tri_comm_change
AFTER UPDATE ON emp
FOR EACH ROW
BEGIN
    IF :OLD.comm != :NEW.comm THEN
        INSERT INTO bak VALUES (SYSDATE, :OLD.comm, :NEW.comm, :OLD.empno, :OLD.ename);
    END IF;
END;
/

11. 创建函数 f_employee，实现更新员工薪水的功能：

• 将薪水低于 5000 且姓孙的员工薪水加 5%。
• 其他员工薪水不变。
• 更新成功则返回 0，否则返回 1。

CREATE OR REPLACE FUNCTION fun_update_sal RETURN NUMBER AS
    v_count NUMBER := 0;
BEGIN
    -- 更新薪水
    UPDATE emp SET sal = sal * 1.05 WHERE sal < 5000 AND ename LIKE '孙%';
    v_count := SQL%ROWCOUNT;  -- 获取受影响的行数

    -- 判断是否更新成功
    IF v_count > 0 THEN
        RETURN 0;  -- 更新成功
    ELSE
        RETURN 1;  -- 更新失败
    END IF;
END;
/

-- 调用函数
DECLARE
    v_result NUMBER;
BEGIN
    v_result := fun_update_sal();
    DBMS_OUTPUT.PUT_LINE('更新结果: ' || v_result);
END;
/