《数据库系统原理与应用——Oracle版》-电子教案-第7章.ppt

第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算术运算符 算术运算符用于执行数字型表达式的算术运算 ，PL/SQL 支持的算术运算符包括： +：加。 -：减。 *：乘。 /：除。 MOD：取模 2比较运算符 比较运算符用来比较两个表达式的值是否相同。PL/SQL支持的比 较运算符包括： 大于。 = 等于。 = 大于等于。 THEN SQL语句语句块 ELSE SQL语句语句块 END IF; 2IFELSIFELSE语句语法格式 IF THEN SQL语句语句块 ELSIF THEN SQL语句语句块 ELSE SQL语句语句块 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 WHEN END LOOP; 2WHILE循环 WHILE LOOP PL/SQL语句语句块 END LOOP 功能：当条件表达式为真时，重复执行 其中的PL/SQL语句或语句块。 3FOR循环 FOR IN REVERSE LOOP PL/SQL语句语句块 循环体 END LOOP 默认情况下，当使用FOR循环时，每次 循环变量会自动加一，如果指定 REVERSE选项，那么每次循环变量会 自动减一。循环变量必须是NUMBER或 INTEGER类型。 返回本节 7.2.3 RETURN语句与NULL语句 1RETURN语句 语法格式：RETURN 表达式 功能：终止子程序的执行，返回到调用者。 在存储过程和触发器中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显式游标属性 显式游标属性用于返回显式游标的执行信息， 当使用显式游标属性时，必须在显式游标属性 之前带有显式游标名作为前缀。显式游标属性 包括： %ISOPEN 判断游标是否打开，如果游标已经 打开，则返回TRUE，否则返回FALSE。 %FOUND 检查是否从结果集中提到了数据， 如果提到了数据则返回TRUE，否则返回 FALSE。 %NOTFOUND 该属性与%FOUND属性值相反 。 %ROWCOUNT 返回当前行为止已经提取的实 际行数。 6参数游标 参数游标定义的语法格式： COUSOR 游标名(参数名 参数类型) IS SELECT 语句; 定义参数游标时，需指定参数名和参 数类型（不要指定参数长度），参数 游标可以用不同的参数值打开多个不 同的结果集。 7.4 异常处理 异常（EXCEPTION）是指PL/SQL程序 在运行时出现的错误或警告，如除数为 零或内存溢出等情况。如果没有异常处 理机制，程序一旦出现错误就会终止执 行。Oracle通过引入异常处理机制，可以 捕获各种异常，根据异常情况进行相应 的处理。 返回本节 PL/SQL 异常处理语法如下： BEGIN 正常处理代码 EXCEPTION WHEN 异常1 THEN 对异常1的处理代码 WHEN 异常2 对异常2的处理代码 WHEN OTHERS THEN 其他的处理代码 END 7.4.2 处理预定义异常 常用的预定义异常 NO_DATA_FOUND TOO_MANG_ROWS 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的错误号关联要使用 PRAGMA EXCEPTION_INIT过程 PRAGMA EXCEPTION_INIT(, ); 7.4.4 处理自定义异常 自定义异常是指由PL/SQL开发人员所 定义的异常，自定义异常与Oracle错误 没有任何关联。自定义异常必须是在代 码中显式触发。显式触发异常的语法格 式为： RAISE 例7-11 自定义异常应用 返回本节 7.4.5 与异常有关的函数 当在PL/SQL块中出现Oracle错误时，通 过使用异常函数可以取得错误号和相关 的错误信息，其中SQLCODE用于获得错 误号，SQLERRM用于取得与之对应的错 误信息。使用异常函数 raise_application_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执行存储过程 在SQL*PLUS中，可以用EXECUTE命令 调用这个过程。其语法格式为： EXECUTE 存储过程名 (实参列表) 在PL/SQL块中，可以直接用过程名调用 4删除存储过程 删除存储过程是指删除由用户创建的 存储过程。格式： DROP PROCEDURE 存储过程名。 注意：删除一个不存在的存储过程， Oracle将会报错 . 7.5.2函数 函数与过程基本类似，也是编译后存储 在数据库中，可供用户重复调用，与过 程不同是，函数必须有一个返回结果。 1创建函数 创建函数的语法为： CREATE OR REPLACE FUNCTION (参数 说明) RETURN IS | AS 本地变量声明 BEGIN PL/SQL语句块 EXCEPTION 异常处理 END 函数名; 2函数调用 在SQL*PLUS中，函数调用语法如下： EXECUTE :全局变量名 :=函数名(实参 列表)； 在SQL或PL/SQL中，函数可以在任何可 以出现表达式的位置调用.例 3删除函数 删除函数是指删除由用户创建的函数。 语法格式为： DROP FUNCTION 函数名。 比如删除例7-12所创建的函数： DROP FUNCTION getreaderborrownum； 7.5.3 包 包类似于面向对象编程中的类，用于逻 辑组合相关PL/SQL类型、公共常量、变 量、游标、存储过程、函数等 . 包由包头和包体两部份组成，包头是包 的描述部份，也称为包规范。创建包时 首先要创建包头，然后创建包体。 1、创建包规范 创建包规范的语法格式： CREATE OR REPLACE PACKAGE IS | AS 变量、常量、数据类型定义 游标定义头部 存储过程说明 函数说明 END 2、定义包体 在包头中说明的过程、函数、游标在包体中必 须实现。此外，在包体中也可以定义包头中没 有说明的私有组件，如过程、函数、游标、常 量、变量等。 定义包体的语法格式： CREATE OR REPLACE PACKAGE BODY IS | AS 游标、函数、过程的具体定义； END 3使用包 使用包的语法为： 包名.变量名 | 包名.过程名 | 包 名.函数名 | 包名.游标名 4删除包 删除包的语法格式为： DROP PACKAGE BODY 。 7.6 触发器 触发器是指存储在数据库中，并被隐含 执行的存储过程 。 1DML触发器基本概念 DML触发器包含以下几个组成部份 ： 触发时间：触发时间有两面种，BEFORE和 AFTER 触发事件：触发事件有INSERT、UPDATE、 DELETE 触发子类型：触发子类型有两种，分别为行触发 （ROW）和语句触发（STATEMENT） 触发条件：指定义触发器时，用WHEN子句指定 的一个条件表达式，只有条件表达式的值为真 时，才会执行触发器代码 触发操作：指包含SQL语句和其他执行代码的 PL/SQL块 DML触发器触发顺序 在单行数据上执行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 REPLACE 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触发器。如果在视图上