第十四章 PLSQL语言基础

1第14章PL/SQL语言基础2本章内容PL/SQL概述PL/SQL基础控制结构游标异常处理314.1PL/SQL概述14.1.1PL/SQL特点PL/SQL是Oracle对标准SQL语言进行了过程化扩展的程序设计语言14.1.3PL/SQL执行过程与开发工具PL/SQL开发工具SQL*PLUSProcedureBuilderOracleForm、OracleReportsPL/SQLDeveloper4Declarev_deptnonumber;Begin

Selectdeptnointov_deptnofromempwhereempno=7844Ifv_deptno=10Then

Updateempsetsal=sal+100whereempno=7844;Else

Updateempsetsal=sal+200whereempno=7844;Endif;End;514.2PL/SQL基础14.2.1PL/SQL程序结构1.PL/SQL块的组成PL/SQL程序的基本单元是语句块一个完整的PL/SQL语句块由声明部分、执行部分和异常处理3部分组成。6定义PL/SQL语句块的语法：

DECLARE

声明部分（可选）

BEGIN

执行部分（必须）

EXCEPTION

异常处理部分（可选）

END;

声明变量、常量、游标和自定义类型描述了所要完成的处理，可以使用SQL语句和控制语句当程序在BEGIN部分出错时执行分号7Declarev_deptnonumber;Begin

Selectdeptnointov_deptnofromscott.emp

whereempno=7844;Ifv_deptno=10Then

Updatescott.empsetsal=sal+100whereempno=7844;ElseUpdatescott.empsetsal=sal+200whereempno=7844;Endif;

DBMS_OUTPUT.PUT_LINE(v_deptno);EXCEPTIONWHENNO_DATA_FOUNDTHEN

DBMS_OUTPUT.PUT_LINE('没有这个人！');END;SETSERVEROUTPUTON在SQL*Plus中需要在PL/SQLDeveloper中不需要8在SQL*PLUS中执行PL/SQL程序1.连接数据库92.输入程序，结束后输入“/”，执行程序10新建测试窗口：调试PL/SQL程序脚本在PL/SQLDEVELOPER中执行PL/SQL程序11查看结果输入程序执行程序12132.PL/SQL块分类匿名块匿名块是指动态生成，只能执行一次的块，不能由其他应用程序调用。命名块命名块是指一次编译可多次执行的PL/SQL程序，包括函数、存储过程、包、触发器。编译后放在服务器中，由应用程序或系统在特定条件下调用执行。141.字符集PL/SQL的字符集包括：大小写字母：A~Z，a~z数字：0~9空白：制表符、空格和回车数字符号：+-*/〈

〉=标点符号：~!@#$%^&*（）_|{}[]？；：,.“‘注意PL/SQL字符集不区分大小写。14.2.2PL/SQL词法单元152.标识符命名规则在PL/SQL程序中，标识符是以字母开头的，后边可以跟字母、数字、美元符号（$）、井号（#）或下划线（_），其最大长度为30个字符，并且所有字符都是有效的。16+-*/=:=赋值<><=>=<>!=~=^=()/**/<<>>%;:.‘“..@||字符串连接=>**乘方-3.分隔符174.常量值字符型文字数字型文字布尔型文字TRUE，FALSE，NULL三个值。日期型文字185.注释单行注释--多行注释以“/*”开始，以“*/”结束。1914.2.3数据类型数字类型字符类型日期/区间类型行标识类型布尔类型原始类型LOB类型引用类型记录类型集合类型%TYPE与%ROWTYPE201.数字类型：用来存储整数、实数和浮点数，常用的数值类型有NUMBER、PLS_INTEGER和BINARY_INTEGER。⑴NUMBER[(P,S)]存储整数或浮点数。其中P是精度（指数值中所有数字的个数），S是刻度(指小数点右边数字的个数)。P和S都是可选的，但是如果指定了刻度S，则必须指定精度P。例：sgradenumber(3,1)P的取值范围为1~3821

(2)BINARY_INTEGER表示-2147483647~+2147483647之间的整数发生溢出时会自动转换为Number类型。

（3）PLS_INTEGER表示范围与BINARY_INTEGER相同发生溢出时会报错BINARY_INTEGER和PLS_INTEGER所需的存储空间要比NUMBER少，运算的速度要高于NUMBER。Oracle数据库中只支持NUMBER222.字符类型变长字符串VARCHAR2定长字符串CHARPL/SQL中的字符类型与Oracle数据库中允许字符串的长度不同。类型PL/SQL中最大字节数Oracle中最大字节数VARCHAR2327674000NVARCHAR2327674000CHAR327672000NCHAR327672000LONG327602GB23

9．记录类型记录类型的内部含有成员分量，类似于C语言中的结构体，声明语法如下DECLARETYPE记录名ISRECORD(字段名1类型,……

字段名n类型);

