本帖最后由 黄文伯 于 2013-5-17 12:56 编辑
数据源:- --表emp:
- DROP TABLE emp PURGE;
- CREATE TABLE emp(
- empno NUMBER(4),
- ename VARCHAR2(10),
- job VARCHAR2(9),
- mgr NUMBER(4),
- hiredate DATE,
- sal NUMBER(7,2),
- comm NUMBER(7,2),
- deptno NUMBER(3)
- );
- insert into emp values(7369,'SMITH','CLERK',7902,'17-12月-05',800,NULL,20);
- insert into emp values(7499,'ALLEN','SALESMAN',7698,'20-2月-02',1600,300,30);
- insert into emp values(7521,'WARD','SALESMAN',7698,'22-2月-96',1250,500,30);
- insert into emp values(7566,'JONES','MANAGER',7839,'02-4月-09',2975,NULL,20);
- insert into emp values(7654,'MARTIN','SALESMAN',7698,'28-9月-10',1250,1400,30);
- insert into emp values(7698,'BLAKE','MANAGER',7839,'01-5月-08',2850,NULL,30);
- insert into emp values(7782,'CLARK','MANAGER',7839,'09-6月-05',2450,NULL,10);
- insert into emp values(7788,'SCOTT','ANALYST',7566,'19-4月-10',3000,NULL,20);
- insert into emp values(7839,'KING','PRESIDENT',NULL,'17-11月-11',5000,NULL,10);
- insert into emp values(7844,'TURNER','SALESMAN',7698,'08-9月-07',1500,0,30);
- insert into emp values(7876,'ADAMS','CLERK',7788,'23-5月-04',1100,NULL,20);
- insert into emp values(7900,'JAMES','CLERK',7698,'03-12月-03',950,NULL,30);
- insert into emp values(7902,'FORD','ANALYST',7566,'03-12月-07',3000,NULL,20);
- insert into emp values(7934,'MILLER','CLERK',7782,'23-1月-09',1300,NULL,10);
- --表dept:
- DROP TABLE dept PURGE;
- CREATE TABLE dept(
- deptno NUMBER(3),
- dname VARCHAR2(14),
- loc VARCHAR2(13)
- );
- insert into dept values(10,'accounting','new york');
- insert into dept values(20,'research','dallas');
- insert into dept values(30,'sales','chicago');
- insert into dept values(40,'operations','boston');
复制代码 1.游标
为表dept增加一个字段totalPerson,用来记录该部门的总人数
totalPerson int
用游标实现修改各部门的总人数
提示如下:
定义一个游标,名为dept_cursor,数据为select deptno from dept,带for update子句
打开游标开始循环
获取游标所指的当前记录的部门编号
在表emp中查询当前部门编号的员工人数
用游标修改dept表中记录的totalPerson字段
循环结束,关闭游标
查看表dept中字段totalPerson的数据是否正确
2.存储过程
查询指定员工编号所在的部门名称
要求使用IN/OUT型参数
处理异常:
当no_data_found时,给出'no data'的信息提示
其他异常时,给出'unkown error'的信息提示
3.函数
编写一个函数求a, b, c的最大值
其功能是:对输入的3个参数比较大小,输出最大的参数
函数中需有异常处理:
若3个参数的值<0则提示错误信息
十分感谢!
|