数据库实验-PL/SQL语言实践

第二部分：PL/SQL语言实践

（一）实验目的

1、熟练掌握存储过程的设计和使用方法，能够熟练使用PL/SQL语句定义存储过程。

2、熟练掌握触发器的设计和使用方法，能够熟练使用PL/SQL语句定义触发器。

（二）实验内容

1、给定部门号（键盘输入），输出该部门每个员工的基本信息及参与项目信息。

2、显示最高工资的职工所在的部门名称和工资，并输出该部门中职工的信息。

3、在表department上创建一个触发器deaprt_update，当更改部门号时同步更改employee表中对应的部门号。

4、在表employee上创建一个触发器employee_delete，当删除职工元组同步删除对应职工的参与项目元组。

5、验证以上两个触发器。

给定部门号（键盘输入），输出该部门每个员工的基本信息及参与项目信息。

存储过程：
DELIMITER //

CREATE PROCEDURE GetEmployeeByDepartment(IN d_number CHAR(3))
BEGIN
    SELECT e.ssn, e.name, e.address, p.pname
    FROM employee e
    JOIN works_on w ON e.ssn = w.essn
    JOIN project p ON w.pno = p.pnumber
    WHERE e.dno = d_number;
END //

DELIMITER ;
调用存储过程：
CALL GetEmployeeByDepartment('d1');

显示最高工资的职工所在的部门名称和工资，并输出该部门中职工的信息。
DROP PROCEDURE IF EXISTS GetTopSalaryEmployee;
DELIMITER //

CREATE PROCEDURE GetTopSalaryEmployee()
BEGIN
    #DECLARE声明局部变量top_salary、top_dno和top_department_name。
    DECLARE top_salary FLOAT;
    DECLARE top_dno CHAR(3);
    DECLARE top_department_name VARCHAR(30);

    -- 找出最高工资和对应的部门号
    SELECT MAX(salary), dno INTO top_salary, top_dno
    FROM employee;

    -- 根据部门号找出部门名称
    SELECT dname INTO top_department_name
    FROM department
    WHERE dnumber = top_dno;

    -- 输出最高工资的职工所在的部门名称和工资
    SELECT CONCAT('The top salary is ', top_salary, ' in department ', top_department_name) AS 'Top Salary Information';

    -- 输出该部门中职工的信息
    SELECT e.ssn, e.name, e.address, e.sex, e.salary
    FROM employee e
    JOIN department d ON e.dno = d.dnumber
    WHERE e.dno = top_dno;
END //

DELIMITER ;

CALL GetTopSalaryEmployee();
在表department上创建一个触发器deaprt_update，当更改部门号时同步更改employee表中对应的部门号。
触发器：
DELIMITER //

CREATE TRIGGER deaprt_update
#在department表上进行AFTER UPDATE操作后触发。
AFTER UPDATE ON department
#表示对每一行都执行触发器的操作。
FOR EACH ROW
BEGIN
    #检查新旧部门号是否不同。
    IF OLD.dnumber <> NEW.dnumber THEN
        #更新employee表中对应的部门号。
        UPDATE employee
        SET dno = NEW.dnumber
        WHERE dno = OLD.dnumber;
    END IF;
END //

DELIMITER ;

在表employee上创建一个触发器employee_delete，当删除职工元组同步删除对应的参与项目元组。
触发器：
DELIMITER //

CREATE TRIGGER employee_delete
BEFORE DELETE ON employee
FOR EACH ROW
BEGIN
    DELETE FROM works_on
    WHERE essn = OLD.ssn;
END //

DELIMITER ;

验证触发器：
-- 测试deaprt_update触发器
UPDATE department
SET dnumber = 'd11'
WHERE dnumber = 'd1';

-- 测试employee_delete触发器
DELETE FROM employee
WHERE ssn = 'XXXXXXXXXXXXXXXXXX';