声明记录类型的变量：变量名记录名引用元素格式:记录变量名.字段名,如teacher1.tnameDeclareTypeJS

IsRecord(tidchar(4),tnamevarchar2(8),tagenumber(3));teacher1JS;编号姓名年龄7900王凡367844张松47表列的数据类型发生变化怎么办？Scott.教师2411.%TYPE与%ROWTYPE%TYPE用于定义与某个变量或数据库表中某个列的数据类型一致的变量变量名模式名.表名.列名%TYPE例如，tnscott.教师.姓名%TYPE;%ROWTYPE用于定义与数据库中某个表结构一致的记录类型的变量，可用于存储表中一行记录变量名模式名.表名%ROWTYPE例如，teacher2scott.教师%ROWTYPE;引用变量中的某个字段值:变量名.字段名例如，teacher2.姓名25例，定义变量v_sal和v_emp，分别用于存储scott.emp表中编号为7844的职工的工资和7900的职工信息DECLAREv_salv_empBEGINSELECTsalFROMscott.empWHEREempno=7844;SELECT*FROMscott.empWHEREempno=7900;DBMS_OUTPUT.PUT_LINE(v_sal);DBMS_OUTPUT.PUT_LINE(v_emp);END;empnoenamesal7900徐立20007844王凤2500scott.emp.sal%TYPE;scott.emp%ROWTYPE;INTO

v_salINTOv_emp复合变量按字段分别输出v_emp.empno||v_emp.ename||v_emp.sal26练习，定义变量num和变量stu，分别用来存储sys.xuesheng表中“张红”的学号和”王娜”的所有信息，并输出

DeclarenumstuBeginSELECTsidintonumFROMsys.xueshengWHEREname=‘张红’;SELECT*intostuFROMsys.xueshengWHEREname=‘王娜’;DBMS_OUTPUT.PUT_LINE(num);DBMS_OUTPUT.PUT_LINE(stu.sid||||stu.age);END;sys.xuesheng.sid%Type;sys.xuesheng%RowType;sidnameage101张红18102王娜172728作业：P297第2题（1）（2）29作业：编写PL/SQL程序，在scott.emp和scott.dept表中查找编号empno为7844的员工的姓名ename和部门名称dname，并输出结果declarev_escott.emp.ename%type;v_dscott.dept.dname%type;beginselectename,dnamefrom

scott.emp,scott.deptwhere

emp.deptno=dept.deptnoandempno=7844;dbms_output.put_line(v_e||v_d);end;intov_e,v_d30变量声明14.2.4变量与常量1.变量与常量的定义变量名数据类型说明每行只能定义一个变量；如果加上关键字CONSTANT，则表示所定义的是一个常量，必须为它赋初值；如果定义变量时使用了NOTNULL关键字，则必须为变量赋初值；如果变量没有赋初值，则默认为NULL；例如，v2NUMBER(4)NOTNULL:=10;[CONSTANT][NOTNULL][:=默认值];DEFAULT|312.变量的作用域如果PL/SQL块相互嵌套，则在内部块中声明的变量是局部的，只能在内部块中引用，而在外部块中声明的变量是全局的，既可以在外部块中引用，也可以在内部块中引用。如果内部块与外部块中定义了同名变量，则在内部块中引用外部块的全局变量时需要使用外部块名进行标识。

32<<OUTER>>DECLAREv_enameCHAR(16);v_outerNUMBER(5);BEGINv_outer:=10;DECLAREv_enameCHAR(20);v_innerDATE;BEGINv_inner:=sysdate;v_ename:='INNERV_ENAME';OUTER.v_ename:='OUTERV_ENAME';END;DBMS_OUTPUT.PUT_LINE(v_ename);END;

输出结果为'OUTERV_ENAME'3314.2.5PL/SQL记录

1.用户定义记录类型及变量2.利用%ROWTYPE获取记录类型定义变量3.记录类型变量的应用在SELECT语句中使用记录类型变量在INSERT语句中使用记录类型变量在UPDATE语句中使用记录类型变量在DELETE语句中使用记录类型变量34（1）在SELECT语句中使用记录类型变量①在SELECTINTO

语句中使用记录类型变量例，在scott.emp表中查询姓名SMITH员工信息DECLAREv_emp

BEGIN

END;empnoenamesal7844SMITH2500scott.emp%ROWTYPE;SELECT*

INTOv_emp

FROMscott.empWHEREename=‘SMITH’;SELECTempno,ename,salINTOv_emp.empno,v_emp.ename,v_emp.sal

FROMscott.empWHEREename=‘SMITH’;个数、顺序、类型一一匹配35（2）在INSERT语句中使用记录类型变量例，向scott.dept表中插入一条新记录DECLAREv_deptBEGINv_dept.deptno:=50;v_dept.loc:=‘北京';v_dept.dname:=‘计算机';

