福建省电力公司oracle培训教材--PLSQL语言篇

1、ORACLE入门 PL/SQL语言篇技术支持部 汤庆锋福州磬基电子有限公司本课程学习内容本课程学习内容PL/SQLPL/SQL简介简介PL/SQLPL/SQL数据类型数据类型( (ORACLEORACLE的数据类型的数据类型) ) ORACLORACL内置的内置的SQLSQL函数函数PL/SQLPL/SQL中使用中使用SQLSQLPL/SQLPL/SQL中游标的使用中游标的使用动态动态PL/SQLPL/SQLPL/SQLPL/SQL的异常处理的异常处理PL/SQLPL/SQL简介简介 PL/SQL(Procedural Language/SQL)PL/SQL(Procedural Langua

2、ge/SQL)即模块化的程序设计语言，用于从各即模块化的程序设计语言，用于从各种环境中访问种环境中访问ORACLEORACLE数据库。它具备了许多数据库。它具备了许多SQLSQL中所没有的过程化属性方面中所没有的过程化属性方面的特点。主要包括：的特点。主要包括： 变量和类型变量和类型 控制结构（条件语句、循环语句控制结构（条件语句、循环语句) 过程、函数过程、函数 游标游标 异常处理异常处理PL/SQLPL/SQL程序的用途程序的用途无名块无名块就是没有命名的就是没有命名的PL/SQLPL/SQL块，它可以嵌入某一个应用之中块，它可以嵌入某一个应用之中. .存储过程、函数存储过程、函数也就是命

3、名了的也就是命名了的PL/SQLPL/SQL块，它可以接收参数，并且可以重复的被调用。块，它可以接收参数，并且可以重复的被调用。触发器触发器是与数据库中的表相关的是与数据库中的表相关的PL/SQLPL/SQL块，可以自动的触发。块，可以自动的触发。包包命名了的命名了的PL/SQLPL/SQL块，由一组相关的过程、函数和标识符组成。块，由一组相关的过程、函数和标识符组成。PL/SQLPL/SQL的程序结构的程序结构 PL/SQLPL/SQL的基本单位是的基本单位是“块块”(”(Block)Block)。所有的所有的PL/SQLPL/SQL程序都是由一个或程序都是由一个或多多个个PL/SQLPL/

4、SQL块构成的，这些块可以相互进行嵌套。通常一个块完成程序的一个块构成的，这些块可以相互进行嵌套。通常一个块完成程序的一个单元的工作。一个基本的块由三个部分组成：单元的工作。一个基本的块由三个部分组成： 定义部分定义部分定义变量、常量、游标、异常处理定义变量、常量、游标、异常处理 可执行部分可执行部分 包括对数据库进行操作的包括对数据库进行操作的SQLSQL语句，以及语句，以及对块中的语句进行组织、控制的对块中的语句进行组织、控制的PL/SQLPL/SQL语句。语句。 异常处理（异常处理（Exception) Exception) 部分部分可执行部分中的语句，在执行过程中可执行部分中的语句，在

5、执行过程中出错或出现非正常现象时，所做的响应出错或出现非正常现象时，所做的响应处理处理DECLAREBEGINEXCEPTIONENDPL/SQL块结构块结构PL/SQLPL/SQL数据类型数据类型字 段 类 型中 文 说 明限 制 条 件其 它 说 明C H A R固 定 长 度 字 符 串最 大 长 度2 0 0 0 b ytesVA R C H A R 2可 变 长 度 的 字 符 串最 大 长 度4 0 0 0 b ytes可 做 索 引 的 最大 长 度 7 4 9N C H A R根 据 字 符 集 而 定 的 固定 长 度 字 符 串最 大 长 度2 0 0 0 b ytesN

6、VA R C H A R 2根 据 字 符 集 而 定 的 可变 长 度 字 符 串最 大 长 度4 0 0 0 b ytesD AT E日 期 （ 日 -月 -年 ）D D -M M -Y Y （ H H -M I-S S ）经过严格测试 ， 无 千 虫 问题L O N G超 长 字 符 串最 大 长 度2 G （ 2 3 1 -1 ）足 够 存 储 大 部头 著 作R AW固 定 长 度 的 二 进 制 数据最 大 长 度2 0 0 0 b ytes可 存 放 多 媒 体图 象 声 音 等L O N G R A W可 变 长 度 的 二 进 制 数据最 大 长 度2 G同 上B L O B

