Oracle之PL/SQL游标练习题（三）

1、定义游标：列出每个员工的姓名部门名称并编程显示第10个到第20个记录

法一：

declarecursor ed_cursor is select ename,dname from(select ename,dname,rownum r from emp e left join dept d on e.deptno=d.deptno)t where t.r between 10 and 20;v_ename varchar2(10);v_dname varchar2(10);
beginopen ed_cursor;fetch ed_cursor into v_ename,v_dname;while ed_cursor%found loopdbms_output.put_line(v_ename||'  '||v_dname);fetch ed_cursor into v_ename,v_dname;end loop;close ed_cursor;
end;

法二：

declarecursor e_d is select * from(select e.ename,d.dname,rownum r from emp e,dept d where e.deptno=d.deptno)where r between 10 and 20;
beginfor e in e_d loopdbms_output.put_line(e.ename||','||e.dname);end loop;
end;

法三：

declarecursor e_d  is select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
beginfor e in e_d loopif e_d%rowcount>=10 and e_d%rowcount<=20 thendbms_output.put_line(e.ename||','||e.dname);end if;end loop;
end;

2、定义游标：从雇员表中显示工资大于3000的记录，只要姓名、部门编号和工资，编程显示其中的奇数记录

法一：

declarecursor emp_cursor is select ename,deptno,sal from emp where sal>3000;v_ename varchar2(10);v_deptno number(10);v_sal number(10);
beginopen emp_cursor;fetch emp_cursor into v_ename,v_deptno,v_sal;while emp_cursor%found loopif emp_cursor%rowcount mod 2=1 thendbms_output.put_line(v_ename||'  '||v_deptno||'  '||v_sal);fetch emp_cursor into v_ename,v_deptno,v_sal;end if;end loop;close emp_cursor;
end;

法二：

declarecursor c_emp is select ename,deptno,sal from emp where sal>3000;
beginfor e in c_emp loopif mod(c_emp%rowcount,2)=1 thendbms_output.put_line(e.ename||','||e.deptno||','||e.sal);end if;end loop;
end;

3、用游标显示所有部门编号与名称，以及其所拥有的员工人数

法一：

declarecursor dept_cursor is select d.deptno,dname,count(*) from dept dleft join emp e on d.deptno=e.deptno group by d.deptno,dname;v_deptno number(20);v_dname varchar2(10);v_sum number(10);
beginopen dept_cursor;fetch dept_cursor into v_deptno,v_dname,v_sum;while dept_cursor%found loopdbms_output.put_line(v_deptno||' '||v_dname||' '||v_sum);fetch dept_cursor into v_deptno,v_dname,v_sum;end loop;close dept_cursor;
end;

法二：

declarecursor c_dept is select * from dept;v_n number:=0;
beginfor d in c_dept loopselect count(*) into v_n from emp where deptno=d.deptno;dbms_output.put_line(d.deptno||','||d.dname||',人数：'||v_n);end loop;
end;

4、用游标属性%rowcount实现输出前十个员工的信息

declarecursor c_emp is select * from emp;v_emp emp%rowtype;
beginopen c_emp;loopfetch c_emp into v_emp;exit when c_emp%notfound or c_emp%rowcount>10;dbms_output.put_line(v_emp.empno||','||v_emp.ename);end loop;   close c_emp;
end;

5、通过使用游标来显示dept表中的部门名称及其相应的员工列表（提示：可以使用双重循环）

法一：

declarecursor dept_cursor is select dname,e.* from dept d left join emp e on e.deptno=d.deptno;v_dname varchar2(10);v_emp emp%rowtype;
beginopen dept_cursor;fetch dept_cursor into v_dname,v_emp.empno,v_emp.ename,v_emp.job,v_emp.mgr,v_emp.hiredate,v_emp.sal,v_emp.comm,v_emp.deptno;while dept_cursor%found loopdbms_output.put_line(v_dname||','||v_emp.empno||','||v_emp.ename||','||v_emp.job||','||v_emp.mgr||','||v_emp.hiredate||','||v_emp.sal||','||v_emp.comm||','||v_emp.deptno); fetch dept_cursor into v_dname,v_emp.empno,v_emp.ename,v_emp.job,v_emp.mgr,v_emp.hiredate,v_emp.sal,v_emp.comm,v_emp.deptno;end loop;close dept_cursor;
end;

法二：

declarecursor c_dept is select * from dept;--定义带参数的游标，根据提供的部门编号，去查询对应的员工列表cursor c_emp(dno emp.deptno%type) is select * from emp where deptno=dno;e emp%rowtype;
beginfor d in c_dept loopdbms_output.put_line('------部门名称：'||d.dname);   open c_emp(d.deptno);--根据外层循环得到的部门编号去查询对应员工信息loopfetch c_emp into e;exit when c_emp%notfound;dbms_output.put_line(e.empno||','||e.ename);end loop;  close c_emp;end loop;  
end;

6、接受一个部门号，使用For循环，从emp表中显示该部门的所有雇员的姓名，工作和薪水

declarecursor emp_cursor is select * from emp where deptno=&deptno;
beginfor e in emp_cursor loopdbms_output.put_line(e.ename||','||e.job||','||e.sal);end loop;
end;

7、编写一个程序块，将emp表中前5人的名字及其出的工资等级（salgrade）显示出来