INSERTINTOscott.deptVALUESv_dept;END;记录类型变量中分量的个数、顺序、类型应该与表中列的个数、顺序、类型完全匹配。deptnolocdname48广州研发49上海运维50北京计算机scott.dept%ROWTYPE;逐个字段赋值36(3)在UPDATE语句中使用记录类型变量例，将deptno为50的部门名称dname改为管理DECLAREv_deptscott.dept%ROWTYPE;BEGINv_dept.deptno:=50;v_dept.loc:=‘北京';v_dept.dname:=‘管理';

UPDATEscott.deptSETdname=v_dept.dnameWHEREdeptno=50;END;DELETEFROMscott.deptWHEREdeptno=v_emp.deptno;deptnolocdname48广州研发49上海运维50北京计算机管理37练习，定义变量v_sal初始值为2000，用于存储scott.emp(empno,ename,sal)表中sal列的值，向scott.emp表中插入数据，其中empno列数据为7500，ename为’JOAN’，sal为v_sal的值。然后将该表中所有sal列值小于v_sal的员工sal增加100。最后将sal列值大于v_sal的员工信息删除DECLAREv_salscott.emp.sal%TYPE:=2000;BEGININSERTINTOscott.emp(empno,ename,sal)VALUES(7500,'JOAN',v_sal);UPDATEscott.empSETsal=sal+100WHEREsal<v_sal;DELETEFROMscott.empWHEREsal>v_sal;END;在PL/SQL程序中可以动态指定变量的值例，将指定员工的工资增加400declare

v_empnonumber(4);begin

v_empno:=

updatescott.empsetsal=sal+400

whereempno=v_empno;end;38&x;在程序运行时输入在SQL*PLUS中运行3914.2.7PL/SQL中SQL语句在PL/SQL中只允许出现:

SELECT、UPDATE、DELETE、INSERT事务控制语句（COMMIT、ROLLBACK、SAVEPOINT）注意DDL语句不可以直接使用401.SELECT语句在PL/SQL程序中，使用SELECT…INTO语句查询一个记录的信息。SELECT…INTO语句只能查询得到一条结果记录，如果没有查询到任何数据，会产生NO_DATA_FOUND异常；如果查询到多个记录，则会产生TOO_MANY_ROWS异常。412.DML语句允许使用变量3.WHERE语句WHERE条件中变量和列名的区分先匹配列名，后匹配变量如，Whereempno=v_empnoandename=‘JOAN’字符串比较①填充比较：Char类型添加空格使字符串等长，比较每个字符ASCII码②非填充比较：Varchar2类型比较每个字符ASCII码，最先结束的字符串小424.RETURNING语句在DML语句末尾使用RETURNING语句返回当前语句操作的记录的信息例，将编号为7844的职工工资提高100并返回修改后的结果DECLAREv_salscott.emp.sal%TYPE;BEGINUPDATEscott.empSETsal=sal+100WHEREempno=7844

RETURNINGsalINTOv_sal;DBMS_OUTPUT.PUT_LINE(v_sal);END;4314.3控制结构选择结构IF语句CASE语句循环结构LOOP循环While循环For循环跳转结构GOTO4414.3.1选择结构1.IF语句IF条件1THEN语句1;ENDIF;[ELSIF

条件2THEN语句2;]……[ELSE语句n+1;]45例如，输入一个员工号，修改该员工的工资，如果该员工的部门号detpno为10，工资增加100，否则增加300。DECLAREv_empnoscott.emp.empno%type;--员工号

v_deptno

scott.emp.deptno%type;--部门号

v_incrementNUMBER(4);--增加工资BEGIN

--变量值由用户在SQL*PLUS输入

v_empno:=&x;--获取该员工的部门号deptno

SELECTdeptnoINTO

v_deptnoFROMscott.empWHEREempno=v_empno;

46--判断v_deptno是否为10，更新的值不同IFv_deptno=10THEN

v_increment:=100;ELSE

v_increment:=300;ENDIF;--更新该员工的salUPDATEscott.empSETsal=sal+v_increment

WHEREempno=v_empno;END;47(1)只进行等值比较的CASE语句CASE变量名

WHEN值1THEN语句1;……WHEN值nTHEN语句n;[ELSE语句n+1;]ENDCASE;判断变量值与WHEN后面的值是否相等，如果相等，执行对应THEN后面的语句注意：当遇到第一个WHEN成立时，执行其后的操作，操作完后结束CASE语句。其他的WHEN不再判断

2

CASE语句48(2)进行多种条件比较的CASE语句CASE

WHEN

条件1THEN语句1;……

