黑马程序员技术交流社区

标题: PL/SQL编程之函数与触发器 [打印本页]
作者: 黄文伯    时间: 2013-5-23 22:55
标题: PL/SQL编程之函数与触发器
本帖最后由 黄文伯 于 2013-5-24 20:00 编辑

数据源
表emp：
  1. create table emp(
  2. empno number(4),   
  3. ename varchar2(10),   
  4. sal number(7,2),
  5. deptno number(3));
  6. insert into emp values(7369,'SMITH',800,20);
  7. insert into emp values(7499,'ALLEN',1600,30);
  8. insert into emp values(7521,'WARD',1250,30);
  9. insert into emp values(7566,'JONES',2975,20);
  10. insert into emp values(7654,'MARTIN',1250,30);
  11. insert into emp values(7698,'BLAKE',2850,30);
  12. insert into emp values(7782,'CLARK',2450,10);
  13. insert into emp values(7788,'SCOTT',3000,20);
  14. insert into emp values(7839,'KING',5000,10);
  15. insert into emp values(7844,'TURNER',1500,30);
  16. insert into emp values(7876,'ADAMS',1100,20);
  17. insert into emp values(7900,'JAMES',950,30);
  18. insert into emp values(7902,'FORD',3000,20);
  19. insert into emp values(7934,'MILLER',1300,10);
复制代码
表dept：
  1. drop table dept;
  2. create table dept(
  3. deptno number(3),
  4. dname varchar2(14),
  5. loc varchar2(13),
  6. totalPerson int);

  8. insert into dept values(10,'accounting','new york',0);
  9. insert into dept values(20,'research','dallas',0);
  10. insert into dept values(30,'sales','chicago',0);
  11. insert into dept values(40,'operations','boston',0);
复制代码
1.包
创建包emp_pkg，实现以下功能：
过程hireEmp，即新增一个员工，参数自行定义
过程fireEmp，即删除一个员工，参数自行定义
函数getSal，即根据员工编号获取其工资，并进行no_data_found的异常处理
调用该包的过程和函数，并查看结果是否正确
新增一员工，数据为：9999,'tiger',1700,40
获取员工编号为7654和8000的工资
删除编号为9999的员工
删除该包
2.触发器
删除emp中所有记录
创建触发器empdepTrg，实现下面的功能：
当新增员工时，其对应的部门人数需 +1；
当删除员工时，其对应的部门人数需 -1；
当修改员工部门时，原部门人数需 -1，新部门人数需 +1
用下列数据进行测试，查看dept表的totalPerson数据是否正确
  1. insert into emp values(7369,'SMITH',800,20);
  2. insert into emp values(7499,'ALLEN',1600,30);
  3. insert into emp values(7521,'WARD',1250,30);
  4. insert into emp values(7698,'BLAKE',2850,30);
  5. insert into emp values(7782,'CLARK',2450,10);
  6. insert into emp values(7788,'SCOTT',3000,20);
  7. insert into emp values(7839,'KING',5000,10);
  8. insert into emp values(7844,'TURNER',1500,30);
  9. insert into emp values(7876,'ADAMS',1100,20);
复制代码
增加下列数据后，请记住dept表的totalPerson数据
  1. insert into emp values(9999,'tiger',1700,40);
复制代码
将员工编号9999的部门编号改为30后，再查看dept表的totalPerson数据有何变化


作者: 袁梦希    时间: 2013-5-23 23:22
我表示技术很高深，另请大神吧{:soso_e163:}
作者: 袁梦希    时间: 2013-5-23 23:24
这种问题可以去javaEE交流专区，这里的的提问都是一些基础性的东西{:soso_e100:}
作者: 黄文伯    时间: 2013-5-24 09:18
袁梦希 发表于 2013-5-23 23:24
这种问题可以去javaEE交流专区，这里的的提问都是一些基础性的东西 ...