7、二 进 制 数 据最 大 长 度4 GC L O B字 符 数 据最 大 长 度4 GN C L O B根 据 字 符 集 而 定 的 字符 数 据最 大 长 度4 GB F IL E存 放 在 数 据 库 外 的 二进 制 数 据最 大 长 度4 GR O W ID数 据 表 中 记 录 的 唯 一行 号1 0 b ytes*为0 或1N R O W ID二 进 制 数 据 表 中 记 录的 唯 一 行 号最 大 长 度4 0 0 0b ytesN U M B E R (P,S )数 字 类 型P 为 整 数 位 ， S 为 小 数 位D E C IM A L (P,S )数 字 类 型P

8、为 整 数 位 ， S 为 小 数 位IN T E G E R整 数 类 型小 的 整 数F L O AT浮 点 数 类 型N U M B E R (3 8 )， 双 精 度R E A L实 数 类 型N U M B E R (6 3 )， 精 度 更 高PL/SQLPL/SQL数据类型数据类型常用的数据类型常用的数据类型CHARCHAR： 存放固定长度的字符串存放固定长度的字符串VARCHAR2VARCHAR2：存放可变长度的字符串存放可变长度的字符串NUMBERNUMBER： 存放存放0 0、正负数、浮点数、正负数、浮点数DATEDATE： 存放时间数据（包括日期和时间）存放时间数据（包括

9、日期和时间）LONGLONG： 存放变长字符串。一般用来存储大文本存放变长字符串。一般用来存储大文本RAW LONG RAW LONG 存放多媒体数据存放多媒体数据, ,如声音、图片如声音、图片例如：创建一雇员表例如：创建一雇员表CREATE TABLE CREATE TABLE empemp( ( empno empno number(4),number(4), ename ename varchar2(10),varchar2(10), hiredatehiredate date, date, sal sal number(7,2), number(7,2), deptno deptno

10、number(2)number(2););ORACLEORACLE内置的内置的SQLSQL函数函数 SQLSQL函数按照传入参数的类型，可分为字符串函数、数值函数、日期函数、函数按照传入参数的类型，可分为字符串函数、数值函数、日期函数、其他函数。以下分别列举较常用的部分进行说明其他函数。以下分别列举较常用的部分进行说明。字符串函数：字符串函数： UPPER(s)UPPER(s)将字符串将字符串ss转换成大写的形式返回。转换成大写的形式返回。 LOWER(s)LOWER(s)将字符串将字符串ss转换成小写的形式返回。转换成小写的形式返回。 SUBSTR(s,a ,b)SUBSTR(s,a ,b)

11、返回从字符位置返回从字符位置a a开始有开始有b b个字符长的个字符长的ss的一部分。的一部分。 若若a a为正数为正数: :从左边向右边计算从左边向右边计算 若若a a为负数为负数: :从右边向左边计算从右边向左边计算实例：实例：Select Select substrsubstr(abcdefg123,4) from dual; (abcdefg123,4) from dual; 结果返回：结果返回：defg123defg123Select Select substrsubstr(abcdefg123,4,2) from dual; (abcdefg123,4,2) from dual;

12、结果返回：结果返回：dedeSelect Select substrsubstr(abcdefg123,-4,2) from dual; (abcdefg123,-4,2) from dual; 结果返回：结果返回：g1g1 RTRIM(s1,s2)RTRIM(s1,s2) 返回删除从最右边算起出现在返回删除从最右边算起出现在s2s2中的字符的中的字符的s1s1。s2s2缺省为空格缺省为空格实例：实例：Select Select rtrimrtrim(aabbccddaabbccdd,cdcd) from dual; ) from dual; 结果返回：结果返回：aabbaabb Select

13、 Select rtrimrtrim(aabbccddaabbccdd,dc) from dual; ,dc) from dual; 结果返回：结果返回：aabbaabbORACLORACL内置的内置的SQLSQL函数函数ConcatConcat(s1,s2)(s1,s2)返回串接上返回串接上s2s2之后的之后的s1.s1.该函数与该函数与|运算符作用相同。运算符作用相同。实例：实例：select select concatconcat(abcabc,def) from dual; ,def) from dual; 返回结果：返回结果：abcdefabcdef select select ab

14、cabc|def from dual; |def from dual; 返回结果：返回结果：abcdefabcdefLength(s)Length(s)以字节为单位返回字符串以字节为单位返回字符串s s的长度。的长度。ORACLORACL内置的内置的SQLSQL函数函数数值函数数值函数Ceil(n)Ceil(n)返回大于或等于返回大于或等于n n的整数的整数Select ceil(18.6),ceil(-18.6) from dual;Select ceil(18.6),ceil(-18.6) from dual;Floor(n)Floor(n)返回小于或等于返回小于或等于n n的整数的整数S

