第十四章 PLSQL语言基础

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

2、no number;Begin Select deptno into v_deptno from emp where empno=7844If v_deptno=10 Then Update emp set sal=sal+100 where empno=7844;Else Update emp set sal=sal+200 where empno=7844;End if;End ;514.2 PL/SQL基础基础14.2.1 PL/SQL程序结构程序结构1. PL/SQL块的组成块的组成pPL/SQL程序的基本单元是语句块程序的基本单元是语句块p一个完整的一个完整的PL/SQL语句块由语句

3、块由声明部分、执声明部分、执行部分行部分和和异常处理异常处理3部分组成。部分组成。6p定义定义PL/SQL语句块的语法：语句块的语法： DECLARE 声明部分（可选）声明部分（可选） BEGIN 执行部分执行部分（必须）（必须） EXCEPTION 异常处理部分（可选）异常处理部分（可选） END; 声明变量、常量、声明变量、常量、游标和自定义类型游标和自定义类型描述了所要完成的处描述了所要完成的处理，可以使用理，可以使用SQL语语句和控制语句句和控制语句当程序在当程序在BEGIN部分部分出错时执行出错时执行分号分号7Declare v_deptno number;Begin Select

4、deptno into v_deptno from scott.emp where empno=7844; If v_deptno=10 Then Update scott.emp set sal=sal+100 where empno=7844; Else Update scott.emp set sal=sal+200 where empno=7844; End if; DBMS_OUTPUT.PUT_LINE(v_deptno);EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(没有这个人！没有这个人！);END;SET SER

5、VEROUTPUT ON在在SQL*Plus中需要中需要在在PL/SQL Developer中中不需要不需要8在在 SQL*PLUS中执行中执行PL/SQL程序程序1.连接数据库连接数据库92.输入程序，结束后输入输入程序，结束后输入“/”，执行程序，执行程序10新建新建测试测试窗口：调试窗口：调试PL/SQL程序脚本程序脚本在在PL/SQL DEVELOPER中执行中执行PL/SQL程序程序11查看结果查看结果输入程序输入程序执行程序执行程序12132. PL/SQL块分类块分类p匿名块匿名块n匿名块是指动态生成，匿名块是指动态生成，只能执行一次只能执行一次的块，的块，不能由其他应用程序调用

6、。不能由其他应用程序调用。p命名块命名块n命名块是指一次编译可命名块是指一次编译可多次执行多次执行的的PL/SQL程序，包括程序，包括函数、存储过程函数、存储过程、包、触发器。、包、触发器。n编译后放在服务器中，由应用程序或系统编译后放在服务器中，由应用程序或系统在特定条件下调用执行。在特定条件下调用执行。 141.字符集字符集pPL/SQL的字符集包括：的字符集包括：n大小写字母：大小写字母：AZ，azn数字：数字：09n空白：制表符、空格和回车空白：制表符、空格和回车n数字符号：数字符号：+ - * / =n标点符号：标点符号： ! # $ % &* （）（）_ | ？ ； ：,

7、. “ p注意注意nPL/SQL字符集字符集不区分大小写不区分大小写。14.2.2 PL/SQL词法单元词法单元152.标识符标识符命名规则命名规则p在在PL/SQL程序中，标识符是程序中，标识符是以字母开头以字母开头的，的，后边可以跟字母、数字、美元符号（后边可以跟字母、数字、美元符号（$）、）、井号（井号（#）或下划线（）或下划线（_），其最大长度为），其最大长度为30个个字符，并且所有字符都是有效的。字符，并且所有字符都是有效的。16p+p-p*p/p=p:= 赋值赋值pp=pp!=p=p=p(p)p/*p*/pp%p;p:p.pp“p.pp| 字符串连接字符串连接p=p* 乘方乘方p-

8、3.分隔符分隔符174. 常量值常量值p字符型文字字符型文字p数字型文字数字型文字p布尔型文字布尔型文字nTRUE，FALSE，NULL三个值。三个值。p日期型文字日期型文字185. 注释注释p单行注释单行注释n-p多行注释多行注释n以以 “/*”开始，以开始，以“*/”结束。结束。1914.2.3 数据类型数据类型p数字类型数字类型p字符类型字符类型p日期日期/区间类型区间类型p行标识类型行标识类型p布尔类型布尔类型p原始类型原始类型pLOB类型类型p引用类型引用类型 p记录类型记录类型p集合类型集合类型p%TYPE与与%ROWTYPE201 . 数字类型：数字类型：p用来存储整数、实数和浮

