Oracle_10g_PL-SQL程序设计.ppt

1、第7章 Oracle 10g PL/SQL程序设计,7.1 PL/SQL程序设计基础 7.2 流程控制语句 7.3 游标 7.4 异常处理 7.5 过程与函数 7.6 触发器,7.1 PL/SQL程序设计基础,PL/SQL简介 PL/SQL既具备SQL的功能，又具备过程化语言的条件判断、循环、出错处理等特点，并支持所有的事务控制命令。 一个完整的语句块一次发送到Oracle，可明显地减少与Oracle之间的通信和调研，提高效益。,7.1 PL/SQL程序设计基础,7.1.1 PL/SQL程序结构 DECLARE -定义部分：变量、常量、游标、自定义数据类型等说明。 BEGIN -执行部分：PL

2、/SQL语句。 EXCEPTION -异常处理部分，异常的处理 END;,*关键词DECLARE、BEGIN、EXCEPTION后不要分号，但是END和SQL语句要求用分号结束。,7.1.2 运算符,1算术运算符 算术运算符用于执行数字型表达式的算术运算，PL/SQL 支持的算术运算符包括： +：加。 -：减。 x：乘。 /：除。 MOD：取模,2比较运算符 比较运算符用来比较两个表达式的值是否相同。PL/SQL支持的比较运算符包括： 大于。 = 等于。 = 大于等于。 = 小于等于。 != 不等于。 like 类似于。 between 在之间。 in在之中。,7.1.2 运算符,3逻辑运算符

3、 逻辑运算符用于测试条件是否为真，它与比较运算符一样，根据测试结果返回布尔值TRUE、FALSE。逻辑运算符有以下几种： AND OR NOT,7.1.2 运算符,4字符串连接符 “|”可以实现字符串之间的连接操作。PL/SQL中，字符串之间的其他操作通过字符串函数实现。 例7-1 下列表达式用字符串连接符实现两字符串间的连接。 SELECT abc | 123 FROM dual 其计算结果为abc123。 注意：dual是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。,7.1.2 运算符,5赋值运算符 PL/SQL中的赋值运算符为等号（:=），它

4、将表达式的值赋给一个变量。例如： 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.2 运算符,7.1.2 运算符,7.1.3 变量与常量,1变量声明 PL/SQL在DECLARE部分中声明变量，语法格式为： 变量名 数据类型 例如：DECLARE x CHAR(10) -定义长度为10字符的变量x 2变量赋值 变量声明后，可以按下面

5、语法为变量赋值： 变量名 := 表达式; 例如：x:=15;,3. 定义常量 变量名 CONSTANT 数据类型 := 值 例如： DECLARE pi CONSTANT NUMBER := 3.1415926;,7.1.3 变量与常量,7.1.3 变量与常量,举例说明变量定义和赋初值 qty NUMBER(2):=12; qty NUMBER(2) DEFAULT 12; address CHARVAR2(20):=beijing; b_date DATE:=08-05月-10; re_mark BOOLEAN:=FALSE;,7.1.4 定义数据类型,定义数据类型的语法格式为： TYPE

6、数据类型名 IS 数据类型 Oracle允许用户定义3种数据类型： RECORD（记录）、TABLE（表）、VARRAY（变长数组） 定义RECORD的语法为： TYPE 数据类型名 IS RECORD(成员变量定义);,例如：定义一个记录类型的student。 DECLARE TYPE student IS RECORD( name VARCHAR2(10), sex CHAR(2), age INT ); stu1 student; BEGIN :=test; END;,7.1.4 定义数据类型,7.1.4 定义数据类型,定义参考类型的变量（1） 当将数据库列的值放到一个

7、变量时，必须保证变量的数据类型与列的数据类型完全一致。可用已定义好的变量的类型或列的类型来定义新变量的类型。 语法 %TYPE 例如： b_date DATE; v_date b_date%TYPE; v_bookname book.bookname%TYPE;,7.1.4 定义数据类型,7.1.4 定义数据类型,定义参考类型的变量（2） 定义变量的类型，使其参考表、视图或游标的数据类型。 语法： %ROWTYPE 例如： rec book%ROWTYPE; rec.qty:=120;,7.1.4 定义数据类型,7.1.4 定义数据类型,变量的作用范围 PL/SQL可以将语句嵌套，嵌套的语句称

8、为子块。 变量的作用范围规则： 一个变量的作用范围是在定义此变量的程序范围中，以及该程序内的子块中。 子块内定义的变量只在子块内有效。 如果变量在子块中被重新定义，则子块内的定义优先。,7.1.4 定义数据类型,例如 DECLARE a VARCHAR2(8); b VARCHAR(40); BEGIN DECLARE a NUMBER(4); BEGIN SELECT COUNT(*) INTO a FROM book; SELECT bookname INTO b FROM book WHERE bookid=TP2001-001; END; END;,7.2 流程控制语句,PL/SQL控

9、制结构 与其他高级语言一样，SQL/PL提供许多控制结构以便用户对程序流程的控制。 控制结构包括条件语句和循环语句两种类型。,7.2.1 条件语句,1IFELSE语句语法格式为： IF THEN SQL语句语句块 ELSE SQL语句语句块 END IF;,*条件表达式的值只能是TRUE、FALSE、NULL；如果为TRUE，则执行THEN后的语句，否则执行ELSE后的语句。,2IFELSIFELSE语句语法格式 IF THEN SQL语句语句块 ELSIF THEN SQL语句语句块 ELSE SQL语句语句块 END IF;,7.2.1 条件语句,*条件语句中可有任意多的ELSIF子句，但

