Oracle数据库应用教程
创建名为“PROC_DEPARTMENT_CHANGE”的存储过程，实现员工部门的调动。
CREATE OR REPLACE PROCEDURE proc_department_change(
p_employee_id employees.employee_id%type,
p_new_department_name departments.department_name%type)
AS
v_old_department_id departments.department_id%type;
v_old_department_name departments.department_name%type;
v_new_department_id departments.department_id%type;
BEGIN
SELECT department_id INTO v_old_department_id FROM employees
WHERE employee_id=p_employee_id;
SELECT department_name INTO v_old_department_name FROM departments
WHERE department_id=v_old_department_name;
IF v_old_department_name=p_new_department_name THEN
RAISE_APPLICATION_ERROR(20001,'the new department name is as same as
before!');
END IF;
SELECT department_id INTO v_new_department_id FROM departments
WHERE department_name=p_new_department_name;
UPDATE employees SET department_id=v_new_department_id
WHERE employee_id=p_employee_id;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(20002,'The department name does not exists!');
END proc_department_change;
19
