如 部门号 10 部门名ACCOUNTING
张三 SALES 1999.1.1 3000
....
2.游标处理中使用for update和where current of
在表中修改字段starts的值，sal字段中每一个1000，一个‘*’，如sal为3500，四舍五入，starts打印4个'*'
建表语句如下：
CREATE TABLE copy_emp as select * from emp;
alter table copy_emp add starts varchar(20);
3.定义游标，查询emp表，如果工资大于2000，并且参加工作时间在82年之前，在屏幕上显示员工的名字，工资和参加工作时间
4.定义代表部门号的两个变量，屏幕上输出这两个部门的所有员工姓名与工资（这两个变量作为游标的参数）
5.在一个块中分别显示薪水低于2000，在2000与4000之间，以
及大于4000的雇员名.(请使用游标参数)
6.汇总每个部门每个职位员工的最高及平均工资，包括显示部门名称
和职位名称，将数据存储到Test表中。要求使用游标
1.DECLARE
CURSOR emp_dept_cursor IS
SELECT d.deptno,d.dname,e.ename,e.job,e.hiredate,e.sal FROM dept d,emp e WHERE d.deptno=e.deptno;
v_deptno dept.deptno%TYPE;
v_dname dept.dname%TYPE;
v_ename emp.ename%TYPE;
v_job emp.job%TYPE;
v_hiredate emp.hiredate%TYPE;
v_sal emp.sal%TYPE;
BEGIN
IF NOT emp_dept_cursor%ISOPEN THEN
OPEN emp_dept_cursor;
END IF;
LOOP
FETCH emp_dept_cursor INTO v_deptno,v_dname,v_ename,v_job,v_hiredate,v_sal;
EXIT WHEN emp_dept_cursor%NOTFOUND;
dbms_output.put_line('部门号:'||v_deptno);
dbms_output.put_line('部门名称:'||v_dname);
dbms_output.put_line(v_ename);
dbms_output.put_line(v_job);
dbms_output.put_line(to_char(v_hiredate,'yyyy-mm-dd'));
dbms_output.put_line(v_sal);
dbms_output.put_line('==================');
END LOOP;
CLOSE emp_dept_cursor;
END;
2.
CREATE TABLE copy_emp as select * from emp;
alter table copy_emp add starts varchar(20);
DECLARE
CURSOR sal_emp IS
SELECT sal FROM copy_emp FOR UPDATE NOWAIT;
v_sal copy_emp.sal%TYPE;
BEGIN
IF NOT sal_emp%ISOPEN THEN
OPEN sal_emp;
END IF;
LOOP
FETCH sal_emp INTO v_sal;
EXIT WHEN sal_emp%NOTFOUND;
dbms_output.put_line(ROUND(v_sal/1000));
UPDATE copy_emp SET starts=LPAD(' ',ROUND(v_sal/1000)+1,'*') WHERE CURRENT OF sal_emp;
END LOOP;
END;
3.
DECLARE
CURSOR emp_cursor IS
SELECT ename,sal,hiredate FROM emp;
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
v_hiredate emp.hiredate%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_ename,v_sal,v_hiredate;
EXIT WHEN emp_cursor%NOTFOUND;
IF v_sal>2000 AND EXTRACT(YEAR FROM v_hiredate)<1982 THEN
dbms_output.put_line('员工姓名:'||v_ename);
dbms_output.put_line('员工薪资:'||v_sal);
dbms_output.put_line('入职时间:'||to_char(v_hiredate,'yyyy.mm.dd'));
dbms_output.put_line('===========================');
END IF;
END LOOP;
END;
4.
DECLARE
CURSOR emp_cursor(v_deptno1 NUMBER DEFAULT 30,v_deptno2 NUMBER DEFAULT 30) IS
SELECT ename,sal FROM emp WHERE deptno=v_deptno1 OR deptno=v_deptno2;
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
OPEN emp_cursor(v_deptno1=>&deptno1,v_deptno2=>&deptno2);
LOOP
FETCH emp_cursor INTO v_ename,v_sal;
EXIT WHEN emp_cursor%NOTFOUND;
dbms_output.put_line('员工姓名:'||v_ename);
dbms_output.put_line('员工工资:'||v_sal);
dbms_output.put_line('==================');
END LOOP;
CLOSE emp_cursor;
END;
5.
DECLARE
CURSOR emp_cursor(i_sal NUMBER,a_sal NUMBER) IS
SELECT ename FROM emp WHERE sal BETWEEN i_sal AND a_sal;
v_ename emp.ename%TYPE;
BEGIN
dbms_output.put_line('薪水低于2000的是:');
FOR emp_record IN emp_cursor(0,2000) LOOP
dbms_output.put_line('员工姓名:'||emp_record.ename);
END LOOP;
dbms_output.put_line('薪水在2000与4000之间的是:');
FOR emp_record IN emp_cursor(2000,4000) LOOP
dbms_output.put_line('员工姓名:'||emp_record.ename);
END LOOP;
dbms_output.put_line('薪水高于4000的是:');
FOR emp_record IN emp_cursor(4000,20000000) LOOP
dbms_output.put_line('员工姓名:'||emp_record.ename);
END LOOP;
END;
6.
CREATE VIEW vw_sal_dept AS
SELECT max(s.sal) max_sal,AVG(s.sal) avg_sal,s.deptno FROM(SELECT e.sal,e.job,d.deptno FROM emp e,dept d WHERE e.deptno=d.deptno) s GROUP BY s.deptno;
CREATE VIEW vw_sal_job_dept AS
SELECT v.max_sal,v.avg_sal,d.dname,e.job FROM vw_sal_dept v,emp e,dept d WHERE v.max_sal=e.sal AND v.deptno=d.deptno AND e.deptno=d.deptno;
DECLARE
CURSOR emp_dept_cursor IS
SELECT v.max_sal,v.avg_sal,v.job,v.dname FROM vw_sal_job_dept v;
v_max vw_sal_job_dept.max_sal%TYPE;
v_avg vw_sal_job_dept.avg_sal%TYPE;
v_job vw_sal_job_dept.job%TYPE;
v_dname vw_sal_job_dept.dname%TYPE;
BEGIN
OPEN emp_dept_cursor;
LOOP
FETCH emp_dept_cursor INTO v_max,v_avg,v_job,v_dname;
EXIT WHEN emp_dept_cursor%NOTFOUND;
INSERT INTO TEST VALUES(v_max,v_avg,v_job,v_dname);
END LOOP;
END;