WHEN条件nTHEN语句n;[ELSE

语句n+1;]ENDCASE;对WHEN后面的条件进行判断，条件为真则执行后面的语句49例，根据输入的员工号，修改该员工工资。如果该员工工资低于1000，则工资增加200；如果工资在1000～2000之间，则增加150；否则增加50。DECLAREv_escott.emp.empno%type;v_salemp.sal%type;v_iNUMBER(4);BEGINv_e:=&x;SELECTsalINTOv_salFROMscott.empWHEREempno=v_e;CASEWHENv_sal<1000THENv_i:=200;WHENv_sal<2000THENv_i:=150;ELSEv_i:=50;ENDCASE;UPDATEscott.empSETsal=sal+v_iWHEREempno=v_e;END;501.简单循环语法

LOOP

循环体;

EXITWHEN条件;ENDLOOP;注意在循环体中一定要包含EXIT语句，否则程序进入死循环14.3.2循环结构512.While循环基本语法WHILE条件

LOOP

循环体;ENDLOOP;51例3.4使用WHILE循环输出1～10的数值。

declarenumnumber(2):=1;beginwhile____________loopdbms_output.put_line(num);_____________________endloop;end;num<=10num:=num+1;523.FOR循环基本语法FOR循环变量IN

下界..上界LOOP

循环体;ENDLOOP;注意：循环变量不需要显式定义；如果使用REVERSE关键字，则表示循环变量从上界向下界递减计数；循环变量只能在循环体中使用，不能在循环体外使用。[REVERSE]53

例3.5使用FOR循环输出1～10的数值。

declarenumnumber(2);beginfor______________________dbms_output.put_line(num);____________end;如果改成fornuminreverse1..10loop输出什么结果？numin1..10loopendloop;54练习：使用PL/SQL程序计算1到100的和declareinumber(3);snumber(5);begini:=0;s:=0;whilei<=100loops:=s+i;i:=i+1;endloop;dbms_output.put_line(s);end;Foriin1..100loops:=s+i;endloop;5514.4游标14.4.1游标的概念及类型游标的概念及类型执行查询语句和操作语句时，Oracle会在内存中开辟一个缓冲区，用来存储语句返回的数据行集。使用游标时，SELECT语句查询的结果可以是单条记录，多条记录，也可以是零条记录。游标有一个指针，最初指向查询结果的首部，随着游标指针的推进，就可以访问相应的记录。56001张红网络1班002宋佳佳网络1班003王楚网络1班004田宇乐网络1班001张红网络1班变量p游标mySelect*fromstudent定义变量pmy%rowtype

游标名%rowtype57游标的类型显式游标由用户定义、操作，用于处理返回多行数据的SELECT查询。隐式游标由系统自动进行定义和操作，用于处理DML语句和返回单行数据的SELECT查询5814.4.2显式游标1.显式游标的操作定义游标打开游标检索游标：循环关闭游标完毕59(1)定义游标CURSOR

游标名

IS

select语句;说明游标在declare部分进行定义；游标定义时若引用变量，则变量必须在游标定义之前定义；定义游标时并没有生成数据，只是保存定义游标定义后，可以使用“游标名%ROWTYPE”定义游标类型变量和保存游标中的数据行。60（2）打开游标OPEN游标名;在内存中分配缓冲区，执行select语句在数据库中检索数据，将查询结果缓存在缓冲区中。一旦游标打开，就无法再次打开，除非先关闭如果游标定义中的变量值发生变化，则只能在下次打开游标时才起作用。61例，定义游标mycus为从表scott.emp中取出的sal列的值大于1000的数据，并打开游标。DeclareCursorBegin

openmycus;End;mycusselect*fromscott.empwheresal>1000;Is7834张红12007844宋佳佳15007859王楚11007934田宇乐200062（3）检索游标使游标指针下移，指向下一个数据行,然后取出指针所指的数据行，将其存入到变量中。FETCH

游标名

INTO

变量列表|记录变量;

游标指针只能向下移动，不能回退由于游标中取出的为一条记录，因此变量需要是能够存储记录的记录变量，或者使用变量列表（多个变量）63两种形式变量：

(1)变量列表：多个变量名，游标中各个字段依次存入各变量中如fetchmyintoa,b,c;变量个数、顺序、数据类型必须与游标中每行记录的字段数、顺序以及数据类型一一对应。

(2)记录变量：使用“游标名%rowtype”定义的记录类型的变量如fetchmyintop;64001张红网络1班002宋佳佳网络1班003王楚网络1班001张红网络1班1.使用三个变量a,b,c分别存储三个值asystem.student.sid%type;bsystem.student.sname%type；csystem.student.sclass%type；游标mySelectsid,sname,sclassfromsystem.student2.定义变量p,存储游标的一行记录p

my%rowtype；

变量p65（4）关闭游标语法格式

CLOSE游标名;说明游标所对应的内存工作区变为无效，释放与游标相关的系统资源。66例题，使用游标在scott.emp表中根据输入的部门号deptno查询某个部门的所有员工信息，部门号在程序运行时指定DECLARE