15、elect floor(18.6),floor(-18.6) from dual;Select floor(18.6),floor(-18.6) from dual;Mod(x,y)Mod(x,y)返回返回x x除以除以y y得余数，若得余数，若y y为为0 0，则返回，则返回x x。Select mod(23,5),mod(4,1.3) from dual; Select mod(23,5),mod(4,1.3) from dual; 返回结果：返回结果：1.1 , 1.1 , 0.10.1Round(x,y)Round(x,y)返回舍入到小数点右边返回舍入到小数点右边y y为的为的x x值

16、。值。Select round(1.56),round(1.56,1),round(123.4,-1)Select round(1.56),round(1.56,1),round(123.4,-1)from dual; from dual; 返回结果：返回结果：1.1 , 1.1 , 0.1 ,1200.1 ,120ORACLORACL内置的内置的SQLSQL函数函数日期函数日期函数 SysdateSysdate返回当前的日期和时间返回当前的日期和时间 Add_months(D,x)Add_months(D,x) Last_day(D)Last_day(D)返回日期返回日期D D的月份的最后一

17、天的日期的月份的最后一天的日期 Months_Between(D1,D2)Months_Between(D1,D2)返回在返回在D1D1和和D2D2之间月的数目之间月的数目。 TruncTrunc(D,format)(D,format)返回结尾由返回结尾由formatformat指定的单位的日期。指定的单位的日期。示例：示例：Select Select trunctrunc( (sysdatesysdate,year) from dual; ,year) from dual; 返回今年的第一天返回今年的第一天Select Select trunctrunc( (sysdatesysdate,m

18、m) from dual; ,mm) from dual; 返回本月的第一天返回本月的第一天Select Select trunctrunc( (sysdatesysdate,D) from dual; ,D) from dual; 返回本周的第一天返回本周的第一天ORACLORACL内置的内置的SQLSQL函数函数转换函数转换函数 To_char(DTo_char(D，format)format)将日期转换为指定格式的字符串。将日期转换为指定格式的字符串。示例：示例：Select to_char(Select to_char(sysdatesysdate,yyyyyyyy/mm/mm/dd

19、hhdd hh:mi:mi:ssss) from dual; ) from dual; To_Date(string,format)To_Date(string,format)将字符串转换成日期格式将字符串转换成日期格式示例：示例：Select to_date(2000/10/01,Select to_date(2000/10/01,yyyyyyyy/mm/mm/dddd) from dual; ) from dual; Last_day(D)Last_day(D)返回日期返回日期D D的月份的最后一天的日期的月份的最后一天的日期 To_Number(string,format)To_Numb

20、er(string,format)ORACLORACL内置的内置的SQLSQL函数函数其它函数其它函数 NvlNvl(a,b)(a,b)空值替换函数，若空值替换函数，若a a为空，则替换成为空，则替换成b b。示例：示例：Select Select enameename, ,salsal, ,salsal+ +nvlnvl( (commcomm,0) from dual; ,0) from dual; DECODE(DECODE(条件条件, ,值值1,1,翻译值翻译值1,1,值值2,2,翻译值翻译值2,.2,.值值n,n,翻译值翻译值n,n,缺省值缺省值) )该函数的含义如下：该函数的含义如下

21、： IF IF 条件条件= =值值1 1 THEN THEN RETURN(RETURN(翻译值翻译值1) 1) ELSIF ELSIF 条件条件= =值值2 2 THEN THEN RETURN(RETURN(翻译值翻译值2) 2) . . ELSIF ELSIF 条件条件= =值值n THEN n THEN RETURN(RETURN(翻译值翻译值n) n) ELSE ELSE RETURN(RETURN(缺省值缺省值) ) END IF END IF PL/SQLPL/SQL的注释的注释 注释增强了可阅读性，使得程序更易于理解。注释增强了可阅读性，使得程序更易于理解。单行注释单行注释-