好的，谢谢斑竹提醒
作者: 黄文伯    时间: 2013-5-24 12:20
老方常说细节决定程序员的高度，PL/SQL中ELSEIF应该写成'ELSIF',参考代码如下所示：
  1. --建立包说明部分
  2. CREATE OR REPLACE PACKAGE emp_pkg IS
  3.         --hireEmp存储过程
  4.         PROCEDURE hireEmp
  5.         (p_empno IN emp.empno%TYPE,
  6.         p_ename IN emp.ename%TYPE,
  7.         p_sal IN emp.sal%TYPE,
  8.         p_deptno IN emp.deptno%TYPE);
  9.         --fireEmp存储过程
  10.         PROCEDURE fireEmp
  11.         (p_empno IN emp.empno%TYPE);
  12.         --getSal函数
  13.         FUNCTION getSal(p_empno IN NUMBER) RETURN NUMBER;
  14. END emp_pkg;
  15. /
  16. --建立包主体
  17. CREATE OR REPLACE PACKAGE BODY emp_pkg
  18. IS
  19.         --hireEmp存储过程
  20.         PROCEDURE  hireEmp
  21.         (p_empno IN emp.empno%TYPE,
  22.         p_ename IN emp.ename%TYPE,
  23.         p_sal IN emp.sal%TYPE,
  24.         p_deptno IN emp.deptno%TYPE)
  25.   IS
  26.         BEGIN
  27.                 INSERT INTO emp(empno,ename,sal,deptno) VALUES(p_empno,p_ename,p_sal,p_deptno);
  28.                 COMMIT;
  29.         END hireEmp;
  30.         --fireEmp存储过程
  31.   PROCEDURE fireEmp
  32.         (p_empno IN emp.empno%TYPE)
  33.         IS
  34.   BEGIN
  35.           DELETE FROM emp WHERE empno = p_empno;
  36.           COMMIT;
  37.   END fireEmp;
  38.   --getSal函数
  39.   FUNCTION getSal(p_empno IN NUMBER) RETURN NUMBER
  40.   IS
  41.           v_sal NUMBER;
  42.   BEGIN
  43.           SELECT sal INTO v_sal FROM emp WHERE empno = p_empno;
  44.           return(v_sal);
  45.   --异常处理
  46.   EXCEPTION
  47.           WHEN NO_DATA_FOUND THEN
  48.                   RAISE_APPLICATION_ERROR('-20091','不存在这个编号的雇员');
  49.   END getSal;
  50. END emp_pkg;
  51. /
  52. --调用hireEmp存储过程
  53. EXECUTE emp_pkg.hireEmp(9999,'tiger',1700,40);
  54. SELECT * FROM emp;
  55. --调用getSal函数
  56. SELECT emp_pkg.getSal(7654) FROM dual;
  57. SELECT emp_pkg.getSal(8000) FROM dual;
  58. --调用fireEmp存储过程
  59. EXECUTE emp_pkg.fireEmp(9999);
  60. SELECT * FROM emp;
  61. --删除包主体
  62. DROP PACKAGE BODY emp_pkg;
  63. --删除包
  64. DROP PACKAGE emp_pkg;
复制代码
  1. --删除emp中所有记录
  2. DELETE FROM emp;
  3. --创建触发器empdepTrg
  4. CREATE OR REPLACE TRIGGER emp_dep_Trg
  5.         AFTER INSERT OR UPDATE OR DELETE ON emp
  6.         REFERENCING OLD AS old NEW AS new
  7.         FOR EACH ROW
  8. BEGIN
  9.         --当新增员工时，其对应的部门人数需 +1
  10.         IF INSERTING THEN
  11.                 UPDATE dept
  12.                         SET totalPerson = totalPerson + 1
  13.                         WHERE deptno = :new.deptno;
  14.         --当删除员工时，其对应的部门人数需 -1；
  15.         ELSIF DELETING THEN
  16.                 UPDATE dept
  17.                         SET totalPerson = totalPerson - 1
  18.                         WHERE deptno = :old.deptno;
  19.          --当修改员工部门时，原部门人数需 -1，新部门人数需 +1
  20.          ELSIF UPDATING('deptno') THEN
  21.           UPDATE dept
  22.                   SET totalPerson = totalPerson - 1
  23.                         WHERE deptno = :old.deptno;
  24.           UPDATE dept
  25.                   SET totalPerson = totalPerson + 1
  26.                         WHERE deptno = :new.deptno;               
  27.         END IF;
  28. END;
  29. /
  30. 测试：
  31. SELECT * FROM dept;
  32. update emp
  33. set deptno=30
  34. where empno=9999;

  36. delete from emp where empno=9999;
复制代码




欢迎光临 黑马程序员技术交流社区 (http://bbs.itheima.com/) 黑马程序员IT技术论坛 X3.2