9、点数，常用的数值类用来存储整数、实数和浮点数，常用的数值类型有型有NUMBER、PLS_INTEGER和和BINARY_INTEGER。 NUMBER(P,S)p存储整数或浮点数。其中存储整数或浮点数。其中P是精度是精度（指数值中所（指数值中所有数字的个数），有数字的个数），S是刻度是刻度(指小数点右边数字指小数点右边数字的个数的个数)。 pP和和S都是可选的，但是如果指定了刻度都是可选的，但是如果指定了刻度S，则必，则必须指定精度须指定精度P。 例：例：sgrade number(3,1)pP的取值范围为的取值范围为13821 (2) BINARY_INTEGERp表示表示 -2147483

10、647 +2147483647 之间的整数之间的整数p发生溢出时会自动转换为发生溢出时会自动转换为Number类型。类型。 （3） PLS_INTEGERp表示范围与表示范围与BINARY_INTEGER相同相同p发生溢出时会报错发生溢出时会报错pBINARY_INTEGER和和PLS_INTEGER所需的所需的存储空间要比存储空间要比NUMBER少，运算的速度要高少，运算的速度要高于于NUMBER。pOracle数据库中只支持数据库中只支持NUMBER222.字符类型字符类型 n变长字符串变长字符串VARCHAR2n定长字符串定长字符串CHAR nPL/SQL中的字符类型与中的字符类型与Or

11、acle数据库中允许数据库中允许字符串的长度不同。字符串的长度不同。类类 型型PL/SQL中最大字节数中最大字节数Oracle中最大字节数中最大字节数VARCHAR2327674000NVARCHAR2327674000CHAR327672000NCHAR327672000LONG327602GB23 9记录记录类型类型n记录记录类型的内部含有成员分量，类似于类型的内部含有成员分量，类似于C语语言中的结构体，言中的结构体， 声明语法如下声明语法如下DECLARE TYPE 记录名记录名 IS RECORD ( 字段名字段名1 类型类型 , 字段名字段名n 类型类型 ); 声明记录类型的变量：声

12、明记录类型的变量： 变量名变量名 记录名记录名 引用元素格式引用元素格式:记录变量名记录变量名.字段名字段名,如如teacher1.tnameDeclare Type JS Is Record(tid char(4), tname varchar2(8), tage number(3) );teacher1 JS;编号编号姓名姓名年龄年龄7900王凡王凡367844张松张松47表列的数据类型表列的数据类型发生变化怎么办？发生变化怎么办？Scott.教师教师2411. %TYPE与与%ROWTYPEp%TYPEn用于定义与某个用于定义与某个变量变量或数据库表中某个或数据库表中某个列列的数据类型一

13、致的数据类型一致的变量的变量n变量名变量名 模式名模式名.表名表名.列名列名% TYPEn例如，例如，tn scott.教师教师.姓名姓名%TYPE;p%ROWTYPEn用于定义与数据库中某个用于定义与数据库中某个表结构一致表结构一致的记的记录类型的变量，可用于录类型的变量，可用于存储表中一行记录存储表中一行记录n变量名变量名 模式名模式名.表名表名% ROWTYPEn例如，例如，teacher2 scott.教师教师%ROWTYPE;n引用变量中的某个字段值引用变量中的某个字段值: 变量名变量名.字段名字段名n例如，例如，teacher2.姓名姓名25例，定义变量例，定义变量v_sal 和和

14、v_emp，分别用于存储，分别用于存储scott.emp表中编号为表中编号为7844的职工的工资和的职工的工资和7900的职工信息的职工信息DECLARE v_sal v_empBEGIN SELECT sal FROM scott.emp WHERE empno=7844; SELECT * FROM scott.emp WHERE empno=7900; DBMS_OUTPUT.PUT_LINE(v_sal); DBMS_OUTPUT.PUT_LINE(v_emp);END; empno ename sal7900徐立徐立20007844王凤王凤2500scott.emp.sal%TYPE

15、;scott.emp%ROWTYPE;INTO v_salINTO v_emp复合变量复合变量按字段分按字段分别输出别输出v_emp.empno|v_emp.ename|v_emp.sal26练习，定义变量练习，定义变量num和变量和变量stu ，分别用来存储，分别用来存储sys.xuesheng表中表中“张红张红”的学号和的学号和”王娜王娜”的所有信的所有信息，并输出息，并输出 Declare num stu Begin SELECT sid into num FROM sys.xuesheng WHERE name=张红张红; SELECT * into stu FROM sys.xues