a

scott.emp.deptno%TYPE;CURSORc_empISSELECT*FROMscott.empWHEREdeptno=a;--变量要先定义

v1BEGINa:=&x;OPENc_emp;--打开游标c_emp%ROWTYPE;67--取游标中的记录LOOPFETCHc_empINTOv1;

EXITWHENc_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v1.empno||v1.ename||v1.sal||a);ENDLOOP;--关闭游标CLOSEc_emp;END;7844张红1200107845宋佳佳1300107846王楚200010Fetch语句执行失败，退出循环682游标的属性

%ISOPEN布尔型。如果游标已经打开，返回TRUE,否则为FALSE。%FOUND布尔型，如果最近一次使用FETCH语句有返回结果，则为TRUE,否则为FALSE;%NOTFOUND布尔型，如果最近一次使用FETCH语句没有返回结果，则为TRUE,否则为FALSE;%ROWCOUNT数值型，返回到目前为止从游标缓冲区检索的元组数。69练习，定义游标mycus为从表scott.emp中取出的sal的值大于1000的数据，显示第一条数据的empno字段内容。Declare_________________--定义

select*fromscott.empwheresal>1000;a_________________--变量a存储游标记录Beginopenmycus;--打开

________

___--推进

dbms_output.put_line(

);closemycus;--关闭End;CursormycusIsfetch

mycusintoa;mycus%rowtype;a.empno70练习：利用游标统计并输出scott.emp表中各个部门编号deptno和各部门工资sal的平均值。DECLARECURSORc1ISSELECTdeptno,avg(sal)asalFROMscott.empGROUPBYdeptno;v_deptc1%ROWTYPE;BEGINOPENc1;

102567202280301340deptnoasal游标c171scott.emp表72LOOPFETCHc1INTOv_dept;

EXITWHENc1%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_dept.deptno||

v_dept.asal);ENDLOOP;CLOSEc1;END;102567202280301340deptnoasal游标c1732.输出所有比本部门平均工资高的员工信息DECLARECURSORc1ISselect*fromscott.empwheresal>(selectavg(sal)fromscott.empgroupbydeptnohaving);74bab.deptno=a.deptno复习：显式游标的使用定义游标Cursor游标名IsSelect语句；打开游标

Open游标名；检索游标：循环

Fetch游标名Into变量；关闭游标

Close游标名；75变量列表或游标名%Rowtype作业：1.输出所有员工的姓名、员工号、工资和部门号DECLARECURSORc1ISSELECTename,empno,sal,deptnoFROMscott.emp;v_ec1%ROWTYPE;BEGINOPENc1;76LOOPFETCHc1INTOv_e;EXITWHENc1%NOTFOUND;DBMS_OUTPUT.PUT_LINE('姓名'||v_e.ename||'员工号'||v_e.empno||'工资'||v_e.sal||'部门号'||v_e.deptno);ENDLOOP;CLOSEc1;END;77各字段依次输出enameempnosaldeptnoJOAN7844130010PETER7848180020SCOTT750020001078

4显式游标的检索利用简单循环检索游标利用WHILE循环检索游标利用FOR循环检索游标79（1）利用简单LOOP循环检索游标OPEN游标名;LOOPFETCH

游标名INTO变量名;

EXITWHEN游标名%NOTFOUND;……ENDLOOP;EXITWHEN子句应该是FETCH…INTO语句的下一条语句。

80（2）利用WHILE循环检索游标OPEN游标名;FETCH游标名INTO

变量名;WHILELOOP……FETCH游标名INTO

变量名;……ENDLOOP;在While循环之前进行一次FETCH操作，作为第一次循环的条件。游标名%FOUND

81例：利用WHILE循环统计并输出各个部门的平均工资。DECLARECURSORc2ISSELECTdeptno,avg(sal)asal

FROMscott.empv_deptc2%ROWTYPE;BEGINOPENc2;

GROUPBYdeptno;82FETCHc2INTOv_dept;WHILE

LOOP

DBMS_OUTPUT.PUT_LINE(v_dept.deptno||v_dept.asal);

FETCHc2INTOv_dept;ENDLOOP;CLOSEc2;END;102567202280301340deptnoasal游标c2c2%FOUND83（3）利用FOR循环检索游标FOR循环变量INLOOP

……ENDLOOP;系统自动定义循环变量，并且其类型为“游标名%ROWTYPE”。系统自动打开游标，不用OPEN语句；系统自动不断从游标中取数据并放入循环变量系统自动进行%FOUND属性检查以确定是否有数据当游标中所有的记录都被提取完毕或循环中断时，系统自动地关闭游标。游标名例，利用FOR循环统计并输出各个部门的平均工资。DECLARECURSORc3ISSELECTdeptno,avg(sal)avgsalFROMscott.empGROUPBYdeptno;BEGIN

FOR

v_deptINc3