22、- commentcomment多行注释多行注释/ /* * comment comment * */ /注意：此注释不能作用在注意：此注释不能作用在SQLSQL语言上。语言上。示例：示例：DECLAREDECLARE v_ v_deptnodeptno number(2); - number(2); -与雇员表中部门代码字段交互的变量与雇员表中部门代码字段交互的变量 v_ v_sal sal number(7,2); -number(7,2); -与雇员表中工资字段交互的变量与雇员表中工资字段交互的变量BEGINBEGIN / /* *this is this is a test! a te

23、st! * */ / select select deptnodeptno, ,salsal into v_ into v_deptnodeptno,v_,v_salsal from from empemp where where empnoempno=7788;=7788;END;END;PL/SQLPL/SQL块的定义部分块的定义部分 在在PL/SQLPL/SQL块中引用的所有标识符，都必须在定义部分中明确定义。块中引用的所有标识符，都必须在定义部分中明确定义。定义常量定义常量 格式：标识符格式：标识符 CONSTANTCONSTANT数据类型：数据类型：= = 表达式表达式 例：定义一常

24、量例：定义一常量PIPI，值为值为3.143.14。PI CONSTANT NUMBER(3,2) := 3.14;PI CONSTANT NUMBER(3,2) := 3.14;定义标量型变量定义标量型变量 标量型数据类型，是指数据类型为个体型。标量型数据类型，是指数据类型为个体型。 格式：格式： NOT NULL :=|DEFAULT NOT NULL :=|DEFAULT 例：定义一宽度为例：定义一宽度为1010个字符的字符串变量个字符的字符串变量X X。 DECLARE DECLARE X CHAR(5) X CHAR(5)； y CHAR(5):=ORACLE; y CHAR(5):

25、=ORACLE; Z CHAR(5) default oracle; Z CHAR(5) default oracle;代表数据库列的变量代表数据库列的变量先看一个示例：创建一先看一个示例：创建一PL/SQLPL/SQL块，根据部门号，返回部门名称块，根据部门号，返回部门名称. .DECLAREDECLARE v_ v_dnamedname dept. dept.dnamedname%type;%type;BEGINBEGIN SELECT SELECT dnamedname INTO v_ INTO v_dnamedname FROM DEPT WHERE FROM DEPT WHERE d

26、eptnodeptno=10;=10; DBMS_OUTPUT.PUT_LINE(v_ DBMS_OUTPUT.PUT_LINE(v_dnamedname););EXCEPTION WHEN NO_DATA_FOUND THENEXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(sorry:no data found!); DBMS_OUTPUT.PUT_LINE(sorry:no data found!);END;END;问题：问题： 所引用的数据库表中的数据类型不知道？所引用的数据库表中的数据类型不知道？ 所引用的数据库表中的数据类

27、型将来改变改变怎么办？所引用的数据库表中的数据类型将来改变改变怎么办？PL/SQLPL/SQL块的定义部分块的定义部分另一种定义标量型变量的方法另一种定义标量型变量的方法%TYPETYPE 定义一个变量定义一个变量, ,其数据类型与已知变量的数据类型相同，或者与数据库其数据类型与已知变量的数据类型相同，或者与数据库表的某个列的数据类型相同表的某个列的数据类型相同。% %TYPETYPE的优点在于：的优点在于： 所引用的数据库表中的数据类型可以不必知道。所引用的数据库表中的数据类型可以不必知道。 所引用的数据库表中的数据类型可以实时改变。所引用的数据库表中的数据类型可以实时改变。格式：格式： N

28、OT NULL :=|DEFAULT NOT NULL :=|DEFAULT .%TYPETYPE例：定义一个变量，其数据类型基于另一个变量例：定义一个变量，其数据类型基于另一个变量DECLAREDECLARE V_1 NUMBER(7,2); V_1 NUMBER(7,2); V_11 V1%TYPE := 12345.6; V_11 V1%TYPE := 12345.6;例：定义一个变量，其数据类型基于数据库中表的列例：定义一个变量，其数据类型基于数据库中表的列DECLAREDECLARE v_ v_ename ename EMP.ENAME%TYPE;EMP.ENAME%TYPE; V_

29、SAL EMP.SAL%TYPE; V_SAL EMP.SAL%TYPE;PL/SQLPL/SQL块的定义部分块的定义部分另一种定义组合型变量的方法另一种定义组合型变量的方法%ROWTYPEROWTYPE 定义一个变量定义一个变量, ,其数据类型与数据库表的数据结构相同。其数据类型与数据库表的数据结构相同。% %ROWTYPEROWTYPE的优点在于：的优点在于： 所引用的数据库表中的数据类型可以不必知道。所引用的数据库表中的数据类型可以不必知道。 所引用的数据库表中的数据类型可以实时改变。所引用的数据库表中的数据类型可以实时改变。简易格式：简易格式： %ROWTYPEROWTYPE例：例：D