16、heng WHERE name=王娜王娜; DBMS_OUTPUT.PUT_LINE(num); DBMS_OUTPUT.PUT_LINE(stu.sid||stu.age); END;sys.xuesheng.sid%Type;sys.xuesheng%RowType;sidname age101 张红张红18102 王娜王娜172728作业：作业：P297 第第2题（题（1）（）（2）29作业：编写作业：编写PL/SQL程序，在程序，在scott.emp和和scott.dept表中查找编号表中查找编号empno为为7844的员工的员工的姓名的姓名ename和部门名称和部门名

17、称dname，并输出结果，并输出结果declare v_e scott.emp.ename%type; v_d scott.dept.dname%type;begin select ename,dname from scott.emp,scott.dept where emp.deptno=dept.deptno and empno=7844; dbms_output.put_line(v_e|v_d);end; into v_e,v_d30变量声明14.2.4 变量与常量变量与常量1. 变量与常量的定义变量与常量的定义变量名变量名 数据类型数据类型p说明说明n每行只能定义一个变量每行只能定义

18、一个变量；n如果加上关键字如果加上关键字CONSTANT，则表示所定，则表示所定义的是一个义的是一个常量常量，必须为它赋初值；必须为它赋初值；n如果定义变量时使用了如果定义变量时使用了NOT NULL关键字，关键字，则必须为变量赋初值；则必须为变量赋初值；n如果变量没有赋初值，则默认为如果变量没有赋初值，则默认为NULL；例如，例如，v2 NUMBER(4) NOT NULL :=10;CONSTANTNOT NULL := 默认值默认值;DEFAULT|312.变量的作用域变量的作用域p如果如果PL/SQL块相互嵌套，则在块相互嵌套，则在内部块中声内部块中声明的变量是局部的明的变量是局部的，

19、只能在内部块中引用，只能在内部块中引用，而在而在外部块中声明的变量是全局的外部块中声明的变量是全局的，既可，既可以在外部块中引用，也可以在内部块中引以在外部块中引用，也可以在内部块中引用。用。p如果内部块与外部块中定义了同名变量，如果内部块与外部块中定义了同名变量，则在则在内部块中引用外部块的全局变量时需内部块中引用外部块的全局变量时需要使用外部块名进行标识。要使用外部块名进行标识。 32DECLARE v_ename CHAR(16); v_outer NUMBER(5);BEGIN v_outer :=10; DECLARE v_ename CHAR(20); v_inner DATE;

20、BEGIN v_inner:=sysdate; v_ename:=INNER V_ENAME; OUTER.v_ename:=OUTER V_ENAME; END; DBMS_OUTPUT.PUT_LINE(v_ename);END; 输出结果为输出结果为OUTER V_ENAME3314.2.5 PL/SQL记录记录 1.用户定义记录类型及变量用户定义记录类型及变量 2.利用利用%ROWTYPE获取记录类型定义变量获取记录类型定义变量 3.记录类型变量的应用记录类型变量的应用 n在在SELECT语句中使用记录类型变量语句中使用记录类型变量 n在在INSERT语句中使用记录类型变量语句中使用记

21、录类型变量 n在在UPDATE语句中使用记录类型变量语句中使用记录类型变量 n在在DELETE语句中使用记录类型变量语句中使用记录类型变量 34（1）在）在SELECT语句中使用记录类型变量语句中使用记录类型变量 在在SELECT INTO 语句中使用记录类型变量语句中使用记录类型变量p例，在例，在scott.emp表中查询姓名表中查询姓名SMITH员工信息员工信息DECLARE v_emp BEGIN END;empnoenamesal7844SMITH2500scott.emp%ROWTYPE;SELECT * INTO v_emp FROM scott.emp WHERE ename=S

22、MITH;SELECT empno, ename, sal INTO v_emp.empno, v_emp.ename, v_emp.sal FROM scott.emp WHERE ename=SMITH;个数、顺序、个数、顺序、类型类型 一一匹配一一匹配35（2）在）在INSERT语句中使用记录类型变量语句中使用记录类型变量例，向例，向scott.dept表中插入一条新记录表中插入一条新记录DECLARE v_deptBEGIN v_dept.deptno:=50; v_dept.loc:=北京北京; v_dept.dname:=计算机计算机; INSERT INTO scott.dept

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

24、tno:=50; v_dept.loc:=北京北京; v_dept.dname:=管理管理; UPDATE scott.dept SET dname=v_dept.dname WHERE deptno=50;END;DELETE FROM scott.dept WHERE deptno=v_emp.deptno;deptno locdname48广州广州研发研发49上海上海运维运维50北京北京计算机计算机管理管理37p练习，定义变量练习，定义变量v_sal初始值为初始值为2000，用于存储，用于存储scott.emp(empno,ename,sal)表中表中sal列的值，向列的值，向scott

