表、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;
/