30、ECLAREDECLARE v_ v_emp empemp emp% %rowtyperowtype; ;BEGINBEGIN SELECT SELECT * * INTO v_ INTO v_empemp FROM FROM empemp WHERE WHERE empnoempno=7788;=7788; DBMS_OUTPUT.PUT_LINE(v_ DBMS_OUTPUT.PUT_LINE(v_empemp. .empnoempno);); DBMS_OUTPUT.PUT_LINE(v_ DBMS_OUTPUT.PUT_LINE(v_empemp. .enameename);); DB

31、MS_OUTPUT.PUT_LINE(v_ DBMS_OUTPUT.PUT_LINE(v_empemp.job);.job); DBMS_OUTPUT.PUT_LINE(v_ DBMS_OUTPUT.PUT_LINE(v_empemp. .salsal););END;END;变量的引用和赋值变量的引用和赋值标量变量赋值标量变量赋值格式：格式： :=:= ；例：例：V_NAME := JOAN;V_NAME := JOAN; v_ v_demptnodemptno:=10;:=10;组合型变量赋值组合型变量赋值格式：变量格式：变量. .域名（主键值）：域名（主键值）：= =表达式；表达式；例：例

32、：v_v_empemp. .salsal:=8888;:=8888; v_ v_empemp. .commcomm:=8888;:=8888;PL/SQLPL/SQL中使用中使用SQLSQL 在在PL/SQLPL/SQL块中，通过块中，通过SQLSQL语句对语句对ORACLEORACLE数据库中的数据进行数据库中的数据进行存取。在存取。在PL/SQLPL/SQL中：中：可以使用的可以使用的SQLSQL语句有：语句有：SELECTSELECT、INSERTINSERT、DELETEDELETE、UPDATEUPDATE、COMMITCOMMIT、ROLLBACKROLLBACK不可以直接使用的不

33、可以直接使用的SQLSQL语句有：语句有：数据定义语句（数据定义语句（DDLDDL），），如：如：CREATE TALBECREATE TALBE，DROP TABLEDROP TABLE数据控制语句（数据控制语句（DCLDCL），），如：如：GRANTGRANT、REVOKEREVOKE备注：在备注：在PL/SQL2.1PL/SQL2.1以上版本，允许通过以上版本，允许通过DBMS_SQLDBMS_SQL包来创建动态包来创建动态SQLSQL语句。语句。PL/SQLPL/SQL中使用中使用SQLSQLSELECTSELECT语句语句SELECTSELECT语句：将数据从数据库中检索出来并放入语

34、句：将数据从数据库中检索出来并放入PL/SQLPL/SQL变量中。变量中。格式：格式：SELECT SELECT INTO INTO FROM FROM 例：查询某个雇员的姓名及工资。例：查询某个雇员的姓名及工资。DECLAREDECLARE v_ v_empno empempno emp. .empnoempno%type:=7788;%type:=7788; v_ v_ename empename emp. .enameename%type;%type; v_ v_sal empsal emp. .salsal%type;%type;BEGINBEGIN select select ena

35、meename, ,salsal into v_ into v_enameename,v_,v_salsal from from empemp where where empnoempno=v_=v_empnoempno; ; DBMS_OUTPUT.PUT_LINE(v_ DBMS_OUTPUT.PUT_LINE(v_empnoempno|v_|v_enameename|v_|v_salsal););EXCEPTION WHEN NO_DATA_FOUND THENEXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(sorry:no

36、data found!); DBMS_OUTPUT.PUT_LINE(sorry:no data found!);END;END;/ /PL/SQLPL/SQL中的中的SELECTSELECT语句中必须包含语句中必须包含INTOINTO子句，而且对应的个数要相同，位置要一一对应。子句，而且对应的个数要相同，位置要一一对应。查询结果只返回一条记录，否则会产生异常情况。查询结果只返回一条记录，否则会产生异常情况。（1 1）查询结果多于一条记录）查询结果多于一条记录 异常变量：异常变量：TOO_MANY_ROWSTOO_MANY_ROWS（2 2）查询结果没有返回记录查询结果没有返回记录 异常变量：

37、异常变量：NO_DATA_FOUNDNO_DATA_FOUNDPL/SQLPL/SQL中使用中使用SQLSQL 在在PL/SQLPL/SQL中，对数据库进行插入中，对数据库进行插入( (INSERT)INSERT)、删除删除( (DELETE)DELETE)、修改（修改（UPDATEUPDATE）语句，其语法形式与语句，其语法形式与SQLSQL中的是完全一样的。中的是完全一样的。例：在例：在EMPEMP表中删去某个雇员。表中删去某个雇员。BEGINBEGIN DELETE DELETE empemp WHERE WHERE empnoempno=7788;=7788; COMMIT; COMM

