《Oracle 10g入门与提高》第6章：PL、SQL程序设计

1、 第6章 PL/SQL程序设计学习重点：PL/SQL的控制结构PL/SQL记录和表过程与函数异常处理本章逻辑辑结构6.1PL/SQL基础6.1.1变量及声声明6.1.2数据类型型6.1.3表达式6.1.4PL/SQL程序块结结构6.1.5PL/SQL程序的运运行环境境6.2PL/SQL的控制结结构6.2.1顺序结构构6.2.2选择结构构6.2.3NULL结构6.2.4循环结构构6.3PL/SQL记录和表表6.3.1使用%TYPE6.3.2记录类型型本章逻辑辑结构6.3.3使用%ROWTYPE6.3.4表6.4游标6.4.1游标的基基本操作作6.4.2游标的属属性操作作6.4.3参数化游游标和隐

2、隐式游标标6.4.4游标变量量6.5过程与函函数6.5.1过程创建建和调用用6.5.2过程参数数设置与与传递6.5.3函数的创创建、查查询和调调用6.5.4删除过程程和函数数6.5.5子程序的的位置本章逻辑辑结构6.5.6子程序的的依赖性性与执行行权限6.6触发器6.6.1触发器的的类型6.6.2建立触发发器6.7异常处理理6.7.1概念6.7.2声明和处处理异常常6.7.3处理异常常的特殊殊方法6.8本章小结结6.9习题6.1PL/SQL基础础ANSI标准SQL的的Oracle版本的的过程化化语言的的扩展PL/SQL语语言的的特点6.1.1变变量及及声明选择变量量名称的的规则变量必须须以字母

3、母(AZ)开开头其后跟可可选的一一个或多多个字母母、数字字(09)或或特殊字字符$、#或或_变量长度度不超过过30个个字符变量名中中不能有有空格6.1.2数数据类类型声明变量量或常量量的通用用语法变量名CONSTANT数据类型型NOTNULLDEFAULT|:=；PL/SQL的的数据类类型与相相关的子子类型数据类型子类描述BINARY_INTEGERNATURAL、NATURALN、POSITIVE、POSITIVEN、SIGNTYPE存储范围在-2 147 483 647到2 147 483 647之间的有符号整数。使用数学库，NATURAL与NATURALN只存储非负整数，后者不允许存储0

4、；POSITIVE和POSITIVEN只存储正数，后者不允许存储0。SIGNTYPE只存储-1、0和1NUMBER(精度，比例)DEC、DECIMAL、DOUBLE PRECISION、FLOAT、INTEGER、INT、NUMBERIC、REAL、SAMLLINT定点数或浮点数，使用数学库PL/SQL的的数据类类型与相相关的子子类型数据类型子类描述PLS_INTEGER存储范围在-2 147 483 647到2 147 483 647之间的有符号整数，使用机器算法以进行快速计算CHAR(长度)CHARACTER(长度)存储定长字符串，最大长度为32 767字节；然而，数据库中CHAR最长为2