10、最多只能有一个ELSE子句。,7.2.1 条件语句,3CASE语句 CASE WHEN THEN PL/SQL语句块1 WHEN THEN PL/SQL语句块2 WHEN THEN PL/SQL语句块N ELSE PL/SQL语句块N+1,7.2.1 条件语句,对WHEN语句按顺序进行检查，ELSE语句可选。,7.2.2 循环语句,1基本循环 LOOP PL/SQL语句语句块 EXIT WHEN END LOOP;,7.2.2 循环语句,7.2.2 循环语句,2WHILE循环 WHILE LOOP PL/SQL语句语句块 END LOOP 功能：当条件表达式为真时，重复执行其中的PL/SQL语

11、句或语句块。,7.2.2 循环语句,7.2.2 循环语句,3FOR循环 FOR IN REVERSE . LOOP PL/SQL语句语句块 循环体 END LOOP 默认情况下，当使用FOR循环时，每次循环变量会自动加一，如果指定REVERSE选项，那么每次循环变量会自动减一。循环变量必须是NUMBER或INTEGER类型。,7.2.2 循环语句,7.3 游标,游标的作用 SQL语言对数据库的操作时面向集合的操作. 某些情况下，需要对结果集进行逐行操纵。PL/SQL游标可以是用户逐行访问SQL返回的结果集。 游标定义应放在PL/SQL块的说明部分。游标的操作放在PL/SQL主体部分或异常处理部

12、分。,7.3 游标,1显式游标的定义 游标定义的语法格式如下： CURSOR IS SELECT语句 FOR UPDATE OF 列名列表NOWAIT FOR UPDATE用于在游标数据集上加共享锁，带FOR UPDATE选项的游标可以使用游标更新或删除数据,2打开游标 OPEN 3从一个打开的游标中提取数据行 FETCH INTO 变量个数及类型应与游标类型及个数一致，FETCH每执行一次，游标指针自动向下移动一行。 4关闭游标 CLOSE ,7.3 游标,5显式游标属性 显式游标属性用于返回显式游标的执行信息，当使用显式游标属性时，必须在显式游标属性之前带有显式游标名作为前缀。显式游标属性

13、包括： %ISOPEN 判断游标是否打开，如果游标已经打开，则返回TRUE，否则返回FALSE。 %FOUND 检查是否从结果集中提到了数据，如果提到了数据则返回TRUE，否则返回FALSE。 %NOTFOUND 该属性与%FOUND属性值相反。 %ROWCOUNT 返回当前行为止已经提取的实际行数。,7.3 游标,7.3 游标,6参数游标 参数游标定义的语法格式： COUSOR 游标名(参数名 参数类型) IS SELECT 语句; 定义参数游标时，需指定参数名和参数类型（不要指定参数长度），参数游标可以用不同的参数值打开多个不同的结果集。,7.3 游标,7.4 异常处理,异常（EXCEPT

14、ION） 是指PL/SQL程序在运行时出现的错误或警告，如除数为零或内存溢出等情况。如果没有异常处理机制，程序一旦出现错误就会终止执行。Oracle通过引入异常处理机制，可以捕获各种异常，根据异常情况进行相应的处理。,PL/SQL 异常处理语法如下： BEGIN 正常处理代码 EXCEPTION WHEN 异常1 THEN 对异常1的处理代码 WHEN 异常2 对异常2的处理代码 WHEN OTHERS THEN 其他的处理代码 END,7.4 异常处理,7.4.2 处理预定义异常,常用的预定义异常 NO_DATA_FOUND TOO_MANG_ROWS DUP_VAL_ON_INDEX VA

15、LUE_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 处理自定义异常,自定义异常是指

16、由PL/SQL开发人员所定义的异常，自定义异常与Oracle错误没有任何关联。自定义异常必须是在代码中显式触发。显式触发异常的语法格式为： RAISE 例7-11 自定义异常应用,返回本节,7.4.5 与异常有关的函数,当在PL/SQL块中出现Oracle错误时，通过使用异常函数可以取得错误号和相关的错误信息，其中SQLCODE用于获得错误号，SQLERRM用于取得与之对应的错误信息。使用异常函数raise_application_error可以在数据库的子程序（过程、函数、触发器）中自定义错误号与错误信息。 例7-12 捕获错误信息和错误号,7.5 过程与函数,过程与函数是被命名的PL/SQ

17、L块，也称为子程序，它们存储在数据库中，可以在不同的应用中多次调用 . 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块中，可以直接用过程名调

18、用,4删除存储过程,删除存储过程是指删除由用户创建的存储过程。格式： DROP PROCEDURE 存储过程名。 注意：删除一个不存在的存储过程，Oracle将会报错 .,7.5.2函数,函数与过程基本类似，也是编译后存储在数据库中，可供用户重复调用，与过程不同是，函数必须有一个返回结果。,1创建函数,创建函数的语法为： CREATE OR REPLACE FUNCTION (参数说明) RETURN IS | AS 本地变量声明 BEGIN PL/SQL语句块 EXCEPTION 异常处理 END 函数名;,2函数调用,在SQL*PLUS中，函数调用语法如下：EXECUTE :全局变量名 :

19、=函数名(实参列表)； 在SQL或PL/SQL中，函数可以在任何可以出现表达式的位置调用.例,3删除函数,删除函数是指删除由用户创建的函数。语法格式为： DROP FUNCTION 函数名。 比如删除例7-12所创建的函数： DROP FUNCTION getreaderborrownum；,7.5.3 包,包类似于面向对象编程中的类，用于逻辑组合相关PL/SQL类型、公共常量、变量、游标、存储过程、函数等 . 包由包头和包体两部份组成，包头是包的描述部份，也称为包规范。创建包时首先要创建包头，然后创建包体。,1、创建包规范,创建包规范的语法格式： CREATE OR REPLACE PACK

20、AGE 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触发器