38、IT;END;END;PL/SQLPL/SQL的执行部分的执行部分流程控制语句流程控制语句流程控制语句主要有三种：流程控制语句主要有三种：条件控制条件控制循环控制循环控制跳转控制跳转控制流程控制语句流程控制语句条件控制条件控制语法格式：语法格式：IF 条件条件THEN 语句；语句；ELSIF 条件条件THEN 语句；语句；ELSE 语句；语句；END IF；例：根据职务浮动工资例：根据职务浮动工资IF v_job=MANAGER THEN v_sal := v_sal*1.3;ELSIF v_job=SALESMAN THEN v_sal := v_sal*1.2;ELSE v_sal :=

39、v_sal*1.1;END IF；update emp set sal=v_salwhere empno=1234;流程控制语句流程控制语句循环控制循环控制在在PL/SQLPL/SQL中循环控制的有以下四种：中循环控制的有以下四种：简单循环简单循环FORFOR循环循环WHEREWHERE循环循环用于游标的用于游标的FORFOR循环循环循环控制循环控制简单循环简单循环语法格式：LOOP语句1；语句2； EXIT WHEN 条件；END LOOP；例：把数值1到50顺序插入表中。V_counter:=1;LOOPINSERT INTO temp_tableVALUES (v_counter);EX

40、IT WHEN v_counter50；V_count :=v_count+1;END LOOP；循环控制循环控制FORFOR循环循环语法格式：FOR 循环变量IN REVERSE 下界.上界LOOP语句1；语句2； END LOOP；REVERSE:使计数器由上界到下界递减计数例：把数值1到50顺序插入表中。FOR v_counter IN 1.50 LOOPINSERT INTO temp_tableVALUES (v_counter);END LOOP；循环控制循环控制WHILEWHILE循环循环语法格式：WHILE 条件LOOP语句1；语句2； END LOOP；例：把数值1到50顺序

41、插入表中。V_counter:=1;WHILE v_counter=50 LOOPINSERT INTO temp_tableVALUES (v_counter);V_count:=v_count+1;END LOOP；跳转控制语句跳转控制语句语法格式：语法格式：GOTO GOTO ;在进行在进行PL/SQLPL/SQL编程时编程时, ,尽量避免或不用尽量避免或不用GOTOGOTO语句语句, ,因为这种无因为这种无条件的跳转语句条件的跳转语句 打破了程序的逻辑性打破了程序的逻辑性, ,有悖于自顶向下的编程有悖于自顶向下的编程风格风格. .PL/SQLPL/SQL游标的使用游标的使用游标游标(

42、(CURSOR)CURSOR)的功能的功能, ,是是ORALCEORALCE系统为了将所有查询结果返回给用户程序而系统为了将所有查询结果返回给用户程序而提供的。一个游标提供的。一个游标, ,实际上是在内存中开辟一个工作区实际上是在内存中开辟一个工作区, ,它对应一条它对应一条SELECTSELECT语语句。当打开游标时，就是执行游标所对应的句。当打开游标时，就是执行游标所对应的SELECTSELECT语句，并将其查询结果放语句，并将其查询结果放入工作区，并且指针指向工作区的首部。通过光标上的操作可以把这些记录入工作区，并且指针指向工作区的首部。通过光标上的操作可以把这些记录检索到客户端的应用程

43、序。检索到客户端的应用程序。CURSOR内存区POINTERSELECTINTO:SELECTINTO:只能查询数据库的单条记录，并把记录的数据赋给变量。只能查询数据库的单条记录，并把记录的数据赋给变量。游标游标定义和操纵游标定义和操纵游标步骤：步骤：1)1) 定义游标定义游标2)2) 打开游标打开游标3)3) 从游标中取值从游标中取值4)4) 关闭游标关闭游标定义游标定义游标 定义游标，就是定义一个游标名，以及与其相对应的定义游标，就是定义一个游标名，以及与其相对应的SELECTSELECT语句。语句。语法格式：语法格式：CURSOR CURSOR 游标名游标名I S I S SELECTS

