oracle中光标,Oracle中的游标（光标）-来源于Oracle赵强老师

Oracle中的游标(光标)--来自Oracle赵强老师



表、select语句、游标：返回结果都能是一个集合。

注意：游标的结果是一个集合。

--查询并打印员工的姓名和薪水

set serveroutput on

/*

光标:

1. 光标的属性： %isopen %rowcount(返回的行数)

%notfound %found

2. 默认情况下，一次性打开300个光标

SQL> show parameter cursor

NAME TYPE VALUE

------------------------------------ ----------- ---------------

cursor_sharing string EXACT

cursor_space_for_time boolean FALSE

open_cursors integer 300

session_cached_cursors integer 20

修改光标:

alter system set open_cursors=400;

*/

declare

--定义一个光标

cursor cemp is select ename,sal from emp;

pename emp.ename%type;引用变量

psal emp.sal%type;引用变量

begin

open cemp;

loop

--取一条记录

fetch cemp into pename,psal;

--退出： fetch没有取到

exit when cemp%notfound;

dbms_output.put_line(pename||'的薪水是'||psal);

end loop;

close cemp;

end;

/

--查询某个部门的员工姓名

set serveroutput on

declare

cursor cemp(dno number) is select ename from emp where deptno=dno;

pename emp.ename%type;

begin

open cemp(20);

loop

fetch cemp into pename;

exit when cemp%notfound;

dbms_output.put_line(pename);

end loop;

close cemp;

end;

/

/*

SQL语句

select to_char(hiredate,'YYYY') from emp

--> 光标 --> 循环 --> 退出条件

变量:

count80 number := 0;

count81 number := 0;

count82 number := 0;

count87 number := 0;

*/

set serveroutput on

declare

cursor cemp is select to_char(hiredate,'YYYY') from emp;

phiredate varchar2(4);

count80 number := 0;

count81 number := 0;

count82 number := 0;

count87 number := 0;

begin

open cemp;

loop

--取一个员工的年份

fetch cemp into phiredate;

exit when cemp%notfound;

--判断年份

if phiredate = '1980' then count80 := count80+1;

elsif phiredate = '1981' then count81 := count81+1;

elsif phiredate = '1982' then count82 := count82+1;

else count87 := count87 + 1;

end if;

end loop;

close cemp;

--输出

dbms_output.put_line('Total:'||(count80+count81+count82+count87));

dbms_output.put_line('1980:'||count80);

dbms_output.put_line('1981:'||count81);

dbms_output.put_line('1982:'||count82);

dbms_output.put_line('1987:'||count87);

end;

/

/*

SQL语句:

select empno,sal from emp order by sal

--> 光标 --> 循环 --> 退出条件: 1. 总额> 5w 2. 所有人涨完

变量:

涨工资的人数： countEmp number := 0;

涨后的工资总额: salTotal number;

1. select sum(sal) into salTotal from emp;

2. 涨后 = 涨前 + sal * 0.1

练习： 工资总额不能超过5w

*/

set serveroutput on

declare

cursor cemp is select empno,sal from emp order by sal;

pempno emp.empno%type;

psal emp.sal%type;

--涨工资的人数：

countEmp number := 0;

--涨后的工资总额:

salTotal number;

begin

--得到工资初始值

select sum(sal) into salTotal from emp;

open cemp;

loop

--1. 总额> 5w

exit when salTotal > 50000;

--取一个员工

fetch cemp into pempno,psal;

--2. 所有人涨完

exit when cemp%notfound;

--涨工资

update emp set sal=sal*1.1 where empno=pempno;

--人数+1

countEmp := countEmp +1;

--总额

salTotal := salTotal + psal * 0.1;

end loop;

close cemp;

commit;

dbms_output.put_line('人数:'||countEmp||' 涨后的工资总额:'||salTotal);

end;

/

/*

SQL语句:

部门: select deptno from dept --> 光标 --> 循环 --> 退出条件

部门中员工的薪水: select sal from emp where deptno=??

--> 带参数的光标 --> 循环 --> 退出条件

变量:

每个段的人数:

count1 number; count2 number; count3 number;

部门的工资总额:

salTotal number;

1. 累加

2. select sum(sal) into salTotal from emp where deptno=??

*/

set serveroutput on

declare

--部门

cursor cdept is select deptno from dept;

pdeptno dept.deptno%type;

--部门中员工的薪水

cursor cemp(dno number) is select sal from emp where deptno=dno;

psal emp.sal%type;

--每个段的人数:

count1 number; count2 number; count3 number;

--部门的工资总额:

salTotal number;

begin

open cdept;

loop

--取一个部门

fetch cdept into pdeptno;

exit when cdept%notfound;

--初始化

count1:=0; count2:=0; count3:=0;

--部门的工资总额

select sum(sal) into salTotal from emp where deptno=pdeptno;

--部门中员工的薪水

open cemp(pdeptno);

loop

-- 取一个员工的薪水

fetch cemp into psal;

exit when cemp%notfound;

--判断薪水

if psal < 3000 then count1:=count1+1;

elsif psal>=3000 and psal< 6000 then count2:=count2+1;

else count3:=count3+1;

end if;

end loop;

close cemp;

--保存当前部门

insert into msg values(pdeptno,count1,count2,count3,nvl(salTotal,0));

end loop;

close cdept;

commit;

dbms_output.put_line('完成');

end;

/