游标和异常处理oracle.docx
《游标和异常处理oracle.docx》由会员分享，可在线阅读，更多相关《游标和异常处理oracle.docx（34页珍藏版）》请在冰豆网上搜索。
游标和异常处理oracle
游标和异常处理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
ROLLBA
升级会员 