44、ELECT子句；子句；示例：定义一个包含所有雇员记录的游标。示例：定义一个包含所有雇员记录的游标。 cursor cur_ cursor cur_empemp is is select select * * from from empemp; ;打开游标打开游标 打开游标，就是执行游标所对应的打开游标，就是执行游标所对应的SELECTSELECT语句，将其查询结语句，将其查询结果放入工作区，并且指针指向工作区的首部。果放入工作区，并且指针指向工作区的首部。语法格式：语法格式：OPEN OPEN 游标名；游标名；从游标中取值从游标中取值 取值工作是将游标工作区中的数据取出一行，放入指定的输取值工

45、作是将游标工作区中的数据取出一行，放入指定的输出变量中。出变量中。语法格式：语法格式：FETCH FETCH 游标名游标名INTO INTO 变量变量1 1, ,变量变量2 2 ；示例：示例：fetch cur_fetch cur_empemp into v_ into v_empnoempno,v_,v_enameename,v_,v_salsal,v_,v_commcomm,v_,v_deptnodeptno关闭游标关闭游标 释放与该游标相关的资源。释放与该游标相关的资源。语法格式：语法格式：CLOSE CLOSE ;示例：示例：close cur_close cur_empemp; ;游

46、标的属性游标的属性 从游标工作区中逐一地取数据，可以在循环中完成。但循环从游标工作区中逐一地取数据，可以在循环中完成。但循环的开始以及结束，需以游标属性为依据。的开始以及结束，需以游标属性为依据。游标属性有：游标属性有： % %ISOPENISOPEN： 判断游标是否被打开判断游标是否被打开 % %NOTFOUNDNOTFOUND：判断何时中断循环判断何时中断循环 % %FOUNDFOUND： 与与% %NOTFOUNDNOTFOUND相反相反 % %ROWCOUNTROWCOUNT：实际从游标工作区抽取的记录数实际从游标工作区抽取的记录数示例：示例：Open cur_Open cur_emp

47、emp; ;LoopLoop fetch cur_ fetch cur_empemp into v_ into v_empnoempno,v_,v_enameename,v_,v_salsal,v_,v_deptnodeptno; ; exit when cur_ exit when cur_empemp%NOTFOUND;%NOTFOUND;End loop;End loop;游标游标用于游标的用于游标的FORFOR循环循环 游标的游标的FORFOR循环，是一种简单的游标操作方法，系统隐式地循环，是一种简单的游标操作方法，系统隐式地进行游标的打开、提取数据、循环、关闭。进行游标的打开、提取数

48、据、循环、关闭。格式：格式： FOR FOR 记录变量记录变量IN IN 游标名游标名LOOPLOOP 语句语句; END LOOP ;END LOOP ; :由系统隐含定义的记录名由系统隐含定义的记录名示例：示例：DeclareDeclare cursor cur_ cursor cur_empemp is select is select * * from from empemp; ;BeginBegin for v_ for v_empemp in cur_ in cur_empemp loop loop DBMS_OUTPUT.PUT_LINE(v_DBMS_OUTPUT.PUT_LI

49、NE(v_empemp. .enameename);); DBMS_OUTPUT.PUT_LINE(v_DBMS_OUTPUT.PUT_LINE(v_empemp. .salsal);); end loop; end loop;End; End; 一个完整的示例一个完整的示例例：建立一存储过程，根据职务修改工资例：建立一存储过程，根据职务修改工资CREATE OR REPLACE PROCEDURE p_update_sal AS CURSOR cur_emp IS SELECT * FROM emp; v_emp cur_emp%ROWTYPE;BEGIN OPEN cur_emp; LOO

50、P FETCH cur_emp INTO v_emp; EXIT WHEN cur_emp%NOTFOUND; IF v_emp.job=MANAGER THEN v_emp.sal:=v_emp.sal*1.3;ELSIF v_emp.job=SALESMAN THEN v_emp.sal:=v_emp.sal*1.2; ELSE v_emp.sal:=v_emp.sal*1.1; END IF； UPDATE emp SET sal=v_emp.sal WHERE empno=v_emp.empno; END LOOP; CLOSE cur_emp; COMMIT;END;一个完整的示例一

51、个完整的示例( (用用FORFOR循环循环) )CREATE PROCEDURE p_update_sal AS CURSOR cur_emp IS SELECT * FROM emp;BEGIN FOR v_emp IN cur_emp LOOP IF v_emp.job=MANAGER THEN v_emp.sal:=v_emp.sal*1.3; ELSIF v_emp.job=SALESMAN THEN v_emp.sal:=v_emp.sal*1.2; ELSE v_sal := v_sal*1.1; END IF； UPDATE emp SET sal=v_emp.sal WHERE

