游标和异常处理 oracle.docx
- 文档编号:6813079
- 上传时间:2023-01-10
- 格式:DOCX
- 页数:28
- 大小:130.51KB
游标和异常处理 oracle.docx
《游标和异常处理 oracle.docx》由会员分享,可在线阅读,更多相关《游标和异常处理 oracle.docx(28页珍藏版)》请在冰豆网上搜索。
游标和异常处理oracle
游标和异常处理
游标的概念
游标是SQL的一个内存工作区,由系统或用户以变量的形式定义。
游标的作用就是用于临时存储从数据库中提取的数据块。
在某些情况下,需要把数据从存放在磁盘的表中调到计算机内存中进行处理,最后将处理结果显示出来或最终写回数据库。
这样数据处理的速度才会提高,否则频繁的磁盘数据交换会降低效率。
游标有两种类型:
显式游标和隐式游标。
在前述程序中用到的SELECT...INTO...查询语句,一次只能从数据库中提取一行数据,对于这种形式的查询和DML操作,系统都会使用一个隐式游标。
但是如果要提取多行数据,就要由程序员定义一个显式游标,并通过与游标有关的语句进行处理。
显式游标对应一个返回结果为多行多列的SELECT语句。
游标一旦打开,数据就从数据库中传送到游标变量中,然后应用程序再从游标变量中分解出需要的数据,并进行处理。
隐式游标
如前所述,DML操作和单行SELECT语句会使用隐式游标,它们是:
*插入操作:
INSERT。
*更新操作:
UPDATE。
*删除操作:
DELETE。
*单行查询操作:
SELECT...INTO...。
当系统使用一个隐式游标时,可以通过隐式游标的属性来了解操作的状态和结果,进而控制程序的流程。
隐式游标可以使用名字SQL来访问,但要注意,通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。
所以通常在刚刚执行完操作之后,立即使用SQL游标名来访问属性。
游标的属性有四种,如下表所示。
范例:
使用隐式游标的属性,判断对雇员工资的修改是否成功。
SETSERVEROUTPUTON
BEGIN
UPDATEempSETsal=sal+100WHEREempno=1234;
IFSQL%FOUNDTHEN
DBMS_OUTPUT.PUT_LINE('成功修改雇员工资!
');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE('修改雇员工资失败!
');
ENDIF;
END;
说明:
本例中,通过SQL%FOUND属性判断修改是否成功,并给出相应信息。
显式游标
游标的定义和操作
游标的使用分成以下4个步骤。
1.声明游标
在DECLEAR部分按以下格式声明游标:
CURSOR游标名[(参数1数据类型[,参数2数据类型...])]
ISSELECT语句;
参数是可选部分,所定义的参数可以出现在SELECT语句的WHERE子句中。
如果定义了参数,则必须在打开游标时传递相应的实际参数。
SELECT语句是对表或视图的查询语句,甚至也可以是联合查询。
可以带WHERE条件、ORDERBY或GROUPBY等子句,但不能使用INTO子句。
在SELECT语句中可以使用在定义游标之前定义的变量。
2.打开游标
在可执行部分,按以下格式打开游标:
OPEN游标名[(实际参数1[,实际参数2...])];
打开游标时,SELECT语句的查询结果就被传送到了游标工作区。
3.提取数据
在可执行部分,按以下格式将游标工作区中的数据取到变量中。
提取操作必须在打开游标之后进行。
FETCH游标名INTO变量名1[,变量名2...];
或
FETCH游标名INTO记录变量;
游标打开后有一个指针指向数据区,FETCH语句一次返回指针所指的一行数据,要返回多行需重复执行,可以使用循环语句来实现。
控制循环可以通过判断游标的属性来进行。
下面对这两种格式进行说明:
第一种格式中的变量名是用来从游标中接收数据的变量,需要事先定义。
变量的个数和类型应与SELECT语句中的字段变量的个数和类型一致。
第二种格式一次将一行数据取到记录变量中,需要使用%ROWTYPE事先定义记录变量,这种形式使用起来比较方便,不必分别定义和使用多个变量。
定义记录变量的方法如下:
变量名表名|游标名%ROWTYPE;
其中的表必须存在,游标名也必须先定义。
4.关闭游标
CLOSE游标名;
显式游标打开后,必须显式地关闭。
游标一旦关闭,游标占用的资源就被释放,游标变成无效,必须重新打开才能使用。
范例:
以下是使用显式游标的一个简单练习。
用游标提取emp表中7788雇员的名称和职务。
(yb1.sql)
SETSERVEROUTPUTON
DECLARE
v_enameVARCHAR2(10);
v_jobVARCHAR2(10);
CURSORemp_cursorIS
SELECTename,jobFROMempWHEREempno=7788;
BEGIN
OPENemp_cursor;
FETCHemp_cursorINTOv_ename,v_job;
DBMS_OUTPUT.PUT_LINE(v_ename||','||v_job);
CLOSEemp_cursor;
END;
说明:
该程序通过定义游标emp_cursor,提取并显示雇员7788的名称和职务。
作为对以上例子的改进,在以下训练中采用了记录变量。
范例:
用游标提取emp表中7788雇员的姓名、职务和工资。
(yb2.sql)
SETSERVEROUTPUTON
DECLARE
CURSORemp_cursorISSELECTename,job,salFROMempWHEREempno=7788;
emp_recordemp_cursor%ROWTYPE;
BEGIN
OPENemp_cursor;
FETCHemp_cursorINTOemp_record;
DBMS_OUTPUT.PUT_LINE(emp_record.ename||','||emp_record.job||','||to_char(emp_record.sal));
CLOSEemp_cursor;
END;
说明:
实例中使用记录变量来接收数据,记录变量由游标变量定义,需要出现在游标定义之后。
注意:
可通过以下形式获得记录变量的内容:
记录变量名.字段名。
范例:
显示工资最高的前3名雇员的名称和工资。
(yb3.sql)
SETSERVEROUTPUTON
DECLARE
v_enameVARCHAR2(10);
v_salNUMBER(5);
CURSORemp_cursorISSELECTename,salFROMempORDERBYsalDESC;
BEGIN
OPENemp_cursor;
FORIIN1..3LOOP
FETCHemp_cursorINTOv_ename,v_sal;
DBMS_OUTPUT.PUT_LINE(v_ename||','||v_sal);
ENDLOOP;
CLOSEemp_cursor;
END;
说明:
该程序在游标定义中使用了ORDERBY子句进行排序,并使用循环语句来提取多行数据。
游标循环
范例:
使用特殊的FOR循环形式显示全部雇员的编号和名称。
(yb4.sql)
SETSERVEROUTPUTON
DECLARE
CURSORemp_cursorIS
SELECTempno,enameFROMemp;
BEGIN
FORemp_recordINemp_cursorLOOP
DBMS_OUTPUT.PUT_LINE(Emp_record.empno||Emp_record.ename);
ENDLOOP;
END;
说明:
可以看到该循环形式非常简单,隐含了记录变量的定义、游标的打开、提取和关闭过程。
Emp_record为隐含定义的记录变量,循环的执行次数与游标取得的数据的行数相一致。
范例:
另一种形式的游标循环。
(yb5.sql)
SETSERVEROUTPUTON
BEGIN
FORreIN(SELECTenameFROMEMP)LOOP
DBMS_OUTPUT.PUT_LINE(re.ename);
ENDLOOP;
END;
说明:
该种形式更为简单,省略了游标的定义,游标的SELECT查询语句在循环中直接出现。
显式游标属性
虽然可以使用前面的形式获得游标数据,但是在游标定义以后使用它的一些属性来进行结构控制是一种更为灵活的方法。
显式游标的属性如下表所示。
可按照以下形式取得游标的属性:
游标名%属性
要判断游标emp_cursor是否处于打开状态,可以使用属性emp_cursor%ISOPEN。
如果游标已经打开,则返回值为“真”,否则为“假”。
具体可参照以下的训练。
范例:
使用游标的属性练习。
(yb6.sql)
SETSERVEROUTPUTON
DECLARE
v_enameVARCHAR2(10);
CURSORemp_cursorIS
SELECTenameFROMemp;
BEGIN
OPENemp_cursor;
IFemp_cursor%ISOPENTHEN
LOOP
FETCHemp_cursorINTOv_ename;
EXITWHENemp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(to_char(emp_cursor%ROWCOUNT)||'-'||v_ename);
ENDLOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('用户信息:
游标没有打开!
');
ENDIF;
CLOSEemp_cursor;
END;
说明:
本例使用emp_cursor%ISOPEN判断游标是否打开;使用emp_cursor%ROWCOUNT获得到目前为止FETCH语句返回的数据行数并输出;使用循环来获取数据,在循环体中使用FETCH语句;使用emp_cursor%NOTFOUND判断FETCH语句是否成功执行,当FETCH语句失败时说明数据已经取完,退出循环。
练习:
去掉OPENemp_cursor;语句,重新执行以上程序。
游标参数的传递(了解)
范例:
带参数的游标。
(yb7.sql)
SETSERVEROUTPUTON
DECLARE
v_empnoNUMBER(5);
v_enameVARCHAR2(10);
CURSORemp_cursor(p_deptnoNUMBER,p_jobVARCHAR2)IS
SELECTempno,enameFROMemp
WHEREdeptno=p_deptnoANDjob=p_job;
BEGIN
OPENemp_cursor(10,'CLERK');
LOOP
FETCHemp_cursorINTOv_empno,v_ename;
EXITWHENemp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename);
ENDLOOP;
END;
说明:
游标emp_cursor定义了两个参数:
p_deptno代表部门编号,p_job代表职务。
语句OPENemp_cursor(10,'CLERK')传递了两个参数值给游标,即部门为10、职务为CLERK,所以游标查询的内容是部门10的职务为CLERK的雇员。
循环部分用于显示查询的内容。
练习:
修改Open语句的参数:
部门号为20、职务为ANALYST,并重新执行。
也可以通过变量向游标传递参数,但变量需要先于游标定义,并在游标打开之前赋值。
对以上例子重新改动如下:
范例:
通过变量传递参数给游标。
(yb8.sql)
SETSERVEROUTPUTON
DECLARE
v_empnoNUMBER(5);
v_enameVARCHAR2(10);
v_deptnoNUMBER(5);
v_jobVARCHAR2(10);
CURSORemp_cursorIS
SELECTempno,enameFROMemp
WHEREdeptno=v_deptnoANDjob=v_job;
BEGIN
v_deptno:
=10;
v_job:
='CLERK';
OPENemp_cursor;
LOOP
FETCHemp_cursorINTOv_empno,v_ename;
EXITWHENemp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename);
ENDLOOP;
END;
说明:
该程序与前一程序实现相同的功能。
利用游标删除和修改数据的时候要注意:
UPDATE表名SET…WHERECURRENTOF游标名;
DELETE表名WHERECURRENTOF游标名;
范例:
定义游标emp_cur。
通过使用游标,根据职务调整雇员的工资(yb9.sql)
SETSERVEROUTPUTON
DECLARE
v_jobemp.job%TYPE;
CURSORemp_cur
IS
SELECTjob
FROMemp
FORUPDATE;
BEGIN
OPENemp_cur;
LOOP
FETCHemp_curINTOv_job;
EXITWHENemp_cur%NOTFOUND;
CASE
WHENv_job='CLERK'THEN
updateempsetsal=sal+50wherecurrentofemp_cur;
WHENv_job='SALESMAN'ORv_job='ANALYST'THEN
updateempsetsal=sal+40wherecurrentofemp_cur;
ELSE
updateempsetsal=sal+10wherecurrentofemp_cur;
ENDCASE;
ENDLOOP;
COMMIT;
END;
/
范例:
用游标For循环,实现打印某一职务(输入一职务)的雇员的雇员编号和雇员姓名。
(yb10.sql)可参考yb4.sql
DECLARE
v_jobemp.job%TYPE;
CURSORemp_cursorIS
SELECTempno,enameFROMempWHEREjob=v_job;
BEGIN
v_job:
='&v_job';
FORemp_recordINemp_cursorLOOP
DBMS_OUTPUT.PUT_LINE(Emp_record.empno||Emp_record.ename);
ENDLOOP;
END;
异常处理
错误处理
错误处理部分位于程序的可执行部分之后,是由WHEN语句引导的多个分支构成的。
错误处理的语法如下:
EXCEPTION
WHEN错误1[OR错误2]THEN
语句序列1;
WHEN错误3[OR错误4]THEN
语句序列2;
WHENOTHERS
语句序列n;
END;
其中:
错误是在标准包中由系统预定义的标准错误,或是由用户在程序的说明部分自定义的错误,参见下一节系统预定义的错误类型。
语句序列就是不同分支的错误处理部分。
凡是出现在WHEN后面的错误都是可以捕捉到的错误,其他未被捕捉到的错误,将在WHENOTHERS部分进行统一处理,OTHERS必须是EXCEPTION部分的最后一个错误处理分支。
如要在该分支中进一步判断错误种类,可以通过使用预定义函数SQLCODE()和SQLERRM()来获得系统错误号和错误信息。
如果在程序的子块中发生了错误,但子块没有错误处理部分,则错误会传递到主程序中。
下面是由于查询编号错误而引起系统预定义异常的例子。
范例:
查询编号为1234的雇员名字。
SETSERVEROUTPUTON
DECLARE
v_nameVARCHAR2(10);
BEGIN
SELECTename
INTOv_name
FROMemp
WHEREempno=1234;
DBMS_OUTPUT.PUT_LINE('该雇员名字为:
'||v_name);
EXCEPTION
WHENNO_DATA_FOUNDTHEN
DBMS_OUTPUT.PUT_LINE('编号错误,没有找到相应雇员!
');
WHENOTHERSTHEN
DBMS_OUTPUT.PUT_LINE('发生其他错误!
');
END;
说明:
在以上查询中,因为编号为1234的雇员不存在,所以将发生类型为“NO_DATA_FOUND”的异常。
“NO_DATA_FOUND”是系统预定义的错误类型,EXCEPTION部分下的WHEN语句将捕捉到该异常,并执行相应代码部分。
在本例中,输出用户自定义的错误信息“编号错误,没有找到相应雇员!
”。
如果发生其他类型的错误,将执行OTHERS条件下的代码部分,显示“发生其他错误!
”
范例:
由程序代码显示系统错误。
SETSERVEROUTPUTON
DECLARE
v_tempNUMBER(5):
=1;
BEGIN
v_temp:
=v_temp/0;
EXCEPTION
WHENOTHERSTHEN
DBMS_OUTPUT.PUT_LINE('发生系统错误!
');
DBMS_OUTPUT.PUT_LINE('错误代码:
'||SQLCODE());
DBMS_OUTPUT.PUT_LINE('错误信息:
'||SQLERRM());
END;
说明:
程序运行中发生除零错误,由WHENOTHERS捕捉到,执行用户自己的输出语句显示错误信息,然后正常结束。
在错误处理部分使用了预定义函数SQLCODE()和SQLERRM()来进一步获得错误的代码和种类信息。
预定义错误(了解)
Oracle的系统错误很多,但只有一部分常见错误在标准包中予以定义。
定义的错误可以在EXCEPTION部分通过标准的错误名来进行判断,并进行异常处理。
常见的系统预定义异常如下表所示。
比如,如果程序向表的主键列插入重复值,则将发生DUP_VAL_ON_INDEX错误。
如果一个系统错误没有在标准包中定义,则需要在说明部分定义,语法如下:
错误名EXCEPTION;
定义后使用PRAGMAEXCEPTION_INIT来将一个定义的错误同一个特别的Oracle错误代码相关联,就可以同系统预定义的错误一样使用了。
语法如下:
PRAGMAEXCEPTION_INIT(错误名,-错误代码);
范例:
定义新的系统错误类型。
SETSERVEROUTPUTON
DECLARE
V_ENAMEVARCHAR2(10);
NULL_INSERT_ERROREXCEPTION;
PRAGMAEXCEPTION_INIT(NULL_INSERT_ERROR,-1400);
BEGIN
INSERTINTOEMP(EMPNO)VALUES(NULL);
EXCEPTION
WHENNULL_INSERT_ERRORTHEN
DBMS_OUTPUT.PUT_LINE('无法插入NULL值!
');
WHENOTHERSTHEN
DBMS_OUTPUT.PUT_LINE('发生其他系统错误!
');
END;
执行结果为:
无法插入NULL值!
PL/SQL过程已成功完成。
说明:
NULL_INSERT_ERROR是自定义异常,同系统错误1400相关联。
自定义异常
程序设计者可以利用引发异常的机制来进行程序设计,自己定义异常类型。
可以在声明部分定义新的异常类型,定义的语法是:
错误名EXCEPTION;
用户定义的错误不能由系统来触发,必须由程序显式地触发,触发的语法是:
RAISE错误名;
RAISE也可以用来引发模拟系统错误,比如,RAISEZERO_DIVIDE将引发模拟的除零错误。
使用RAISE_APPLICATION_ERROR函数也可以引发异常。
该函数要传递两个参数,第一个是用户自定义的错误编号,第二个参数是用户自定义的错误信息。
使用该函数引发的异常的编号应该在20000和20999之间选择。
自定义异常处理错误的方式同前。
范例:
插入新雇员,限定插入雇员的编号在7000~8000之间。
SETSERVEROUTPUTON
DECLARE
new_noNUMBER(10);
new_excp1EXCEPTION;
new_excp2EXCEPTION;
BEGIN
new_no:
=6789;
INSERTINTOemp(empno,ename)
VALUES(new_no,'小郑');
IFnew_no<7000THEN
RAISEnew_excp1;
ENDIF;
IFnew_no>8000THEN
RAISEnew_excp2;
ENDIF;
COMMIT;
EXCEPTION
WHENnew_excp1THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('雇员编号小于7000的下限!
');
WHENnew_excp2THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('雇员编号超过8000的上限!
');
END;
执行结果为:
雇员编号小于7000的下限!
PL/SQL过程已成功完成。
说明:
在此例中,自定义了两个异常:
new_excp1和new_excp2,分别代表编号小于7000和编号大于8000的错误。
在程序中通过判断编号大小,产生对应的异常,并在异常处理部分回退插入操作,然后显示相应的错误信息。
范例:
使用RAISE_APPLICATION_ERROR函数引发系统异常。
SETSERVEROUTPUTON
DECLARE
New_noNUMBER(10);
BEGIN
New_no:
=6789;
INSERTINTOemp(empno,ename)
VALUES(new_no,'JAMES');
IFnew_no<7000THEN
ROLLBACK;
RAISE_APPLICATIO
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 游标和异常处理 oracle 游标 异常 处理