25、.emp表中插入数据，其中表中插入数据，其中empno列数据为列数据为7500，ename为为JOAN，sal为为v_sal 的值。然后将该表中所的值。然后将该表中所有有sal列值小于列值小于v_sal的员工的员工sal增加增加100。最后将。最后将sal列值列值大于大于v_sal的员工信息删除的员工信息删除DECLARE v_sal scott.emp.sal%TYPE :=2000;BEGIN INSERT INTO scott.emp(empno,ename,sal) VALUES(7500, JOAN, v_sal); UPDATE scott.emp SET sal=sal+100

26、WHERE salv_sal;END; p在在PL/SQL程序中可以动态指定变量的值程序中可以动态指定变量的值例，将指定员工的工资增加例，将指定员工的工资增加400 declare v_empno number(4); begin v_empno:= update scott.emp set sal=sal+400 where empno=v_empno; end;38&x;在程序运行时输入在程序运行时输入在在SQL*PLUS中运行中运行3914.2.7 PL/SQL中中SQL语句语句p在在PL/SQL中只允许出现中只允许出现: nSELECT 、UPDATE、DELETE、INSER

27、Tn事务控制语句（事务控制语句（COMMIT、ROLLBACK、SAVEPOINT）p注意注意nDDL语句不可以直接使用语句不可以直接使用401.SELECT语句语句p在在PL/SQL程序中，使用程序中，使用SELECTINTO语句查询一个记录的信息。语句查询一个记录的信息。pSELECTINTO语句只能查询得到一条结语句只能查询得到一条结果记录果记录，如果没有查询到任何数据，会产，如果没有查询到任何数据，会产生生NO_DATA_FOUND异常；如果查询到多异常；如果查询到多个记录，则会产生个记录，则会产生TOO_MANY_ROWS异异常。常。412.DML语句语句允许允许使用变量使用变量3.

28、WHERE语句语句pWHERE条件中变量和列名的区分条件中变量和列名的区分先匹配列名，后匹配变量先匹配列名，后匹配变量如，如，Where empno=v_empno and ename=JOANp字符串比较字符串比较填充比较：填充比较：Char类型类型添加空格使字符串等长，比较每个字符添加空格使字符串等长，比较每个字符ASCII码码非填充比较：非填充比较：Varchar2类型类型比较每个字符比较每个字符ASCII码，最先结束的字符串小码，最先结束的字符串小424.RETURNING语句语句p在在DML语句末尾语句末尾使用使用RETURNING语句返语句返回当前语句操作的记录的信息回当前语句操作

29、的记录的信息例，将编号为例，将编号为7844的职工工资提高的职工工资提高100并返回修并返回修改后的结果改后的结果DECLARE v_sal scott.emp.sal%TYPE;BEGIN UPDATE scott.emp SET sal=sal+100 WHERE empno=7844 RETURNING sal INTO v_sal ; DBMS_OUTPUT.PUT_LINE(v_sal);END;4314.3 14.3 控制结构控制结构 p选择结构选择结构nIF语句语句nCASE语句语句p循环结构循环结构nLOOP循环循环nWhile循环循环nFor循环循环p跳转结构跳转结构nGOT

30、O4414.3.1选择结构选择结构1. IF语句语句pIF 条件条件1 THEN 语句语句1; END IF; ELSIF 条件条件2 THEN 语句语句2; ELSE 语句语句n+1;45例如，例如，输入输入一个员工号，修改该员工的工资，一个员工号，修改该员工的工资，如果该员工的部门号如果该员工的部门号detpno为为10，工资增加，工资增加100，否则增加，否则增加300。 DECLARE v_empno scott.emp.empno%type; -员工号员工号 v_deptno scott.emp.deptno%type; -部门号部门号 v_increment NUMBER(4);

31、-增加工资增加工资BEGIN -变量值由用户在变量值由用户在SQL*PLUS输入输入 v_empno:= &x ; -获取该员工的部门号获取该员工的部门号deptno SELECT deptno INTO v_deptno FROM scott.emp WHERE empno=v_empno; 46-判断判断v_deptno是否为是否为10，更新的值不同，更新的值不同IF v_deptno=10 THEN v_increment:=100;ELSE v_increment:=300;END IF;-更新该员工的更新该员工的salUPDATE scott.emp SET sal=sal+

