A股上市公司传智教育(股票代码 003032)旗下技术交流社区北京昌平校区

 找回密码
 加入黑马

QQ登录

只需一步,快速开始

© 黄文伯 金牌黑马   /  2013-5-23 22:55  /  1650 人查看  /  4 人回复  /   0 人收藏 转载请遵从CC协议 禁止商业使用本文

本帖最后由 黄文伯 于 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);

  7. insert into dept values(10,'accounting','new york',0);
  8. insert into dept values(20,'research','dallas',0);
  9. insert into dept values(30,'sales','chicago',0);
  10. 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数据有何变化


评分

参与人数 1技术分 +1 收起 理由
袁梦希 + 1 赞一个!

查看全部评分

4 个回复

倒序浏览

回帖奖励 +5 黑马币

我表示技术很高深,另请大神吧{:soso_e163:}
回复 使用道具 举报
这种问题可以去javaEE交流专区,这里的的提问都是一些基础性的东西{:soso_e100:}
回复 使用道具 举报
袁梦希 发表于 2013-5-23 23:24
这种问题可以去javaEE交流专区,这里的的提问都是一些基础性的东西 ...

好的,谢谢斑竹提醒
回复 使用道具 举报
老方常说细节决定程序员的高度,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;

  35. delete from emp where empno=9999;
复制代码
回复 使用道具 举报
您需要登录后才可以回帖 登录 | 加入黑马