LOOP

DBMS_OUTPUT.PUT_LINE(v_dept.deptno||v_dept.avgsal);

ENDLOOP;

END;102567202280301340deptnoasal游标c3无需声明，自动定义游标名85可以不在声明部分定义游标，而在FOR语句中直接使用子查询。DECLARECURSORc3ISSELECTdeptno,avg(sal)avgsalFROMscott.empGROUPBYdeptno;BEGINFORv_empINDBMS_OUTPUT.PUT_LINE(v_emp.empno||v_emp.ename);ENDLOOP;END;(SELECTdeptno,avg(sal)avgsalFROMscott.empGROUPBYdeptno)LOOPc3LOOP练习：输出所有员工的姓名、员工号、工资和部门号，用For循环实现DECLARECURSORc1ISSELECTename,empno,sal,deptnoFROMscott.emp;BEGINForv_einc1LOOPDBMS_OUTPUT.PUT_LINE('姓名'||v_e.ename||'员工号'||v_e.empno||'工资'||v_e.sal||'部门号'||v_e.deptno);ENDLOOP;END;86873参数化显式游标通过参数的取值来限制游标的SQL查询结果参数值不同选取的数据行不同，从而能够动态使用游标

DeclareCursor游标名IsSelect语句

BeginOpen游标名(参数值)(参数名数据类型)Where条件中体现参数88例，根据部门编号deptno输出该部门员工姓名DECLARECURSORc4ISSELECT*FROMscott.empWHEREv_empc4%ROWTYPE;BEGINOPENc4(10);LOOPFETCHc4INTOv_emp;EXITWHENc_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp.ename);ENDLOOP;CLOSEc4;END;deptno=p;(p

emp.deptno%TYPE)89注意：定义参数化游标时，只能指定参数的类型，而不能指定参数的长度、精度、刻度；打开带参数的游标时，实参的个数和数据类型等必须与游标定义时形参个数和数据类型等相匹配。905利用游标更新或删除数据游标定义语法CURSOR游标名ISSELECT列FROM表名FORUPDATE注意打开游标时对相应的表加锁,其他用户不能对该表进行更新操作；若数据对象已经被其他会话加锁，则当前会话挂起等待，若指定了NOWAIT子句，则不等待，返回ORACLE错误。当用户执行COMMIT或ROLLBACK操作时，数据上的锁会自动被释放。[OF列名][NOWAIT];

91例如，修改员工的工资，如果员工的部门号为10，则工资提高100；如果部门号为20，则工资提高150，否则工资提高250。DECLARECURSORc_empISSELECT*FROMempFORUPDATE;v_incrementNUMBER;BEGINFORv_empINc_empLOOPCASEv_emp.deptno

WHEN10THENv_increment:=100;WHEN20THENv_increment:=150;ELSEv_increment:=250;ENDCASE;

UPDATEempSETsal=sal+v_incrementWHERECURRENTOFc_emp;ENDLOOP;COMMIT;END;

9214.4.3隐式游标概念所有的SQL语句都有一个执行的缓冲区，隐式游标就是指向该缓冲区的指针，由系统隐含地打开、处理和关闭。隐式游标又称为SQL游标。隐式游标主要用于处理INSERT、UPDATE，DELETE以及单行的SELECT…INTO语句，没有OPEN，FETCH，CLOSE等操作命令。9314.5异常处理14.5.1异常概述1.Oracle错误处理机制Oracle中对运行时错误的处理采用了异常处理机制。一个错误对应一个异常，当错误产生时抛出相应的异常，并被异常处理器捕获，程序控制权传递给异常处理器。942.异常的类型（1）预定义的Oracle异常（Oracle错误）错误产生时，与错误对应的预定义异常被自动抛出，通过捕获该异常可以对错误进行处理。表14-4（2）非预定义的Oracle异常（Oracle错误）错误没有预定义异常与其关联，需要在语句块的声明部分声明一个异常，通过编译指示PRAGMAEXCEPTION_INIT将该异常与错误相关联。此后，当执行过程出现该错误时将自动抛出该异常。95（3）用户自定义的异常有些操作并不会产生Oracle错误，但是从业务规则角度考虑认为是一种错误。用户自定义异常必须在声明部分进行声明。当异常发生时，系统不能自动触发，需要用户使用RAISE语句。在EXCEPTION部分捕捉并处理异常。9614.5.2异常处理过程异常处理分3个步骤进行：在声明部分为错误定义异常，包括非预定义异常和用户定义异常。预定义异常无需声明在执行部分Begin中当错误产生时抛出与错误对应的异常，并跳转到Exception部分。在异常处理Exception部分通过异常处理器捕获异常，并进行异常处理。97预定义异常的处理例，查询名为SMITH的员工工资，如果该员工不存在，输出“不存在该员工!”；如果存在多个同名的员工，则输出“存在多个同名员工!”DECLAREv_salscott.emp.sal%type;BEGINSELECTsalINTOv_salFROMscott.emp