32、v_increment WHERE empno=v_empno;END;47(1)只进行只进行等值比较等值比较的的CASE语句语句CASE 变量名变量名 WHEN 值值1 THEN 语句语句1; WHEN 值值n THEN语句语句n; ELSE 语句语句n+1;END CASE; p判断判断变量值变量值与与WHEN后面的值是否后面的值是否相等相等，如，如果相等，执行对应果相等，执行对应THEN后面的语句后面的语句p注意注意 ：当遇到第一个当遇到第一个WHEN成立时，执行成立时，执行其后的操作，操作完后其后的操作，操作完后结束结束CASE语句。语句。其其他的他的WHEN不再判断不再判断 2 CA

33、SE语句语句48(2) 进行多种进行多种条件比较条件比较的的CASE语句语句CASE WHEN 条件条件1 THEN 语句语句1; WHEN 条件条件n THEN 语句语句n; ELSE 语句语句n+1; END CASE;p对对WHEN后面的条件进行判断，后面的条件进行判断，条件为真条件为真则则执行后面的语句执行后面的语句49例，根据输入的员工号，修改该员工工资。如果该员例，根据输入的员工号，修改该员工工资。如果该员工工资低于工工资低于1000，则工资增加，则工资增加200；如果工资在；如果工资在10002000之间，则增加之间，则增加150；否则增加；否则增加50。DECLAREv_e s

34、cott.emp.empno%type;v_sal emp.sal%type;v_i NUMBER(4);BEGINv_e:=&x;SELECT sal INTO v_sal FROM scott.emp WHERE empno=v_e;CASE WHEN v_sal1000 THEN v_i:=200; WHEN v_sal2000 THEN v_i:=150; ELSE v_i:=50;END CASE;UPDATE scott.emp SET sal=sal+v_i WHERE empno=v_e;END; 501. 简单循环简单循环p语法语法 LOOP 循环体循环体; EXIT

35、 WHEN 条件条件 ;END LOOP;p注意注意n在循环体中在循环体中一定要包含一定要包含EXIT语句语句，否则程，否则程序进入死循环序进入死循环 14.3.2循环结构循环结构512.While循环循环p基本语法基本语法WHILE 条件条件 LOOP 循环体循环体;END LOOP; 例例3.4 使用使用WHILE循环输出循环输出110的数值。的数值。 declare num number(2):=1; begin while _ loop dbms_output.put_line(num); _ end loop; end;num=10num:=num+1;523. FOR循环循环p基本

36、语法基本语法FOR 循环变量循环变量 IN 下界下界.上界上界LOOP 循环体循环体;END LOOP;p注意：注意：n循环变量循环变量不需要显式定义不需要显式定义；n如果使用如果使用REVERSE关键字，则表示循环关键字，则表示循环变量从变量从上界向下界上界向下界递减计数；递减计数；n循环变量只能在循环体中使用，不能在循循环变量只能在循环体中使用，不能在循环体外使用。环体外使用。REVERSE53 例例3.5 使用使用FOR循环输出循环输出110的数值。的数值。 declare num number(2); begin for _ dbms_output.put_line(num); _ e

37、nd;如果改成如果改成for num in reverse 1.10 loop输输出什么结果？出什么结果？num in 1.10 loopend loop;54练习：使用练习：使用PL/SQL程序计算程序计算1到到100的和的和declare i number(3);s number(5);begini:=0;s:=0;while i1000;Is7834张红张红12007844宋佳佳宋佳佳 15007859王楚王楚11007934田宇乐田宇乐 200062（3）检索游标）检索游标p使游标使游标指针下移指针下移，指向下一个数据行，指向下一个数据行,然后取然后取出出指针所指的数据行，将其指针所指

38、的数据行，将其存入到变量中存入到变量中。pFETCH 游标名游标名 INTO 变量列表变量列表|记录变量记录变量; p游标指针只能向下移动，游标指针只能向下移动，不能回退不能回退p由于游标中取出的为一条记录，因此变量需由于游标中取出的为一条记录，因此变量需要是能够存储记录的要是能够存储记录的记录变量记录变量，或者使用，或者使用变变量列表（多个变量）量列表（多个变量）63p两种形式变量：两种形式变量： (1) 变量列表：多个变量名，游标中各个字段变量列表：多个变量名，游标中各个字段依次存入各变量中依次存入各变量中 如如 fetch my into a,b,c;n变量个数、顺序、数据类型必须与游标

