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

第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参数化游标和隐式游标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变量及声明选择变量名称的规则•变量必须以字母(A~Z)开头•其后跟可选的一个或多个字母、数字(0~9)或特殊字符$、#或_•变量长度不超过30个字符•变量名中不能有空格6.1.2数据类型声明变量或常量的通用语法变量名[CONSTANT]数据类型[[NOTNULL]{DEFAULT|:=}]；PL/SQL的数据类型与相关的子类型数据类型子类描述BINARY_INTEGERNATURAL、NATURALN、POSITIVE、POSITIVEN、SIGNTYPE存储范围在-2147483647到2147483647之间的有符号整数。使用数学库，NATURAL与NATURALN只存储非负整数，后者不允许存储0；POSITIVE和POSITIVEN只存储正数，后者不允许存储0。SIGNTYPE只存储-1、0和1NUMBER(精度，比例)DEC、DECIMAL、DOUBLEPRECISION、FLOAT、INTEGER、INT、NUMBERIC、REAL、SAMLLINT定点数或浮点数，使用数学库PL/SQL的数据类型与相关的子类型数据类型子类描述PLS_INTEGER存储范围在-2147483647到2147483647之间的有符号整数，使用机器算法以进行快速计算CHAR(长度)CHARACTER(长度)存储定长字符串，最大长度为32767字节；然而，数据库中CHAR最长为2000字节VARCHAR2(长度)VARCHAR(长度)、STRING存储变长字符串，最大长度为32767字节；然而，数据库中CHAR最长为4000字节DATE存储与时间相关的信息，包括日期、小时、分、秒PL/SQL的数据类型与相关的子类型数据类型子类描述ROWID存储数据库表每一行的物理地址ROWID存储数据库表每一行的物理地址UROWID存储数据库表中每一行的物理的、逻辑的或外部的(非Oracle的)地址CLOB存储巨型、单字节字符对象BLOB存储巨型二进制对象BFILE存储数据库外文件系统管理的LOB的文件指针BOOLEAN存储逻辑值(真、假与空)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引擎是数据库服务器的一部分SQL语句执行器SQL语句执行器PL/SQL块过程化语句执行器PL/SQL引擎Oracle服务器图6.2PL/SQL引擎在Oracle服务器中的位置6.2PL/SQL的控制结构程序设计语言的核心检测不同条件并加以处理是程序控制的主要部分流向控制结构PL/SQL程序控制程序流的主要结构•顺序结构•选择结构•NULL结构•循环结构6.2.1顺序结构按照语句出现的先后顺序执行6.2.2选择结构if逻辑结构3种if逻辑结构•if-then•if-then-else•if-then-elsifif逻辑规则规则1：每个if语句都有自己的then，以if开始的语句行不跟语句结束符“；”规则2：每个if语句块以相应的endif结束规则3：每个if语句有且只有一个else。规则4：else语句行后面不跟语句结束符规则5：elsif无匹配的endif6.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;执行语句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声明变量、常量、记录中的单个字段以及与数据库列、表属性或其他与程序结构相匹配的记录变量简化了程序结构的定义程序相对于数据库的修改更加灵活6.3.2记录类型用户自定义的数据类型声明记录类型的一般语法TYPE记录类型ISRECORD(字段1数据类型[NOTNULL]{DEFAULT|:=}表达式][,字段2…])6.3.3使用%ROWTYPE简单地声明记录变量和其他结构％TYPE属性与％ROWTYPE属性的区别6.3.4表声明PL/SQL表的语法TYPEPL/SQL表名ISTABLEOF数据类型6.4游标PL/SQL用游标来管理SQL的SELECT语句，游标是为处理这些语句而分配的一大块内存，有时用户手工定义游标。游标定义类似于其他PL/SQL变量，并且必须遵守同样的命名规则。6.4.1游标的基本操作操作顺序(1)声明游标，使用查询来定义游标的列和行(2)打开游标，使用PL/SQL命令OPEN来打开一个声明的游标(3)提取数据，从游标中重复提取每条记录到数据结构中，直到数据集合被提空(4)关闭游标，使用完游标之后将其关闭6.4.2游标的属性操作游标的属性•％ISOPEN•％FOUND•％NOTFOUND•％ROWCOUNT6.4.3参数化游标和隐式游标两种特殊形式的游标参数化游标隐式游标6.4.4游标变量1.创建一个游标变量2.打开游标变量•OPENFORSELECT•OPENFOREXECUTE•OPEN一个绑定的查询3.使用游标变量4.关闭游标变量6.5过程与函数PL/SQL中的块的类型•命名块•匿名块区别6.5.1过程创建和调用定义创建CREATE[ORREPLACE]PROCEDURE过程名称[(参数[{IN|OUT|INOUT}]类型,...参数[{IN|OUT|INOUT}]类型)][AUTHID{CURRENT_USER|DESIGNER}]{IS|AS}过程体调用调用方式•直接利用EXECUTE命令•在PL／SQL块中调用6.5.2过程参数设置与传递1.参数模式2.使用AUTHID选项创建存储过程3.在形参和实参之间传递值4.对形参的限制5.按引用和按值传递参数6.使用NOCOPY参数6.5.3函数的创建、查询和调用创建语法CREATE[ORREPLACE]FUNCTION函数名称[(参数[{IN|OUT|INOUT}]数据类型,...参数[{IN|OUT|INOUT}]数据类型)]RETURN返回类型[AUTHID{CURRENT_USER|DESIGNER}]{IS|AS}函数体6.5.4删除过程和函数删除过程的语法如下：•DROPPROCEDURE过程名称;删除函数的语法是：•DROPFUNCTION函数名称;6.5.5子程序的位置1.存储子程序和数据字典2.本地子程序3.存储子程序与本地子程序的比较6.5.6子程序的依赖性与执行权限子程序的依赖性子程序的执行权限6.6触发器触发器(trigger)是一些过程，当发生一个特定的数据库事件时就执行这些过程触发器的适用情况•维护在表创建阶段通过声明限制无法实现的复杂完整性限制•通过记录修改内容和修改者来审计表中的信息•在表内容发生变更时，自动通知其他程序采取相应的处理•在订阅发布环境下，发布有关各种事件的信息6.6.1触发器的类型DML触发器替代触发器系统事件触发器DDL触发器6.6.2建立触发器创建触发器的通用语法：CREATE[ORREPLACE]TRIGGER触发器名称{BEFORE|AFTER|INSTEADOF}激发触发器事件referencing_clause[WHENtrigger_condition][FOREACHROW]触发器代码;建立触发器需要注意的问题触发器主体不能超过32K。如果触发器长度超过了该限制，就要把该体内的某些代码放到单独编译的包或存储子程序中，并从触发器主体中调用这些代码。6.7异常处理PL/SQL处理错误情况的方法。在PL/SQL代码部分执行的过程中无论何时发生错误，控制自动地转向执行异常部分进行异常处理。6.7.1概念常见的有以下两种异常：Oracle预定义的异常用户自定义的异常PL/SQL中常见的预定义异常异常名ORA错误序号SQL代码说明CURSOR_ALREADY_OPENORA-06511-6511试图打开一个已经打开了的游标DUP_VAL_ON_INDEXORA-00001-1试图向一个表插入一行，但违反了独一无二的索引约束INVALID_CURSORORA-01001-1001试图对游标进行操作，却不能打开该游标INVALID_NUMBERORA-01722-1722试图将一个字符串传递给一个数值变量LOGIN_DENIEDORA-01017-1017试图和Oracle数据库建立连接，却不能提供有效的用户名和口令NO_DATA_FOUNDORA-01403+100执行了SELECTINTO语句，却没有找到有效的行信息NOT_LOGGED_ONORA-01012-1012试图进行数据库的一个调用，却没有登录到该数据库PL/SQL中常见的预定义异常异常名ORA错误序号SQL代码说明PROGRAM_ERRORORA-06501-6501PL/SQL发生内部错误ROWTYPE_MISMATCHORA-06504-6504PL/SQL返回的游标变量和主游标不相匹配STORAGE_ERRORORA-06500-6500PL/SQL运行已超出内存空间TOO_MANY_ROWSORA-01422-1422SELECTINTO命令返回了不止一条结果VALUE_ERRORORA-06502-6502所赋变量值与变量类型不匹配ZERO_DIVIDEORA-01476-1476试图用0作除数TIMEOUT_ON_RESOURCEORA-00051-51Oracle等待资源时超时6.7.2声明和处理异常1