本帖最后由 黄文伯 于 2013-5-24 20:00 编辑
数据源
表emp:- create table emp(
- empno number(4),
- ename varchar2(10),
- sal number(7,2),
- deptno number(3));
- insert into emp values(7369,'SMITH',800,20);
- insert into emp values(7499,'ALLEN',1600,30);
- insert into emp values(7521,'WARD',1250,30);
- insert into emp values(7566,'JONES',2975,20);
- insert into emp values(7654,'MARTIN',1250,30);
- insert into emp values(7698,'BLAKE',2850,30);
- insert into emp values(7782,'CLARK',2450,10);
- insert into emp values(7788,'SCOTT',3000,20);
- insert into emp values(7839,'KING',5000,10);
- insert into emp values(7844,'TURNER',1500,30);
- insert into emp values(7876,'ADAMS',1100,20);
- insert into emp values(7900,'JAMES',950,30);
- insert into emp values(7902,'FORD',3000,20);
- insert into emp values(7934,'MILLER',1300,10);
复制代码 表dept:- drop table dept;
- create table dept(
- deptno number(3),
- dname varchar2(14),
- loc varchar2(13),
- totalPerson int);
- insert into dept values(10,'accounting','new york',0);
- insert into dept values(20,'research','dallas',0);
- insert into dept values(30,'sales','chicago',0);
- 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数据是否正确- insert into emp values(7369,'SMITH',800,20);
- insert into emp values(7499,'ALLEN',1600,30);
- insert into emp values(7521,'WARD',1250,30);
- insert into emp values(7698,'BLAKE',2850,30);
- insert into emp values(7782,'CLARK',2450,10);
- insert into emp values(7788,'SCOTT',3000,20);
- insert into emp values(7839,'KING',5000,10);
- insert into emp values(7844,'TURNER',1500,30);
- insert into emp values(7876,'ADAMS',1100,20);
复制代码 增加下列数据后,请记住dept表的totalPerson数据- insert into emp values(9999,'tiger',1700,40);
复制代码 将员工编号9999的部门编号改为30后,再查看dept表的totalPerson数据有何变化
|