39、中每变量个数、顺序、数据类型必须与游标中每行记录的字段数、顺序以及数据类型行记录的字段数、顺序以及数据类型一一对一一对应应。 (2)记录变量：使用记录变量：使用“游标名游标名%rowtype”定义的定义的记录类型的变量记录类型的变量 如如 fetch my into p;64001张红张红网络网络1班班002宋佳佳宋佳佳 网络网络1班班003王楚王楚网络网络1班班001 张红张红网络网络1班班1.使用三个变量使用三个变量a, b, c分别存储三个值分别存储三个值a system.student.sid%type;b system.student.sname%type；c system.stud

40、ent.sclass%type；游标游标mySelect sid, sname, sclass from system.student2.定义变量定义变量 p, 存储游标的一行记录存储游标的一行记录 p my%rowtype； 变量变量 p65（4）关闭游标）关闭游标n语法格式语法格式p CLOSE 游标名游标名; n说明说明p游标所对应的内存工作区变为无效，游标所对应的内存工作区变为无效，释放与游标相关的系统资源释放与游标相关的系统资源。66例题，例题， 使用游标在使用游标在scott.emp表中根据输入的表中根据输入的部门号部门号deptno查询某个部门的所有员工信息，查询某个部门的所有员

41、工信息，部门号在程序运行时指定部门号在程序运行时指定DECLARE a scott.emp.deptno%TYPE; CURSOR c_emp IS SELECT * FROM scott.emp WHERE deptno=a; -变量要先定义变量要先定义 v1 BEGIN a:=&x; OPEN c_emp; -打开游标打开游标 c_emp%ROWTYPE;67-取游标中的记录取游标中的记录LOOP FETCH c_emp INTO v1; EXIT WHEN c_emp%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v1.empno| v1.ename| v1.sa

42、l | a); END LOOP; -关闭游标关闭游标CLOSE c_emp;END; 7844 张红张红1200107845 宋佳佳宋佳佳 1300107846 王楚王楚200010Fetch语句执语句执行失败，退行失败，退出循环出循环682 游标的属性游标的属性 p%ISOPENn布尔型。如果游标已经打开，返回布尔型。如果游标已经打开，返回TRUE,否则为否则为FALSE。p%FOUNDn布尔型，如果最近一次使用布尔型，如果最近一次使用FETCH语句有语句有返回结果返回结果，则为，则为TRUE,否则为否则为FALSE;p%NOTFOUNDn布尔型，如果最近一次使用布尔型，如果最近一次使用F

43、ETCH语句没语句没有返回结果有返回结果，则为，则为TRUE,否则为否则为FALSE;p%ROWCOUNTn数值型，返回到目前为止从游标缓冲区检数值型，返回到目前为止从游标缓冲区检索的元组数。索的元组数。69练习，定义游标练习，定义游标mycus为从表为从表scott.emp中取出的中取出的sal的值大于的值大于1000的数据，显示第一条数据的的数据，显示第一条数据的empno字段内容。字段内容。Declare _ -定义定义 select * from scott.emp where sal1000; a _ -变量变量a存储游标记录存储游标记录Begin open mycus; -打开打开

44、 _ _ -推进推进 dbms_output.put_line( ); close mycus; -关闭关闭End;Cursor mycus Isfetch mycus into a;mycus%rowtype;a.empno70练习：利用游标统计并输出练习：利用游标统计并输出scott.emp表中各个表中各个部门编号部门编号deptno和各部门工资和各部门工资sal的平均值。的平均值。DECLARE CURSOR c1 IS SELECT deptno, avg(sal) asal FROM scott.emp GROUP BY deptno; v_dept c1%ROWTYPE;BEGIN

45、 OPEN c1; 102567202280301340deptnoasal游标游标c171pscott.emp表表72LOOP FETCH c1 INTO v_dept; EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_dept.deptno| v_dept.asal);END LOOP;CLOSE c1;END; 102567202280301340deptnoasal游标游标c1732.输出所有比本部门平均工资高的员工信息输出所有比本部门平均工资高的员工信息DECLARECURSOR c1 ISselect * from scott.emp w

46、here sal (select avg(sal) from scott.emp group by deptno having ); 74bab.deptno=a.deptnop复习：显式游标的使用复习：显式游标的使用p定义游标定义游标Cursor 游标名游标名 Is Select 语句；语句；p打开游标打开游标 Open 游标名；游标名；p检索游标：循环检索游标：循环 Fetch 游标名游标名 Into 变量；变量；p关闭游标关闭游标 Close 游标名；游标名；75变量列表变量列表 或或游标名游标名%Rowtype作业：作业：1.输出所有员工的姓名、员工号、工资输出所有员工的姓名、员工号、

