(oracle数据库基础)第六章 游标和异常管理
来源：互联网 发布：perl语言编程pdf 编辑：程序博客网 时间：2024/05/22 14:48
游标的出现是为了解决从某一结果集中逐一读取一条记录。
--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_enameisselect enamefrom emp;
--例子declare2：声明游标，对应的集合是表emp中所有的员工信息
declare
cursor c_empisselect *from emp;
--例子declare3：声明游标，对应的集合是表emp中根据输入员工号而确定的员工信息
declare
cursor c_emp(v_empnonumber(4))isselect *from empwhere empno=v_empno;
--*****2.12 open语句，语法：*****--
open cursor_name;
--*****2.13 fetch语句从游标提取一行数据使用，每一次提取数据后，游标都指向结果集的下一行*****--
语法：fetch cursor_nameintovariable[,variable,...]
--例子fetch1：显示例子declare1中第一行信息
declare
cursor c_emp_enameisselect enamefrom emp;
v_ename emp.ename%type;
begin
open c_emp_ename;
fetch c_emp_enameinto v_ename;
dbms_output.put_line(v_ename);
end;
--例子fetch2：显示例子declare1中游标所有信息
declare
cursor c_emp_enameisselect enamefrom emp;
v_ename emp.ename%type;
v_count binary_integer;
begin
selectcount(rowid)into v_countfrom 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 iin1..v_countloop--in后面的语句只有两个"."。
fetch c_emp_enameinto 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_salisselect salfrom emp;
v_sal emp.sal%type;
v_count binary_integer;
v_sum number:=0;
begin
selectcount(rowid)into v_countfrom emp;
open c_emp_sal;
for iin1..v_countloop
fetch c_emp_salinto 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_empisselect empno,jobfrom emp1;
v_empno emp1.empno%type;
v_job emp1.job%type;
v_count binary_integer;
begin
selectcount(rowid)into v_countfrom emp1;
open c_emp;--开启游标
for iin1..v_countloop--loop开始
fetch c_empinto v_empno,v_job;--从游标中取值
case v_job--case开始
when'CLERK'then
update emp1 eset e.sal=sal*(1+0.1)where e.empno=v_empno;
dbms_output.put_line('CLERK OK!');
when'SALESMAM'then
update emp1 eset e.sal=sal*(1+0.2)where e.empno=v_empno;
dbms_output.put_line('SALESMAM OK!');
when'MANAGER'then
update emp1 eset e.sal=sal*(1+0.3)where e.empno=v_empno;
dbms_output.put_line('MANAGER OK!');
when'ANALYST'then
update emp1 eset e.sal=sal*(1+0.4)where e.empno=v_empno;
dbms_output.put_line('ANALYST OK!');
else
update emp1 eset 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_empisselect *from emp;
v_emp emp%rowtype;
v_count binary_integer;
begin
selectcount(rowid)into v_countfrom emp;
open c_emp;
for iin1..v_countloop
fetch c_empinto 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 c1into v_ename,v_sal;
if c1 %foundthen
...;
else
...;
exit;
endif;
endloop;
--2.22 %notfound属性与%found属性相反
loop
fetch c1into 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 c1into 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_empisselect empno, ename, salfrom empwhere sal+nvl(comm,0)>2000;
begin
open c_emp;
fetch c_empinto 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_empinto 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_deptisselect *from deptorderby deptno;
cursor c_emp(p_dept emp.deptno%type)isselect ename, salfrom empwhere deptno = p_deptorderby 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_deptinto 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_empinto 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_namein(cursor_name[parameter[,parameter]...])
loop
statements
endloop;
--cursor for循环的例子1：统计每一个部门的工资总额，并按部门显示。
declare
cursor c_deptisselect deptno,dnamefrom deptorderby deptno;
cursor c_emp(p_dept emp.deptno%type)isselect ename, salfrom empwhere deptno = p_deptorderby ename;--带参数的游标，可以想象成java中带参数的方法
v_tot_salary emp.sal%type;--用于存储每一个部门的工资总额
begin
for r_deptin c_deptloop
dbms_output.put_line('##########################department：'||r_dept.deptno||'-'||r_dept.dname);
v_tot_salary :=0;
for r_empin 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_deptisselect deptno, dnamefrom deptorderby deptno;
cursor c_emp(v_deptno emp.deptno%type)isselect enamefrom empwhere deptno=v_deptno;
begin
for r_deptin c_deptloop
for r_empin 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_typeistableof emp.ename%typenotnullindexbybinary_integer;--定义一个index_by类型的集合，从这里体会和嵌套表的区别
type dname_typeistableof dept.dname%typeindexbybinary_integer;
enamelist ename_type;
dnamelist dname_type;
subscript binary_integer:=0;
begin
forvarin(select ename, dname from emp, deptwhere 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_typeistableof emp.ename%typenotnull;--定义一个嵌套表类型的集合，从这里体会和index_by的区别
type dname_typeistableof dept.dname%type;
enamelist ename_type;
dnamelist dname_type;
subscript binary_integer:=0;
begin
forvarin(select ename, dname from emp, deptwhere 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_empfrom empwhere 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 emp1set 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 emp1where 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_empisselect *from emp;
v_emp emp%rowtype;
begin
open c_emp;
fetch c_empinto v_emp;
while c_emp%found
loop
fetch c_empinto v_emp;
insertinto emp1values(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_namethen
codefor handing exception_name
[when another_exceptionthen
codefor handing another_exception]
[whenothersthen
codefor handingany other exception.]
--例子:增加异常处理
declare
v_empno emp.empno%type:=&v_empno;
v_emp emp%rowtype;
begin
select *into v_empfrom empwhere empno=v_empno;
ifSQL%foundthen
dbms_output.put_line(v_emp.empno);
dbms_output.put_line(v_emp.ename);
endif;
exception--加上异常处理
--如果没有找到记录则返回相应信息
when no_data_foundthen--no_data_found是oracle预定义的异常名
dbms_output.put_line('have not found record');
--如果找到的记录不止一条，则返回相应信息
when too_many_rowsthen--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_variablenumber(14,2);
begin
radius:=4;
some_variable:=1/(radius-4);
area:=pi*power(radius,2);
exception
when zero_dividethen--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_rangeexception;--定义异常empno_out_of_range
begin
if v_empno<=7000or v_empno>=8000then
raise empno_out_of_range;--抛出异常
endif;
select *into v_empfrom empwhere 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_rangethen
dbms_output.put_line('empno is out of range');
when no_data_foundthen
dbms_output.put_line('not found record');
when too_many_rowsthen
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_rangeexception;
begin
select *into v_empfrom emp;
ifSQL%foundthen
dbms_output.put_line(v_emp.empno);
dbms_output.put_line(v_emp.ename);
endif;
exception
when empno_out_of_rangethen
dbms_output.put_line('empno is out of range');
when no_data_foundthen
dbms_output.put_line('not found record');
when too_many_rowsthen
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_rangethen
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_deptnoisselect deptnofrom dept;
cursor c_emp(v_deptno emp.deptno%type)isselect *from empwhere 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_deptnointo v_deptno;
exitwhen c_dept_deptno%notfound;
open c_emp(v_deptno);
loop
fetch c_empinto 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_deptnoisselectdistinct e.deptnofrom emp e;
cursor c_dname_sal(p_deptno emp.deptno%type)isselect d.dname,e.salfrom dept d, emp ewhere d.deptno=e.deptnoand 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_deptnoin c_emp_deptnoloop
dbms_output.put_line(r_emp_deptno.deptno);
for r_dname_salin 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;
- (oracle数据库基础)第六章 游标和异常管理
- ORACLE数据库 游标管理基础
- ORACLE数据库 游标管理基础
- 异常和游标管理
- 异常和游标管理
- 源码-Oracle数据库管理-第十五章-触发器和游标-Part 2（理解游标）
- Oracle--游标和异常处理
- 源码-Oracle数据库管理-第十五章-触发器和游标-Part 1（理解触发器）
- 源码-Oracle数据库管理-第十五章-触发器和游标-Part 2（理解触发器）
- 源码-Oracle数据库管理-第十五章-触发器和游标-Part 3（理解触发器）
- 源码-Oracle数据库管理-第十五章-触发器和游标-Part 4（理解触发器）
- 源码-Oracle数据库管理-第六章-索引和约束-约束部分-Part 1
- 源码-Oracle数据库管理-第六章-索引和约束-约束部分-Part 2
- 源码-Oracle数据库管理-第六章-索引和约束-约束部分-Part 3
- 第六章 数据库管理
- （oracle数据库基础）第二章 表管理和查询
- oracle 数据库管理基础
- Oracle 游标 隐式游标,显示游标,游标循环,动态SELECT语句和动态游标,异常处理,自定义异常
- 宏展开出错
- 3D图形学矩阵完全解析傻瓜版
- 在PC机上BC45编译运行ucosii方法
- 浅谈指针使用中注意事项
- EXT GWT报错
- (oracle数据库基础)第六章 游标和异常管理
- 利用WebService制作天气预报查询
- Per Pixel Lighting [Part 2]
- .Net反编译工具Reflector .
- oracle 数据备份服务器安装 之乱码篇
- Struts2中实现多文件上传于下载
- 详解java三目条件运算符
- android 关于特定短信电话拦截
- hdoj1008