法一：

declarecursor es_cursor is select ename,sal,gradefrom(select ename,sal,rownum r from emp)e,salgrade swhere r<=5 and sal between losal and hisal;v_ename varchar2(10);v_sal number(10,2);v_grade number(2);
beginopen es_cursor;fetch es_cursor into v_ename,v_sal,v_grade;while es_cursor%found loopdbms_output.put_line(v_ename||', '||v_sal||', '||v_grade);fetch es_cursor into v_ename,v_sal,v_grade;end loop;close es_cursor;
end;

法二：

declarecursor c_e is select e.ename,s.grade,rownum from emp e,salgrade swhere e.sal between s.losal and s.hisal and rownum<=5;
beginfor e in c_e loopdbms_output.put_line(e.ename||','||e.grade);   end loop;
end;

8、emp表中对所有雇员按他们基本薪水的10%给他们加薪，如果所增加后的薪水大于5000，则取消加薪

declarecursor cemp is select * from emp;
beginfor e in cemp loopif e.sal*1.1<=5000 thenupdate emp set sal=sal*1.1 where empno=e.empno;end if;end loop;
end;

9、按照salgrade表中的标准，给员工加薪，1：5%，2：4%，3：3%，4：2%，5：1%，并打印输出每个人加薪前后的工资

法一：

declarecursor es_cursor is select ename,sal,grade from emp e,salgrade s where sal between losal and hisal;v_ename varchar2(10);v_sal number(10,2);v_grade number(2);
beginopen es_cursor;fetch es_cursor into v_ename,v_sal,v_grade;while es_cursor%found loopdbms_output.put_line('加薪前: '||v_ename||', '||v_sal||', '||v_grade);case v_grade when 1 then v_sal:=v_sal*1.05;when 2 then v_sal:=v_sal*1.04;when 3 then v_sal:=v_sal*1.03;when 4 then v_sal:=v_sal*1.02;when 5 then v_sal:=v_sal*1.01;end case;dbms_output.put_line('加薪后: '||v_ename||', '||v_sal);update emp set sal=v_sal where ename=v_ename;fetch es_cursor into v_ename,v_sal,v_grade;end loop;close es_cursor;
end;

法二：

declarecursor emp_sal_cursor is select sal,empno from emp;v_sal emp.sal%type;v_id emp.empno%type;
beginopen emp_sal_cursor;fetch emp_sal_cursor into v_sal,v_id;while emp_sal_cursor%found loopdbms_output.put_line('加薪前: '||v_id || ': ' || v_sal);if v_sal between 700 and 1200 then v_sal:=v_sal+v_sal*0.05;elsif v_sal between 1201 and 1400 then v_sal:=v_sal+v_sal*0.04;elsif v_sal between 1401 and 2000 then v_sal:=v_sal+v_sal*0.03;elsif v_sal between 2001 and 3000 then v_sal:=v_sal+v_sal*0.02;else v_sal:=v_sal+v_sal*0.01;end if;update emp set sal=v_sal where empno=v_id;dbms_output.put_line('加薪后: '||v_id || ': ' || v_sal);fetch emp_sal_cursor into v_sal,v_id;end loop;close emp_sal_cursor;
end;

法三：

declarecursor emp_sal_cursor is select sal,empno from emp;temp number(4,2);
beginfor c in emp_sal_cursor loopdbms_output.put_line(c.empno||': '||c.sal);if c.sal between 700 and 1200 then c.sal:=c.sal+c.sal*0.05;elsif c.sal between 1201 and 1400 then c.sal:=c.sal+c.sal*0.04;elsif c.sal between 1401 and 2000 then c.sal:=c.sal+c.sal*0.03;elsif c.sal between 2001 and 3000 then c.sal:=c.sal+c.sal*0.02;else c.sal:=c.sal+c.sal*0.01;end if;dbms_output.put_line(c.empno||': '||c.sal);update emp set sal=c.sal where empno=c.empno;end loop; 
end;

法四：

declarecursor c_e is select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;sal_1 emp.sal%type;--加薪前的工资sal_2 emp.sal%type;--加薪后的工资     
beginfor e in c_e loopsal_1:=e.sal;case e.gradewhen 1 thensal_2:=e.sal*1.05;when 2 thensal_2:=e.sal*1.04;when 3 thensal_2:=e.sal*1.03;when 4 thensal_2:=e.sal*1.02;when 5 thensal_2:=e.sal*1.01;end case;update emp set sal=sal_2 where empno=e.empno;dbms_output.put_line(e.empno||','||e.ename||','||sal_1||','||sal_2);end loop;
end;

10、用游标获取所有收入超过2000的 salesman

法一：

declarecursor emp_cursor is select * from emp where (sal+nvl(comm,0))>2000 and job='SALESMAN';v_emp emp%rowtype;
beginopen emp_cursor;fetch emp_cursor into v_emp;while emp_cursor%found loopdbms_output.put_line(v_emp.empno||','||v_emp.ename);fetch emp_cursor into v_emp;end loop;close emp_cursor;
end;

法二：

declarecursor c_emp is select * from emp where (sal+nvl(comm,0))>2000 and job=upper('salesman');
beginfor e in c_emp loopdbms_output.put_line(e.ename);end loop;
end;