47、工资和部门号和部门号DECLARE CURSOR c1 IS SELECT ename, empno, sal, deptno FROM scott.emp; v_e c1%ROWTYPE;BEGIN OPEN c1;76LOOP FETCH c1 INTO v_e; EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE( 姓名姓名|v_e.ename|员工号员工号|v_e.empno| 工资工资|v_e.sal|部门号部门号|v_e.deptno);END LOOP;CLOSE c1;END; 77各字段依各字段依次输出次输出enameempno salde

48、ptnoJOAN78441300 10PETER 78481800 20SCOTT 75002000 1078 4 显式游标的检索显式游标的检索p利用简单循环检索游标利用简单循环检索游标p利用利用WHILE循环检索游标循环检索游标p利用利用FOR循环检索游标循环检索游标79（1）利用简单）利用简单LOOP循环检索游标循环检索游标OPEN游标名游标名 ;LOOP FETCH 游标名游标名 INTO变量名变量名; EXIT WHEN 游标名游标名%NOTFOUND; END LOOP;pEXIT WHEN子句应该是子句应该是FETCHINTO语语句的下一条语句。句的下一条语句。 80（2）利用）利

49、用WHILE循环检索游标循环检索游标OPEN 游标名游标名;FETCH 游标名游标名 INTO 变量名变量名;WHILE LOOP FETCH 游标名游标名 INTO 变量名变量名; END LOOP;p在在While循环之前进行一次循环之前进行一次FETCH操作，操作，作为第一次循环的条件。作为第一次循环的条件。游标名游标名%FOUND 81例：利用例：利用WHILE循环统计并输出各个部门的平循环统计并输出各个部门的平均工资。均工资。DECLARE CURSOR c2 IS SELECT deptno, avg(sal) asal FROM scott.emp v_dept c2%ROWTY

50、PE;BEGIN OPEN c2; GROUP BY deptno;82FETCH c2 INTO v_dept; WHILE LOOP DBMS_OUTPUT.PUT_LINE(v_dept.deptno| v_dept.asal); FETCH c2 INTO v_dept;END LOOP;CLOSE c2;END; 102567202280301340deptnoasal游标游标c2c2%FOUND83（3）利用）利用FOR循环检索游标循环检索游标FOR 循环变量循环变量 IN LOOP END LOOP;p系统系统自动定义自动定义循环变量，并且其类型为循环变量，并且其类型为“游标游标

51、名名%ROWTYPE” 。p系统系统自动打开游标自动打开游标，不用，不用OPEN语句；语句；p系统系统自动自动不断从游标中不断从游标中取数据取数据并放入循环变量并放入循环变量p系统系统自动进行自动进行%FOUND属性检查属性检查以确定是否有以确定是否有数据数据p当游标中所有的记录都被提取完毕或循环中断当游标中所有的记录都被提取完毕或循环中断时，系统时，系统自动地关闭游标自动地关闭游标。游标名游标名例，利用例，利用FOR循环统计并输出各个部门的平均循环统计并输出各个部门的平均工资。工资。DECLARE CURSOR c3 IS SELECT deptno, avg(sal) avgsal FRO

52、M scott.emp GROUP BY deptno;BEGIN FOR v_dept IN c3 LOOP DBMS_OUTPUT.PUT_LINE (v_dept.deptno|v_dept.avgsal); END LOOP; END; 102567202280301340deptnoasal游标游标c3无需声明，无需声明，自动定义自动定义游标名游标名85p可以可以不在声明部分定义游标不在声明部分定义游标，而在，而在FOR语句语句中直接使用子查询。中直接使用子查询。DECLARE CURSOR c3 IS SELECT deptno, avg(sal) avgsal FROM scot

53、t.emp GROUP BY deptno;BEGINFOR v_emp IN DBMS_OUTPUT.PUT_LINE(v_emp.empno| v_emp.ename);END LOOP;END; (SELECT deptno, avg(sal) avgsal FROM scott.emp GROUP BY deptno) LOOP c3 LOOP 练习：输出所有员工的姓名、员工号、工资和部练习：输出所有员工的姓名、员工号、工资和部门号，用门号，用For循环实现循环实现DECLARECURSOR c1 IS SELECT ename, empno, sal, deptno FROM sco