WHEREename='SMITH';DBMS_OUTPUT.PUT_LINE(v_sal);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(‘不存在该员工!');WHENTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE(‘存在多个同名员工!');END;异常名9899100非预定义异常的处理例，删除dept表中部门号为10的部门信息，如果不能删除则输出“Therearesubrecordsinemptable!”DECLARE

e_deptno_fk

EXCEPTION;

PRAGMAEXCEPTION_INIT(e_deptno_fk,-2292);BEGINDELETEFROMdeptWHEREdeptno=10;EXCEPTIONWHENe_deptno_fkTHENDBMS_OUTPUT.PUT_LINE('Therearesubrecordsinemptable!');END;101修改7844员工的工资，保证修改后工资不超过6000。DECLAREe_highlimitEXCEPTION;v_salemp.sal%TYPE;BEGINUPDATEempSETsal=sal+100WHEREempno=7844RETURNINGsalINTOv_sal;IFv_sal>6000THENRAISEe_highlimit;ENDIF;EXCEPTIONWHENe_highlimitTHENDBMS_OUTPUT.PUT_LINE('Thesalaryistoolarge!');ROLLBACK;END;102（1）异常的定义Oracle中的3种异常，其中预定义异常由系统定义，而其他两种异常则需要用户定义。定义异常方法e_exceptionEXCEPTION;如果是非预定义的异常，需要将异常与一个Oracle错误相关联，其语法为：PRAGMAEXCEPTION_INIT(e_exception,-#####);注意Oracle内部错误号用一个负的5位数表示，如-02292。其中

-20999～-20000为用户定义错误的保留号。103（2）异常的抛出由于系统可以自动识别Oracle内部错误，因此当错误产生时系统会自动抛出与之对应的预定义异常或非预定义异常。但是，系统无法识别用户定义错误，因此当用户定义错误产生时，需要用户手动抛出与之对应的异常。用户定义异常的抛出语法为RAISEuser_define_exception；104（3）异常的捕获与处理异常处理器的基本形式为EXCEPTIONWHENexception1[ORexcetpion2…]THENsequence_of_statements1;WHENexception3[ORexception4…]THENsequence_of_statements2;……WHENOTHERSTHENsequence_of_statementsn;END;注意：一个异常处理器可以捕获多个异常，只需在WHEN子句中用

OR连接即可；一个异常只能被一个异常处理器捕获，并进行处理。105（4）OTHERS异常处理器OTHERS异常处理器是一个特殊的异常处理器，可以捕获所有的异常。通常，OTHERS异常处理器总是作为异常处理部分的最后一个异常处理器，负责处理那些没有被其他异常处理器捕获的异常。

106DECLAREv_salemp.sal%TYPE;e_highlimitEXCEPTION;BEGINSELECTsalINTOv_salFROMempWHEREename='JOAN';UPDATEempSETsal=sal+100WHEREempno=7900;IFv_sal>6000THENRAISEe_highlimit;ENDIF;EXCEPTIONWHENe_highlimitTHENDBMS_OUTPUT.PUT_LINE('Thesalaryistoolarge!');ROLLBACK;WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('Thereissomewronginselecting!');END;107可以通过两个函数来获取错误相关信息。SQLCODE：返回当前错误代码。如果是用户定义错误返回值为1；如果是ORA-1403：NODATAFOUND错误，返回值为100其他Oracle内部错误返回相应的错误号。

SQLERRM：返回当前错误的消息文本。如果是Oracle内部错误，返回系统内部的错误描述；如果是用户定义错误，则返回信息文本为“User-definedException”。108DECLAREv_salemp.sal%TYPE;e_highlimitEXCEPTION;v_codeNUMBER(6);v_textVARCHAR2(200);BEGINSELECTsalINTOv_salFROMempWHEREename='JOAN';UPDATEempSETsal=sal+100WHEREempno=7900;IFv_sal>6000THENRAISEe_highlimit;ENDIF;EXCEPTIONWHENe_highlimitTHENDBMS_OUTPUT.PUT_LINE('Thesalaryistoolarge!');ROLLBACK;WHENOTHERSTHENv_code:=SQLCODE;v_text:=SQLERRM;DBMS_OUTPUT.PUT_LINE(v_code||''||v_text);END;10914.5.3异常的传播执行部分的异常声明部分和异常处理部分的异常110（1）执行部分异常的传播如果当前语句块有该异常的处理器，则执行之，并且成功完成该语句块。然后，控制权传递到外层语句块。

如果当前语句块没有该异常的处理器，则通过在外层语句块中产生该异常来传播该异常。然后，执行对外层语句块执行步骤1。如果没有外层语句块，则该异常将传播到调用环境。

111DECLAREv_salemp.sal%TYPE;BEGINBEGINSELECTsalINTOv_salFROMempWHEREename='JOAN';EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thereisnotsuchanemployee!');END;DBMS_OUTPUT.PUT_LINE('Nowthisisoutputtedbyouterblock!');END;/Thereisnotsuchanemployee!Nowthisisoutputtedbyouterblock!

112DECLAREv_salemp.sal%TYPE;BEGINBEGINSELECTsalINTOv_salFROMempWHEREdeptno=10;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('Thereisnotsuchanemployee!');END;DBMS_OUTPUT.PUT_LINE('Nowthisisoutputtedbyouterblock!');EXCEPTIONWHENTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE('Therearemorethanoneemployee!');END;/Therearemorethanoneemployee!113声明部分和异常处理部分的异常会立刻传播到外层语句块的异常处理部分，即使当前语句块有该异常的异常处理器。

（2）声明部分和异常处理部分的异常114BEGINDECLAREv_numberNUMBER(6):='ABC';BEGINv_number:=10;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('Thisisoutputtedbyinnerblock!');END;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('Thisisoutputtedbyouterblock!');END;/Thisisoutputtedbyouterblock!115结论无论是执行部分的异常，还是声明部分或异常处理部分的异常，如果在本块中没有处理，最终都将向外层块中传播。通常在程序最外层块的异常处理部分放置OTHERS异常处理器，以保证没有错误被漏掉检测，否则错误将传递到调用环境。116隐式游标属性SQL%ISOPEN：布尔型值，判断隐式游标是否已经打开。对用户而言，该属性值始终为FALSE，因为操作时系统自动打开，操作完后立即自动关闭。SQL%FOUND：布尔型值，判断当前的操作是否会对数据库产生影响。如果有数据的插入、删除、修改或查询到数据，则返回TRUE，否则返回FALSE。SQL%NOTFOUND：布尔型值，判断当前的操作是否对数据库产生影响。如果没有数据的插入、删除、修改或没有查询到数据，则返回TRUE，否则返回FALSE。SQL%ROWCOUNT：数值型，返回当前操作所涉及的数据库中的行数。117修改员工号为1000的员工工资，将其工资增加100。如果该员工不存在，则向emp表中插入一个员工号为1000，工资为1600的员工。118BEGINUPDATEempSETsal=sal+100WHEREempno=1000;IFSQL%NOTFOUNDTHENINSERTINTOemp(empno,sal)VALUES(1000,1600);ENDIF;END;或BEGINUPDATEempSETsal=sal+100WHEREempno=1000;IFSQL%ROWCOUNT=0THENINSERTINTOemp(empno,sal)VALUES(1000,1600);ENDIF;END;11914.4.4游标变量概念游标变量是一个指向多行查询结果集的指针，不与特定的查询绑定，因此具有非常大的灵活性，可以在打开游标变量时定义查询，可以返回不同结构的结果集使用游标变量包括游标引用类型（REFCURSOR）声明游标变量打开游标变量检索游标变量关闭游标变量120（1）定义游标引用类型及游标变量

语法TYPEref_cursor_type_nameISREFCURSOR[RETURNreturn_type]RETURN子句用于指定定义的游标类型返回结果集的类型，该类型必须是记录类型。如果定义游标引用类型时带有RETURN子句，则用其定义的变量称为强游标变量，否则称为弱游标变量。在Oracle10g中，系统预定义了一个游标引用类型，称为SYS_REFCURSOR，可以直接使用它定义游标变量。121语法ref_cursor_type_namevariable_name;例如TYPEemp_cursor_typeISREFCURSORRETURNemp%ROWTYPE;TYPEgeneral_cursor_typeISREFCURSOR;v_empemp_cursor_type;v_generalgeneral_cursor_type;my_cursorSYS_REFCURSOR;122（2）打开游标变量语法OPENcursor_variableFORselect_statement;注意如果打开的游标变量是强游标变量，则查询语句的返回类型必须与游标引用类型定义中RETURN子句指定的返回类型相匹配。例如OPENv_empFORSELECT*FROMemp;OPENv_generalFORSELECTempno,ename,sal,deptnoFROMemp;OPENmy_cursorFORSELECT*FROMdept;123（3）检索游标变量语法LOOPFETCHcursor_variableINTOvariable1,variable2,…;EXITWHENcursor_variable%NOTFOUND;……ENDLOOP;

注意检索游标变量时只能使用简单循环或WHILE循环，不能采用FOR循环。124（4）关闭游标变量语法CLOSEcursor_variable;125DECLARETYPEemp_cursor_typeISREFCURSORRETURNemp%ROWTYPE;TYPEgeneral_cursor_typeISREFCURSOR;v_empemp_cursor_type;v_generalgeneral_cursor_type;my_cursorSYS_REFCURSOR;v_empinfoemp%RO