5、000字节VARCHAR2(长度) VARCHAR(长度)、STRING 存储变长字符串，最大长度为32 767字节；然而，数据库中CHAR最长为4000字节 DATE 存储与时间相关的信息，包括日期、小时、分、秒 PL/SQL的的数据类类型与相相关的子子类型数据类型子类描述ROWID 存储数据库表每一行的物理地址 ROWID 存储数据库表每一行的物理地址 UROWID 存储数据库表中每一行的物理的、逻辑的或外部的(非Oracle的)地址 CLOB 存储巨型、单字节字符对象 BLOB 存储巨型二进制对象BFILE 存储数据库外文件系统管理的LOB的文件指针 BOOLEAN 存储逻辑值(真、假与

6、空)6.1.3表表达式式PL/SQL表表达式中中常用的的符号类型字符说明数学运算符+-*/*加号减号乘号除号乘幂布尔运算符=相等不等于小于大于小于等于大于等于PL/SQL表表达式中中常用的的符号类型字符说明其他符号.|&“”范围运算符字符串连接远程数据库指示符字符串起始符号字符串结束符号捆绑变量指示器引用开始符号引用结束符号6.1.4PL/SQL程序块块结构未命名的的PL/SQL包括DECLARE-块声声明部分分BEGIN-块体体的可执执行部分分EXCEPTION-异常常处理部部分6.1.5PL/SQL程序的的运行环环境PL/SQL程程序由PL/SQL引引擎执行行。PL/SQL引擎擎是数据据库

7、服务务器的一一部分图 6.2PL/SQL引擎在在Oracle服务器器中的位位置6.2PL/SQL的控控制结构构程序设计计语言的的核心检测不同同条件并并加以处处理是程程序控制制的主要要部分流向控制制结构PL/SQL程程序控制制程序流流的主要要结构顺序结构构选择结构构NULL结构循环结构构6.2.1顺顺序结结构按照语句句出现的的先后顺顺序执行行6.2.2选选择结结构if逻逻辑结构构3种if逻辑结结构if-thenif-then-elseif-then-elsifif逻辑辑规则规则1：每个if语句句都有自自己的then，以if开始始的语句句行不跟跟语句结结束符“；”规则2：每个if语句句块以相相应的

8、endif结结束规则3：每个if语句句有且只只有一个个else。规则4：else语句句行后面面不跟语语句结束束符规则5：elsif无无匹配的的end if6.2.3NULL结构构空操作或或空值结结构处理方法法ifMarks60thenNull；elseINSERTINTOStudent_CourseVALUES(Pass,StudentNo, CourseNo)；endif；6.2.4循循环结结构3种循环环形式。1.LOOP-EXIT-END循环2.WHILE-LOOP-END循循环3.FOR-IN-LOOP-END循循环1.LOOP-EXIT-END循环环语法如下下loop执行语句句1;执行

9、语句句2;.endloop2.WHILE-LOOP-END循循环语法while布布尔表达达式loop执行语句句1;执行语句句2;.endloop;3.FOR-IN-LOOP-END循环语法for计计数循循环变量量 in reverse起起始值.终止止值loop执行语句句1;执行语句句2;.endloop;6.3PL/SQL记录录和表用户自定定义的类类型使用自定定义类型型去声明明相应的的程序变变量用户自定定义的类类型记录类类型6.3.1使使用%TYPE声明变量量、常量量、记录录中的单单个字段段以及与与数据库库列、表表属性或或其他与与程序结结构相匹匹配的记记录变量量简化了程程序结构构的定义义程序相

10、对对于数据据库的修修改更加加灵活6.3.2记记录类类型用户自定定义的数数据类型型声明记录录类型的的一般语语法TYPE记录类型型ISRECORD( 字段1数据类型型NOT NULL DEFAULT|:=表达式 ,字段2 )6.3.3使使用%ROWTYPE简单地声声明记录录变量和和其他结结构TYPE属性性与ROWTYPE属性的的区别6.3.4表表声明PL/SQL表的的语法TYPEPL/SQL表表名ISTABLEOF数据类型型6.4游游标标PL/SQL用用游标来来管理SQL的的SELECT语句，游标是是为处理理这些语语句而分分配的一一大块内内存，有有时用户户手工定定义游标标。游标标定义类类似于其其他

11、PL/SQL变量量，并且且必须遵遵守同样样的命名名规则。6.4.1游游标的的基本操操作操作顺序序(1)声声明游标标，使用用查询来来定义游游标的列列和行(2)打打开游标标，使用用PL/SQL命令OPEN来打开开一个声声明的游游标(3)提提取数据据，从游游标中重重复提取取每条记记录到数数据结构构中，直直到数据据集合被被提空(4)关关闭游标标，使用用完游标标之后将将其关闭闭6.4.2游游标的的属性操操作游标的属属性ISOPENFOUNDNOTFOUNDROWCOUNT6.4.3参参数化化游标和和隐式游游标两种特殊殊形式的的游标参数化游游标隐式游标标6.4.4游游标变变量1.创建一个个游标变变量2.打

12、开游标标变量OPEN FORSELECTOPEN FOREXECUTEOPEN 一个个绑定的的查询3.使用游标标变量4.关闭游标标变量6.5过过程程与函数数PL/SQL中中的块的的类型命名块匿名块区别6.5.1过过程创创建和调调用定义创建 CREATEOR REPLACE PROCEDURE过程名称称(参数IN |OUT |INOUT类型,.参数IN |OUT |INOUT类型) AUTHID CURRENT_USER| DESIGNERIS|AS过程体调用调用方式式直接利用用EXECUTE命令令在PLSQL块中调调用6.5.2过过程参参数设置置与传递递1.参数模式式2.使用AUTHID选项项

13、创建存存储过程程3.在形参和和实参之之间传递递值4.对形参的的限制5.按引用和和按值传传递参数数6.使用NOCOPY参数数6.5.3函函数的的创建、查询和和调用创建语法法CREATEOR REPLACE FUNCTION函数名称称(参数IN |OUT |INOUT数据类型型,.参数IN |OUT |INOUT数据类型型)RETURN返回类型型AUTHID CURRENT_USER| DESIGNERIS|AS函数体6.5.4删删除过过程和函函数删除过程程的语法法如下：DROP PROCEDURE过程名称称;删除函数数的语法法是：DROP FUNCTION函数名称称;6.5.5子子程序序的位置置

14、1.存储子程程序和数数据字典典2.本地子程程序3.存储子程程序与本本地子程程序的比比较6.5.6子子程序序的依赖赖性与执执行权限限子程序的的依赖性性子程序的的执行权权限6.6触触发发器器触发器(trigger)是是一些过过程，当当发生一一个特定定的数据据库事件件时就执执行这些些过程触发器的的适用情情况维护在表表创建阶阶段通过过声明限限制无法法实现的的复杂完完整性限限制通过记录录修改内内容和修修改者来来审计表表中的信信息在表内容容发生变变更时，自动通通知其他他程序采采取相应应的处理理在订阅发发布环境境下，发发布有关关各种事事件的信信息6.6.1触触发器器的类型型DML触触发器替代触发发器系统事件

15、件触发器器DDL触触发器6.6.2建建立触触发器创建触发发器的通通用语法法：CREATEOR REPLACE TRIGGER触发器名名称BEFORE |AFTER|INSTEAD OF激发触发发器事件件referencing_clauseWHENtrigger_conditionFOR EACHROW触发器代代码;建立触发发器需要要注意的的问题触发器主主体不能能超过32K。如果触触发器长长度超过过了该限限制，就就要把该该体内的的某些代代码放到到单独编编译的包包或存储储子程序序中，并并从触发发器主体体中调用用这些代代码。6.7异异常常处处 理PL/SQL处处理错误误情况的的方法。在PL/SQL代

16、码码部分执执行的过过程中无无论何时时发生错错误，控控制自动动地转向向执行异异常部分分进行异异常处理理。6.7.1概概念常见的有有以下两两种异常常：Oracle预预定义的的异常用户自定定义的异异常PL/SQL中中常见的的预定义义异常异常名ORA错误序号SQL代码说明CURSOR_ALREADY_OPEN ORA-06511-6511试图打开一个已经打开了的游标DUP_VAL_ON_INDEX ORA-00001-1试图向一个表插入一行，但违反了独一无二的索引约束INVALID_CURSOR ORA-01001 -1001试图对游标进行操作，却不能打开该游标INVALID_NUMBER ORA-0

17、1722-1722试图将一个字符串传递给一个数值变量LOGIN_DENIED ORA-01017-1017试图和Oracle数据库建立连接，却不能提供有效的用户名和口令NO_DATA_FOUND ORA-01403 +100执行了SELECT INTO语句，却没有找到有效的行信息NOT_LOGGED_ON ORA-01012-1012试图进行数据库的一个调用，却没有登录到该数据库PL/SQL中中常见的的预定义义异常异常名ORA错误序号SQL代码说明PROGRAM_ERROR ORA-06501-6501 PL/SQL发生内部错误ROWTYPE_MISMATCH ORA-06504-6504PL

18、/SQL返回的游标变量和主游标不相匹配STORAGE_ERROR ORA-06500-6500PL/SQL运行已超出内存空间TOO_MANY_ROWS ORA-01422-1422SELECT INTO命令返回了不止一条结果VALUE_ERROR ORA-06502-6502所赋变量值与变量类型不匹配ZERO_DIVIDE ORA-01476 -1476试图用0作除数TIMEOUT_ON_RESOURCEORA-00051-51Oracle等待资源时超时6.7.2声声明和和处理异异常1.处处理预预定义的的异常语法:EXCEPTIONWHEN异常1OR异常2THEN.;WHEN异常3OR异常4THEN.;.WHEN OTHERSTHEN.;END;2.声声明和和处理用用户自定定义的异异常语法异常名称称EXCEPTION；6.7.3处处理异异常的特特殊方法法利用触发发器生成成一段阻阻止SQL语句句发生异异常的异异常处理理代码