1、第第7章章 Oracle 10g PL/SQL程序设计程序设计 7.1 PL/SQL程序设计基础 7.2 流程控制语句 7.3 游标 7.4 异常处理 7.5 过程与函数 7.6 触发器7.1 PL/SQL程序设计基础程序设计基础7.1.1 PL/SQL程序结构，PL/SQL也是一种模块化结构的语言，它的大体结构如下：DECLARE -定义部分：变量、常量、游标、自定义数据类型等说明。BEGIN -执行部分：PL/SQL语句。EXCEPTION -异常处理部分，异常的处理请参考7.4节。END;返回首页返回首页7.1.2 运算符运算符 1算术运算符 算术运算符用于执行数字型表达式的算术运算，P
2、L/SQL 支持的算术运算符包括：+：加。-：减。*：乘。/：除。MOD：取模 2比较运算符比较运算符比较运算符用来比较两个表达式的值是否相同。PL/SQL支持的比较运算符包括：大于。=等于。=大于等于。=小于等于。!=不等于。like 类似于。between 在之间。in在之中。3逻辑运算符 逻辑运算符用于测试条件是否为真，它与比较运算符一样，根据测试结果返回布尔值TRUE、FALSE。逻辑运算符有以下几种：AND。OR。NOT。4字符串连接符“|”可以实现字符串之间的连接操作。PL/SQL中，字符串之间的其他操作通过字符串函数实现。例7-1 下列表达式用字符串连接符实现两字符串间的连接。S
3、ELECT abc|123 FROM dual 其计算结果为abc123。注意：dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。返回首页返回首页 5赋值运算符 PL/SQL中的赋值运算符为等号（:=），它将表达式的值赋给一个变量。例如：DECLARE x INT;BEGIN x:=100+50;dbms_output.put_line(x);END;注意：在SQL*PLUS中使用dbms_output.put_line来输出变量的值，需要执行SET SERVEROUTPUT ON命令打开环境变量serveroutput 返回本节返回本节7.1
4、.3 变量与常变量与常 1变量声明 PL/SQL在DECLARE部分中声明变量，语法格式为：变量名 数据类型 2变量赋值 变量声明后，可以按下面语法为变量赋值：变量名:=表达式;返回首页返回首页7.1.4 定义数据类型定义数据类型 定义数据类型的语法格式为：TYPE 数据类型名 IS 数据类型 Oracle允许用户定义3种数据类型：RECORD（记录）、TABLE（表）、VARRAY（变长数组）定义RECORD的语法为：TYPE 数据类型名 IS RECORD(成员变量定义);其他类型的定义请参考ORACLE的帮助文档.7.2 流程控制语句流程控制语句 流程控制语句用于控制PL/SQL语句、语
5、句块、存储过程或函数的执行流程 7.2.1 条件语句条件语句 1IFELSE语句语句语法格式为：IF THENSQL语句语句块 ELSESQL语句语句块 END IF;2IFELSIFELSE语句语法格式 IF THENSQL语句语句块 ELSIF THENSQL语句语句块 ELSESQL语句语句块 END IF;3CASE语句 CASE WHEN THEN PL/SQL语句块1 WHEN THEN PL/SQL语句块2 WHEN THEN PL/SQL语句块N ELSE PL/SQL语句块N+1 7.2.2 循环语句循环语句 1基本循环基本循环LOOP PL/SQL语句语句块 EXIT WH
6、EN END LOOP;2WHILE循环循环WHILE LOOPPL/SQL语句语句块END LOOP 功能：当条件表达式为真时，重复执行其中的PL/SQL语句或语句块。3FOR循环循环FOR IN REVERSE.LOOPPL/SQL语句语句块 循环体END LOOP 默认情况下，当使用FOR循环时，每次循环变量会自动加一，如果指定REVERSE选项，那么每次循环变量会自动减一。循环变量必须是NUMBER或INTEGER类型。返回本节返回本节7.2.3 RETURN语句与语句与NULL语句语句 1RETURN语句语句 语法格式：RETURN 表达式 功能：终止子程序的执行，返回到调用者。在存
7、储过程和触发器中RETURN 不能带“表达式”，在函数中必须用带表达式的RETURN语句以返回函数结果给调用者。2NULL语句语句 NULL语句即空语句，表示什么都不做，但起到一个占位作用。7.3 游标游标 1显式游标的定义显式游标的定义 游标定义的语法格式如下：CURSOR IS SELECT语句 FOR UPDATE OF 列名列表NOWAIT FOR UPDATE用于在游标数据集上加共享锁，带FOR UPDATE选项的游标可以使用游标更新或删除数据 2打开游标 OPEN 3从一个打开的游标中提取数据行 FETCH INTO 4关闭游标关闭游标 CLOSE 5显式游标属性 显式游标属性用于
8、返回显式游标的执行信息，当使用显式游标属性时，必须在显式游标属性之前带有显式游标名作为前缀。显式游标属性包括：%ISOPEN 判断游标是否打开，如果游标已经打开，则返回TRUE，否则返回FALSE。%FOUND 检查是否从结果集中提到了数据，如果提到了数据则返回TRUE，否则返回FALSE。%NOTFOUND 该属性与%FOUND属性值相反。%ROWCOUNT 返回当前行为止已经提取的实际行数。6参数游标参数游标 参数游标定义的语法格式：COUSOR 游标名(参数名 参数类型)IS SELECT 语句;定义参数游标时，需指定参数名和参数类型（不要指定参数长度），参数游标可以用不同的参数值打开多
9、个不同的结果集。7.4 异常处理异常处理 异常（EXCEPTION）是指PL/SQL程序在运行时出现的错误或警告，如除数为零或内存溢出等情况。如果没有异常处理机制，程序一旦出现错误就会终止执行。Oracle通过引入异常处理机制，可以捕获各种异常，根据异常情况进行相应的处理。返回本节返回本节PL/SQL 异常处理语法如下：BEGIN正常处理代码EXCEPTION WHEN 异常1 THEN 对异常1的处理代码 WHEN 异常2 对异常2的处理代码 WHEN OTHERS THEN 其他的处理代码END7.4.2 处理预定义异常 常用的预定义异常 NO_DATA_FOUND TOO_MANG_RO
10、WS DUP_VAL_ON_INDEX VALUE_ERROR STORAGE_ERROR ZERO_DIVIDE CASE_NOT_FOUND CURSOR_ALREADY_OPEN TIMEOUT_ON_RESOURCE INVALID 7.4.3 处理非预定义异常处理非预定义异常 在在PL/SQL程序中处理非预定义异常，可程序中处理非预定义异常，可以使用以使用OTHERS异常处理。或定义异常，异常处理。或定义异常，并将定义的异常与并将定义的异常与Oracle的错误号关联的错误号关联.定义异常是在程序的说明部分，语法格式为：EXCEPTION 将异常名与Oracle的错误号关联要使用PRA
11、GMA EXCEPTION_INIT过程 PRAGMA EXCEPTION_INIT(,);7.4.4 处理自定义异常处理自定义异常 自定义异常是指由PL/SQL开发人员所定义的异常，自定义异常与Oracle错误没有任何关联。自定义异常必须是在代码中显式触发。显式触发异常的语法格式为：RAISE 例7-11 自定义异常应用 返回本节返回本节7.4.5 与异常有关的函数与异常有关的函数 当在PL/SQL块中出现Oracle错误时，通过使用异常函数可以取得错误号和相关的错误信息，其中SQLCODE用于获得错误号，SQLERRM用于取得与之对应的错误信息。使用异常函数raise_applicatio
12、n_error可以在数据库的子程序（过程、函数、触发器）中自定义错误号与错误信息。例7-12 捕获错误信息和错误号 7.5 过程与函数过程与函数 过程与函数是被命名的PL/SQL块，也称为子程序，它们存储在数据库中，可以在不同的应用中多次调用.7.5.1 存储过程1存储过程的优点:（1）减少网络流量（2）增强代码的重用性和共享性（3）加快系统运行速度（4）加强安全性 2创建存储过程 创建存储过程的语法为：CREATE OR REPLACE PROCEDURE (参数说明)IS|AS 本地变量声明 BEGIN PL/SQL语句块 EXCEPTION 异常处理 END 过程名;3执行存储过程 在S
13、QL*PLUS中，可以用EXECUTE命令调用这个过程。其语法格式为：EXECUTE 存储过程名(实参列表)在PL/SQL块中，可以直接用过程名调用 4删除存储过程 删除存储过程是指删除由用户创建的存储过程。格式：DROP PROCEDURE 存储过程名。注意：删除一个不存在的存储过程，Oracle将会报错 .7.5.2函数函数 函数与过程基本类似，也是编译后存储在数据库中，可供用户重复调用，与过程不同是，函数必须有一个返回结果。1创建函数创建函数 创建函数的语法为：CREATE OR REPLACE FUNCTION (参数说明)RETURN IS|AS 本地变量声明 BEGIN PL/SQ
14、L语句块 EXCEPTION 异常处理 END 函数名;2函数调用函数调用 在SQL*PLUS中，函数调用语法如下：EXECUTE:全局变量名:=函数名(实参列表)；在SQL或PL/SQL中，函数可以在任何可以出现表达式的位置调用.例3删除函数删除函数 删除函数是指删除由用户创建的函数。语法格式为：DROP FUNCTION 函数名。比如删除例7-12所创建的函数：DROP FUNCTION getreaderborrownum；7.5.3 包包 包类似于面向对象编程中的类，用于逻辑组合相关PL/SQL类型、公共常量、变量、游标、存储过程、函数等.包由包头和包体两部份组成，包头是包的描述部份，
15、也称为包规范。创建包时首先要创建包头，然后创建包体。1、创建包规范、创建包规范 创建包规范的语法格式：CREATE OR REPLACE PACKAGE IS|AS变量、常量、数据类型定义游标定义头部存储过程说明函数说明END 2、定义包体、定义包体 在包头中说明的过程、函数、游标在包体中必须实现。此外，在包体中也可以定义包头中没有说明的私有组件，如过程、函数、游标、常量、变量等。定义包体的语法格式：CREATE OR REPLACE PACKAGE BODY IS|AS游标、函数、过程的具体定义；END 3使用包使用包 使用包的语法为：包名.变量名|包名.过程名|包名.函数名|包名.游标名
16、4删除包删除包 删除包的语法格式为：DROP PACKAGE BODY 。7.6 触发器触发器 触发器是指存储在数据库中，并被隐含执行的存储过程。1DML触发器基本概念触发器基本概念DML触发器包含以下几个组成部份：触发时间：触发时间有两面种，BEFORE和AFTER 触发事件：触发事件有INSERT、UPDATE、DELETE 触发子类型：触发子类型有两种，分别为行触发（ROW）和语句触发（STATEMENT）触发条件：指定义触发器时，用WHEN子句指定的一个条件表达式，只有条件表达式的值为真时，才会执行触发器代码 触发操作：指包含SQL语句和其他执行代码的PL/SQL块 DML触发器触发顺
17、序触发器触发顺序 在单行数据上执行DML触发器的执行顺序(1)执行表的BEFORE语句触发器(2)再执行BEFORE行触发器(3)执行DML操作(4)AFTER行触发器(5)最后执行AFTER语句触发器 与触发器有关的两个特殊对象与触发器有关的两个特殊对象 DML行触发器被触发时，Oracle会创建两个特殊的对象：OLD和NEW，分别对应被触发行中的旧值和新值 NEW存放INSERT或UPDATE语句将要插入或更新的新行 OLD存放DELETE或UPDATE语句的执行而导致要从被该触发器作用的表中删除的行值 DML触发器定义触发器定义 创建DML触发器的语法如下：CREATE OR REPLA
18、CE TRIGGER 触发时间 事件1 OR 事件2 OR 事件3 ON FOR EACH ROW WHEN PL/SQL块；762 INSTEAD OF触发器触发器 INSTEAD OF是基于视图的DML触发器，对于复杂视图，不允许直接执行INSERT、UPDATE、DELETE操作，要对复杂视图进行DML操作，只能使用INSTEAD OF触发器。如果在视图上定义了INSTEAD OF INSERT（或UPDATE或DELETE）触发器，则在视图上执行INSERT（或UPDATE或DELETE）操作时，Oracle只触发相关触发器，而不进行实际的DML操作。所以实际的DML操作应该由触发器完成。创建INSTEAD OF触发器的语法格式为 CREATE OR REPLACE TRIGGER INSTEAD OF INSERT|UPDATE|DELETE ON FOR EACH ROWPL/SQL块