52、 empno=v_emp.empno; END LOOP; COMMIT;END;示例示例DECLARE DECLARE CURSOR c1 is CURSOR c1 is SELECT SELECT enameename, , empnoempno, , salsal FROM FROM emp emp ORDER BY ORDER BY salsal DESC; - start with highest paid employee DESC; - start with highest paid employee my_ my_enameename CHAR(10); CHAR(10); m

53、y_ my_empnoempno NUMBER(4); NUMBER(4); my_ my_salsal NUMBER(7,2); NUMBER(7,2); BEGIN BEGIN OPEN c1; OPEN c1; FOR i IN 1.5 LOOP FOR i IN 1.5 LOOP FETCH c1 INTO my_ FETCH c1 INTO my_enameename, my_, my_empnoempno, my_, my_salsal; ; EXIT WHEN c1%NOTFOUND; EXIT WHEN c1%NOTFOUND; INSERT INTO temp VALUES

54、(my_ INSERT INTO temp VALUES (my_salsal, my_, my_empnoempno, my_, my_enameename); ); COMMIT; COMMIT; END LOOP; END LOOP; CLOSE c1; CLOSE c1; END; END; 异常处理异常处理 PL/SQLPL/SQL中，将程序执行过程中的一个警告或错误称为一个异中，将程序执行过程中的一个警告或错误称为一个异常常( (EXCEPTION)EXCEPTION)。异常情况的种类有三种：异常情况的种类有三种：1.1. 预定义的预定义的ORACLEORACLE错误错误ORACL

55、EORACLE预定一的异常情况大约有预定一的异常情况大约有2424个。对这种异常情况的处理，无个。对这种异常情况的处理，无须在程序中定义，由须在程序中定义，由ORACLEORACLE自动将其引发。自动将其引发。2.2. 非预定义的非预定义的ORACLEORACLE错误错误即其他标准的即其他标准的ORACLEORACLE错误。对这种异常情况的处理，需在定义部分定义错误。对这种异常情况的处理，需在定义部分定义，然后由，然后由ORACLEORACLE自动将其引发。自动将其引发。3.3. 用户定义的错误用户定义的错误程序执行过程中，出现编程人员认为非正常的。对这种异常情况的处理程序执行过程中，出现编程

56、人员认为非正常的。对这种异常情况的处理，需在定义部分定义，然后显式由地将其引发。，需在定义部分定义，然后显式由地将其引发。异常处理异常处理语法格式：语法格式：EXCEPTIONEXCEPTION WHEN WHEN 1 THENTHEN 语句；语句； WHEN WHEN 异常情况异常情况2 2 THENTHEN 语句；语句； WHEN OTHERS THENWHEN OTHERS THEN 语句；语句； OTHERSOTHERS：指没有列在异常处理部分中的指没有列在异常处理部分中的其他异常情况。其他异常情况。DECLAREBEGINEXCEPTIONENDPL/SQL块执行过程块执行过程异常发

57、生异常处理异常处理异常处理预定义的预定义的ORACLEORACLE错误错误预定义的异常名称预定义的异常名称错误号错误号说明说明CURSOR_ALREADY_OPENCURSOR_ALREADY_OPENORA-6511ORA-6511试图打开一个已打开的光标试图打开一个已打开的光标LOGIN_DENIED LOGIN_DENIED ORA-1017ORA-1017无效的用户名或者口令无效的用户名或者口令NO_DATA_FOUNDNO_DATA_FOUNDORA-1403ORA-1403查询未找到数据查询未找到数据NOT_LOGGED_ON NOT_LOGGED_ON ORA-1012ORA-1

58、012还未连接就试图数据库操作还未连接就试图数据库操作DUP_VAL_ON_INDEX DUP_VAL_ON_INDEX ORA-0001ORA-0001试图破坏一个唯一性限制试图破坏一个唯一性限制TIMEOUT_ON_RESOURCE TIMEOUT_ON_RESOURCE ORA-0051ORA-0051发生超时发生超时TRANSACTION_BACKED_OUT TRANSACTION_BACKED_OUT ORA-006ORA-006由于死锁提交被退回由于死锁提交被退回TOO_MANY_ROWSTOO_MANY_ROWSORA-1422ORA-1422SELECT INTDSELECT INTD命令返回的多行命令返回的多行异常处理异常处理预定义异常示例：预定义异常示例：BEGINBEGIN insert into insert into empemp ( (empnoempno, ,enameename) values (7788,) values (7788,testuserte