游标的出现是为了解决从某一结果集中逐一读取一条记录。
--1.游标的类型:显式游标和隐式游标。
--2.显式游标:通过open(打开)、fetch(读取)和close(关闭)三个命令控制游标。
--****2.11 declare语句,语法:****--
cursor cursor_name[(parameter[,parameter]...)]
[return return_type] is select_statement
--例子declare1:声明游标,对应的集合是表emp中所有的员工姓名
declare
cursor c_emp_ename isselect ename from emp;
--例子declare2:声明游标,对应的集合是表emp中所有的员工信息
declare
cursor c_emp isselect * from emp;
--例子declare3:声明游标,对应的集合是表emp中根据输入员工号而确定的员工信息
declare
cursor c_emp(v_empno number(4)) isselect * from emp where empno=v_empno;
--*****2.12 open语句,语法:*****--
open cursor_name;
--*****2.13 fetch语句从游标提取一行数据使用,每一次提取数据后,游标都指向结果集的下一行*****--
语法:fetch cursor_name intovariable[,variable,...]
--例子fetch1:显示例子declare1中第一行信息
declare
cursor c_emp_ename isselect ename from emp;
v_ename emp.ename%type;
begin
open c_emp_ename;
fetch c_emp_ename into v_ename;
dbms_output.put_line(v_ename);
end;
--例子fetch2:显示例子declare1中游标所有信息
declare
cursor c_emp_ename isselect ename from emp;
v_ename emp.ename%type;
v_count binary_integer;
begin
selectcount(rowid) into v_count from emp;--rowid:伪列,表示表中每一行记录在数据文件中的物理地址。
open c_emp_ename;
--方法1
/*loop
fetch c_emp_ename into v_ename;
dbms_output.put_line(v_ename);
v_count := v_count-1;
exit when v_count=0;
end loop;*/
--方法2
for i in1..v_count loop--in后面的语句只有两个"."。
fetch c_emp_ename into v_ename;
dbms_output.put_line(v_ename);
endloop;
end;
--*****2.14close语句,语法*****--
close cursor_name;
--例子close1:在emp表中,计算所有员工的工资
--select sum(sal) from emp;
declare
cursor c_emp_sal isselect sal from emp;
v_sal emp.sal%type;
v_count binary_integer;
v_sum number:=0;
begin
selectcount(rowid) into v_count from emp;
open c_emp_sal;
for i in1..v_count loop
fetch c_emp_sal into v_sal;
v_sum := v_sum+v_sal;
endloop;
close c_emp_sal;
dbms_output.put_line(v_sum);
end;
--例子close2:在表emp中,如果职务是CLERK提高工资1%,如果职务是SALESMAN提高工资2%,如果职务是MANAGER提高工资4%,
--如果职务是ANALYST提高工资3%。
declare
cursor c_emp isselect empno,job from emp1;
v_empno emp1.empno%type;
v_job emp1.job%type;
v_count binary_integer;
begin
selectcount(rowid) into v_count from emp1;
open c_emp;--开启游标
for i in1..v_count loop--loop开始
fetch c_emp into v_empno,v_job;--从游标中取值
case v_job--case开始
when'CLERK'then
update emp1 e set e.sal=sal*(1+0.1) where e.empno=v_empno;
dbms_output.put_line('CLERK OK!');
when'SALESMAM'then
update emp1 e set e.sal=sal*(1+0.2) where e.empno=v_empno;
dbms_output.put_line('SALESMAM OK!');
when'MANAGER'then
update emp1 e set e.sal=sal*(1+0.3) where e.empno=v_empno;
dbms_output.put_line('MANAGER OK!');
when'ANALYST'then
update emp1 e set e.sal=sal*(1+0.4) where e.empno=v_empno;
dbms_output.put_line('ANALYST OK!');
else
update emp1 e set e.sal=sal*(1+0.5) where e.empno=v_empno;
dbms_output.put_line('PRESIDENT OK!');
endcase;--case结束
endloop;--loop结束
close c_emp;--关闭游标
end;
--例子close3:通过记录存储游标每一条信息
declare
cursor c_emp isselect * from emp;
v_emp emp%rowtype;
v_count binary_integer;
begin
selectcount(rowid) into v_count from emp;
open c_emp;
for i in1..v_count loop
fetch c_emp into v_emp;
dbms_output.put_line('salary of emplyee '||v_emp.ename||' is '||v_emp.sal);
endloop;
close c_emp;
end;
--2.2显式游标属性:%found、%notfound、%isopen、%rowcount。
--%2.21 %fount是一个布尔属性,如果前一个fetch语句返回一个行,那么它返回true,否则返回false。如果当前游标还没打开引用,则返回错误。
--使用形式:
loop
fetch c1 into v_ename,v_sal;
if c1 %foundthen
...;
else
...;
exit;
endif;
endloop;
--2.22 %notfound属性与%found属性相反
loop
fetch c1 into v_ename,v_sal;
exitwhen c1 %notfound;
...;
endloop;
说明:在第一次提取之前,%notfound属性为null。所以,如果fetch语句没有一次成功执行,那么一直不退出循环。这就是
为什么当when条件为真时,exit when语句才执行。
--2.23 %isopen属性,如果游标或游标变量打开,则返回true,否则返回false。
if c1 %isopenthen
...
else
open c1;
endif;
--2.24 %rowcount属性,当游标或者游标变量打开时,%rowcount属性置零。
-- 在第一次提取之前,它的值为0,其后为到该次提取为止所提取的次数。
loop
fetch c1 into v_ename,v_sal;
if c1 %rowcount>10then
...;
endif;
endloop;
--综合例子1:输出薪金总额(薪水+奖金)高于2000的员工号、员工姓名和工资。
declare
v_empno emp.empno%type;
v_ename emp.ename%type;
v_sal emp.sal%type;
cursor c_emp isselect empno, ename, sal from emp where sal+nvl(comm,0)>2000;
begin
open c_emp;
fetch c_emp into v_empno, v_ename, v_sal;
if c_emp%foundthen
dbms_output.put_line('employees are found with v_sal greater than 2000');
elsif c_emp%notfoundthen
dbms_output.put_line('all employee are earning less than or equal to 2000');
endif;
if c_emp%isopenthen
dbms_output.put_line('cursor is already open');
close c_emp;
endif;
open c_emp;
loop
fetch c_emp into v_empno, v_ename, v_sal;
exitwhen c_emp%notfound;
dbms_output.put_line(v_empno||','||v_ename||','||v_sal);
endloop;
dbms_output.put_line('no of employees are '||c_emp%rowcount);
close c_emp;
end;
--综合例子2:统计每一个部门的工资总额,并按部门显示。
declare
cursor c_dept isselect * from dept orderby deptno;
cursor c_emp(p_dept emp.deptno%type) isselect ename, sal from emp where deptno = p_dept orderby ename;--带参数的游标,可以想象成java中带参数的方法
r_dept dept%rowtype;
v_ename emp.ename%type;
v_salary emp.sal%type;
v_tot_salary emp.sal%type;--用于存储每一个部门的工资总额
begin
open c_dept;--打开游标c_dept
loop
fetch c_dept into r_dept;--fetch游标c_dept
exitwhen c_dept%notfound;
dbms_output.put_line('##########################department:'||r_dept.deptno||'-'||r_dept.dname);
v_tot_salary:=0;
open c_emp(r_dept.deptno);--打开游标c_emp
loop
fetch c_emp into v_ename, v_salary;
exitwhen c_emp%notfound;
dbms_output.put_line('name:'||v_ename||' salary:'||v_salary);
v_tot_salary := v_tot_salary+v_salary;
endloop;
close c_emp;--关闭游标c_emp
dbms_output.put_line('##toltal salary for dept:'||v_tot_salary);
endloop;
close c_dept;--关闭游标c_dept
end;
--2.3 cursor for循环:不需要显式打开、关闭和提取数据。很好用的东东~~
--综上所述:
1、打开游标
2、开始循环
3、从游标中取值
4、检查哪一行被返回
5、处理
6、关闭循环
7、关闭游标
cursor for的语法:
for record_name in(cursor_name[parameter[,parameter]...])
loop
statements
endloop;
--cursor for循环的例子1:统计每一个部门的工资总额,并按部门显示。
declare
cursor c_dept isselect deptno,dname from dept orderby deptno;
cursor c_emp(p_dept emp.deptno%type) isselect ename, sal from emp where deptno = p_dept orderby ename;--带参数的游标,可以想象成java中带参数的方法
v_tot_salary emp.sal%type;--用于存储每一个部门的工资总额
begin
for r_dept in c_dept loop
dbms_output.put_line('##########################department:'||r_dept.deptno||'-'||r_dept.dname);
v_tot_salary := 0;
for r_emp in c_emp(r_dept.deptno) loop
dbms_output.put_line('name:'||r_emp.ename||' salary:'||r_emp.sal);
v_tot_salary := v_tot_salary+r_emp.sal;
endloop;
dbms_output.put_line('##toltal salary for dept:'||v_tot_salary);
endloop;
end;
--cursor for循环的例子2:显示员工姓名和员工所在的部门名称
--方式1:自己写的,for循环嵌套for循环,效率不高,用时0.016s
declare
cursor c_dept isselect deptno, dname from dept orderby deptno;
cursor c_emp(v_deptno emp.deptno%type) isselect ename from emp where deptno=v_deptno;
begin
for r_dept in c_dept loop
for r_emp in c_emp(r_dept.deptno) loop
dbms_output.put_line('employee name:'||r_emp.ename||' in department '||r_dept.dname);
endloop;
endloop;
end;
--方式2:书上的数据量太小,用时没显示
declare
type ename_type istableof emp.ename%typenotnullindexbybinary_integer;--定义一个index_by类型的集合,从这里体会和嵌套表的区别
type dname_type istableof dept.dname%typeindexbybinary_integer;
enamelist ename_type;
dnamelist dname_type;
subscript binary_integer:=0;
begin
forvarin(select ename, dname from emp, dept where emp.deptno=dept.deptno) loop--等值连接的思想要好好用
enamelist(subscript):=var.ename;
dnamelist(subscript):=var.dname;
dbms_output.put_line('employee name is '||enamelist(subscript)||', department name is '||dnamelist(subscript));
subscript := subscript+1;
endloop;
dbms_output.put_line('total number of employees are '||subscript);
end;
--方式3 不熟,说明还不太清楚三种集合类型的区别,多看
declare
type ename_type istableof emp.ename%typenotnull;--定义一个嵌套表类型的集合,从这里体会和index_by的区别
type dname_type istableof dept.dname%type;
enamelist ename_type;
dnamelist dname_type;
subscript binary_integer:=0;
begin
forvarin(select ename, dname from emp, dept where emp.deptno=dept.deptno) loop--等值连接的思想要好好用
enamelist:=ename_type(var.ename);--给嵌套表赋值
dnamelist:=dname_type(var.dname);
dbms_output.put_line('employee name is '||enamelist(1)||', department name is '||dnamelist(1));--参数为1,必须为1,只有一条记录
subscript := subscript+1;
endloop;
dbms_output.put_line('total number of employees are '||subscript);
end;
--2.3 隐式游标也称SQL游标,用来处理insert、update、delete和返回一行的select into语句,
--一个SQL游标不管打开还是关闭都不能用open、fetch和close来操作。它和显示游标一样,有属性:%found、%notfound、%isopen、%rowcount。
--2.3.1 SQL%found属性:SQL数据操作语句执行之前,%found值为null。执行成功对记录产生影响,%found为true,否则%found为false。
--SQL%found例子:理解select into中的SQL游标
declare
v_empno emp.empno%type:=&v_empno;
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno=v_empno;
ifSQL%foundthen
dbms_output.put_line(v_emp.empno);
dbms_output.put_line(v_emp.ename);
endif;
end;
--2.3.2 SQL%notfound属性:%found的逻辑取反
--例子:理解update中的SQL游标
declare
v_empno emp.empno%type:=7000;
begin
update emp1 set ename='sem 108'where empno=v_empno;
ifSQL%foundthen
dbms_output.put_line('Deleted OK!');
endif;
ifSQL%notfoundthen
dbms_output.put_line('employee number:'||v_empno||' does not exists!');
endif;
end;
--2.3.3 SQL%isopen属性:当相关的SQL语句执行完以后,oracle自动关闭SQL游标。因此%isopen总是false。
--2.3.4 SQL%rowcount属性:%rowcount的值返回所作用的行的数目。
--例子:理解delete中的SQL游标
declare
v_deptno emp.deptno%type := 10;
begin
delete emp1 where deptno=v_deptno;
ifSQL%foundthen
dbms_output.put_line(SQL%rowcount||' Deleted OK!');
endif;
ifSQL%notfoundthen
dbms_output.put_line('employee number:'||v_deptno||' does not exists!');
endif;
end;
--例子:复制表emp中的数据到表emp1中
--drop table emp1;
--create table emp1 as select * from emp where 1=2;
--select * from emp1;
declare
cursor c_emp isselect * from emp;
v_emp emp%rowtype;
begin
open c_emp;
fetch c_emp into v_emp;
while c_emp%found
loop
fetch c_emp into v_emp;
insertinto emp1 values(v_emp.empno, v_emp.job, v_emp.mgr, v_emp.mgr, v_emp.hiredate, v_emp.sal, v_emp.comm, v_emp.deptno);
ifSQL%foundthen
dbms_output.put_line('empno number:'||v_emp.empno||' inserted OK! '||SQL%rowcount||' row inserted OK!');
endif;
endloop;
close c_emp;
end;
--2.4 异常处理概述
在运行过程中,发生错去时成为抛出异常。有三种抛出异常:通过PL/SQL运行时、使用raise语句、调用raise_application_error。
--异常处理的语法形式:
exception
when exception_name then
codefor handing exception_name
[when another_exception then
codefor handing another_exception]
[whenothersthen
codefor handing any other exception.]
--例子:增加异常处理
declare
v_empno emp.empno%type:=&v_empno;
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno=v_empno;
ifSQL%foundthen
dbms_output.put_line(v_emp.empno);
dbms_output.put_line(v_emp.ename);
endif;
exception--加上异常处理
--如果没有找到记录则返回相应信息
when no_data_found then--no_data_found是oracle预定义的异常名
dbms_output.put_line('have not found record');
--如果找到的记录不止一条,则返回相应信息
when too_many_rows then--too_many_rows是oracle预定义的异常名
dbms_output.put_line('too many record');
end;
--2.5 异常处理种类
--2.5.1 预定义异常
--例子:处理zero_divide异常
declare
pi constantnumber(9,7):=3.1419526;
radius integer(5);
area number(14,2);
some_variable number(14,2);
begin
radius:=4;
some_variable:=1/(radius-4);
area:=pi*power(radius,2);
exception
when zero_divide then--zero_divide为oracle预定义异常,用0作除数
dbms_output.put_line('The radius is zero');
whenothersthen
dbms_output.put_line('some problem in execution');
end;
--2.5.2 自定义异常
--语法:
declare
exception_name exception;
begin
statements;
raise <exception_name>
exception
when <exception_name> then
end;
--例子:自定义异常,当输入员工号小于7000或大于8000时,提示输入超出正常范围。
declare
v_empno emp.empno%type:=&v_empno;
v_emp emp%rowtype;
empno_out_of_range exception;--定义异常empno_out_of_range
begin
if v_empno<=7000or v_empno>=8000then
raise empno_out_of_range;--抛出异常
endif;
select * into v_emp from emp where empno=v_empno;
ifSQL%foundthen
dbms_output.put_line(v_emp.empno);
dbms_output.put_line(v_emp.ename);
endif;
exception--处理异常
when empno_out_of_range then
dbms_output.put_line('empno is out of range');
when no_data_found then
dbms_output.put_line('not found record');
when too_many_rows then
dbms_output.put_line('too many record');
end;
--2.5.3 SQLCODE和SQLERRM,分别返回oracle的错误代码和错误信息
--在一个异常中,SQLCODE返回Oracle错误的序号,而SQLERRM返回的是相应的错误消息,错误消息首先显示的是错误代码。
--例子:查看异常too_many_rows和SQLCODE和SQLERRM
declare
v_emp emp%rowtype;
empno_out_of_range exception;
begin
select * into v_emp from emp;
ifSQL%foundthen
dbms_output.put_line(v_emp.empno);
dbms_output.put_line(v_emp.ename);
endif;
exception
when empno_out_of_range then
dbms_output.put_line('empno is out of range');
when no_data_found then
dbms_output.put_line('not found record');
when too_many_rows then
dbms_output.put_line('too many record');
dbms_output.put_line(SQLCode||','||SQLERRM);
end;
--2.5.4 给自定义错误标注号码
--语法形式:
raise_application_error(error_name,error_message);
--例子:
exception
when empno_out_of_range then
raise_application_error(-20010, 'empno is out of range');
--课后习题
2. 用显示游标编写程序,程序的功能使计算每一个部门的平均工资
--select deptno,avg(sal) from emp group by deptno order by deptno;
--思路:显示部门名称、平均工资
--方法1 菜鸟的写法
declare
cursor c_dept_deptno isselect deptno from dept;
cursor c_emp(v_deptno emp.deptno%type) isselect * from emp where deptno=v_deptno;
v_deptno dept.deptno%type;
v_emp emp%rowtype;
v_dname dept.dname%type;
v_avgSal emp.sal%type:=0;
v_sumSal emp.sal%type:=0;
v_count binary_integer:=0;
begin
open c_dept_deptno;
loop
fetch c_dept_deptno into v_deptno;
exitwhen c_dept_deptno%notfound;
open c_emp(v_deptno);
loop
fetch c_emp into v_emp;
exitwhen c_emp%notfound;
v_sumSal:=v_sumSal+v_emp.sal;
v_count:=v_count+1;
endloop;
close c_emp;
if(v_count!=0) then
v_avgSal:=v_sumSal/v_count;
v_sumSal:=0;
v_count:=0;
dbms_output.put_line(v_emp.deptno||':'||v_avgSal);
endif;
endloop;
close c_dept_deptno;
end;
--方法2 比菜鸟好一点~~ 用到cursor for循环
declare
cursor c_emp_deptno isselectdistinct e.deptno from emp e;
cursor c_dname_sal(p_deptno emp.deptno%type) isselect d.dname,e.sal from dept d, emp e where d.deptno=e.deptno and d.deptno=p_deptno;
v_dname dept.dname%type;
v_avgSal emp.sal%type:=0;
v_sumSal emp.sal%type:=0;
v_count binary_integer:=0;
begin
for r_emp_deptno in c_emp_deptno loop
dbms_output.put_line(r_emp_deptno.deptno);
for r_dname_sal in c_dname_sal(r_emp_deptno.deptno) loop
v_sumSal := v_sumSal+r_dname_sal.sal;
v_count := v_count+1;
v_dname := r_dname_sal.dname;
endloop;
v_avgSal := v_sumSal/v_count;
dbms_output.put_line('部门 '||v_dname||' 的平均工资为 '||v_avgSal);
v_sumSal :=0;
v_count :=0;
v_avgSal:=0;
endloop;
end;