54、tt.emp; BEGINFor v_e in c1 LOOP DBMS_OUTPUT.PUT_LINE(姓名姓名|v_e.ename |员工号员工号|v_e.empno|工资工资|v_e.sal |部门号部门号|v_e.deptno);END LOOP;END; 86873 参数化显式游标参数化显式游标p通过参数的取值来通过参数的取值来限制限制游标的游标的SQL查询结果查询结果p参数值不同选取的数据行不同，从而能够动态参数值不同选取的数据行不同，从而能够动态使用游标使用游标 Declare Cursor 游标名游标名 Is Select 语句语句 Begin Open 游标名游标名(参数值参

55、数值)(参数名参数名 数据类型数据类型)Where 条件中条件中体现参数体现参数88例，根据部门编号例，根据部门编号deptno输出该部门员工姓名输出该部门员工姓名DECLARECURSOR c4 IS SELECT * FROM scott.emp WHERE v_emp c4%ROWTYPE;BEGIN OPEN c4(10); LOOP FETCH c4 INTO v_emp; EXIT WHEN c_emp%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emp.ename); END LOOP; CLOSE c4;END;deptno=p;(p emp.deptno

56、%TYPE)89p注意：注意：n定义参数化游标时，只能指定参数的类型，定义参数化游标时，只能指定参数的类型，而而不能指定参数的长度、精度、刻度；不能指定参数的长度、精度、刻度；n打开带参数的游标时，实参的个数和数据类打开带参数的游标时，实参的个数和数据类型等必须与游标定义时形参个数和数据类型型等必须与游标定义时形参个数和数据类型等相等相匹配匹配。905 利用游标更新或删除数据利用游标更新或删除数据p游标定义语法游标定义语法CURSOR 游标名游标名 IS SELECT 列列 FROM 表名表名 FOR UPDATEp注意注意n打开游标时对相应的表加锁打开游标时对相应的表加锁, 其他用户不其他用

57、户不能对该表进行更新操作；能对该表进行更新操作；n若数据对象已经被其他会话加锁，则当前若数据对象已经被其他会话加锁，则当前会话挂起等待，若指定了会话挂起等待，若指定了NOWAIT子句，子句，则不等待，返回则不等待，返回ORACLE错误。错误。n当用户执行当用户执行COMMIT或或ROLLBACK操作操作时，数据上的锁会自动被释放。时，数据上的锁会自动被释放。 OF 列名列名 NOWAIT; 91p例如，修改员工的工资，如果员工的部门号为例如，修改员工的工资，如果员工的部门号为10，则工资，则工资提高提高100；如果部门号为；如果部门号为20，则工资提高，则工资提高150，否则工资提，否则工资提

58、高高250。DECLARE CURSOR c_emp IS SELECT * FROM emp FOR UPDATE; v_increment NUMBER;BEGIN FOR v_emp IN c_emp LOOP CASE v_emp.deptno WHEN 10 THEN v_increment:=100; WHEN 20 THEN v_increment:=150; ELSE v_increment:=250; END CASE; UPDATE emp SET sal=sal+v_increment WHERE CURRENT OF c_emp; END LOOP; COMMIT;EN

59、D; 9214.4.3 隐式游标隐式游标p概念概念n所有的所有的SQL语句都有一个执行的缓冲区，语句都有一个执行的缓冲区，隐式游标就是指向该缓冲区的指针，由系隐式游标就是指向该缓冲区的指针，由系统隐含地打开、处理和关闭。隐式游标又统隐含地打开、处理和关闭。隐式游标又称为称为SQL游标。游标。n隐式游标主要用于处理隐式游标主要用于处理INSERT、UPDATE，DELETE以及单行的以及单行的SELECTINTO语句，没有语句，没有OPEN，FETCH，CLOSE等操作命令。等操作命令。9314.5 异常处理异常处理14.5.1 异常概述异常概述1.Oracle错误处理机制错误处理机制pOrac

60、le中对运行时错误的处理采用了异中对运行时错误的处理采用了异常处理机制。常处理机制。 p一个错误对应一个异常，当错误产生时一个错误对应一个异常，当错误产生时抛出相应的异常，并被异常处理器捕获，抛出相应的异常，并被异常处理器捕获，程序控制权传递给异常处理器。程序控制权传递给异常处理器。 942.异常的类型异常的类型（1）预定义的）预定义的Oracle异常（异常（ Oracle错误）错误）p错误产生时，与错误对应的预定义异常被自错误产生时，与错误对应的预定义异常被自动抛出，通过捕获该异常可以对错误进行处理。动抛出，通过捕获该异常可以对错误进行处理。表表14-4（2）非预定义的）非预定义的Oracle异常（异常（ Oracle错误）错