第6章 PL/SQL程序设计学习重点：学习重点：PL/SQL的控制结构的控制结构PL/SQL记录和表记录和表过程与函数过程与函数异常处理异常处理本章逻辑结构本章逻辑结构6.16.1 PL/SQLPL/SQL基础基础 6.1.16.1.1 变量及声明变量及声明 6.1.26.1.2 数据类型数据类型 6.1.36.1.3 表达式表达式 6.1.46.1.4 PL/SQLPL/SQL程序块结构程序块结构 6.1.56.1.5 PL/SQLPL/SQL程序的运行环境程序的运行环境6.26.2 PL/SQLPL/SQL的控制结构的控制结构 6.2.16.2.1 顺序结构顺序结构 6.2.26.2.2 选择结构选择结构 6.2.36.2.3 NULLNULL结构结构 6.2.46.2.4 循环结构循环结构6.36.3 PL/SQLPL/SQL记录和表记录和表 6.3.16.3.1 使用使用%TYPE%TYPE 6.3.26.3.2 记录类型记录类型本章逻辑结构本章逻辑结构 6.3.36.3.3 使用使用%ROWTYPE%ROWTYPE 6.3.46.3.4 表表6.46.4 游标游标 6.4.16.4.1 游标的基本操作游标的基本操作 6.4.26.4.2 游标的属性操作游标的属性操作 6.4.36.4.3 参数化游标和隐式游标参数化游标和隐式游标 6.4.46.4.4 游标变量游标变量6.56.5 过程与函数过程与函数 6.5.16.5.1 过程创建和调用过程创建和调用 6.5.26.5.2 过程参数设置与传递过程参数设置与传递 6.5.36.5.3 函数的创建、查询和调用函数的创建、查询和调用 6.5.46.5.4 删除过程和函数删除过程和函数 6.5.56.5.5 子程序的位置子程序的位置本章逻辑结构本章逻辑结构 6.5.66.5.6 子程序的依赖性与执行权限子程序的依赖性与执行权限6.66.6 触发器触发器 6.6.16.6.1 触发器的类型触发器的类型 6.6.26.6.2 建立触发器建立触发器6.76.7 异常处理异常处理 6.7.16.7.1 概念概念 6.7.26.7.2 声明和处理异常声明和处理异常 6.7.36.7.3 处理异常的特殊方法处理异常的特殊方法6.86.8 本章小结本章小结6.96.9 习题习题 6.1 PL/SQL基础基础 ANSI标准SQL的Oracle版本的过程化语言的扩展 PL/SQL语言 的特点6.1.1 变量及声明变量及声明 选择变量名称的规则变量必须以字母(AZ)开头其后跟可选的一个或多个字母、数字(09)或特殊字符$、# 或_变量长度不超过30个字符变量名中不能有空格6.1.2 数据类型数据类型声明变量或常量的通用语法 变量名CONSTANT数据类型NOT NULLDEFAULT|:=； PL/SQL的数据类型与相关的子类型 数据类型子类描述BINARY_INTEGERNATURAL、NATURALN、POSITIVE、POSITIVEN、SIGNTYPE存储范围在-2 147 483 647到2 147 483 647之间的有符号整数。使用数学库，NATURAL与NATURALN只存储非负整数，后者不允许存储0；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最长为2000字节VARCHAR2(长度) VARCHAR(长度)、STRING 存储变长字符串，最大长度为32 767字节；然而，数据库中CHAR最长为4000字节 DATE 存储与时间相关的信息，包括日期、小时、分、秒 PL/SQL的数据类型与相关的子类型数据类型子类描述ROWID 存储数据库表每一行的物理地址 ROWID 存储数据库表每一行的物理地址 UROWID 存储数据库表中每一行的物理的、逻辑的或外部的(非Oracle的)地址 CLOB 存储巨型、单字节字符对象 BLOB 存储巨型二进制对象BFILE 存储数据库外文件系统管理的LOB的文件指针 BOOLEAN 存储逻辑值(真、假与空)6.1.3 表达式表达式 l PL/SQL表达式中常用的符号 类型字符说明数学运算符+-*/*加号减号乘号除号乘幂布尔运算符=相等不等于小于大于小于等于大于等于 PL/SQL表达式中常用的符号 类型字符说明其他符号.|&“”范围运算符字符串连接远程数据库指示符字符串起始符号字符串结束符号捆绑变量指示器引用开始符号引用结束符号6.1.4 PL/SQL程序块结构程序块结构 未命名的PL/SQL包括DECLARE-块声明部分BEGIN-块体的可执行部分EXCEPTION-异常处理部分6.1.5 PL/SQL程序的运行环境程序的运行环境 lPL/SQL程序由PL/SQL引擎执行。PL/SQL引擎是数据库服务器的一部分 图图 6.2 PL/SQL引擎在引擎在Oracle服务器中的位置服务器中的位置 6.2 PL/SQL的控制结构的控制结构 程序设计语言的核心检测不同条件并加以处理是程序控制的主要部分流向控制结构PL/SQL程序控制程序流的主要结构 顺序结构选择结构NULL结构循环结构6.2.1 顺序结构顺序结构 按照语句出现的先后顺序执行 6.2.2 选择结构选择结构 if 逻辑结构 3种if逻辑结构if-thenif-then-elseif-then-elsif if逻辑规则 规则1：每个if语句都有自己的then，以if开始的语句行不跟语句结束符“；”规则2：每个if语句块以相应的end if 结束 规则3：每个if语句有且只有一个else。规则4：else语句行后面不跟语句结束符 规则5：elsif无匹配的end if 6.2.3 NULL结构结构 空操作或空值结构 处理方法if Marks60 thenNull；elseINSERT INTO Student_Course VALUES(Pass,StudentNo, CourseNo)；end if；6.2.4 循环结构循环结构 3种循环形式。1. LOOP-EXIT-END循环2. WHILE-LOOP-END 循环 3. FOR-IN-LOOP-END循环1. LOOP-EXIT-END循环 语法如下loop 执行语句1; 执行语句2; .end loop2. WHILE-LOOP-END 循环语法while 布尔表达式 loop执行语句1;执行语句2;.end loop;3. FOR-IN-LOOP-END循环 语法for 计数循环变量 in reverse 起始值.终止值 loop执行语句1;执行语句2;.end loop;6.3 PL/SQL记录和表记录和表 用户自定义的类型使用自定义类型去声明相应的程序变量用户自定义的类型记录类型 6.3.1 使用%TYPE声明变量、常量、记录中的单个字段以及与数据库列、表属性或其他与程序结构相匹配的记录变量 简化了程序结构的定义 程序相对于数据库的修改更加灵活 6.3.2 记录类型记录类型 用户自定义的数据类型 声明记录类型的一般语法TYPE 记录类型 IS RECORD( 字段1 数据类型 NOT NULL DEFAULT|:= 表达式 , 字段2 )6.3.3 使用使用%ROWTYPE 简单地声明记录变量和其他结构 TYPE属性与ROWTYPE属性的区别 6.3.4 表表 声明PL/SQL表的语法 TYPE PL/SQL表名 IS TABLE OF 数据类型 6.4 游游 标标 PL/SQL用游标来管理SQL的SELECT语句，游标是为处理这些语句而分配的一大块内存，有时用户手工定义游标。游标定义类似于其他PL/SQL变量，并且必须遵守同样的命名规则。6.4.1 游标的基本操作游标的基本操作 操作顺序(1)声明游标，使用查询来定义游标的列和行(2)打开游标，使用PL/SQL命令OPEN来打开一个声明的游标(3)提取数据，从游标中重复提取每条记录到数据结构中，直到数据集合被提空(4)关闭游标，使用完游标之后将其关闭6.4.2 游标的属性操作游标的属性操作 游标的属性ISOPENFOUNDNOTFOUNDROWCOUNT 6.4.3 参数化游标和隐式游标参数化游标和隐式游标 两种特殊形式的游标 参数化游标隐式游标6.4.4 游标变量游标变量 1. 创建一个游标变量 2. 打开游标变量 OPEN FOR SELECT OPEN FOR EXECUTE OPEN 一个绑定的查询 3. 使用游标变量4. 关闭游标变量 6.5 过程与函数过程与函数 PL/SQL中的块的类型命名块匿名块 区别6.5.1 过程创建和调用过程创建和调用 定义创建 CREATE OR REPLACE PROCEDURE 过程名称(参数IN | OUT | IN OUT 类型,.参数IN | OUT | IN OUT 类型 ) AUTHID CURRENT_USER| DESIGNERIS | AS过程体调用调用方式直接利用EXECUTE命令在PLSQL块中调用 6.5.2 过程参数设置与传递过程参数设置与传递 1. 参数模式2. 使用AUTHID选项创建存储过程 3. 在形参和实参之间传递值 4. 对形参的限制 5. 按引用和按值传递参数6. 使用NOCOPY参数6.5.3 函数的创建、查询和调用函数的创建、查询和调用 创建语法CREATE OR REPLACE FUNCTION 函数名称 ( 参数IN | OUT | IN OUT 数据类型,.参数IN | OUT | IN OUT数据类型)RETURN 返回类型AUTHID CURRENT_USER| DESIGNERIS | AS函数体6.5.4 删除过程和函数删除过程和函数 删除过程的语法如下：DROP PROCEDURE 过程名称;删除函数的语法是：DROP FUNCTION 函数名称;6.5.5 子程序的位置子程序的位置 1. 存储子程序和数据字典2. 本地子程序 3. 存储子程序与本地子程序的比较6.5.6 子程序的依赖性与执行权限子程序的依赖性与执行权限 子程序的依赖性子程序的执行权限 6.6 触触 发发 器器 触发器(trigger)是一些过程，当发生一个特定的数据库事件时就执行这些过程 触发器的适用情况维护在表创建阶段通过声明限制无法实现的复杂完整性限制通过记录修改内容和修改者来审计表中的信息在表内容发生变更时，自动通知其他程序采取相应的处理在订阅发布环境下，发布有关各种事件的信息6.6.1 触发器的类型触发器的类型 DML触发器替代触发器系统事件触发器DDL触发器 6.6.2 建立触发器建立触发器 创建触发器的通用语法：CREATE OR REPLACE TRIGGER 触发器名称BEFORE | AFTER | INSTEAD OF 激发触发器事件referencing_clauseWHEN trigger_conditionFOR EACH ROW触发器代码;建立触发器需要注意的问题 触发器主体不能超过32K。如果触发器长度超过了该限制，就要把该体内的某些代码放到单独编译的包或存储子程序中，并从触发器主体中调用这些代码。6.7 异异 常常 处处 理理 PL/SQL处理错误情况的方法。在PL/SQL代码部分执行的过程中无论何时发生错误，控制自动地转向执行异常部分进行异常处理。 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-01722-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/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. 处理预定义的异常 语法:EXCEPTION WHEN 异常1OR 异常2 THEN.;WHEN 异常3OR 异常4 THEN.;.WHEN OTHERS THEN.;END; 2. 声明和处理用户自定义的异常 语法异常名称EXCEPTION；6.7.3 处理异常的特殊方法处理异常的特殊方法 利用触发器生成一段阻止SQL语句发生异常的异常处理代码 用-20 000-20 999之间的任何数值来代替error_number 6.8 本本 章章 小小 结结 介绍了Oracle数据库系统的过程语言PL/SQL详细介绍了PL/SQL的基本概念PL/SQL块分为匿名块和命名块两种介绍了异常处理的概念和方法 6.9 习习 题题 选择题(1)在PL/SQL中，下列( )变量名合法。A.3dffswaB.A VariableC.Rose$_1D.Ta%a(2)在PL/SQL中，数据类型CHAR字符串的最大长度为( )。A.32 767B.2000C.4000D.2 147 483 6476.9 习习 题题 填空题(1)控制结构是所有程序设计语言的核心，PL/SQL程序可以使用 、 、 和 来控制程序流。(2)PL/SQL用 来管理SQL的SELECT语句。6.9 习习 题题 简答题(1)SQL语言已经很强大了，Oracle为什么还要引入PL/SQL语言？(2)PL/SQL提供了哪些循环结构？举例说明。(3)简要介绍游标的操作顺序。6.9 习习 题题 上机操作题(1) 在SQL*Plus环境下，编写一个学生信息的打印过程。 首先建立学生信息记录，包括学号、姓名、性别、籍贯、学习成绩和活动成绩。 编写学生信息打印过程PrintStudentRecord，把建立的学生信息记录类型作为参数；