Oracle PL/SQL 开发基础


OracleOracleOracleOracle PLSQL PLSQL PLSQL PLSQL 开发基础 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 前 言 课程简介。 本教材主要针对需要在平台数据上查询数据和察看代码的测试人员。要求学 员已经掌握 Oracle 的基本概念和 Oracle SQL 语言。 本教材由质控办廖昊星编写,在编写过程中得到了测试部同事的大力支持, 特此鸣谢。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 目 录 课程目标:............................................................................................................................................. 1 第 1 章 PL/SQL 语言基础.................................................................................................................2 1.1 什么是 PL/SQL.........................................................................................................................2 1.2 PL/SQL 块结构基础..................................................................................................................2 1.3 PL/SQL 的数据与数据类型..................................................................................................... 5 1.4 PL/SQL 的操作符......................................................................................................................8 1.5 PL/SQL 流程控制......................................................................................................................9 1.6 PL/SQL 的异常处理............................................................................................................... 17 第 2 章 复合数据..............................................................................................................................22 2.1 PL/SQL 记录...........................................................................................................................22 2.2 PL/SQL 集合...........................................................................................................................28 第 3 章 PL/SQL 中的 SQL............................................................................................................. 37 3.1 PL/SQL 中的 DML..................................................................................................................37 3.2 事务管理.................................................................................................................................. 46 3.3 数据检索与游标......................................................................................................................49 3.4 游标变量和 REF CURSOR 类型........................................................................................ 79 3.5 游标表达式.............................................................................................................................. 92 第 4 章 PL/SQL 应用程序结构......................................................................................................96 4.1 过程、函数和参数..................................................................................................................96 4.2 包.............................................................................................................................................130 下载源码就到源码网,www.codepub.com 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 课程目标: 学员学完本课程之后,应该掌握如下内容: 1、了解 Oracle 数据库表的结构和数据存储方式。 2、能够使用 SQL 编写查询语句,获取所需的测试数据。 3、能够看懂一般的 PL/SQL 代码,并能找编写规范上的错误。 4、能够编写简单的 PL/SQL 程序。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 第 1111 章 PL/SQL 语言基础 PL/SQL 是ORACLE 对标准数据库语言的扩展,ORACLE 公司已经将 PL/SQL 整合到 ORACLE 服务器和其他工具中了,近几年中更多的开发人员和 DBA 开始使用 PL/SQL,本文将讲述 PL/SQL 基础语法,结构和组件、以及如何 设计并执行一个 PL/SQL 程序。 1.1 什么是 PL/SQLPL/SQLPL/SQLPL/SQL 关于 PL/SQL 语言有以下几个特点的描述: 结构化、易读和易于理解。如果你是刚刚开始进行程序开发工作(比如说你 原来是一个业务人员),那么选择 PL/SQL 是一个适当的开始。PL/SQL 语言包 含了大量的关键字、结构,这使得 PL/SQL 写成的程序非常易于理解,也容易学 习。 是一种标准化、轻便式的 Oracle 开发模式。大家都知道,Java 是一种最有 名的跨平台语言,号称“一次编译,随处运行”,但是,事实上 Oracle PL/SQL 早就已经“实现”了,只不过必需运行在 Oracle 上而已。 嵌入式语言。PL/SQL 程序不能独立运行的,它必须有一个宿主环境,也就 是说必须把 PL/SQL 程序嵌入到 Oracle 数据库中才能运行。 简而言之,PL/SQL 就是 SQL 语句的超集,也可称为“可程序化”的SQL 语言,是一种容易学习上手的语言。 1.2 PL/SQL PL/SQL PL/SQL PL/SQL 块结构基础 PL/SQL 是一种块结构的语言,组成 PL/SQL 程序的单元是逻辑块,一个 PL/SQL 程序包含了一个或多个逻辑块,每个块都可以划分为三个部分。与其他 语言相同,变量在使用之前必须声明,PL/SQL 提供了独立的专门用于处理异常 的部分,下面描述了 PL/SQL 块的不同部分: 1.2.1 声明部分(Declaration(Declaration(Declaration(Declaration section)section)section)section) 声明部分包含了变量和常量的数据类型和初始值。这个部分是由关键字 DECLARE 开始,如果不需要声明变量或常量,那么可以忽略这一部分;需要说 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 明的是游标的声明也在这一部分。 1.2.2 执行部分(Executable(Executable(Executable(Executable section)section)section)section) 执行部分是 PL/SQL 块中的指令部分,由关键字 BEGIN 开始,所有的可执 行语句都放在这一部分,其他的 PL/SQL 块也可以放在这一部分。 异常处理部 分(Exception section) 这一部分是可选的,在这一部分中处理异常或错误,对异 常处理的详细讨论我们在后面进行。 1.2.3 异常处理部分(Exception(Exception(Exception(Exception section)section)section)section) 这一部分是可选的,在这一部分中处理异常或错误,对异常处理的详细讨论 我们在后面进行。 1.2.4 PL/SQL PL/SQL PL/SQL PL/SQL 块语法 [DECLARE] ---declaration statements BEGIN ---executable statements [EXCEPTION] ---exception statements END PL/SQL 块中的每一条语句都必须以分号结束,SQL 语句可以使多行的,但 分号表示该语句的结束。一行中可以有多条 SQL 语句,他们之间以分号分隔。 每一个 PL/SQL 块由BEGIN 或DECLARE 开始,以 END 结束。注释由“—” (两个减号)标示。 1.2.5 PL/SQL PL/SQL PL/SQL PL/SQL 块的命名和匿名 PL/SQLPL/SQLPL/SQLPL/SQL 程序块可以是一个命名的程序块也可以是一个匿名程序块。匿名程序块可以 用在服务器端也可以用在客户端。 命名程序块可以出现在其他 PL/SQL 程序块的声明部分,这方面比较明显的 是子程序,子程序可以在执行部分引用,也可以在异常处理部分引用。 PL/SQL 程序块可独立编译并存储在数据库中,任何与数据库相连接的应用 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 程序都可以访问这些存储的 PL/SQL 程序块。ORACLE 提供了四种类型的可存 储的程序:函数、过程、包、触发器。 1.2.5.1 函数 函数是命名了的、存储在数据库中的 PL/SQL 程序块。函数接受零个或多个 输入参数,有一个返回值,返回值的数据类型在创建函数时定义。定义函数的语 法如下: FUNCTION name [{parameter[,parameter,...])] RETURN datatypes IS [local declarations] BEGIN execute statements [EXCEPTION exception handlers] END[name] 1.2.5.2 过程 存储过程是一个 PL/SQL 程序块,接受零个或多个参数作为输入(INPUT)或 输出(OUTPUT)、或既作输入又作输出(INOUT),与函数不同,存储过程没有返 回值,存储过程不能由 SQL 语句直接使用,只能通过 EXECUT 命令或 PL/SQL 程序块内部调用,定义存储过程的语法如下: PROCEDURE name [(parameter[,parameter,...])] IS [local declarations] BEGIN execute statements [EXCEPTION exception handlers ] END[name] 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 1.2.5.3 包(package)(package)(package)(package) 包其实就是被组合在一起的相关对象的集合,当包中任何函数或存储过程被 调用,包就被加载入内存中,包中的任何函数或存储过程的子程序访问速度将大 大加快。 包由两个部分组成:规范和包主体(body),规范描述变量、常量、游标、 和子程序,包体完全定义子程序和游标。 1.2.5.4 触发器(trigger)(trigger)(trigger)(trigger) 触发器与一个表或数据库事件联系在一起的,当一个触发器事件发生时,定 义在表上的触发器被触发。因为中心的《数据库开发规范》中规定不能使用触发 器,所以本教材将不作阐述。 1.3 PL/SQL PL/SQL PL/SQL PL/SQL 的数据与数据类型 1.3.1 变量 变量存放在内存中以获得值,能被 PL/SQL 块引用。你可以把变量想象成一 个可储藏东西的容器,容器内的东西是可以改变的。 1.3.1.1 声明变量 变量一般都在 PL/SQL 块的声明部分声明,PL/SQL 是一种强壮的类型语言, 这就是说在引用变量前必须首先声明,要在执行或异常处理部分使用变量,那么 变量必须首先在声明部分进行声明。 声明变量的语法如下: Variable_name [CONSTANT] databyte [NOT NULL][:=|DEFAULT expression] 注意:可以在声明变量的同时给变量强制性的加上 NOTNULL(非空)约束 条件,此时变量在初始化时必须赋值。 1.3.1.2 给变量赋值 给变量赋值有两种方式: 1. 直接给变量赋值 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com X:=200; Y:=Y+(X*20); . 2. 通过 SQLSELECTINTO 或FETCHINTO给变量赋值 SELECT SUM(SALARY),SUM(SALARY*0.1) INTO TOTAL_SALARY,TATAL_COMMISSION FROMEMPLOYEEWHERE DEPT=10; 1.3.2 常量 常量与变量相似,但常量的值在程序内部不能改变,常量的值在定义时赋予,它 的声明方式与变量相似,但必须包括关键字 CONSTANT。常量和变量都可被定 义为 SQL 和用户定义的数据类型。 ZERO_VALUECONSTANT NUMBER:=0; 这个语句定了一个名叫 ZERO_VALUE、数据类型是 NUMBER、值为 0的常量。 1.3.3 标量(scalar)(scalar)(scalar)(scalar)数据类型 标量(scalar)数据类型没有内部组件,它们大致可分为以下四类: 1. number 2. character 3. date/time 4. boolean 1.3.3.1 数字型 几种主要的数字类型,见下表: 数据类型 范围 子类型 描述 number 1.0E-130-9.99E125 DEC DECIMAL DOUBLE PRECISION FLOAT INTEGERIC 存储数字值,包括整数和浮 点数。可以选择精度和刻度 方式,语法: number[([,])]。 缺省的 精度是 38,小数位数是 0. 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com INT NUMERIC REAL SMALLINT 举个例子,声明一个每月工资变量,该变量整数部分是 6,小数部分是 2, 如下: month_salary number(8,2) 我们可以看到,括号里面的第一个数字表示整个变量的长度,而第二个数字 就表示小数部份的长度。 1.3.3.2 字符数据类型 详细说明见下表: 数据类型 范围 子类型 说明 CHAR 最大长度 32767 字节 CHARACTER 存储定长字符串,如果 长度没有确定,缺省是 1 LONG 最大长度 2147483647 字节 存储可变长度字符串 RAW 最大长度 32767 字节 用于存储二进制数据 和字节字符串,当在两 个数据库之间进行传 递时,RAW 数据不在 字符集之间进行转换。 LONGRAW 最大长度 2147483647 与LONG 数据类型相 似,同样他也不能在字 符集之间进行转换。 ROWID 18 个字节 与数据库 ROWID 伪列 类型相同,能够存储一 个行标示符,可以将行 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 标示符看作数据库中 每一行的唯一键值。 VARCHAR2VARCHAR2VARCHAR2VARCHAR2 最大长度 32763276327632767777 字节 STRINGVARCHARSTRINGVARCHARSTRINGVARCHARSTRINGVARCHAR 存储可变长度的字符 串。声明变量时需要附 带变量的长度 字符类型变量使用得最多的就是 VARCAHR2 类型,同事也是 PL/SQL 程序 中使用得最频繁的数据类型,下面是两个变量的声明例子: remark varchar2(400);--备注 accNo varchar2(18);--18 位帐号 1.3.3.3 日期和布尔类型 数据类型 范围 子类型 描述 BOOLEANTRUE/FALSE 存储逻辑值 TRUE 或 FALSE,无参数 DATE 01/01/4712 BC 存储固定长的日期和时间值,日期值 中包含时间 1.4 PL/SQL PL/SQL PL/SQL PL/SQL 的操作符 与其他程序设计语言相同,PL/SQL 有一系列操作符。操作符分为下面几类: 算术操作符、关系操作符、比较操作符、逻辑操作符。 1.4.1 算术操作符 操作符 操作 + 加 - 减 / 除 * 乘 ** 乘方 1.4.2 关系操作符 关系操作符主要用于条件判断语句或用于 where 子句中,关系操作符检查 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 条件和结果是否为 true 或false,下表是 PL/SQL 中的关系操作符 操作符 操作 < 小于操作符 <= 小于或等于操作符 > 大于操作符 >= 大于或等于操作符 = 等于操作符 != 不等于操作符 <> 不等于操作符 := 赋值操作符 1.4.3 比较操作符 操作符 操作 ISNULL 如果操作数为 NULL 返回 TRUE LIKE 比较字符串值 BETWEEN 验证值是否在范围之内 IN 验证操作数在设定的一系列值中 1.4.4 逻辑操作符 操作符 操作 AND 两个条件都必须满足 OR 只要满足两个条件中的一个 NOT 取反 1.5 PL/SQL PL/SQL PL/SQL PL/SQL 流程控制 PL/SQL 支持条件控制和循环控制结构,以下是详细的说明: 1.5.1 条件控制 1.5.1.1 IFIFIFIF…………THEN THEN THEN THEN 语句 语法: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com IF condition THEN Statements 1; Statements 2; .... ENDIF; IF 语句判断条件 condition 是否为 TRUE,如果是,则执行 THEN后面的语 句,如果 condition 为false 或NULL 则跳过 THEN 到END IF 之间的语句,执 行END IF 后面的语句。 1.5.1.2 IF..THEN...ELSE IF..THEN...ELSE IF..THEN...ELSE IF..THEN...ELSE 语句 语法: IF condition THEN Statements 1; Statements 2; .... ELSE Statements 1; Statements 2; .... ENDIF; 如果条件 condition 为TRUE,则执行 THEN 到ELSE 之间的语句,否则执行 ELSE 到END IF 之间的语句。 IF 可以嵌套,可以在 IF 或IF..ELSE 语句中使用 IF 或IF..ELSE 语句。 IF(a>b) AND(a>c) THEN g:=a; ELSE g:=b; IF c>g THEN g:=c; ENDIF; 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com ENDIF; 1.5.1.3 IFIFIFIF…………ELSIFELSIFELSIFELSIF…………ELSEELSEELSEELSE…………THEN THEN THEN THEN 语句 语法: IF condition1 THEN statement1; ELSIF condition2 THEN statement2; ELSIF condition3 THEN statement3; ELSE statement4; ENDIF; statement5; 如果条件 condition1 为TRUE则执行 statement1,然后执行 statement5,否 则判断 condition2 是否为 TRUE,若为TRUE则执行 statement2, 然后执行 statement5,对于 condition3 也是相同的,如果 condition1,condition2,condition3 都不成立,那么将执行 statement4,然后执行 statement5。 1.5.2 循环控制 PL/SQL 的循环语句主要包含了 LOOP,WHILE 和FOR 三种循环。 1.5.2.1 LOOP LOOP LOOP LOOP 语句 循环控制的基本形式是 LOOP 语句,LOOP 和END LOOP 之间的语句将无 限次的执行。LOOP 语句的语法如下: LOOP statements; ENDLOOP; LOOP 和END LOOP 之间的语句无限次的执行显然是不行的,那么在使用 LOOP 语句时必须使用 EXIT 语句,强制循环结束,例如: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com X:=100; LOOP X:=X+10; IF X>1000 THEN EXIT; ENDIF ENDLOOP; Y:=X; 最后得到 Y的值就是 1010。 上机练习示范: 如果不使用 IF…THEN 语句的话,也可以使用 EXITWHEN condition 语句, 同样结束循环,如果条件 condition 为TRUE,则结束循环。 上机示范练习: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 1.5.2.2 WHILEWHILEWHILEWHILE…………LOOP LOOP LOOP LOOP 循环 WHILE condition LOOP 有一个条件与循环相联系,如果条件为 TRUE,则执 行循环体内的语句,如果结果为 FALSE,则结束循环。 X:=100; WHILE X<=1000 LOOP X:=X+10; ENDLOOP; Y=X; 上机示范: 1.5.2.3 FOR...LOOP FOR...LOOP FOR...LOOP FOR...LOOP 循环 语法: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com FOR counter IN[REVERSE] start_range....end_range LOOP statements; ENDLOOP; LOOP 和WHILE 循环的循环次数都是不确定的,FOR 循环的循环次数是固 定的,counter 是一个隐式声明的变量,它的初始值是 start_range,第二个值是 start_range+1,直到end_range。如果start_range 等于end _range,那么循环 将执行一次。如果使用了 REVERSE 关键字,那么范围将是一个降序。例子代 码如下: X:=100; FOR v_counter in 1..10 loop x:=x+10; end loop; y:=x; 如果要退出 for 循环可以使用 EXIT 语句。 上机示范: 1.5.3 标签 用户可以使用标签使程序获得更好的可读性。程序块或循环都可以被标记。 标签的形式是<<标签名>>。使用标签的好处是能够大大加强程序的逻辑自由度, 配合GOTO 语句,你可以使程序跳转到任意地方,但是这也大大增加了程序维 护难度,所以在一般的开发应用中,极少应用到标签和 GOTO 语句,这里只作 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 简单的介绍。 1.5.3.1 标记程序块 <> BEGIN INSERTINTO catalog VALUES(...); EXCEPTION WHENDUP_VAL_ON_INDEXTHEN NULL; END insert_but_ignore_dups; 从上面的代码可以看出,虽然 BEGIN 开始的是一个匿名块,实际上,我们 用标签 insert_but_ignore_dups 对它进行了命令,这对于增加程序易读性是非常 有帮助的。 1.5.3.2 标记循环 BEGIN <> LOOP <> LOOP Statements1; WHEN condition1 EXIT outer_loop; ENDLOOP; Statements2 ; WHEN condition2 exit; ENDLOOP; Statements3; END; 可以用标签来标记一个循环,特别是在内嵌多层循环的时候,可以用 EXIT 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 加标签的形式使循环推出到想要的地方。比如上面的例子代码,如果 condition1 条件为 TRUE的话,那么就会跳出外层循环,执行到 Statements3。 上机练习示范: 1.5.4 PL/SQL PL/SQL PL/SQL PL/SQL 嵌套块 一般来说,我们只要在 PL/SQL 程序中看到“begin”关键字就可以认为是 一个 PL/SQL 块的开始。在“块”里面,还可以继续书写“块”,这就是嵌套块。 一个典型嵌套块实例如下图所示: 这是一个三层的嵌套块,用不同深浅的颜色标记了出来。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 嵌套块要注意的是变量,定义在最外部程序块中的变量可以在所有子块中使 用,如果在子块中定义了与外部程序块变量相同的变量名,在执行子块时将使用 子块中定义的变量。子块中定义的变量不能被父块引用。 1.6 PL/SQL PL/SQL PL/SQL PL/SQL 的异常处理 当PL/SQL 程序发生错误时,程序就会无条件地跳转到异常处理部分。这就 要求代码能够非常干净地把执行语句和异常处理语句分割开来。Oracle 还允许 声明其他异常条件类型,来扩展异常处理,这种扩展使 PL/SQL 的异常处理非常 灵活。 1.6.1 抛出异常 由三种方式抛出异常: 1. 通过 PL/SQL 运行时引擎.; 2. 使用 RAISE 语句.; 3. 调用 RAISE_APPLICATION_ERROR 存储过程; 当数据库或 PL/SQL 在运行时发生错误时,一个异常被 PL/SQL 运行时引擎 自动抛出。异常也可以通过 RAISE 语句显式抛出。格式如下: RAISE exception_name; 显式抛出异常是程序员处理声明的异常的习惯用法,但 RAISE 不限于声明 了的异常,它可以抛出任何任何异常。例如,你希望用 TIMEOUT_ON_RESOURCE 错误检测新的运行时异常处理器,你只需简单的在 程序中使用下面的语句: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com RAISETIMEOUT_ON_RESOUCE; 这种自定义异常抛出的方法,在记录错误日志的时候非常有用,比如当输入 参数为空的时候,我们就可以定义一个统一异常:INPUT_NULL_ERR,这样每 个PL/SQL 程序块都可以声明并抛出这个异常。当发生相应状况时,就会自动进 入处理模块(可以记录日志等)。 下面是一个订单输入系统,当库存小于订单时抛出一个 inventory_too_low 异常。 DECLARE inventory_too_low EXCEPTION;---其他声明语句 BEGIN … IF order_rec.qty>inventory_rec.qty THEN RAISERAISERAISERAISE inventory_too_low;inventory_too_low;inventory_too_low;inventory_too_low; ENDIF; … EXCEPTION WHEN inventory_too_low THEN order_rec.staus:='backordered'; … --其他处理语句 1.6.2 处理异常 PL/SQL 程序块的异常部分包含了程序处理错误的代码,当异常被抛出时, 一个异常陷阱就自动发生,程序控制离开执行部分转入异常部分,一旦程序进入 异常部分就不能再回到同一块的执行部分。下面是异常部分的一般语法: EXCEPTION WHEN exception_name THEN Code for handing exception_name [WHEN another_exception THEN Code for handing another_exception] 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com [WHEN others THEN Code for handing any other exception.] 用户必须在独立的 WHEN 子串中为每个异常设计异常处理代码,WHEN OTHERS 子串必须放置在最后面作为缺省处理器处理没有显式处理的异常。当 异常发生时,控制转到异常部分,ORACLE 查找当前异常相应的 WHEN..THEN 语句,捕捉异常,THEN之后的代码被执行,如果错误陷阱代码只是退出相应的 嵌套块,那么程序将继续执行内部块 END 后面的语句。如果没有找到相应的异 常陷阱,那么将执行 WHENOTHERS。在异常部分 WHEN 子串没有数量限制。 EXCEPTION WHEN inventory_too_low THEN order_rec.staus:='backordered'; WHEN discontinued_item THEN --code for discontinued_item processing WHEN zero_divide THEN --code for zero_divide WHENOTHERSTHEN --code for any other exception END; 当异常抛出后,控制无条件转到异常部分,这就意味着控制不能回到异常发 生的位置,当异常被处理和解决后,控制返回到上一层执行部分的下一条语句。 BEGIN DECLARE bad_credit; BEGIN RAISE bad_credit; --发生异常,控制转向; EXCEPTION WHEN bad_credit THEN dbms_output.put_line('bad_credit'); END;--bad_credit 异常处理后,控制转到这里 EXCEPTION WHENOTHERSTHEN 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com --控制不会从 bad_credit 异常转到这里 --因为 bad_credit 已被处理 END; 当异常发生时,在块的内部没有该异常处理器时,控制将转到或传播到上一 层块的异常处理部分。 BEGINDECLARE---内部块开始 bad_credit Exception; BEGINRAISE bad_credit; --发生异常,控制转向; EXCEPTION WHENZERO_DIVIDETHEN--不能处理 bad_credite 异常 dbms_output.put_line('divide by zero error'); END;--结束内部块 --控制不能到达这里,因为异常没有解决; --异常部分 EXCEPTIONWHENOTHERSTHEN --由于 bad_credit 没有解决,控制将转到这里 END; 1.6.3 异常传递 没有处理的异常将沿检测异常调用程序传播到外面,当异常被处理并解决或 被传递到程序的最外层。执行部分抛出的异常将首先传递到同一块的异常部分, 如果在同一块的异常部分没有处理这个异常的处理器,那么异常将会传播到上一 层的异常部分中,一直到最外层。 注意:在异常部分抛出的异常将控制转到上一层的异常部分。 处理异常将停止异常的传播和解决。有时用户希望在错误发生时,程序仍然 能执行一些动作,要达到这个目的,可以把希望执行的动作放在异常处理器中, 然后执行不带参数的 RAISE 语句,RAISE 语句将重新抛出出现的异常,允许他 继续传播。 DECLARE order_too_old EXCEPTION; 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com BEGIN RAISE order_too_old; EXCEPTION WHEN order_too_old THEN DECLARE file_handle UTL_FILE.FILE_TYPE; BEGIN --open file file_handle:=UTL_FILE.FOPEN(location=>'/ora01/app/oracle/adm in/test/utlsir' ,filename=>'error.log' .open_mode=>'W'); --write error stack UTL_FILE.PUT_LINE(filehandle, DBMS_UTILITY.FORMAT_ERROR_STACK); --write the call stack UTL_FILE.PUT_LINE(filehandle, DBMS_UTILITY.FORMAT_CALL_STACK); --close error log UTL_FILE.FCLOSE(file_handle); RAISE;--re-raise the exception END; END; 如果从FORMAT_XXX_STACK 输出一个很大的值,那么使用 DBMS_OUTPUT 或UTL_FILE 显示错误或调用堆的异常部分自身也会抛出异 常,这两个堆常规下最多能返回 2000 字节,但 utl_file.put_line 被限制在 1000 字节以内,而 dbms_output.put_line 限制在 512 字节内。如果使用前面的代码 并且不允许这种可能性,那么在异常处理器中将抛出一个未处理的异常。 注意:GOTO 语句不能用于将程序流程从执行部分传递到异常部分或反之。 1.6.4 已命名异常 在PL/SQL 块的异常部分只有已命名的异常才能被 WHEN 子串处理, 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com ORACLE 包含了一系列已命名的异常,这些异常都声明在 STANDARD 包中, 这些内建异常在这里就不一一讲述,有兴趣的读者可以查阅有关资料。 第 2222 章 复合数据 PL/SQL 有两种复合数据结构:记录和集合。记录由不同的域组成,集合由 不同的元素组成。在本文中我们将讨论记录和集合的类型、怎样定义和使用记录 和集合。 2.1 PL/SQLPL/SQLPL/SQLPL/SQL 记录 记录是 PL/SQL 的一种复合数据结构,标量数据类型和其他数据类型只是简 单的在包一级进行预定义,但复合数据类型在使用前必须被定义,记录之所以被 称为复合数据类型是因为它由域这种由数据元素的逻辑组所组成。域可以是标量 数据类型或其他记录类型, 它与 c语言中的结构相似,记录也可以看成表中的数据行,域则相当于表中 的列,在表和虚拟表(视图或查询)中非常容易定义和使用,行或记录中的每一 列或域都可以被引用或单独赋值,也可以通过一个单独的语句引用记录所有的 域。在存储过程或函数中记录也可能有参数。 2.1.1 创建记录 在PL/SQL 中有两种定义方式:显式定义和隐式定义。一旦记录被定义后, 声明或创建定义类型的记录变量,然后才是使用该变量。隐式声明是在基于表的 结构或查询上使用%TYPE 属性,隐式声明是一个更强有力的工具,这是因为这 种数据变量是动态创建的。 2.1.1.1 显示定义: 显式定义记录是在 PL/SQL 程序块中创建记录变量之前在声明部分定义。使 用type 命令定义记录,然后在创建该记录的变量。语法如下: TYPE record_type ISRECORD(field_definition_list); field_definition_list 是由逗号分隔的列表。 域定义的语法如下: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com field_name data_type_and_size [NOT NULL][{:=|DEFAULT} default_value] 域名必须服从与表或列的命名规则相同的命名规则。下面我们看一个例子: DELCARE TYPE stock_quote_rec ISRECORD( symbol stock.symbol%TYPE , bid NUMBER(10,4) , ask NUMBER(10,4) , volume NUMBERNOT NULL:=0 , exchange VARCHAR2(6) DEFAULT'NASDAQ'); real_time_quote stock_quote_rec; --声明一个叫 real_time_quote 的stock_quote_rec 类型的变量 上面代码中的阴影部分就是这个纪录类型的域列表。 域定义时的%TYPE 属性用于引用数据库中的表或视图的数据类型和大小, 而在此之前程序不知道类型和大小。在上面的例子记录中的 symbol 域在编译时 将被定义为与表 stock 的 SYMBOL 列相同的数据类型和大小,当代码中要使用来 自数据库中的数据时,在变量或域定义中最好使用%TYPE 来定义。 上机练习: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 2.1.1.2 隐式定义记录 隐式定义记录中,我们不用描述记录的每一个域。这是因为我们不需要定义 记录的结构,不需要使用 TYPE 语句,相反在声明记录变量时使用%ROWTYPE 命令定义与数据库表,视图,游标有相同结构的记录。与TYPE 命令一样,它也 是一种定义获得数据库数据记录的好方法。 DECLARE accounter_info accounts%ROWTYPR; 有一些 PL/SQL 指令在使用隐式定义记录时没有使用%ROWTYPE 属性, 比如游标 FOR 循环或触发器中的:old 和:new 记录。(触发器在中心数据库开发 规范中不允许使用,所以本文不作阐述) DELCARE CURSOR c1 ISSELECTDISTINCT tm200001012 dqh FROM TM200001; BEGIN FOR rec1 IN c1 LOOP 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com … ENDLOOP; 上述代码中的 rec1 就是一个隐式声明的纪录类型,虽然在声明段中没有对 它进行声明,但是在程序运行中,仍然可以把它当成一个变量来用。 上机练习: 2.1.2 使用纪录 用户可以给记录赋值、将值传递给其他程序。记录作为一种复合数据结构意 味作他有两个层次可用。用户可以引用整个记录,使用 select into 或fetch 转移 所有域,也可以将整个记录传递给一个程序或将所有域的值赋给另一个记录。在 更低的层次,用户可以处理记录内单独的域,用户可以给单独的域赋值或者在单 独的域上运行布尔表达式,也可以将一个或更多的域传递给另一个程序。 2.1.2.1 引用记录 记录由域组成,访问记录中的域使用点(.)符号。在前面的上机练习中, 我们已经接触到了这种表示方式。如 empName.emp_name 和rec.ename。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 2.1.2.2 给记录赋值 给记录或记录中的域赋值的方法有几种,可以使用 SELECT INTO 或 FETCH 给整个记录或单独的域赋值, 可以将整个记录的值赋给其他记录,也可 以通过给每个域赋值来得到记录,以下我们通过实例讲解每一种赋值方法。 1111、使用 SELECTSELECTSELECTSELECTINTOINTOINTOINTO 使用 SELECT INTO 给记录赋值要将记录或域放在 INTO子句中,需要注意 的是 INTO 子句中的变量与 SELECT 中列的位置相对应。 上机练习: 2222、使用 FETCHFETCHFETCHFETCH 如果 SQL 语句返回多行数据或者希望使用带参数的游标,那么就要使用游 标,这种情况下使用 FETCH 语句代替 SELECT INTO 是一个更简单、更有效率 的方法: 上机练习: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 2.1.2.3 注意事项 使用赋值语句将整个记录复制给另一个记录是一项非常有用的技术,不过记 录必须精确地被声明为相同的类型,不能是基于两个不同的 TYPE 语句来获得相 同的结构。 实例代码: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 第二个要注意的是,记录与纪录之间不能直接比较。比如下面的代码就是错 误的: IF emp1>emp1_too THEN 此外,还要注意的是,虽然有的纪录变量声明为%ROWTYPE 类型,但是 不能用在 INSERT 语句里面。比如说下面的 SQL 语句: Emp_Info EMP%ROWTYPE; … INSERTINTOEMP VALUES(Emp_Info);--错误! 2.2 PL/SQLPL/SQLPL/SQLPL/SQL 集合 在PL/SQL 中,“集合”类型和其他编程语言(如 C)里的数组很类似。集 合有以下三种类型: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com � Index_by 表——也就是所谓的“联合数组”,你可以使用一个数字或字 符串作为索引来直接查找表里的数据。好比其他语言中的哈希表类型。 � 嵌套表——可以存储任意数量的元素,以连续的数字作为下标索引。和 Oracle 中的其他表一样,你可以使用 DML 语言对其进行操纵。 � VARRAY——和嵌套表类似,但是存储元素的数量必须是指定的。而且 灵活性也要比嵌套表差很多,所以比较少用。 Index_by 表不能存储在数据库中,但嵌套表和 VARRAY 可以被存储在数据 库中。 2.2.1 集合定义 2.2.1.1 Index_by Index_by Index_by Index_by 表(在 Oracle9i Oracle9i Oracle9i Oracle9i 中叫联合数组) Index_by 表集合的定义语法如下: TYPE type_name ISTABLEOF element_type [NOTNULL] INDEXBY[BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size_limit)]; INDEXBY key_type; 这里面重要的关键字是 INDEXBYBINARY_INTERGET,没有这个关键字, 那么集合将是一个嵌套表,element_type 可以是任何合法的 PL/SQL 数据类型, 包括:PLS/INTEGER、SIGNTYPE、和BOOLEAN。其他的集合类型对数据库 的数据类型都有限制,但Index_by 表不能存储在数据库中,所以没有这些限制。 一旦定义了 index_by 表,就可以向创建其他变量那样创建 index_by 表的变量: DECLARE TYPE symbol_tab_typ ISTABLEOF VARCHAR2(5) INDEXBY BINARY_INTEGER; symbol_tab symbol_tab_typ; BEGIN 2.2.1.2 嵌套表 嵌套表非常类似于 Index_by 表,创建的语法也非常相似。使用 TYPE 语句, 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 只是没有 INDEXBY BINARY_INTEGER 子串。 TYPE type_name ISTABLEOF element_type [NOTNULL]NOTNULL; 这里的选项要求集合所有的元素都要有值,element_type 可以是一个记录, 但是这个记录只能使用标量数据类型字段以及只用于数据库的数据类型(不能是 PLS_INTEGER,BOOLEAN 或SIGNTYPE)。嵌套表和 VARRAY 都能作为列存 储在数据库表中,所以集合自身而不是单个的元素可以为 NULL,ORACLE 称这 种整个集合为 NULL 的为“自动设置为 NULL(atomically NULL)”以区别元素为 NULL 的情况。当集合为 NULL 时,即使不会产生异常,用户也不能引用集合中 的元素。用户可以使用 IS NULL 操作符检测集合是否为 NULL。 存储在一个数据库中的嵌套表并不与表中的其它数据存放在同一个数据块 中,它们实际上被存放在第二个表中。正如没有 order by 子句select 语句不能 保证返回任何有顺序的数据,从数据库中取回的嵌套表也不保证元素的顺序。 2.2.1.3 VARRAYVARRAYVARRAYVARRAY VARRAY 或数据变量都有元素的限制。像其他集合一样 VARRAY 定义仍然 使用TYPE 语句,但关键字 VARRAY 或VARRYING ARRAY 告诉ORACLE 这 是一个 VARRAY 集合。 TYPE type_name IS[VARRAY|VARYING ARRAY](max_size) OF element_type [NOTNULL] max_size 是一个整数,用于标示 VARRAY 集合拥有的最多元素数目。 VARRAY 集合的元素数量可以低于 max_size, 但不能超过 max_size 。 element_type 是一维元素的数据类型,如果 element_type 是记录,那么这个记 录只能使用标量数据字段(与嵌套表相似)。NOT NULL 子串表示集合中的每一 个元素都必须有值。与嵌套表相似,VARRAY 能够自动为 NULL,可以使用 IS NULL 操作符进行检测。与嵌套表不同的是,当 VARRAY 存储在数据库中时与 表中的其他数据存放在同一个数据块中。正象列的排序保存在表的 SELECT*中 一样,元素的顺序保存在 VARRAY 中。同样由于集合是在线存储的,VARRAY 很适合于小型集合。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 2.2.2 使用集合 像记录一样,集合可以在两个层面上使用:操作整个集合、访问集合中的单 个元素。第一种情况使用集合名,第二种情况使用下标: collection(subscript) 2.2.2.1 初始化集合 使用集合之前必须要初始化,对于 Index_by 表初始化是自动进行的,但是 对于嵌套表和 VARRAY 就必须使用内建的构造函数。如果重新调用,嵌套表和 VARRAY 自动置 NULL,这不只是元素置 NULL,而是整个集合置 NULL。给集 合内的元素赋值需要使用下标符号。将一个集合的值赋给另一个集合,只需要简 单的使用赋值操作符。Index_by 集合初始化是最简单的,只要引用其中的一个 元素,集合就被初始化了。 嵌套表和 VARRAY 由构造函数初始化,构造函数和集合的名字相同,同时 有一组参数,每个参数对应一个元素,如果参数为 NULL,那么对应的元素就被初 始化为 NULL,如果创建了元素,但没有填充数据,那么元素将保持 null 值,可 以被引用,但不能保持数据。如果元素没有初始化,那么就不能引用该元素。 嵌套表初始化示例: DECLARE TYPE CourseList ISTABLEOF VARCHAR2(16); my_courses CourseList; BEGIN my_courses := CourseList(’Econ 2010’,’Acct 3401’,’Mgmt 3100’); END; 上面是一个嵌套表的初始化函数,初始化的同时给表赋值,由于嵌套表对元 素的个数没有限制,所以你可以根据需要在初始化阶段给变量 my_courses 赋予 任意多的值。 VARRAY VARRAY VARRAY VARRAY 初始化示例: DECLARE 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com TYPE ProjectList IS VARRAY(50) OF VARCHAR2(16); accounting_projects ProjectList; BEGIN accounting_projects := ProjectList(’Expense Report’,’Outsourcing’,’Auditing’); END; 上面的代码初始化了一个叫 accounting_projects 的VARRAY 变量,同时赋 予它三个值(其余的 47 个元素为 NULL)。 2.2.2.2 引用集合及其元素 引用集合只要使用其标志符就行了。 引用集合中的元素,就要使用下标。 � 嵌套表的下标范围是 1——2**31。 � VARRAY 类型下表的范围是 1——MAX_SIZE。 � index_by 表(联合数组)下标是两位的整数(如果定义了 INDEXBY BINARY_INTERGET 的话),可以为正也可以为负,范围是: -2147483647—2147483647。 在引用集合之前,对于嵌套表和 VARRAY 类型,必须初始化了才能引用其 中的元素。如下例所示: 上机练习: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 正确的应该是: 可见在初始化之后,就能够引用嵌套表和 VARRAY 了。 对于INDEX_BY 表,只要对其中一个元素进行赋值,就是初始化了整个集 合变量。 上机练习 2.2.2.3 给集合赋值 给集合赋值的方法和普通变量相类似。我们可以通过 DML SQL 语句来对它 们进行赋值,也可以使用以下形式: Collection(subscript) := expression;; 对集合赋值需要注意的是,必须保证所赋的值的类型必须和定义集合的类型 相匹配,不然的话,就会出现异常。 下面举一个给集合赋空值的例子: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 2.2.2.4 比较集合 对于比较两个集合变量是否相同,首先要确定这两个集合变量是同意类型 的。然后就得逐个比较这些集合变量中的元素个数和每一个元素的值是否相等。 下面是一个错误比较的例子: 不过,从 Oracle10.1 开始,这样的比较是允许的,如下图所示: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 2.2.2.5 集合的方法 PL/SQL 提供了一系列内建函数和过程,被称为“集合方法”,让你能够方 便地操纵集合。 引用集合方法的语法如下: Collection_name.method Collection_name.method(index_number [, index_number]) 下面是关于这些方法一个完整的列表: 方法 描述 使用限制 COUNT 返回集合中元素的个数 DELETE 删除集合中所有元素 DELETE() 删除元素下标为 x的元素,如果 x为null,则集合保持不变 对VARRAY 非法 DELETE(,) 删除元素下标从 X到Y的元素,如果 X>Y 集合保持不变 对VARRAY 非法 EXIST() 如果集合元素 x已经初始化,则返回 TRUE, 否则返回 FALSE EXTEND 在集合末尾添加一个元素 对Index_by 非法 EXTEND() 在集合末尾添加 x个元素 对Index_by 非法 EXTEND(,) 在集合末尾添加元素 n的x个副本 对Index_by 非法 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com FIRST 返回集合中的第一个元素的下标号,对于 VARRAY 集合始终 返回 1。 LAST 返回集合中最后一个元素的下标号, 对于VARRAY返回值始 终等于 COUNT. LIMIT 返回 VARRY 集合的最大的元素个数,对于嵌套表和对于嵌 套表和 Index_by 为null 对Index_by 无用 NEXT() 返回在元素 x之后及紧挨着它的元素的值,如果该元素是最 后一个元素,则返回 null. PRIOR() 返回集合中在元素 x之前紧挨着它的元素的值,如果该元素 是第一个元素,则返回 null。 TRIM 从集合末端开始删除一个元素 对Index_by 非法 TRIM() 从集合末端开始删除 x个元素 对Index_by 非法 每个方法具体的使用范例,可参考 Oracle 自带的文档,这里就不做赘述了。 2.2.3 选择使用 PL/SQL PL/SQL PL/SQL PL/SQL 的集合类型 如果你已经使用了其他语言来实现商业逻辑,现在决定要用 PL/SQL 来重 写,那么以下的一些建议对于使用 PL/SQL 集合类型会有所帮助: � 其他语言中的数组可以对应于 PL/SQL 中的 VARRAY。 � 其他语言中的 set 或bag 类型对应于 PL/SQL 的嵌套表类型。 � 其他语言中的哈希表或其他非排序的查寻链表对应于 PL/SQL 的联合数 组(Index_by 表) 如果你一开始就使用 PL/SQL 进行设计和开发,那么要仔细考虑它们各自的 优缺点。 下面就简单阐述有关嵌套表和联合数组之间的选择。 嵌套表和联合数组都使用相同的下标符号,但是当它们需要保证持久性和快 速参数传递时,就有不同的特性。 嵌套表能够以列的形式存储在数据库里面,但是联合数组不能。所以,嵌套 表比较适合于那些需要持久存储的重要数据。 联合数组比较适合那些数据量小的查询链表结构。它会在每次调用存储过程 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 或程序包初始化的时候创建在内存中。它们特别适合那些在创建前不知道容量大 小的信息集合,因为联合数组的大小是无限制的。而且它们的索引值也更加灵活, 可谓负数,非排序数值,甚至可以使用字符串而不是数字。 PL/SQL 会自动在宿主数组和联合数组之间转换。与 Oracle 服务器进行集 合信息交互最有效的途径就是使用 PL/SQL 块把输入输出的宿主数组和联合数 组(Index-by 表)绑定起来。 第 3333 章 PL/SQL 中的 SQL 3.1 PL/SQL PL/SQL PL/SQL PL/SQL 中的 DMLDMLDMLDML 在讲解游标之前,我们先回顾一下之前所学习的 SQL 知识,特别是使用频 率最高的数据操作语言(DML)。 下表列出了 Oracle 支持的 DML: 语句 描述 INSERT 向表中插入(添加)数据行 UPDATE 更新存储在表中的数据 DELETE 删除行 SELECTFORUPDATE 禁止其他用户访问 DML 语句正在处理的行。 LOCKTABLE 禁止其他用户在表中使用 DML 语句 3.1.1 插入数据(INSERTINSERTINSERTINSERT) 首先看一下 INSERT 的语法: 1、 插入单行数据: INSERTINTO table [(col1, col2, ..., coln)] VALUES(val1, val2, ..., valn); 2、插入多行数据: INSERTINTO table [(col1, col2, ..., coln)] AS SELECT...; 表名(table)后面的列目录是可选的,缺省的列目录是所有的列名,包括 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com comlumn_id ,comlumn_id 可以在数据字典视图 ALL_TAB_COLUMNS,USER_TAB_COLUMNS ,或者 DBA_TAB_COLUMNS 中找到。 插入行的数据的数量和数据类型必须和列的数量和数据类型相匹配。不符合 列定义的数据类型将对插入值实行隐式数据转换。NULL 字符串将一个 NULL 值 插入适当的列中。关键字 NULL 常常用于表示将某列定义为 NULL 值。 下面的两个例子是等价的。 insert into emp(empno,ename,job,mgr) values(1111,'test1',NULL,7900); 或 insert into emp(empno,ename,job,mgr) values(1111,'test1','',7900); 上机练习: 3.1.2 更新数据(UPDATEUPDATEUPDATEUPDATE) UPDATE 命令用于修改表中的数据。语法如下: UPDATE table SET col1 = val1 [, col2 = val2, ... colN = valN] [WHERE where clause]; 上机练习: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 3.1.3 删除数据(DELETEDELETEDELETEDELETE) DELETE 语句用来从表中删除一行或多行数据,该命令包含两个语句: 1、关键字 DELETE FROM 后跟准备从中删除数据的表名。 2、WHERE 后跟删除条件 基本语法如下: DELETEFROM table [WHERE where-clause]; 上机练习: 3.1.4 DML DML DML DML 语句的游标属性 Oracle 会存储最近一次执行的 DML 语句的属性,如下表所示: 属性名 描述 SQL%FOUND 如果一条或多条数据行被成功修改(创建、更新、删除),则返回 “TRUE”。 SQL%NOTFOUND 如果没有数据行被 DML 语句修改,则返回 TRUE。 SQL%ROWCOUNT 返回被 DML 语句修改的数据行数。 SQL%ISOPEN 对于隐式游标,总是返回 FALSE,因为 Oracle 自动打开和关闭隐式 游标。 我们可以在 PL/SQL 中引用这些属性来控制 DML 流程。 上机练习: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 3.1.5 DML DML DML DML 语句的返回信息 在使用 INSERT、UPDATE 和DELETE 语句时,可以使用 RETURNING 子 句来获得 DML 的执行结果,请看下面的例子: 上机练习: 练习 1、建立一个叫 test1 的存储过程,然后执行 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 执行后输入结果: 3.1.6 DML DML DML DML 和异常 在一个程序块中,如果 DML 语句发生了异常(这几乎是 PL/SQL 最常见的 异常),程序是否回滚这一块程序中所有 DML 语句,取决于你的异常处理。 让我们来考虑下面的一个过程: CREATEORREPLACEPROCEDURE empty_library ( pre_empty_count OUTPLS_INTEGER) 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com IS BEGIN -- The tabCount function returns the number -- of rows in the specified table pre_empty_count := tabcount ('books'); DELETEFROM books; RAISENO_DATA_FOUND; END; 注意上面的代码,我们在唤起“NO_DATA_FOUND”异常前,返回了一个 整数值表示 books 表中的纪录数。现在,让我们使用一段匿名块来引用这个过 程,从而演示一下效果: DECLARE table_count NUMBER:= -1; BEGIN INSERTINTO books VALUES(...); empty_library (table_count); EXCEPTION WHENOTHERSTHEN DBMS_OUTPUT.put_line (tabcount ('books')); DBMS_OUTPUT.put_line (table_count); END; 输出为: 0 -1 注意:我们发现虽然有异常发生,但是 books 表里面的纪录还是被删除了。 Oracle 不会自动地进行回滚。但是,奇妙的是,变量 table_count 却回到了初始 值。 1. 所以为了避免混乱,我们一般都要自己写回滚块。下面是要注意的几个 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 事项: 2. 如果你的程序块是一个独立自治的事务,那么当异常发生时,你就必须 执行一个 commit 或rollback。 3. 你可以使用 savepoin 来控制回滚的范围。但要注意 savepoint 的设置范 围。 如果异常被传递到最外层(比如说,它跑到“非控制”区域),那么,对于 大部分 PL/SQL 的执行环境(比如 SQL*Plus),一个不可预见或没经检查的回滚 将会自动执行,造成了一些错误变更。 3.1.7 DML DML DML DML 和纪录 从Oracle 9i Release2 开始,你就可以在 insert 和update 语句中使用纪录 了。比如下面的代码例子: CREATEORREPLACEPROCEDURE set_book_info (book_in IN books%ROWTYPE) IS BEGIN INSERTINTO books VALUES book_in; EXCEPTION WHENDUP_VAL_ON_INDEX THEN UPDATE books SETROW = book_in WHERE isbn = book_in.isbn; END; 这样带来的好处有两个: 1、 简化代码,用时髦的话说,就是把代码提高到了“记录层”。 2、 增加了程序的健壮性,使用“%ROWTYPE”,当表结构发生变化时, 需要维护的地方就少一些。 3.1.7.1 基于“记录”(recordrecordrecordrecord)的 INSERT INSERT INSERT INSERT 语句 你能够声明一个基于某张表的记录变量,然后直接把这个变量的值插入到表 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 里去。 参看以下代码: 需要注意的是,在INSERT 语句中的 VALUES 子句后面是不能跟“()”的, 如果你按照一般的规范写了以下的代码: INSERTINTO dept VALUES(my-dept); 那么 Oracle 就会报以下错误: 此外,记录变量的结构必须和表结构完全一致,这也要我们在使用时多加小 心。 3.1.7.2 基于“记录”(recordrecordrecordrecord)的 UPDATE UPDATE UPDATE UPDATE 语句 With Oracle9i Database Release 2, you can also perform updates of an entire row with a record. The following example inserts a row into the books table with a %ROWTYPE record. Notice that I use a new keyword, ROW, to indicate that I am updating the entire row with a record: 同插入语句一样,你也可以使用记录变量对整条记录进行更新。下面的这个 例子就是对 DEPT 表中的一条记录进行更新,其中使用了一个关键字:ROW, 表示对整一条记录进行更新: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 下面有两点关于利用记录变量更新数据表的限制: 1、 你必须使用 ROW语法来更新整个记录,不能仅仅更新某些字段 组。 2、 在更新语句中不能使用子查询。 3.1.7.3 记录变量与 RETURNING RETURNING RETURNING RETURNING 子句结合使用 如果 DML 语句影响了一条记录,你可以把这条记录的数值返回给一个记录 或者组合变量。如下代码所示: 在RETURNING 子句中,我们还必须把每个列名写出来,因为 Oracle 还没 有支持“*”的写法。 3.1.7.4 使用记录变量插入或更新的限制 使用记录变量来插入或更新,需要注意以下几点: 在SET 子句的左边,你必须使用 ROW关键字。而且不能有其他的 SET 子 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 句 ,也就是说,你不能在更新一条记录的同时更新一个单独的列。 如果你插入一条记录时,不能只给某个单独列赋值。 你不能插入或更新一个含有内嵌记录的记录变量。 你不能在动态 SQL 的DML 语句中使用记录变量。因为这需要 Oracle 支持 PL/SQL 记录类型和 SQL 语句的绑定,目前只支持 SQL 类型的绑定。 3.2 事务管理 Oracle 的数据库管理系统提供了一套非常健壮的事务管理模型。你的应用 决定哪一些代码是一整块的逻辑单元,需要提交或者回滚。一个事务在第一局 SQL 语句执行就隐式地开始了,直到 COMMIT 或ROLLBACK ,或者在 ROLLBACKTO SAVEPOINT 之后继续。 Oracle 提供了以下事务控制语句: COMMITCOMMITCOMMITCOMMIT 提交从上一个 COMMIT 或ROLLBACK 开始的变更,并且释放所有的锁。 ROLLBACKROLLBACKROLLBACKROLLBACK 消除所有从上一个 COMMIT 或ROLLBACK 开始的变更,并且释放所有的 锁。 ROLLBACKROLLBACKROLLBACKROLLBACKTOTOTOTOSAVEPOINTSAVEPOINTSAVEPOINTSAVEPOINT 回滚到特定的保存点状态,并且释放所有代码范围内所形成的锁。 SAVEPOINTSAVEPOINTSAVEPOINTSAVEPOINT 创建一个“保存点”,让你可以进行部分回滚。 SETSETSETSETTRANSACTIONTRANSACTIONTRANSACTIONTRANSACTION 允许你开始一个只读或可读写的会话,创建一个隔离层,或者把当前会话分 配给一个特定的回滚段。 LOCKLOCKLOCKLOCKTABLETABLETABLETABLE 允许你在某种特定方式下所定整张数据库表。它会超越普通应用于表的行级 别锁。 下面将会对以上命令做一个详细的讨论。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 3.2.1 COMMIT COMMIT COMMIT COMMIT 语句 当你COMMIT,你就把你会话中对数据库的更改永久保留了下来。一旦你 提交,你所做的更改就会对其他的用户或会话可见。COMMIT 语句的语法是: COMMITCOMMITCOMMITCOMMIT[WORK][WORK][WORK][WORK][COMMENT[COMMENT[COMMENT[COMMENT text];text];text];text]; WORK WORK WORK WORK 关键字是可选的,能够用来增加可读性。 COMMENT 关键字确定了一个与当前事务联系的注释。这个注释必须用单 引号括起来,而且长度不能超过 50 个字符。注释文本一般是由分布式事务使用, 在二相提交框架中,可用来分析可疑事务。它与事务 ID 一起存储在数据字典中。 注意,COMMIT 释放你会话中所有的行级别和表级别的锁,比如一条 SELECTFOR UPDATE 语句。它还会清楚所有从上一个 COMMIT 或 ROLLBACK 开始设定的保存点。 一旦你 COMMIT 了你的变更,你就不能使用 ROLLBACK 语句回滚它们。 下面的语句都是 COMMIT 命令的有效使用: COMMIT; COMMITWORK; COMMITCOMMENT'maintaining account balance'. 3.2.2 ROLLBACK ROLLBACK ROLLBACK ROLLBACK 语句 当你使用了 ROLLBACK 时,你就会把当前会话事务中对数据库所作的全部 或一些变更回退到初始状态。为什么你要撤销变更?从 SQL 的角度看来, ROLLBACK 给你一个消除错误的机会。比如在下面的语句中: DELETEDELETEDELETEDELETEFROMFROMFROMFROM orders;orders;orders;orders; ROLLBACK 的语法如下: ROLLBACKROLLBACKROLLBACKROLLBACK[WORK][WORK][WORK][WORK][TO[TO[TO[TO[SAVEPOINT[SAVEPOINT[SAVEPOINT[SAVEPOINT]]]] savepoint_name];savepoint_name];savepoint_name];savepoint_name]; There are two basic ways to use ROLLBACK: without parameters or with the TO clause to indicate a savepoint at which the ROLLBACK should stop. The parameterless ROLLBACK undoes all outstanding changes in your transaction. 有两种方式来使用 ROLLBACK:一种是没有参数的,另一种是含有保存点 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 参数的。 没有参数的 ROLLBACK 会回滚所有事务中未完成的改变。而 ROLLBACK TO 会回退到用户指定的保存点状态。 下面是 ROLLBACK 的正确用法: ROLLBACK;ROLLBACK;ROLLBACK;ROLLBACK; ROLLBACKROLLBACKROLLBACKROLLBACKWORK;WORK;WORK;WORK; ROLLBACKROLLBACKROLLBACKROLLBACKTOTOTOTO begin_cleanup;begin_cleanup;begin_cleanup;begin_cleanup; 注意,当你回滚到一个特定的保存点时,所有在这个保存点之后的保存点都 将被撤销,但是这个特定的保存就不会。这意味着你的事务,如果发生了其他错 误,可以从一个保存点回滚到相同的保存点。 一旦你执行了 INSERT、UPDATE 或DELETE 语句,PL/SQL 就会自动且 隐式地生成一个保存点,如果你的 DML 语句发生错误,就会自动地回滚到这个 隐式保存点。但是用这种方式,只能回滚最后一条 DML 语句。 3.2.3 SAVEPOINTSAVEPOINTSAVEPOINTSAVEPOINT SAVEPOINT gives a name to, and marks a point in, the processing of your transaction. This marker allows you to ROLLBACKTO that point, erasing any changes and releasing any locks issued after that savepoint, but preserving any changes and locks that occurred before you marked the savepoint. SAVEPOINT 命令可以在你的事务过程中创建一个保存点。这样就允许你把 事务回滚到这个点上,从而撤销所做的修改以及释放这个保存点之后的锁,但是 保留了在这个保存点之前的所有变更和锁。 SAVEPOINT 的语法是 SAVEPOINTSAVEPOINTSAVEPOINTSAVEPOINT savepoint_name;savepoint_name;savepoint_name;savepoint_name; 在这里“savepoint_name”是一个不需要声明的标志符,所以保存点的名 字必须符合 Oracle 的标志符命名标准。 要注意的是,savepoint(保存点)是不会被 PL/SQL 程序块所限制的。如 果你在一个会话中使用了保存点,那么会话就会回退到这个点上,而不会考虑这 个保存点是在哪个过程、函数或是匿名块执行的。因此,如果你在一个递归程序 中使用了保存点,虽然在每一层递归中都回生成一个保存点,但是你只能回滚到 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 最近标记的那个保存点上。 3.3 数据检索与游标 关于 PL/SQL 的特点,就在于它紧密地与数据库结合在一起,包括改变数据 库内的数据以及从数据库中提取信息。本小节主要探讨了 PL/SQL 如何从 Oracle 数据库中查询数据,以及如何让查询出来的数据可用。 当你执行了一条 PL/SQL 程序中的 SQL 语句时,Oracle RDBMS 就会分配 一块私有的内存区域给这条语句,并且在系统全局区(SGA)中管理这条语句所 提取的数据。这一块私有内存区含有这条 SQL 语句相关的信息以及被这条语句 影响或检索的数据结果集。 PL/SQL 提供了数种方式来操纵上述的私有内存区和其中的数据,这些方式 包含以下: 1、隐式游标; 2、显式游标; 3、游标变量; 4、游标表达式; 5、动态 SQL。 本节将会重点阐述隐式游标、显式游标以及与游标相关的一些特性。 3.3.1 游标的基础概念 简单来说,你可以把游标看成是指向数据表的一种指针,如下面的这个游标 就与 EMP 表的全部列关联在一起: CURSOR emp_cur ISSELECT*FROMEMP; 一旦申明了这样一个游标,你就可以打开它,如下: OPEN emp_cur; 然后还可以从中取出记录: FETCH emp_cur INTO emp_rec; 最后,你还要关闭这个游标: CLOSE emp_cur; 当然,你所声明的游标也不必要和数据表中的整条记录关联起来,也可以仅 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 仅和其中的某些列关联,如下所示: CURSOR joke_feedback_cur IS SELECT J.name, R.laugh_volume, C.name FROM joke J, response R, comedian C WHERE J.joke_id = R.joke_id AND J.joker_id = C.joker_id; 在这里,上述游标实际上并不是作为一种指针与数据库中实际数据表相互作 用,这个游标实际上是指向一个虚拟的数据表或视图,这个虚拟的数据表或视图 是由游标后面的 SQL 语句所表示。如果上述 SQL 语句返回了 20 条记录,那么 这个游标的功能就相当于一个指向这 20 条记录的指针。 3.3.1.1 典型查询操作 在PL/SQL 中,查询主要遵循以下几个步骤(如果你使用显式游标的话,也 可以自己制定各个步骤): 1111、解析 第一步就是处理一条 SQL 语句,并进行解析,以确认它是有效的,然后制 定执行计划(关于执行计划的内容在《Oracle SQL 调优》的教材中详述)。 2222、绑定 当你进行绑定,你就把你程序中的变量(称为宿主变量)与 SQL 语句中的 占位符关联起来。PL/SQL 引擎自己就会执行这些绑定。但是如果你想使用绑定 变量的话,就必须显式地要求对一个变量值进行绑定。 3333、打开游标 当你打开了一条游标,绑定变量就会决定 SQL 语句的结果集。指向当前记 录的指针就会指向第一行。有时候你不必显式地打开一个游标,PL/SQL 引擎会 自动地执行这个步骤。 4444、执行 在执行步骤中,SQL 语句就会在 SQL 引擎中运行。 5555、提取 如果你正在执行一个查询,那么 FETCH 命令就会检索并返回这个游标结果 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 集的下一条记录。每一次提取,PL/SQL 就会把指针向前移动一次。要注意的是, 当你操作的是显式游标的话,如果结果集中已经没有记录,那么使用 FETCH 是 不会做任何事情的(包括报错)。 6666、关闭 这一步就关闭游标并且释放这个游标使用的所有内存。一旦关闭,这个游标 就不再拥有结果集。 下图显示了查询数据中的几个步骤: 3.3.1.2 游标属性简介 Oracle 为游标提供了 6种属性,下面简单介绍其中常用的 4种: 名称 描述 %FOUND 如果记录返回成功,则返回 True,否则返回 False %NOTFOUND 如果记录返回不成功,则返回 True,否则返回 False %ROWCOUNT 实时返回这一点上从游标提取的记录数 %ISOPEN 如果游标开启则返回 True,否则返回 False。 1111、%FOUND %FOUND %FOUND %FOUND 属性 %FOUND 属性反映了你最近对游标执行 FETCH 命令的状态。如果针对显 式游标最近执行的 FETCH 命令返回了一条记录,则这个属性为 True;如果没有 记录返回,则为 False。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 如果游标还没有打开,一个对%FOUND 属性的引用就会引发一个 INVALID_CURSOR(无效游标)的异常。你可以获取每一个打开了的显式游标 的%FOUND 属性值。 在下面的范例中,是%FOUND 属性最常用的方式,从游标中取记录,然后 循环更新数据表,每一次循环开始时都要检查游标中是否还有记录,如果没有的 话就跳出循环。在更新数据表之后,又再一次检查游标的%FOUND 属性,如果 返回为 True,则显示接下来要更新的内容。 OPEN caller_cur; LOOP FETCH caller_cur INTO caller_rec; EXITWHENNOT caller_cur%FOUND; UPDATE call SET caller_id = caller_rec.caller_id WHERE call_timestamp < SYSDATE; IFSQL%FOUNDTHEN DBMS_OUTPUT.PUT_LINE( 'Calls updated for ' || caller_rec.caller_id); ENDIF; ENDLOOP; CLOSE caller_cur; 2222、%NOTFOUND %NOTFOUND %NOTFOUND %NOTFOUND 属性 %NOTFOUND 属性与%FOUND 刚好相反。如果显式游标不能返回下一条 记录,则为 True,否则为 False。如果游标是因为别的错误不能返回记录,则一 个异常就会被引发。 如果游标还没有被打开,则与%FOUND 属性一样,发出一个 INVALID_CURSOR 的异常,你也可以检查每一个游标的%NOTFOUND 属性。 和上面的程序相比,在跳出循环的语句那里,我更倾向于使用%NOTFOUND 属性,这样程序更具有可读性,如下所示: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com EXITWHEN caller_cur%NOTFOUND; 3333、%ROWCOUNT %ROWCOUNT %ROWCOUNT %ROWCOUNT 属性 %ROWCOUNT 属性返回游标已经提取的记录数,到引用这个属性为止。 当你刚打开游标时,它的%ROWCOUNT 属性值为 0。如果引用是一个没有打开 的游标的%ROWCOUNT 属性,则会报错:INVALID_CURSOR。当提取一条记 录后,%ROWCOUNT 属性值也会相应地加 1。 使用%ROWCOUNT 属性可以来确认有多少记录已经被提取。在处理了特 定记录数之后,可以停止你的程序。 4444、%ISOPEN%ISOPEN%ISOPEN%ISOPEN 如果游标是打开的,那么%ISOPEN 属性返回 True,否则返回 False。 3.3.2 隐式游标 每一次当你执行 SQL DML 语句时,Oracle PL/SQL 就会声明并管理一个隐 式游标。比如:SELECT INTO 语句从数据库中直接返回数据给 PL/SQL 中的数 据结构。这一类的游标就被称为隐式游标,Oracle 会为你自动执行与之相关的 许多操作,比如说定位一个游标,打开游标,提取数据等等。 一个隐式游标就是一个包含了以下特征的 SELECT 语句: The SELECT statement appears in the executable section of your block; it is not defined in the declaration section, as explicit cursors are. 1、SELECT 语句是放在你程序块中的执行段,而不是像显式游标那样在声 明段定义。 2、查询语句包含了 INTO 子句。INTO子句是 PL/SQL 语言的一部分,主要 用来把从数据库而来的数据传递给本地 PL/SQL 数据结构。 3、你不必打开、获取或关闭 SELECT 语句,所有这些操作,Oracle 都自 动地为你做了。 下面是一个普遍使用的隐式查询结构: SELECTSELECTSELECTSELECT column_listcolumn_listcolumn_listcolumn_list [BULK[BULK[BULK[BULKCOLLECT]COLLECT]COLLECT]COLLECT]INTOINTOINTOINTOPL/SQLPL/SQLPL/SQLPL/SQL variablevariablevariablevariable listlistlistlist ...rest...rest...rest...rest ofofofof SELECTSELECTSELECTSELECT statement...statement...statement...statement... 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 如果你使用一个隐式游标,Oracle 将会自动地为你执行打开、提取和关闭 等动作,这些动作不属于你程序控制范围之内。但是,通过检查隐式游标的属性, 你能够获取最近一条执行了的 SQL 语句的信息,这在后面将有详述。 3.3.2.1 隐式游标的范例 一个最简单的例子就是查找特定的字段或记录,如下所示: 例1:取 EMP 表中的雇员名 例2:取 EMP 表中的一条记录 除了上面两个例子,你还可以在隐式游标中返回更加复杂的数据结构,比如 说对象和集合。 3.3.2.2 隐式游标的错误处理 SELECT 语句的隐式游标就像一种黑盒子。你输入 SQL 语句到数据库中的 SQL 引擎,然后它返回一条信息。你不能获取里面的各个单独步骤地信息。但 是你也会被 Oracle 自动触发的异常所纠缠。 如果一个查询不能返回符合你标准的记录,那么 Oracle 就会触发一个 NO_DATA_FOUND 的异常,这也是最常见的异常。 如果SELECT 语句返回了多于一条的记录,Oracle 也会触发 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com TOO_MANY_ROWS 异常。 当这些异常情景中的任何一个发生,当前程序块的执行就会被中止,程序控 制就会交给异常处理模块。所以,任何时候你使用了隐式游标,安全起见,你都 要把这些异常(至少上述两种)包含到错误处理模块中来。 在下面这个例子中,我根据雇员的编号来查询雇员名成,并且捕捉可能出现 的异常,如果数据完整性被破坏,程序就会触发异常,到上一层的处理程序中去: 一般来说,你应该在使用了隐式游标的情况下,一定要在异常处理块中至少 捕捉NO_DATA_FOUND 和 TOO_MANY_ROWS 两种异常。因为,我们不知 道数据结构在未来是否有变化,为了保证程序健壮性,所以必须对有可能出现的 异常进行捕捉。此外,对于不同的错误可进行不同的处理。NO_DATA_FOUND 和 TOO_MANY_ROWS 两种错误就应有不同的处理方式,可根据不同的应用要 求进行处理。 3.3.2.3 隐式游标属性 隐式游标的属性和普通游标属性类似(详见前述),需要注意的是,如果你 的会话没有执行 SQL DML 语句,那么所有的游标属性就会返回空值(NULL)。 但是,如果隐式游标有数值,那么这些数值是与最近执行的 SQL DML 语句所关 联,而与你程序的模块或是逻辑控制无关。 下面的这个例子能让我们更加清楚隐式游标的这种特性: create or replace procedure remove_from_emp(i_empNo in 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com emp.empNo%TYPE) is begin Delete from emp t where t.empno = i_empNo; end remove_from_emp; create or replace procedure show_emp_count is l_count integer; begin select count(*) into l_count from emp; remove_from_emp(0000); --没有编号为 0000 的员工 dbms_output.put_line(SQL%ROWCOUNT); end show_emp_count; 我们会发现,不论有 EMP 表中有多少条记录,程序的返回值都是 0。这是 因为根本就没有编号为“0000”的员工记录。我们把程序改一下,把 SELECT 语句修改成 UPDATE 语句。如下所示: create or replace procedure upd_emp_rec is begin UPDATE emp set deptno=0 where deptno=20; dbms_output.put_line(SQL%ROWCOUNT); ROLLBACK; end show_emp_count; 输出结果为 5,表示有 5行记录被更新。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 3.3.3 显式游标 一个显式游标就是在你代码的声明段中,显式地定义一个 SELECT 语句, 并且给这个 SELECT 语句赋予名称。显然,对于其他的 DML 语句,如:UPDATE、 DELETE 和INSERT 语句,是不存在游标定义的。 使用显式游标,你可以具体地掌控 PL/SQL 中检索数据的各个步骤。你可以 决定什么时候打开游标,提取数据以及关闭游标。你还可以决定要提取多少条记 录,对提取出来的游标进行再加工等等。显式游标的控制粒度对于开发人员来说 是一个非常有价值的工具。 让我们来看看一个例子。下面是一个函数,它可以计算在某个部门内某个员 工的工资排名: 1 create or replace function Find_Sal_Rank(i_empNo in number) return number is 2 Result number(3); 3 l_emprec emp%rowtype; 4 Cursor rank_cur is 5 select * from emp t where t.empno = i_empNo; 6 begin 7 open rank_cur; 8 fetch rank_cur into l_emprec; 9 if rank_cur%notfound then 10 dbms_output.put_line('emp not found!'); 11 return -1; 12 end if; 13 select rank(l_emprec.sal) within group(order by sal desc) 14 into Result 15 from emp t where t.deptno=l_emprec.deptno; 16 close rank_cur; 17 return(Result); 18 end Find_Sal_Rank; 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 输出结果为: 有关游标的操作与行数的对应关系如下所示: 行号 游标操作说明 4-5 声明一个游标。 7 打开游标。 8 从游标中提取记录。 9 检查游标属性,进行异常处理。 13-15 根据游标提取出来的数据,计算该名员工的工资排名。 16 关闭游标。 3.3.3.1 声明游标 声明游标的语法如下所示: CURSORCURSORCURSORCURSOR cursor_namecursor_namecursor_namecursor_name [[[[(((([[[[ parameterparameterparameterparameter [,[,[,[, parameterparameterparameterparameter ...]...]...]...]))))]]]] [[[[RETURNRETURNRETURNRETURN return_specificationreturn_specificationreturn_specificationreturn_specification ]]]] ISISISIS SELECT_statementSELECT_statementSELECT_statementSELECT_statement [FOR[FOR[FOR[FORUPDATEUPDATEUPDATEUPDATE[OF[OF[OF[OF[column[column[column[column list]];list]];list]];list]]; 在这里,cursor_name 使游标的名称,return_specification 规定了游标返回 的数据类型(返回项可选),而SELECT_statement 是任意一个有效的 SQL SELECT 语句。你可以给游标传递参数,这在后面的章节中有详述。一旦你声 明了一个游标,你就可以打开或关闭它。 下面是一些简单的例子: 1、无参数的游标,检索公司的 ID CURSOR company_cur IS SELECT company_id FROM company; 2、有参数的游标,根据输入参数检取公司 ID CURSOR name_cur (company_id_in INNUMBER) IS 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com SELECT name FROM company WHERE company_id = company_id_in; 3、带返回子句的游标 CURSOR emp_cur RETURN employee%ROWTYPE IS SELECT*FROM employee WHERE department_id = 10; 3.3.3.2 命名游标 一个显式游标的名字可以为 30 个字符长的符合 PL/SQL 标志符规范的字符 串。我们要注意,一个游标的名字其实并不是一个变量,它只是一个未声明的标 志符,用来引用或指向一个查询结果集。你不能给游标赋值,或者在表达式中使 用游标。你只能对游标使用 OPEN,FETCH 和CLOSE 操作,以及引用它特有 的一些游标属性。 3.3.3.3 打开显式游标 使用游标的第一步就是在声明段定义一个游标。接下来的一步就是打开这个 游标。OPEN 语句的语法如下: OPENOPENOPENOPEN cursor_namecursor_namecursor_namecursor_name [[[[(((( argumentargumentargumentargument [,[,[,[, argumentargumentargumentargument ...]...]...]...]))))];];];]; 不论你是何时取出第一条记录,在 Oracle RDBMS 的读取一致性模型中, 所获取的记录在打开游标时就已经确定下来了。换句话说,从你打开游标到你关 闭游标的这段时间中,所有提取的数据将会忽略游标打开之后的插入、更新和删 除操作。 更进一步,如果你在游标声明中的 SELECT 语句中使用了“FORUPDATE” 关键字,那么所被选中的记录都会在游标的打开期间被锁定。 如果你试图打开一个已经打开了的游标,则你会得到一个如下的错误: ORA-06511:ORA-06511:ORA-06511:ORA-06511: PL/SQL:PL/SQL:PL/SQL:PL/SQL: cursorcursorcursorcursor alreadyalreadyalreadyalready openopenopenopen 在你试图打开一个游标之前,你可以使用游标的%ISOPEN 属性来检查该游 标是否被打开。如下代码所示: IFIFIFIFNOTNOTNOTNOT company_cur%ISOPENcompany_cur%ISOPENcompany_cur%ISOPENcompany_cur%ISOPEN 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com THENTHENTHENTHEN OPENOPENOPENOPEN company_cur;company_cur;company_cur;company_cur; ENDENDENDENDIF;IF;IF;IF; 在下一节“显式游标的属性”中,将会阐述不同的属性,并且如何在程序中 使用它们。 注意:如果你使用了 OPENFOR LOOP 形式的循环结构,你就不用专门去 打开游标了,Oracle 的PL/SQL 引擎自动完成。 3.3.3.4 从显式游标中提取数据 在程序中,游标其实可以看成是一张“虚拟”的表,里面包含了 SELECT 子句检索出来的数据。几乎在所有的程序中,定义并且打开一个游标的目的就是 为了对游标进行数据提取,操纵等工作。 PL/SQL provides a FETCH statement for this action.。The general syntax for a FETCH is: PL/SQL 提供了 FETCH 语句来提取数据。FETCH 语句的一般语法形式如 下: FETCHFETCHFETCHFETCH cursor_namecursor_namecursor_namecursor_name INTOINTOINTOINTO record_or_variable_list;record_or_variable_list;record_or_variable_list;record_or_variable_list; 在这里 cursor_name cursor_name cursor_name cursor_name 是指提取数据游标的名字,record_or_variable_lisrecord_or_variable_lisrecord_or_variable_lisrecord_or_variable_listttt 是Oracle 数据结构(列表)——它们用来存储提取出来的数据。你可以把提取 数据赋予一个或多个变量。 1.1.1.1. 显式游标的范例 以下的几个例子示范了几种数据提取的方式: 提取数据到一个记录中: DECLAREDECLAREDECLAREDECLARE CURSORCURSORCURSORCURSOR company_curcompany_curcompany_curcompany_cur isisisis SELECTSELECTSELECTSELECT...;...;...;...; company_reccompany_reccompany_reccompany_rec company_cur%ROWTYPE;company_cur%ROWTYPE;company_cur%ROWTYPE;company_cur%ROWTYPE; BEGINBEGINBEGINBEGIN OPENOPENOPENOPEN company_cur;company_cur;company_cur;company_cur; FETCHFETCHFETCHFETCH company_curcompany_curcompany_curcompany_cur INTOINTOINTOINTO company_rec;company_rec;company_rec;company_rec; 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 提取数据到一个变量中去: FETCHFETCHFETCHFETCH new_balance_curnew_balance_curnew_balance_curnew_balance_cur INTOINTOINTOINTO new_balance_dollars;new_balance_dollars;new_balance_dollars;new_balance_dollars; 注意:在编程中,应尽量使用记录(Record)类型的变量,与提取数据到 多个变量的方式相比,使用记录变量能够让代码更简洁些。 2.2.2.2. 超量提取 当你把游标中所有的数据都提取出来之后,继续提取,这个时候,PL/SQL 不会抛出任何异常。它仅仅是什么都不做。因为已经没有数据可供提取,所以 FETCH 语句不再会改变 INTO子句里面变量的值,也不会把这些变量设为空值 (NULL)。 对于超量提取的情况,我们推荐使用测试%FOUND 或者 %NOTFOUND 属性的方法。 3.3.3.5 在显式游标中使用列匿名 在某些情况下,我们为了程序的可读性,在SELECT 语句中使用了列匿名, 如下所示: SELECTSELECTSELECTSELECT company_name,company_name,company_name,company_name, SUMSUMSUMSUM(inv_amt)(inv_amt)(inv_amt)(inv_amt) total_salestotal_salestotal_salestotal_sales FROMFROMFROMFROM companycompanycompanycompany C,C,C,C, invoiceinvoiceinvoiceinvoice IIII WHEREWHEREWHEREWHERE C.company_idC.company_idC.company_idC.company_id ==== I.company_idI.company_idI.company_idI.company_id ANDANDANDAND I.invoice_dateI.invoice_dateI.invoice_dateI.invoice_date BETWEENBETWEENBETWEENBETWEEN'01-JAN-2001''01-JAN-2001''01-JAN-2001''01-JAN-2001' ANDANDANDAND '31-DEC-2001';'31-DEC-2001';'31-DEC-2001';'31-DEC-2001'; 以上语句的 total_sales 就是一个列匿名,它表示了 inv_amt 列的总和。 在显示游标中使用列匿名与对于正常的列是一样的,如下范例所示: 在EMP 表根据员工的部门编号分组,显示每个部门的员工工资总和。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 注意:引用游标的属性%NOTFOUND 一般是在 FETCH 语句之后,因为只 有在 FETCH 之后,游标的属性才会发生变化。 3.3.3.6 关闭显式游标 当使用游标时,一定要确认在使用完毕之后关闭了游标以节省系统开销。以 下是 CLOSE 命令的语法: CLOSECLOSECLOSECLOSE cursor_name;cursor_name;cursor_name;cursor_name; 这里 cursor_name cursor_name cursor_name cursor_name 就是要关闭的游标的名字。 下面有一些有关关闭显式游标特别需要考虑的地方: 如果你在程序中打开了一个游标,当你使用完毕之后一定要确定将其关闭。 不然的话,你可能会引起一个内存泄露。严格来说,一个游标(有点像一种另类 的数据结构)应该在其使用范围结束后能自动关闭或被销毁。事实上,在许多情 况下 PL/SQL 在过程、函数或匿名块调用结束时,检查并暗中关闭了任何打开了 的游标。但是,在某些情况下,由于效率的关系,PL/SQL 并不会立即检查和关 闭在 PL/SQL 块中已经打开的游标。再如,REF CURSOR 类型就是被设计用来 不能隐式自动关闭的。 OTN提供了一些关于 PL/SQL 关闭游标的细节分析,大家可以上网学习, 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 这里就不赘述了。 如果你在包级别中声明了一个游标,然后在某个程序或块中打开了它,那么 这个游标就会一直保持打开状态,直到你明确地关闭了它又或者直到你的回话结 束。因此,对于任何包级别的游标,你一定要在使用完了它们之后及时地显式地 关闭,如下所示: BEGINBEGINBEGINBEGIN OPENOPENOPENOPEN my_package.my_cursor;my_package.my_cursor;my_package.my_cursor;my_package.my_cursor; ............ DoDoDoDo stuffstuffstuffstuff withwithwithwith thethethethe cursorcursorcursorcursor CLOSECLOSECLOSECLOSE my_package.my_cursor;my_package.my_cursor;my_package.my_cursor;my_package.my_cursor; EXCEPTIONEXCEPTIONEXCEPTIONEXCEPTION WHENWHENWHENWHENOTHERSOTHERSOTHERSOTHERS THENTHENTHENTHEN CLOSECLOSECLOSECLOSE my_package.my_cursor;my_package.my_cursor;my_package.my_cursor;my_package.my_cursor; END;END;END;END; 如果你打开了一个 SELECTFOR UPDATE 查询,则你一定要在使用结束之 后立即关闭它,因为这类查询会造成行级别锁。 在关闭游标之前,你可以通过检查游标的%ISOPEN 属性避免发生异常,如 下所示: IFIFIFIF company_cur%ISOPENcompany_cur%ISOPENcompany_cur%ISOPENcompany_cur%ISOPEN THENTHENTHENTHEN CLOSECLOSECLOSECLOSE company_cur;company_cur;company_cur;company_cur; ENDENDENDENDIF;IF;IF;IF; 如果你遗漏了太多的游标处于打开状态,你就可能超出数据库所设置的初始 化参数——OPEN_CURSOR。如果这种情况发生,你就会遇到一个“可怕”的 错误信息: ORA-01000:ORA-01000:ORA-01000:ORA-01000: maximummaximummaximummaximum openopenopenopen cursorscursorscursorscursors exceededexceededexceededexceeded 如果你遇到这种情况,你就的检查你的基于包级别的游标使用,从而确定它 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 们在使用结束之后已经关闭。 3.3.3.7 显式游标的属性 Oracle 给游标赋予了四种属性,你可以通过如下形式来引用它们: cursor%attributecursor%attributecursor%attributecursor%attribute 下表是游标属性的返回值: 名字 描述 cursor%FOUND 如果记录被成功取出,则返回 TRUE。 cursor%NOTFOUND 如果记录没有被成功取出,则返回 TRUE。 cursor%ROWCOUNT 返回在这个时刻从游标中提取的记录数目 cursor%ISOPEN 如果游标已经打开就返回 TRUE 以下是一些关于使用游标属性的建议: 1、如果你试图在游标打开或关闭之前使用%FOUND,%NOTFOUND 或 %ROWCOUNT 属性,Oracle 会抛出一个 INVALID_CURSORINVALID_CURSORINVALID_CURSORINVALID_CURSOR errorerrorerrorerror (ORA-01001)(ORA-01001)(ORA-01001)(ORA-01001)异常; 2、如果在第一次提取(FETCH)之后,属性将会返回:%FOUND = FALSE, %NOTFOUND = TRUE和%ROWCOUNT = 0; 3、如果你使用了批量集合(BULKCOLLECT),则从%ROWCOUNT 属性 中取出的值就可能不是 1或0,事实上,它返回提取到相应集合变量中的记录数。 要想了解更多的内容,可参看下一小节批量集合。 下面我们看一下在一个程序包中如何使用游标的属性: --定义了一个包,其中包含一个游标 create or replace package empinfo is --定义游标,取出部门编号为 20 的员工信息 CURSOR emp_cur is select t.empno,t.ename,t.job,t.hiredate from emp t where t.deptno=20; end empinfo; 下面在 SQL*Plus 中的匿名块中使用这个游标: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 3.3.3.8 游标的参数 PL/SQL 允许你把参数传递给游标。使用原理和过程以及函数差不多。使用 带参数游标的好处是什么呢?有以下两点: 1、增加了游标的重用性。 一般来说,在游标定义中的 Where 子句中,如果把条件写死,则非常不利 于代码的重用,增加了工作量。使用参数的话,可以灵活在每次打开游标时定义 不同的条件。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 2、避免了作用域的问题。 如果你使用游标参数,那么游标所返回的结果就不再绑定于某个程序或块的 变量。如果你的程序含有“内嵌块”(一般都有),则你可以在高级别的块里定义 参数游标,然后在子程序块中使用,在使用时就可以与当前块的变量相结合。 你可以按你所需设定相应数目的游标参数。当你打开游标(OPEN),你必 须为每一个参数指定一个变量,除了有默认值的后缀参数。 什么时候你应该给游标加上参数呢?对于过程和函数来说,有以下一些建 议。如果我要在不同的地方使用相同的游标,而且每个地方都要有特定 的 WHERE 条件,则就应该使用一个参数游标。 下面我们就来看一看游标参数的例子: 范例 在一个过程里面定义一个带参数的游标,如下所示: 执行结果: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 事实上,我们在使用带参数的游标时,可以使用多种打开方式: 如: OPEN emp_cur(20); OPEN emp_cur(20+n);--这里 n是一个 number 型变量 OPEN emp_cur(nvl(i_deptno,0)); …… 游标参数的作用域 游标参数的作用于被限制在游标的作用域中。你不能在与游标相关联的 SELECT 子句之外指定游标参数。下面的 PL/SQL 代码片断的错误就在于游标 参数标志符:program_name 并不是程序块中的本地变量。相反,它是游标的正 规参数,只能在游标中被定义。 DECLARE CURSOR scariness_cur (program_name VARCHAR2) IS SELECTSUM(scary_level) total_scary_level FROM tales_from_the_crypt WHERE prog_name = program_name; BEGIN program_nameprogram_nameprogram_nameprogram_name :=:=:=:= 'THE'THE'THE'THEBREATHINGBREATHINGBREATHINGBREATHINGMUMMY';MUMMY';MUMMY';MUMMY'; /* 错误的引用 */ 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com OPEN scariness_cur (program_name); END; 游标参数的模式 对于游标参数来说,它的语法和过程、函数的参数定义相类似,不同的是, 游标参数只能是输入型的。你不能把游标参数设置为 OUT或INOUT类型。游 标不能通过参数返回检索出来的数值。 游标参数的默认值 以下是一个设置游标参数默认值的范例: CURSOR emp_cur (emp_id_in NUMBER:= 0) IS SELECT employee_id, emp_name FROM employee WHERE employee_id = emp_id_in; 如果 Joe Smith 的雇员 ID 是1001,那么以下的代码就会把游标参数设置为 1001,同时,my_emp_name 也取值为:JOESMITH OPEN emp_cur (1001); FETCH emp_cur INTO my_emp_id, my_emp_name; 因为参数 emp_id_in 本身有一个默认值,所以我也可以不给游标定义参数九 打开并提取记录,这是,游标参数使用的就是默认值。 3.3.4 批量集合(BULKBULKBULKBULKCOLLECTCOLLECTCOLLECTCOLLECT) 从Oracle8i 开始,就引入了一个非常有用的特性,大大提高了查询的效率, 它就是:BULK COLLECT 子句。 使用 BULKCOLLECT,你可以通过一个显式或隐式游标,只需循环一次就 能从数据库中取出多行记录。BULK COLLECT 减少了 PL/SQL 和SQL 引擎之 间的上下文交换,因此减少了检索数据的开销。 特别是对于某些时候,我们需要使用游标一条记录、一条记录地处理,就可 能使用了多次循环。在这种情况,我们就可以使用批量集合,一次性地把记录取 出,放入一个集合变量中,然后再对该变量进行处理,减少系统的开销。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 下面我们来看两个范例,一个是使用 BULK COLLECT 的,一个没有: 范例 1、使用多次循环来检索并显示数据。 范例 2、一次性获取数据,然后再循环显示。 执行: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 下面是一些有关 BULK COLLECT 的建议: 1、从Oracle9i 开始,你都可以在静态 SQL 和动态SQL 中使用BULK COLLECT。 2、你可以在任何地方的 SELECTINTO,FETCHINTO,和RETURNING INTO 子句中使用 BULK COLLECT 关键字。 3、在Oracle9i 以前,你引用的集合变量只能够存储标量值(字符串、数字 和日期)。换句话说,你不能提取一条记录存放到一个记录型的数据结构中(这 个数据结构是一个集合变量中的一行记录)。 4、SQL 引擎会自动地初始化和扩展你在 BULK COLLECT 子句中引用的集 合变量。它会从索引 1开始,连续地把记录填充进集合中,并且会重写任何以前 定义了的数据。 5、你不能在一个 FORALL 语句中使用 SELECT...BULK COLLECT 语句。 6、如果找不到任何记录,SELECT...BULK COLLECT 也不会产生 NO_DATA_FOUND 异常。相应地,你必须检查集合的内容,以察看里面是否有 数据。 7、在执行查询以前,BULK COLLECT 操作会清空在 INTO 子句中引用的 集合。如果查询没有返回任何记录,这个集合的 COUNT方法就会返回 0。 如果对于 Oracle9i 或以上的版本,我们把上面的例子写得更简单一些: create or replace procedure Bulk_Demo2(i_deptno in number) is type t_tbl1 is table of emp%ROWTYPE index by binary_integer; emp_info1 t_tbl1; type emp_rec is record( empno emp.empno%type, 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com ename emp.ename%type); type t_tbl2 is table of emp_rec index by binary_integer; emp_info2 t_tbl2; begin Select t.* BULKCOLLECT INTO emp_info1 From emp t Where t.deptno = i_deptno; Dbms_Output.put_line('------批量存放整行记录类型-------'); For i in 1 .. emp_info1.count Loop Dbms_Output.put_line(emp_info1(i).empno || '' || emp_info1(i) .ename || '' || emp_info1(i) .job || '' || emp_info1(i) .sal || '' || emp_info1(i).hiredate); End Loop; Dbms_Output.put_line('+++++++++++++++++++++++++++++++++++++++'); Dbms_Output.put_line('------批量存放自定义记录类型-------'); select t.empno, t.ename BULKCOLLECT INTO emp_info2 From emp t Where t.deptno = i_deptno; For i in 1 .. emp_info2.count Loop Dbms_Output.put_line(emp_info2(i).empno ||' '|| emp_info2(i).ename); End Loop; Dbms_Output.put_line('++++++++++++++++++++++++++++++++++++++'); end BULK_demo2; 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 运行结果: 3.3.4.1 限制 BULKBULKBULKBULK COLLECT COLLECT COLLECT COLLECT 提取的记录数目 Oracle 对于BULK COLLECT 提供了一个 LIMIT 关键字来让你限制从数据 库检索出来的记录数目。语法如下: FETCHFETCHFETCHFETCH cursorcursorcursorcursor BULKBULKBULKBULKCOLLECTCOLLECTCOLLECTCOLLECTINTOINTOINTOINTO............[LIMIT[LIMIT[LIMIT[LIMIT rows];rows];rows];rows]; 这里的 rows 参数可以是一个字面值,变量或者是表达式——都指向一个整 数(不然的话,Oracle 将会给出一个 VALUE_ERROR 的异常信息)。 LIMIT 对于 BULK COLLECT 来说是非常有用的。它帮助你清楚地了解并管 理程序所用到过程数据。比如说,你要精确地查询并处理 1000 条记录。你可以 使用 BULK COLLECT 来取出这些记录,并且残生了一个相对较大的集合。但是 这种方法会消耗许多 PGA(全局进程区)内存。如果这个代码是被很多独立的 会话使用,那么你的应用就会因为 PGA 频繁的页面交换而效率低下。 下面的这段代码(在 Oracle9i 的 HR 模式下),在 FETCH 语句中使用了 LIMIT 语句。请注意,这里使用了集合的 COUNT方法来确认是否有数据被取出。而 %FOUND 和%NOTFOUND 属性在这里就不能用来检测是否有数据被取出。 create or replace procedure LimitDemo is CURSOR allrows_cur IS SELECT employee_id FROM employees; TYPE employee_aat ISTABLEOF 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com employees.employee_id%Type INDEXBYBINARY_INTEGER; l_employees employee_aat; begin OPEN allrows_cur; Loop EXITWHEN allrows_cur%NOTFOUND; FETCH allrows_cur BULKCOLLECT INTO l_employees LIMIT 100; Dbms_Output.put_line(l_employees.count); end loop; close allrows_cur; end LimitDemo; 获得结果是: 3.3.5 使用 SELECTSELECTSELECTSELECT............FORFORFORFORUPDATEUPDATEUPDATEUPDATE 当你使用一般的 Select 语句来查询数据库中的数据时,被选中的行并不会 加锁。一般而言,你所读取到的记录都是在更新(或其他 DML 语句)没有提交 之前的状态。但是,有的时候,你希望在你改变它们之前就能够锁住它们(而不 是在执行变更时加锁)。Oracle 为SELECT 提供了 FOR UPDATE 子句来实现这 个功能。 当你执行了一条 SELECT...FOR UPDATE 语句,Oracle 就会自动地给所有 被选中的记录加上排他的行级别锁,使这些记录只能“因你而改变”。别的用户 是不能对这些记录执行任何变更,直到你执行了一条 ROLLBACK 或COMMIT 语句。 以下是两个实例(在 HR模式下),显示了在游标中使用 FOR UPDATE 子 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 句的方法。 CURSOR emp_cur1 IS SELECTEMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL FROM employees WHEREEMPLOYEE_ID <200 FORUPDATE; CURSOR emp_cur2 IS SELECTEMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL FROM employees WHEREEMPLOYEE_ID <200 FORUPDATEOFEMPLOYEE_IDEMPLOYEE_IDEMPLOYEE_IDEMPLOYEE_ID; 第一个游标使用了无标志的 FOR UPDATE 子句,而第二个游标使用了查询 里面的一个列名(EMPLOYEE_ID)来标志 FOR UPDATE 子句。 你也可以在 SELECT 中,针对多张表来使用 FOR UPDATE 子句。在这种情况 下,只有在 FOR UPDATE 子句中引用到的列的所属表,其里面被选中的纪录才 会被锁定。在下面的例子中(HR模式下),FOR UPDATE 子句就不会在 jobs 表中产生任何锁。 Select e.employee_id,e.first_name,e.last_name From employees e,jobs j Where e.job_id=j.job_id and j.job_title='Programmer' FORFORFORFORUPDATEUPDATEUPDATEUPDATEOFOFOFOF e.employee_id 我们用 DBA 用户登录到 Oracle,察看被锁住的对象的情况: SELECT a.owner,t.LOCKED_MODE,a.object_name,s.SID From v_$locked_object t,v$session s, all_objects a WHERE t.SESSION_ID=s.SID AND t.OBJECT_ID=a.object_id 得到: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 在FOR UPDATE 子句中仅仅提及了 employee_id 列,在查询列表中,没有 jobs 表的列。 在FOR UPDATE 子句中的 OF关键字后面跟着的列,并不局限于你所要查 询的列。但是,系统依然会对所有被选中的行加锁;而OF列表仅仅是给你一条 途径来更清晰地纪录你要变更的东西。如果你仅仅是使用了 FORUPDATE 子句, 而没有在 OF关键字后面跟上列名,那么数据库就会锁住所有 FROM 关键字后 面表里的被选中的数据行。 此外,你也不必一定要在 SELECT...FORUPDATE 语句之后执行 UPDATE 或DELETE 语句。 最后,你还可以给 FOR UPDATE 子句扩展 NOWAIT 关键字,告诉 Oracle ——如果表被其他用户锁住的话,就不必等待,而是立即把控制权转移到你的程 序上,从而你就可以执行其他的工作或者仅仅简单地等待一段时间。如果没有 NOWAIT 关键字,你的进程将会被阻塞,直到数据表可用。除非数据表是在远 程数据库上,否则等待时间是没有限制的。Oracle 的初始化参数: DISTRIBUTED_LOCK_TIMEOUT 就是被用来设置这个参数的。 3.3.5.1 使用 COMMIT COMMIT COMMIT COMMIT 释放锁 一旦一个使用了 FOR UPDATE 子句的游标被打开,那么所有被选中的纪录 都会被锁住,直到你的会话结束,或者执行了一个 COMMIT 语句来保存所有数 据变更的结果,又或者执行一个 ROLLBACK 语句来取消所有的数据变更。所以, 在你执行了 COMMIT 或ROLLBACK 之后,你就再不能对使用了 FORUPDATE 的游标进行 FETCH 操作。 考虑以下的范例: DECLARE /*把所有工资小于 1000 的都提到 1000*/ CURSOR all_sal_cur IS SELECT empno,sal FROM emp FORUPDATEOF empno; BEGIN 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com FOR sal_rec IN all_sal_cur LOOP IF sal_rec.sal < 1000 THEN UPDATE emp SET sal=1000 WHERE empno = sal_rec.empno; COMMIT; ENDIF; ENDLOOP; END; 得到的结果为: Suppose this loop finds its first YOUCANDOIT job. It then commits an assignment of a job to STEVEN. When it tries to FETCH the next record, the program raises the following exception: 假设上面的 LOOP 循环,取出了一条纪录(其中的 sal 字段小于 1000), 那么程序就会进行数据更新,然后 commit。但是,在下一次循环中,如果程序 又试图去读取游标的下一条纪录,系统就会报以下错误: ORA-01002:ORA-01002:ORA-01002:ORA-01002: fetchfetchfetchfetch outoutoutout ofofofof sequencesequencesequencesequence (读取违反顺序)。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 如果你试图从一个使用了 SELECTFOR UPDATE 的游标中提取数据,并且 提交或回滚事务,那么你就应在这些代码中,防止从游标中再次读取纪录。 3.3.5.2 WHEREWHEREWHEREWHERECURRENTCURRENTCURRENTCURRENTOFOFOFOF 子句 PL/SQL 为游标里的 UPDATE 和 DELETE 命令提供了 WHERECURRENT OF子句。这个子句允许你很容易地对最新读取的数据进行操作。 更新最近读取出来的记录: UPDATE table_name SET set_clause WHERECURRENTOF cursor_name; 从数据库中删除最近读取出来的记录: DELETE FROM table_name WHERECURRENTOF cursor_name; 需要注意的是 WHERECURRENTOF子句引用的是游标,而不是下一条存储下 一条数据行的记录。(源码网整理,www.codepub.com) 使用 WHERECURRENTOF子句的最大好处就是,你不必在查找纪录所需 条件的两个地方写代码了。如果没有 WHERECURRENTOF,你就重复使用游 标中的 WHERE 子句来定位需要操作的记录。这样的结果就是,如果以后数据 表结构发生了变化,你就得重写几乎所有的 SQL 代码来支持这种变更。如果你 使用了 WHERECURRENTOF子句,那么你就仅仅需要改写 SELECT 命令中 的WHERE 子句。 这个看起来似乎是一个微不足道的优势,但是它可以在你程序中的许多地方 起作用。使用 WHERECURRENTOF子句,%TYPE 与%ROWTYPE 声明属性, 游标LOOP 循环,本地模块化和其他 PL/SQL 结构可以很大程度上地减少数据 库程序的维护成本。 让我们来看看这个子句是如何作用的。 在下面这个例子中,我读取纪录中的工资,如果工资小于 1000 的,那么就 增加到 1000。有一种很普遍的写法是在 UPDATE 子句中,根据游标记录里的 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com empno 来重复写 WHERE 子句,如下: WHEREWHEREWHEREWHERE empno=emp_rec.empno;empno=emp_rec.empno;empno=emp_rec.empno;empno=emp_rec.empno; 如果使用了 WHERECURRENTOF子句的话,就可以不用写这些和表结构 相关的代码。 下面是使用了 WHERECURRENTOF子句的另一个版本: DECLARE /*把所有工资小于 1000 的都提到 1000*/ CURSOR all_sal_cur IS SELECT empno,sal FROM emp FORUPDATEOF empno; BEGIN FOR sal_rec IN all_sal_cur LOOP IF sal_rec.sal < 1000 THEN UPDATE emp SET sal=1000 WHERECURRENTOF all_sal_cur; ENDIF; ENDLOOP; COMMIT; END; 得到的结果为: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 3.4 游标变量和 REFREFREFREF CURSOR CURSOR CURSOR CURSOR 类型 一个游标变量就是指向或者引用一个潜在游标的变量,也就是说,游标变量 就是对 PL/SQL 工作区的引用。显式或隐式游标都是静态的——与特定的查询绑 定。而游标变量却能够为任何查询打开,甚至在同一个程序的执行过程中。 使用游标变量最大的好处在于,它提供了一个不同 PL/SQL 程序之间传递查 询结果集(results of queries)的方法,这种传递也可以在客户端的 PL/SQL 程 序和服务器端程序之间进行。 它同时也意味着:不同程序可以有效地共享游标。比如,在 CS环境下,一 个客户端程序能够打开和从游标变量中读取数据,并且能够把这个变量作为一个 参数传递给服务端的存储过程。这个存储过程就能够继续读取数据并把控制权返 回给客户端程序,使之能够关闭游标。你也可以在不同的存储过程或不同的数据 库之间执行同样的动作。 这个过程,如下图所示,为PL/SQL 程序管理和共享游标数据提供了巨大的 可能性: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 在不同程序中引用游标变量 3.4.1 为什么使用游标变量 游标变量允许你做以下的工作: 在程序的执行阶段,你可以让游标在不同的时候与不同的查询联系起来。换 句话说,一个游标变量能够从不同的结果集中获取数据。 把游标变量作为一个参数传递给过程或者函数。你能够,从本质上说,通过 传递结果集的引用从而共享一个游标的查询数据。 对于游标变量,和静态游标一样,你可以在程序中打开、关闭和读取游标指 向的数据。你也能够对游标变量引用游标属性:%ISOPEN、%FOUND、 %NOTFOUND 和%ROWCOUNT。 你也可以把一个游标的内容赋给另外一个游标变量。因为游标变量是一个变 量,所以它能够在赋值操作中使用。但是,对于这一类的变量引用是有限制的。 这会在下面的章节中阐述。 3.4.2 与静态游标的共同点 对于游标变量设计的一个关键之处就是:任何可能的时候,对游标对象的管 理命令应该和管理静态游标的一样。除了声明和打开游标变量的语法有所提升之 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 外,其他对于游标变量的操作和静态变量是一样的。 CLOSE CLOSE CLOSE CLOSE 语句 在下面的例子中,我声明了一个引用游标类型和一个基于此类型的游标变 量。然后我使用与静态游标相同的语法来关闭这个游标变量。 DECLARE TYPE var_cur_type ISREFCURSOR; var_cur var_cur_type; BEGIN CLOSE var_cur; END; 游标的属性 你能够使用以下的四种游标属性,用法和静态游标完全一致。如果我声明了 一个游标变量(就像上一个例子),然后我可以对这个游标变量使用游标属性, 如下所示; var_cur%ISOPEN var_cur%FOUND var_cur%NOTFOUND var_cur%ROWCOUNT 从游标变量中提取数据 但从一个游标变量提取数据到 PL/SQL 的数据结构时,你可以使用 FETCH 语句。但是,PL/SQL 也对游标变量的数据检取设置了额外的规范——也就是游 标对象返回的数据类型必须和 INTO关键字右边的变量的数据类型保持一致。 因为游标变量的大部分特性都和显式游标相同,所以下面的大部分内容将集 中在游标变量特有的属性上。 3.4.3 声明引用游标类型 就像定义一个 PL/SQL 表或一个基于程序定义的记录一样,你必须进行两种 完全不同的声明步骤来实现一个游标变量的创建: 创建一个引用游标变量类型 根据这个类型创建实际的游标变量。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 创建一个被引用的游标类型的语法如下: TYPETYPETYPETYPE cursor_type_namecursor_type_namecursor_type_namecursor_type_name ISISISISREFREFREFREFCURSORCURSORCURSORCURSOR[[[[RETURNRETURNRETURNRETURN return_typereturn_typereturn_typereturn_type ];];];]; 在这里,cursor_type_name 是这个游标类型的名字,return_type 是这种游 标返回的数据类型。return_type 对于一个普通游标的 RETURN 字句来说,可以 为任何有效的数据结构,也可以是使用了%ROWTYPE 属性定义的记录类型。 注意:RETURN 子句对于 REF CURSOR 类型来说是可选,所以下面两个 声明都是有效的: TYPETYPETYPETYPE company_curtypecompany_curtypecompany_curtypecompany_curtype ISISISISREFREFREFREFCURSORCURSORCURSORCURSORRETURNRETURNRETURNRETURN company%ROWTYPE;company%ROWTYPE;company%ROWTYPE;company%ROWTYPE; TYPETYPETYPETYPE generic_curtypegeneric_curtypegeneric_curtypegeneric_curtype ISISISISREFREFREFREFCURSOR;CURSOR;CURSOR;CURSOR; 第一种 REF CURSOR 的定义要强一些,因为它把一个记录类型(或数据行 类型)与游标变量类型在声明时就结合在了一起。任何属于这种类型的游标变量 都可以被 SQL 命令操作,并且从中检取与声明时已确定好的数据类型相匹配的 数据。使用强 REF TYPE 的优点在于,编译器可以知道开发人员在 FETCH 语 句中的数据结构是否与游标变量所指定的数据类型相吻合。 第二种类型是没有 RETURN 子句的,被称为弱类型。这类游标变量不会与 任何数据结构相关联。弱类型的游标变量与强类型相比,要灵活得多。它们可以 在任何查询中使用,可以与任何数据类型相关联,甚至在一个程序的范围内变更。 从Oracle9i 开始,Oracle 就提供了一种预定义的弱 REF CURSOR 类型, 称之为 SYS_REFCURSOR。你可以不再用定义自己的弱游标变量类型了,使 用Oracle 提供的就好了,如下所示: DECLAREDECLAREDECLAREDECLARE my_cursormy_cursormy_cursormy_cursor SYS_REFCURSOR;SYS_REFCURSOR;SYS_REFCURSOR;SYS_REFCURSOR; 3.4.4 声明游标变量 创建一个游标变量的语法如下: cursor_namecursor_namecursor_namecursor_name cursor_type_name;cursor_type_name;cursor_type_name;cursor_type_name; 在这里,cursor_name 就是这个游标的名字,而cursor_type_name 是在前 面代码中 TYPE 语句所定义的游标类型名。 以下是一个游标变量的创建例子: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com DECLARE /* Create a cursor type for sports cars. */ TYPE sports_car_cur_type ISREFCURSORRETURN car%ROWTYPE; /* Create a cursor variable for sports cars. */ sports_car_cur sports_car_cur_type; BEGIN ... END; 分清定义一个游标变量和创建一个确实存在的游标对象结果集(被游标的 SQL 语句所定义)之间的区别是很重要的。一个常量就是一个数值,而一个变 量就是指向或引用一个游标对象。这些区别如下图所示,注意在不同程序中的两 个不同的游标变量都指向同一个游标对象。 声明一个游标变量并没有创建一个游标对象。要想创建一个游标对象,你必 须使用 OPENFOR 语法来创建一个新的游标对象并且分配给这个变量。 3.4.5 打开游标变量 当你打开游标时,你就给这个游标分配了一个值(游标对象)。所以,对于 传统的 OPEN 语句来说,允许游标变量在 FOR 子句后面跟 SELECT 命令,如 下所示: OPENOPENOPENOPEN cursor_namecursor_namecursor_namecursor_name FORFORFORFOR select_statement;select_statement;select_statement;select_statement; 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 在这里 cursor_name 是一个游标或游标变量的名字,而 select_statement 是一个 SQL SELECT 语句。 对于强 REF CURSOR 类型的游标变量来说,SELECT 语句的结构(列或 字段集合)必须与声明游标变量时在 RETURN 子句中指定的数据类型相匹配。 如下所示: 如果游标变量是被定义成一个弱 REF CURSOR 类型,你就能够把任意一个 查询赋予它。如下所示:我打开了一个游标变量三次,每一次都使用了不同的查 询。 DECLARE TYPE emp_curtype ISREFCURSOR; emp_curvar emp_curtype; BEGIN OPEN emp_curvar FORSELECT*FROM emp; OPEN emp_curvar FORSELECT employee_id FROM emp; OPEN emp_curvar FORSELECT company_id, name FROM company; END; 最后一个 OPEN 语句甚至与 employee 表没有任何关系。 如果游标变量还没有被分配给任何一个游标对象,那么 OPEN FOR 语句就 会隐式地给这个变量创建一个游标对象。如果在打开游标变量的时候,这个变量 已经指向了一个游标对象,那么 OPEN FOR 语句就不会创建一个新的对象,相 反,它会重用已经存在的对象并且把新的查询赋予这个对象。游标对象本身是与 游标或查询分开存储的。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 3.4.6 从游标变量中检取数据 正如前面所描述的一样,对游标变量使用 FETCH 语句和静态游标一样。如 下所示: FETCHFETCHFETCHFETCH cursor_variable_namecursor_variable_namecursor_variable_namecursor_variable_name INTOINTOINTOINTO record_name;record_name;record_name;record_name; FETCHFETCHFETCHFETCH cursor_variable_namecursor_variable_namecursor_variable_namecursor_variable_name INTOINTOINTOINTO variable_name,variable_name,variable_name,variable_name, variable_namevariable_namevariable_namevariable_name ...;...;...;...; 当游标变量被声明为强 REF CURSOR 类型,那么 PL/SQL 编译器就会确认 在INTO 关键字后面的数据类型是否与该游标变量绑定的查询的数据结构想匹 配。 如果游标变量是属于弱 REF CURSOR 类型,那么 PL/SQL 编译器就不能像 对强 REF CURSOR 类型一样进行数据类型匹配检查。因为在声明游标变量时并 没有指定数据类型,所以在编译时,是没有办法知道哪一个游标对象会被分配给 这个变量。 所以,对数据类型匹配性的检查只能在运行时进行,也就是当 FETCH 将要 被执行时。在这个时刻,如果查询和 INTO 子句不能在结构上匹配,那么 PL/SQL 运行引擎就会抛出预定义的 ROWTYPE_MISMATCH 异常。但是要注意的是, 如果有可能的话,PL/SQL 竟会自动地对数据类型进行转换。 3.4.7 游标变量的使用规范 本小节将会更加详细地对游标变量的使用进行探讨。其中包含了数据行类型 的匹配规则,游标变量的别名和范围问题。 首先要记住的是,游标变量是一个游标对象或数据库查询的引用。它本身并 不是对象。一个游标变量满足以下条件中的任意一个,那么就可以说它引用了一 个给定的查询: 1、对游标变量执行了一个 OPENFOR 语句,与一个查询绑定在一起。 2、一个游标变量从另一个游标变量那里获值,从而指向查询。 对于游标变量,你可以执行赋值操作,并且把这个值作为参数传递给存储过 程和函数。为了在两个游标变量之间执行这种动作,不同的游标变量必须遵从一 组编译时和运行时的数据类型匹配规则。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 3.4.7.1 编译时游标数据类型匹配规则 以下是一些 PL/SQL 遵循的编译时规则。 如果以下条件为真,那么两个游标变量(包括存储过程的参数)是相匹配的: 1、两个变量(参数)都是强 REF CURSOR 类型,而且都有相同的返回类 型。 2、两个变量(或参数)都是一个弱 REF CURSOR 类型,不论返回类型是 什么。 3、一个变量(或参数)是任意的强 REF CURSOR 类型,而另一个是任意 的弱 REF CURSOR 类型。 4、一个强 REF CURSOR 类型的游标变量(或参数)可以打开一个查询并 返回记录类型,而这个记录类型在结构上与声明时的 rowtype_ name 是一致的。 5、一个弱 REF CURSOR 类型的游标变量(或参数)可以打开任意的查询, 而且可以从这个变量中检取数据到任意的变量列表或记录结构中。 如果其中一个是弱 REF CURSOR 类型的游标变量,那么 PL/SQL 编译器就 不能够真正地判断这两个不同的游标变量是否相匹配。而这种情况可能在运行时 发生。下一节将提到这种情况。 3.4.7.2 运行时游标数据类型匹配规则 一个弱 REF CURSOR 类型的游标变量(或参数)能够被创建来引用一个返 回任意数据类型的查询,而与其之前所指向的游标对象无关。 一个强 REF CURSOR 类型的游标变量只能用来打开一个具有相同返回数 据类型的查询。 如果以下两个条件为真,两个记录(或一组变量)将会被认为在结构上与隐 式类型转换相匹配: 在两个记录(或变量组)里,域的数目是一致的。 对于一个记录里的域,与之相对应的另一个组里的域具有相同的 PL/SQL 数 据类型,或者是具有系统能够隐式转换的域。 对于一个在 FETCH 语句中使用的游标变量(或参数),与之联系的查询必 须在结构上与 INTO子句后面的记录或变量组相匹配。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 3.4.7.3 游标变量别名 如果你指派一个游标变量给另一个游标变量,它们对于同一个游标对象来说 就互为别名。它们共享同一个指向游标对象的引用(游标查询出来的结果集)。 任何通过其中一个游标变量对游标对象进行操作,也会影响到另一个游标变量。 下面这个匿名块显示了游标别名的工作方式: 从上图的程序中我们可以看到,在第 8行,我们把 cur_emp1 指派给了 cur_emp2,所以两个游标共享一个游标对象;但是在第 11 行时关闭了 cur_emp1,也就是说销毁了公有的游标对象,所以第 12 行,在从 cur_emp2 游 标中提取数据时,就会报错。 游标对象的状态的任何改变都会通过任何一个引用这个游标对象的游标变 量看到。 3.4.7.4 游标对象的作用域 一个游标变量的作用域与一个静态游标一样:在游标变量被声明的 PL/SQL 块中(除非是在包中声明,这样就使得变量为全局可用)。而分配给游标变量的 游标对象的作用域,却完全不同。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 一旦执行一个 OPEN FOR 语句就会创建一个游标对象,这个游标对象会一 直保持可用,直到指向这个游标对象的活动游标变量执行了关闭操作。这就意味 着,你能够创建一个游标对象在某个范围内,同时把它分配给一个游标变量。同 样,如果把这个游标对象分配给另一个处于不同作用域的游标变量,那么这个对 象的作用域就会跨越原有的作用域(有可能到另一个 PL/SQL 块中)。 在下面这个例子,我使用一个内嵌块来演示在内嵌块中创建的游标对象如何 把它的作用域扩展到外围域中。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 3.4.8 把游标变量作为参数传递 在对过程或函数的调用时,你可以把游标变量作为一个参数来进行传递。当 你在程序中的参数列表中使用了一个游标变量,你就必须设置这个参数的输入输 出模式和数据类型(REF CURSOR 类型)。 3.4.8.1 确定 REFREFREFREF CURSOR CURSOR CURSOR CURSOR 类型 在你的程序头部,你必须确定作为参数的游标变量的 REF CURSOR 类型。 要做到这一点,这个游标类型必须是已经被定义的。 如果你在一个程序中创建了一个本地模块,你就能够在同一个程序中定义游 标类型。它就可以作为参数使用,这个方法如下所示: DECLARE /* 定义 REFCURSOR 类型.*/ TYPE curvar_type ISREFCURSORRETURN company%ROWTYPE; /*在参数列表中引用它 */ PROCEDURE open_query (curvar_out OUT curvar_type) IS local_cur curvar_type; BEGIN OPEN local_cur FORSELECT*FROM company; curvar_out := local_cur; END; BEGIN ... END; 如果你正在创建一个独立的过程或函数,那么你引用一个已预先定义好的 REF CURSOR 类型的唯一途径就是,把TYPE 语句放到一个包定义段里。所有 在包定义段里声明的变量,都可以在你的会话中作为全局变量使用,所以你能够 使用“.”引用的方式在程序中引用它们,如下面这个例子所示: � 创建一个有 REF CURSOR 类型声明的包: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com PACKAGE company IS /* Define the REFCURSOR type. */ TYPE curvar_type ISREFCURSORRETURN company%ROWTYPE; END package; � 在一个独立的过程中,就可以通过引用这个 REF CURSOR 类型,如下 所示: PROCEDURE open_company (curvar_out OUT company.curvar_typecompany.curvar_typecompany.curvar_typecompany.curvar_type)IS BEGIN ... END; 3.4.8.2 设定参数的模式 就像其他参数一样,一个游标变量参数可以为以下模式中的一种: 1.1.1.1. ININININ 在程序中为只读。 2.2.2.2. OUTOUTOUTOUT 仅能被程序写 3.3.3.3. ININININOUTOUTOUTOUT 可被程序读或者写。 要记住,游标变量的值是游标对象的引用,而不是游标对象的状态。换句话 说,在你从一个游标减去数据或关闭游标之后,游标变量的值并没有发生变化。 Only two operations, in fact, may change the value of a cursor variable (that is, the cursor object to which the variable points): 事实上,只有两种操作恩能够改变一个游标变量的值: 1. 对一个游标变量进行赋值 2. 使用一个 OPEN FOR 语句 如果游标变量已经指向了一个游标对象,那么 OPEN FOR 将不会确实地改 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 变这个引用。它只是简单地改变联系这个对象的查询。 FETCH 和CLOSE 操作能够影响游标对象的状态,但是不会改变这个游标 对象本身的引用,而这就是游标变量的值。 这里有一个具有游标变量参数的程序范例: PROCEDURE assign_curvar (old_curvar_in IN company.curvar_type, new_curvar_out OUT company.curvar_type) IS BEGIN new_curvar_out := old_curvar_in; END; 这个过程拷贝老的游标变量给新的变量。第一个参数是输入模式,因为它只 在表达式的右边出现。而第二个参数必须为输出模式(或输入输出模式),因为 它在程序被改变。注意 curvar_type 是在 company 包中预先定义了的。 3.4.9 游标变量的限制 有关游标变量的限制如下所示;需要注意的是,随着 Oracle 版本的升级, 这些限制可能有部分被取消: � 游标变量不能在包中声明,因为它们没有一个持久的状态。 � 你不能使用 RPC(remote procedure calls)来从一个服务器传递游标 变量给另一个服务器。 � 如果你在 PL/SQL 中,把一个游标变量作为一个绑定变量或宿主变量, 你就不能在服务器中从中获取数据,除非你也在相同的服务器调用中打 开了它。 � 在OPEN FOR 语句中与一个游标变量相关联的查询是不能使用 FOR UPDATE 子句的。(但是这在 Oracle9i 或以上版本是允许的)。 � 你不能使用比较操作符来对游标变量进行相等性、不等性或是否为空进 行检测。 � 你不能对一个游标变量赋 NULL 值。如果你试图这样做,Oracle 系统就 会产生一个 PLS-00382 的异常。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com � 数据库的列不能存储游标变量的值。你不能在 CREATE TABLE 语句中 对列的定义中使用 REF CURSOR 类型。 在一个内嵌表,联合数组或数组中的元素,是不能存储游标变量的值。你不 能使用 REF CURSOR 类型来定义集合众的元素类型。 3.5 游标表达式 Oracle 在SQL 语言中提供了一个强有力的工具:游标表达式。一个游标表 达式从一个查询中返回一个内嵌的游标。在这个内嵌游标的结果集中,每一行数 据包含了在 SQL 查询中的可允许的数值范围;它也能包含被其他子查询所产生 的游标。 因此,你能够使用游标表达式来返回一个大的和复杂的,从一张或多张表获 取的数据集合。游标表达式的复杂程度,取决于查询和结果集。然而,了解所有 从Oracle RDBMS 提取数据的可能途径,还有大有好处的。 You can use cursor expressions in any of the following: 你能够在以下任何一种情况使用游标表达式: � 显式游标声明 � 动态 SQL 查询。 � REFCURSOR 声明和变量。 你不能在一个隐式查询中使用游标表达式。 游标表达式的语法是相当简单的: CURSORCURSORCURSORCURSOR(subquery)(subquery)(subquery)(subquery) 当Oracle 从父游标或外围游标那里检取包含游标表达式的数据行时, Oracle 就会隐式地打开一个内嵌的游标,这个游标就是被上述的游标表达式所 定义。在以下情况发生时,这个内迁游标将会被关闭: � 你显式地关闭这个游标。 � 外围或父游标被重新执行,关闭或撤销。 � 当从父游标检取数据时,发生异常。内嵌游标就会与父游标一起被关闭。 3.5.1 使用游标表达式 你可以通过两种不同的,但是非常有用的方法来使用游标表达式: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 1. 在一个外围查询中把字查询作为一列来检取数据。 2. 把一个查询转换成一个结果集,而这个结果集就可以被当成一个参数传 递给一个流型或变换函数。 3.5.1.1 把子查询作为列检取数据 下面这个过程(HR模式)展示了使用内嵌 CURSOR 表达式来获取子查询 数据的方法。最外层的查询取出数据的两个部分:城市的地址和一个内嵌游标— —其包含了在这个城市的部门。这个内嵌游标,依次地从更深一层的内嵌游标中 取出数据,其包含了在每一个部门中的所有雇员。 我当然也可以创建并操作多个独立的游标来获取信息。但是,CURSOR 表 达式给我们使用一个完全不同方法的机会,这个方法更精确和高效,它使所有的 操作都在 SQL 语句执行器中完成,并且减少了上下文的交互。 CREATEORREPLACEPROCEDURE emp_report(p_locid NUMBER) IS TYPE refcursor ISREFCURSOR; -- 这个查询返回两列, 但是第二列是一个游标 -- 这个游标让我们获取有关联的结果集 CURSOR all_in_one_cur is SELECT l.city, CURSOR(SELECT d.department_name, CURSOR(SELECT e.last_name FROM employees e WHERE e.department_id = d.department_id) AS ename FROM departments d WHERE l.location_id = d.location_id) AS dname FROM locations l WHERE l.location_id = p_locid; departments_cur refcursor; employees_cur refcursor; 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com v_city locations.city%TYPE; v_dname departments.department_name%TYPE; v_ename employees.last_name%TYPE; BEGIN OPEN all_in_one_cur; LOOP FETCH all_in_one_cur INTO v_city, departments_cur; EXITWHEN all_in_one_cur%NOTFOUND; -- 现在我基于部门开始循环 同时 我不需要显式地 -- 打开这个游标,Oracle 会自动完成。 LOOP FETCH departments_cur INTO v_dname, employees_cur; EXITWHEN departments_cur%NOTFOUND; -- 现在我基于每个部门的员工开始循环. -- 同样,我也不需要显式地打开游标 LOOP FETCH employees_cur INTO v_ename; EXITWHEN employees_cur%NOTFOUND; DBMS_OUTPUT.put_line(v_city || '' || v_dname || '' || v_ename); ENDLOOP; ENDLOOP; ENDLOOP; CLOSE all_in_one_cur; END; 程序运行结果如下图所示: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 3.5.2 游标表达式的限制 对于游标表达式,有以下一些限制条件: � 你不能对一个隐式游标使用游标表达式,因为目前还没有提供从内嵌游 标中直接提取数据到一个 PL/SQL 数据结构的方法。 � 游标表达式只能在最外层的 SELECT 子句的字段列表中出现。 � 你能够把游标表达式放置在一个 SELECT 语句中,而且这个语句是不 能嵌套在其他查询表达式里面,除非它本身就被定义为一个游标表达式 的子查询。 � 一个游标表达式能够被用来作为一个表函数的参数,这个表函数可以在 SELECT 语句中 FROM 子句中被调用。 � 在声明一个视图时,游标表达式不能使用。 � 在动态 SQL 中使用游标表达式时,你是不能使用 BIND 和EXECUTE 命令的。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 第 4444 章 PL/SQL 应用程序结构 在前面,我们已经对 PL/SQL 语言的一些编程细节,如游标、循环、变量等 进行了比较详细的探讨。现在我们需要知道是,如何在我们的应用中使用这些 PL/SQL 代码,如何把它们组织起来,从而形成一个易于理解,架构合理,维护 方便的应用程序。 一般来说,我们编写的应用系统都是很复杂,模块与模块之间有非常多的关 联的。更进一步,由于用户需求的不断变更,反复,或是有根多的新需求被提出 来,从而使得我们的应用程序相应地变得非常复杂。 在我们软件行业中,最大的挑战就是如何找到方法减少我们环境的复杂程 度。如何在一片从满新需求,新特性的丛林中找到出路,这就是我们要考虑的重 点。 人的能力都是有限的,没有人能够比得上大型并行计算机。我们需要做的就 是把巨大的,复杂的项目分割成更小的,更易于管理的组件,然后再把这些组件 分解成单个独立,具有逻辑内聚的程序。我们还要知道如何组建这些程序,并对 之进行有效的测试(完美的测试是不存在的),最后我们还要使用这些程序建造 成一个完整的应用。 不管你使用的是“由顶至下”,还是别的什么设计方式,有一点是不容置疑 的,就是你必须根据模块化的思想,把你的代码用存储过程,函数和对象类型组 织起来,这样,你才能找到一条通往高质量、易管理代码的道路。 4.1 过程、函数和参数 4.1.1 模块化编码 模块化的本质就是把一个大的代码块,“打碎”成小的部件——这种部件就 成为“模块”。代码的模块化就有点像数据的规范化,它们具有许多相同的特点。 当使用了模块化编码之后,你的程序就会变得 1、提高重用性。 2、更易于管理。 3、提高可读性。 4、提高可靠性。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 一旦你掌握了 PL/SQL 的语法,如循环、条件选择和游标等,你就可以编写 PL/SQL 程序,但是这样并不意味着你就能够创建一个有效的应用程序,除非你 掌握了 PL/SQL 的模块化编程。 PL/SQL 提供了以下几种模块化编程的方法: 1、过程(一般又叫存储过程) 这个一个由执行一个或更多动作的 PL/.SQL 代码块所组成的程序。你可以 通过它的参数输入数据并得到输出信息。 2、函数 返回单个数值的程序,在使用上有点像 PL/SQL 表达式,你可以通过参数给 函数传递信息。 Database trigger 3、数据库触发器 当数据库发生某些变化时,就会被触发的一组命令。 4、包 由一组非匿名的过程,函数,类型和变量组成。一个包其实并不是一个模块, 但是在某种程度上也很接近。 5、对象类型或一个对象类型的实例 Oracle 的对象类型封装了状态、行为,把数据(像是一个关系型数据表) 和规则(操作数据的过程和函数)结合起来。 在这里,我使用了一个术语“模块”来描述一个函数或一个过程。在本小节 中,我们主要关注于如何对这些模块进行有效的设计。 4.1.2 存储过程 存储过程也叫做“过程”,在本章中,我们统一简称为“过程”。 一个过程就是执行一个或多个动作的模块。因为一个过程的调用,在 PL/SQL 中是一个独立的语句。“过程”是模块化编码中最基本的,它允许你能够巩固和 重用程序逻辑。 一般的 PL/SQL 过程模块格式如下: PROCEDUREPROCEDUREPROCEDUREPROCEDURE[schema.]name[schema.]name[schema.]name[schema.]name [([([([( parameterparameterparameterparameter [,[,[,[, parameterparameterparameterparameter ...]...]...]...]))))]]]] [AUTHID[AUTHID[AUTHID[AUTHIDDEFINERDEFINERDEFINERDEFINER |||| CURRENT_USER]CURRENT_USER]CURRENT_USER]CURRENT_USER] 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com ISISISIS [declarations][declarations][declarations][declarations] BEGINBEGINBEGINBEGIN executableexecutableexecutableexecutable statementsstatementsstatementsstatements [[[[EXCEPTIONEXCEPTIONEXCEPTIONEXCEPTION exceptionexceptionexceptionexception handlers]handlers]handlers]handlers] ENDENDENDEND[name];[name];[name];[name]; 以下是对各个元素的说明: schemaschemaschemaschema “模式名”对于过程来说是一个可选项。默认值就是当前用户,如果当前用 户想在别的模式下创建过程的话,他就必须有授权。 namenamenamename 过程的名字,紧跟在关键字 PROCEDURE PROCEDURE PROCEDURE PROCEDURE 的后面。 parametersparametersparametersparameters 一个可选的参数列表。通过这个列表,你可以定义需要与过程交互的信息, 并且把过程传回的信息反馈给调用程序。 AUTHIDAUTHIDAUTHIDAUTHID clauseclauseclauseclause 决定这个过程能否在过程的创建者权限下,还是在当前用户的权限下运行。 declarationsdeclarationsdeclarationsdeclarations 声明段。声明这个过程的标志符。如果你没有任何需要声明的东西,那么在 IS 和BEGIN 之间就没有任何语句。 executableexecutableexecutableexecutable statementsstatementsstatementsstatements 可执行语句。在BEGIN 和END(或EXCEPTION)之间,你必须至少写一 条可执行语句。 exceptionexceptionexceptionexception handlershandlershandlershandlers 异常处理块。如果你不想显式地处理异常,那么你就可以不必写 EXCEPTION 关键字,而仅仅用一个 END 结束程序就行了。 下图是一个典型的过程结构: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 4.1.2.1 调用一个过程 一个过程可以作为 PL/SQL 语句进行调用,并且必须用“;”来结尾。下面 这个例子就表示调用上图的 apply_discount 过程。 BEGINBEGINBEGINBEGIN apply_discount(apply_discount(apply_discount(apply_discount( new_company_id,new_company_id,new_company_id,new_company_id, 0.150.150.150.15 ););););-------- 15%15%15%15% discountdiscountdiscountdiscount END;END;END;END; 如果过程没有任何参数,那么你就可以不必在过程名后跟参数。如下: display_store_summary;display_store_summary;display_store_summary;display_store_summary; 在Oracle8i 数据库和以后的版本中,你也可以用括号把空的参数列表扩起 来。 display_store_summary();display_store_summary();display_store_summary();display_store_summary(); 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 4.1.2.2 过程首部 在IS 关键字前面的过程定义段被称之为“过程首部”。这个“首部”提供了 有关这个过程调用的所有信息: 过程名称 AUTHID AUTHID AUTHID AUTHID 子句(如果有的话) 参数列表(如果有的话) 程序员不需要了解过程的内部详细过程,也可以正确从另外一个程序中调用 该过程。 以下是在前一节所提到的过程 apply_discount 的首部: PROCEDUREPROCEDUREPROCEDUREPROCEDURE apply_discountapply_discountapply_discountapply_discount (company_id_in(company_id_in(company_id_in(company_id_in ININININ company.company_id%TYPE,company.company_id%TYPE,company.company_id%TYPE,company.company_id%TYPE, discount_indiscount_indiscount_indiscount_in ININININNUMBER)NUMBER)NUMBER)NUMBER) 它包含了这个模块的类型,名字,和两个参数的说明。 4.1.2.3 过程主体 过程主体部分就是实现这个过程的所有代码,它包含了:声明段、执行段和 异常段。任何在 IS 关键字后面的代码都可以看作是这个过程的主体代码。异常 段和声明段是可选的。如果你没有异常处理部分,那么就留下一个 EXCEPTION 的关键字,仅仅使用 END 结束该过程。如果你没有声明段,那么 BEGIN 语句 就简单地紧跟在 IS 关键字后面。 在一个过程里面,你必须至少写一条可执行语句。当然,这个一般不是什么 问题;相反,要特别关注执行段,让它不要变得很长,这样就很难管理。你应该 努力让执行段变得紧凑和易读。有关提高“可读性”的内容在后面的章节有所探 讨。 4.1.2.4 END END END END 描述符 你可以在 END 关键字后面紧跟着过程名,这样就表示你的过程已全部结束。 PROCEDUREPROCEDUREPROCEDUREPROCEDURE display_storesdisplay_storesdisplay_storesdisplay_stores (region_in(region_in(region_in(region_in ININININ VARCHAR2)VARCHAR2)VARCHAR2)VARCHAR2) ISISISIS BEGINBEGINBEGINBEGIN 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com ............ ENDENDENDEND display_stores;display_stores;display_stores;display_stores; 过程名就像一个标签,它连接了这个程序的头和尾。你应该养成这样一个习 惯:在 END 后面加上过程名(END 描述符)。特别是当你在一个包主体里有多 个过程的时候,这个描述符就显得特别有用。 4.1.2.5 RETURN RETURN RETURN RETURN 语句 一般而言,RETURN 语句是针对函数的,因为函数往往需要返回一个值。 有趣的是,PL/SQL 也允许你在过程中使用一个 RETURN 语句。事实上,在过 程中的 RETURN 语句并不能返回任何值给调用程序,它仅仅是停止代码的执行, 并且返回到调用代码的控制中来。 其实你很少看到有合适的地方在过程中使用 RETURN 语句,因为这样做的 话,很容易让你的代码的结构化程度减弱,因为这势必产生退出过程的两个出口, 从而让过程变得难以理解和维护。同样,你也要尽量避免使用 GOTO 语句,以 免破坏程序的结构性。 4.1.3 函数 一个函数就是一个能够返回值得模块。与过程不同,函数可以独立成为一条 可执行语句中的一部分,比如说作为表达式中的一个元素,或赋值语句中的一个 值(比如说在声明段中分配给变量的默认值)。 因为函数返回一个数值,所以它就可以认为拥有一个类型。所以一个函数能 够在 PL/SQL 中的任何语句中作为一个数据类型使用,只要这个数据类型与这个 函数返回数值的类型相同。 对于模块化编码来说,函数是至关重要的。比如说,在你的应用中,每一条 商业规则或公式都应该用函数来表示。每一个单行查询也可以在函数中定义,这 样就更易读和重用。 可以肯定地说,如果一个应用太少使用函数,那么这个应用的维护和重用性 将不会理想。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 4.1.3.1 函数的结构 函数的结构与过程几乎一模一样,唯一的区别就是函数有一个 RETURN 子 句。函数的一般结构模式如下: FUNCTIONFUNCTIONFUNCTIONFUNCTION[schema.]name[schema.]name[schema.]name[schema.]name [([([([( parameterparameterparameterparameter [,[,[,[, parameterparameterparameterparameter ...]...]...]...]))))]]]] RETURNRETURNRETURNRETURN return_datatypereturn_datatypereturn_datatypereturn_datatype [AUTHID[AUTHID[AUTHID[AUTHIDDEFINERDEFINERDEFINERDEFINER |||| CURRENT_USER]CURRENT_USER]CURRENT_USER]CURRENT_USER] [DETERMINISTIC][DETERMINISTIC][DETERMINISTIC][DETERMINISTIC] [PARALLEL[PARALLEL[PARALLEL[PARALLELENABLEENABLEENABLEENABLE...]...]...]...] [PIPELINED][PIPELINED][PIPELINED][PIPELINED] ISISISIS [declaration[declaration[declaration[declaration statements]statements]statements]statements] BEGINBEGINBEGINBEGIN executableexecutableexecutableexecutable statementsstatementsstatementsstatements [EXCEPTION[EXCEPTION[EXCEPTION[EXCEPTION exceptionexceptionexceptionexception handlerhandlerhandlerhandler statements]statements]statements]statements] ENDENDENDEND[[[[ namenamenamename ];];];]; 下面是对各个结构元素的说明: 模式(schemaschemaschemaschema) 可选项。默认为当前用户模式(该用户必须拥有在这个模式下创建函数的权 限)。 函数名(namenamenamename) 函数的名称,紧跟在 FUNCTION 关键字后面。 参数(parametersparametersparametersparameters) 输入输出参数列表。作用于过程的参数相同。 返回类型(return_datatypereturn_datatypereturn_datatypereturn_datatype) 函数返回的数据类型。这在函数的首部是必要的,这在后面的章节中将会详 述。 AUTHID AUTHID AUTHID AUTHID 子句 决定这个函数能够在那个用户的权限下执行。可选项。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com DETERMINISTIC DETERMINISTIC DETERMINISTIC DETERMINISTIC 子句 一个优化提示。如果可用的话,系统就会使用一个已保存的函数返回值拷贝 副本。查询优化器就能够选择是使用已保存的结果,还是重新调用函数。 并行可用子句(PARALLEL_ENABLEPARALLEL_ENABLEPARALLEL_ENABLEPARALLEL_ENABLE) 一个优化提示。使函数能够在被 SELECT 语句调用的时候,以并行模式运 行。 PIPELINEDPIPELINEDPIPELINEDPIPELINED 子句 规定这个表函数的返回结果必须通过 PIPEROW 命令循环返回。 声明语句 对这个函数的本地标志符进行声明。如果你没有任何声明语句,那么在 IS 和BEGIN 关键字间就不会有任何语句。 可执行语句 函数的执行语句。在BEGIN 和END(或EXCEPTION)之间,至少有一条 可执行语句。 异常处理语句 函数可选的异常处理语句段。如果你不显式地处理异常情况,那么就不需要 这一段。 下图显示了一个标准函数所具备的各个代码段和相关的说明: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 4.1.3.2 返回的数据类型 一个函数可以返回任何已知的 PL/SQL 数据类型——从标量到矢量,从简单 数据到复杂结构的集合、对象类型,游标变量和大对象。不过,你不能返回一个 异常,因为在 PL/SQL 里面,异常不能算作一种数据类型。 下面是几个函数 RETURN 子句的例子。 从一个独立函数中返回一条字符串: CREATECREATECREATECREATEORORORORREPLACEREPLACEREPLACEREPLACEFUNCTIONFUNCTIONFUNCTIONFUNCTION favorite_nicknamefavorite_nicknamefavorite_nicknamefavorite_nickname (((( name_inname_inname_inname_in ININININ VARCHAR2)VARCHAR2)VARCHAR2)VARCHAR2) RETURNRETURNRETURNRETURN VARCHAR2VARCHAR2VARCHAR2VARCHAR2 ISISISIS............ 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com END;END;END;END; 一个对象的内部函数返回一个日期类型 CREATECREATECREATECREATETYPETYPETYPETYPE pet_tpet_tpet_tpet_t ISISISISOBJECTOBJECTOBJECTOBJECT(((( tag_notag_notag_notag_no INTEGER,INTEGER,INTEGER,INTEGER, NAMENAMENAMENAME VARCHAR2VARCHAR2VARCHAR2VARCHAR2 (60),(60),(60),(60), breedbreedbreedbreed VARCHAR2(100),VARCHAR2(100),VARCHAR2(100),VARCHAR2(100), dobdobdobdob DATE,DATE,DATE,DATE, MEMBERMEMBERMEMBERMEMBERFUNCTIONFUNCTIONFUNCTIONFUNCTION ageageageage (new_tag_no(new_tag_no(new_tag_no(new_tag_no INININININTEGER)INTEGER)INTEGER)INTEGER) RETURNRETURNRETURNRETURNDATEDATEDATEDATE )))) 返回与表结构相同的记录类型。 CREATECREATECREATECREATEORORORORREPLACEREPLACEREPLACEREPLACEPACKAGEPACKAGEPACKAGEPACKAGE book_infobook_infobook_infobook_info ISISISIS FUNCTIONFUNCTIONFUNCTIONFUNCTION onerowonerowonerowonerow (isbn_in(isbn_in(isbn_in(isbn_in ININININ books.isbn%TYPE)books.isbn%TYPE)books.isbn%TYPE)books.isbn%TYPE) RETURNRETURNRETURNRETURN books%ROWTYPE;books%ROWTYPE;books%ROWTYPE;books%ROWTYPE; ............ 返回一个已定义的游标变量 CREATECREATECREATECREATEORORORORREPLACEREPLACEREPLACEREPLACEPACKAGEPACKAGEPACKAGEPACKAGE book_infobook_infobook_infobook_info ISISISIS TYPETYPETYPETYPE overdue_rtoverdue_rtoverdue_rtoverdue_rt ISISISISRECORDRECORDRECORDRECORD(((( isbnisbnisbnisbn books.isbn%TYPE,books.isbn%TYPE,books.isbn%TYPE,books.isbn%TYPE, days_overduedays_overduedays_overduedays_overdue PLS_INTEGER);PLS_INTEGER);PLS_INTEGER);PLS_INTEGER); TYPETYPETYPETYPE overdue_rctoverdue_rctoverdue_rctoverdue_rct ISISISISREFREFREFREFCURSORCURSORCURSORCURSORRETURNRETURNRETURNRETURN overdue_rt;overdue_rt;overdue_rt;overdue_rt; FUNCTIONFUNCTIONFUNCTIONFUNCTION overdue_infooverdue_infooverdue_infooverdue_info (username_in(username_in(username_in(username_in ININININ lib_users.username%TYPE)lib_users.username%TYPE)lib_users.username%TYPE)lib_users.username%TYPE) RETURNRETURNRETURNRETURN overdue_rct;overdue_rct;overdue_rct;overdue_rct; .... 4.1.3.3 END END END END 描述符 你可以在 END 后面直接跟函数名,如下所示: FUNCTIONFUNCTIONFUNCTIONFUNCTION tot_salestot_salestot_salestot_sales (company_in(company_in(company_in(company_in INININININTEGER)INTEGER)INTEGER)INTEGER)RETURNRETURNRETURNRETURNNUMBERNUMBERNUMBERNUMBER 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com ISISISIS BEGINBEGINBEGINBEGIN ............ ENDENDENDEND tot_sales;tot_sales;tot_sales;tot_sales; 函数的 END 描述符的作用与过程的完全一样。它作为一个标签,连接了程 序的开始和结尾。 4.1.3.4 调用一个函数 不能在那里使用表达式,都可以把函数作为 PL/SQL 可执行语句的一部分。 下面这个例子显示在几种情况下,函数是如何被调用的。 1、使用函数给一个变量赋默认值: DECLARE v_nickname VARCHAR2(100) := favorite_nickname ('Steven'); 2、在 pet 对象中,使用成员函数作为条件表达式的一部分: DECLARE my_parrot pet_t := pet_t (1001, 'Mercury', 'African Grey', TO_DATE('09/23/1996', 'MM/DD/YYYY')); BEGIN IF my_parrot.age < INTERVAL'50' YEAR-- 9i INTERVAL type THEN DBMS_OUTPUT.PUT_LINE('Still a youngster!'); ENDIF; 3、从一张表中直接查询单条记录: DECLARE my_first_book books%ROWTYPE; BEGIN my_first_book := book_info.onerow ('1-56592-335-9'); ... 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 4、根据某个特定的用户名查找该用户的相关信息。 DECLARE my_overdue_info overdue_rct; BEGIN my_overdue_info := book_info.overdue_info ('STEVEN_FEUERSTEIN'); ... 以下是一个 CREATE VIEW 语句,其中包含了一个函数的调用,该函数的 参数可以是一个游标表达式返回的结果集。 CREATEORREPLACEVIEW young_managers AS SELECT managers.employee_id manager_employee_id FROM employees managers WHERE Most_Reports_Before_Manager ( CURSOR(SELECT reports.hire_date FROM employees reports WHERE reports.manager_id = managers.employee_id ), managers.hire_date ) = 1; 4.1.3.5 没有参数的函数 If a function has no parameters, the function call is written without parentheses. The following code illustrates this with a call to a method named "age" of the pet_t object type: 如果一个函数没有参数,那么这个参数的调用就不用圆括号了。如下所示的 代码,其中调用了一个叫“age”的函数。 IF my_parrot.ageageageage < INTERVAL'50' YEAR-- 9i INTERVAL type 在Oracle9i 或以上版本,你也可以加上空的圆括号以提示这是一个函数: IF my_parrot.age()age()age()age() < INTERVAL'50' YEAR 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 4.1.3.6 函数首部 在IS 关键字前面的函数的定义部分被称为函数的首部。这个首部给想要调用这 个函数的程序员,提供了所有的信息。(下载源码就到源码网,www.codepub.com) 1、函数名 2、定义函数特性和行为的修饰符 3、函数列表 4、返回的数据类型。 一个程序员不需要知道函数内部详细的实现情况,就能够在一个程序中调用 这个函数。 下面我们来看一下函数:tot_sales 的首部: FUNCTIONFUNCTIONFUNCTIONFUNCTION tot_salestot_salestot_salestot_sales (company_id_in(company_id_in(company_id_in(company_id_in ININININ company.company_id%TYPE,company.company_id%TYPE,company.company_id%TYPE,company.company_id%TYPE, status_instatus_instatus_instatus_in ININININ order.status_code%TYPEorder.status_code%TYPEorder.status_code%TYPEorder.status_code%TYPE :=:=:=:= NULL)NULL)NULL)NULL) RETURNRETURNRETURNRETURNNUMBERNUMBERNUMBERNUMBER 它包含了模块类型,名字,两个参数的列表,和一个返回数据类型(NUMBER 型)。这就意味着任何一个 PL/SQL 代码或表达式,如果引用了一个数字形数值 就能够对这个函数进行调用。以下就是范例代码: DECLARE v_sales NUMBER; BEGIN v_sales := tot_salestot_salestot_salestot_sales (1505, 'ACTIVE'); ... 4.1.3.7 函数主体 函数的主体和过程相像,也包含声明段,执行段和异常处理段。所有在 IS 关键字后面的代码都可看为函数主体。如果你没有声明段,那么 BEGIN 后面就 直接跟着 IS 关键字。 一个函数的执行段应该有一个 RETURN 语句,虽然它对于函数的编译并不 是必须。但是,如果你的函数在执行结束时没有 RETURN,那么 Oracle 就会报 以下错误(这是不良设计函数的标签): 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com ORA-06503:ORA-06503:ORA-06503:ORA-06503: PL/SQL:PL/SQL:PL/SQL:PL/SQL: FunctionFunctionFunctionFunction returnedreturnedreturnedreturned withoutwithoutwithoutwithout valuevaluevaluevalue 4.1.3.8 返回语句 一个函数必须有一个 RETURN 语句,也可以有多个 RETURN 语句,但是 在函数被调用时,只有一个 RETURN 会被执行。当 RETURN 语句被执行时, 函数就会立即中止函数的执行,并且把控制权交还给调用模块。 在函数首部的 RETURN 子句和在执行段中的 RETURN 语句是不同的,它 主要作用是定义这个函数返回的数据类型,而后者则是返回一个确切的数值;这 个数值的类型必须与首部定义的 RETURN 数据类型相匹配。 1.1.1.1. RETURN RETURN RETURN RETURN 任何有效表达式 返回语句可以返回任意的表达式,只要这个表达式的值与首部 RETURN 子 句所定义的数据类型相匹配。这个表达式可以是另一个函数的调用,复杂计算, 甚至是数据转换。所有以下的 RETURN 应用都是有效的: RETURNRETURNRETURNRETURN'buy'buy'buy'buy memememe lunch';lunch';lunch';lunch'; RETURNRETURNRETURNRETURNPOWERPOWERPOWERPOWER(max_salary,(max_salary,(max_salary,(max_salary, 5);5);5);5); RETURNRETURNRETURNRETURN(100(100(100(100 ---- pct_of_total_salarypct_of_total_salarypct_of_total_salarypct_of_total_salary (employee_id));(employee_id));(employee_id));(employee_id)); RETURNRETURNRETURNRETURNTO_DATETO_DATETO_DATETO_DATE('01'('01'('01'('01' |||||||| earliest_monthearliest_monthearliest_monthearliest_month |||||||| initial_year,initial_year,initial_year,initial_year, 'DDMMYY');'DDMMYY');'DDMMYY');'DDMMYY'); 你也可以返回复杂数据类型,比如说对象类型的实例,集合和记录。 在RETURN 语句中的表达式会在 RETURN 执行时进行计算。当控制权被 交还给调用模块时,表达式的计算结果也会被相应地传递。 2.2.2.2. 多个 RETURN RETURN RETURN RETURN 语句 我们可以在一个函数主体中使用多个 RETURN 语句,对于不同的情况,我 们就可以有不同的返回。但是有一点需要注意的是,你最好在函数主体的最后一 行写上一个 RETURN 语句。 3.3.3.3. 把RETURN RETURN RETURN RETURN 语句作为最后一条执行语句 一般来说,最好确认函数能够 RETURN 的方法就是把 RETURN 语句作为 函数执行段的最后一句。如下所示: FUNCTION do_it_all (parameter_list) RETURNNUMBERIS return_value NUMBER; 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com BEGIN ... lots of executable statements ... RETURN return_value; END; 对于异常要特别注意。如果一个异常发生,那么你的程序就有可能跳过后面 的执行语句,而直接到异常处理段中去。如果你的异常处理段没有 RETURN 语 句,你就会发现系统报 ORA-06503 的错误。不管你是如何处理实际异常的,函 数都不会返回任何错误值。 4.1.4 参数 过程和函数都使用参数来与调用模块进行通信。 一个模块的参数和这个模块的实现代码一样重要。我们创建模块的目的就是 为了让这个模块能被调用,最理想的情况是可以被很多的模块调用。如果参数列 表是混乱或者设计不良的,那么对于其他程序员来说,使用这个模块是一件困难 的事情,不管你的内部实现是多么的精妙。 许多开发人员并没有认识到模块参数列表的重要性。事实上,一个经过精心 设计的参数列表需要考虑以下几个问题: 1111、参数的数目 如果参数太少的话,则会降低你程序的重用性;但是如果参数太多的话,也 就没人敢用了。所以,程序参数的个数一般都取决于应用的需要,但是这里也有 几种方法来决定参数(比如说把多个参数绑定到一个记录中去)。 2222、参数的类型 你是否希望参数是“只读”、还是“只写”或者是“可读写”的呢? 3333、参数的命名 你应该如何命名参数,从而使得你的程序更易于理解。 4444、参数的默认值 你应该如何设置参数的默认值?一个参数是否应该有初始值?你的程序是 否必须要有输入值? PL/SQL 提供了几种特性来帮你有效地设计参数。以下的章节将会作详细的 说明。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 4.1.4.1 定义参数 参数的定义和变量的声明语法非常类似。但是有两个不同点:第一,一个参 数有其传输模式,而变量声明没有;其次,一个参数的声明必须是无限制的。 所谓有限制的声明就是指,在声明变量的同时,限制了这个变量的数值类型 和大小。而无限制声明就是指不限制大小的声明。下面的这个变量的声明就限制 了变量的长度为 60 个字符。 DECLARE company_name VARCHAR2(60)VARCHAR2(60)VARCHAR2(60)VARCHAR2(60); 当你声明一个参数时,你就必须给这个声明的限制部分留空。 PROCEDURE display_company (company_name IN VARCHAR2VARCHAR2VARCHAR2VARCHAR2)IS... 4.1.4.2 实参和形参 我们需要区分两种不同的参数:形参和实参。形参就是在模块头部参数列表 声明的参数名。而实参就是传递给形参的具体数值或表达式,也就是调用模块时, 放到参数列表里的参数。 让我们来看看在使用 tot_sales 函数时,形参和实参的区别,下面就是该函 数的首部: FUNCTION tot_sales (company_id_incompany_id_incompany_id_incompany_id_in ININININ company.company_id%TYPE,company.company_id%TYPE,company.company_id%TYPE,company.company_id%TYPE, status_instatus_instatus_instatus_in ININININ order.status_code%TYPEorder.status_code%TYPEorder.status_code%TYPEorder.status_code%TYPE :=:=:=:= NULLNULLNULLNULL) RETURN std_types.dollar_amount; 该函数的形参就是: company_id_incompany_id_incompany_id_incompany_id_in:company 表的主健。 status_instatus_instatus_instatus_in:订单的状态。 这些形参在函数外是不存在的。你可以把它们想象为一种占位符,而真正坐 在这个位置上的是“实参”。 当你在你的代码中调用 tot_sales 函数时,你就的在形参的位置上填上实参, 如下面的例子所示: new_sales := tot_sales (company_id, 'N'); 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com paid_sales := tot_sales (company_id, 'P'); shipped_sales := tot_sales (company_id, 'S'); all_sales := tot_sales (company_id); 最后一个例子,我们没有给 status_in 这个形参传递实参,在这种情况下, 函数就会自动把默认值让在这个位置上。 当tot_sales 函数被调用,所有的实参就会被估值。估值的结果就会被传递 给函数内部的所对应形参。(注意,这种情况只适用于 IN 和INOUT 参数,对于 OUT模式参数是不会拷贝实参的数值的)。 对应的形参和实参必须具有相同的或相匹配的数据类型。在许多情况下, Oracle 会自动地为你进行数据类型转换。但是一般地,你就好避开这些隐式数 据类型转换。使用一个标准的数据类型转换函数,比如说 TO_CHAR 或 TO_DATE,这样你就能够精确地知道你传递给模块的数据是哪种类型。 4.1.4.3 参数的模式 When you define the parameter, you also specify the way in which it can be used. There are three different modes of parameters: 当你定义参数的时候,你也就规定了这个参数的使用方式。下面有三种参数 模式: 1、IN 模式:只读。在模块里面,实参的数值只能被引用,而这个参数不能 被改变。 2、OUT模式:只写。模块能够给这个参数进行赋值,但是这个参数的数值 不能被引用。 3、INOUT模式:可读写。这个模块的数值在模块内可被引用或改变。 模式决定了程序如何使用和操纵赋给形参的数值。你能够在参数的名字后面 立即定义这个参数的模式。下面一个过程的首部给出了这三种模式的使用方法: PROCEDURE predict_activity (last_date_in ININININDATE, task_desc_inout ININININOUTOUTOUTOUT VARCHAR2, next_date_out OUTOUTOUTOUTDATE) 下面我们就这三种参数模式的细节进行探讨。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 1.1.1.1. IN IN IN IN 模式 一个 IN 参数允许你传递数值到模块中去,而不能从模块中传递出任何信息 到调用该模块的 PL/SQL 代码中。换句话说,IN 模式参数就只能读,而不能写, 这和常量有点相似:它在整个模块的运行过程中是不能被改变的。你不能给 IN 模式参数赋值或改变它已有的值。 如果你不为参数设定模式的话,那么 IN 就是默认模式,参数会被编译器自 动设为 IN 模式。所以,我们建议,在一般情况下,你都应该给每一个参数设置 好模式,以增加程序的可读性。 IN 模式参数能够在程序的首部设定默认值(详情可参看后面的章节)。 IN 模式参数的实际数值可以是一个变量,一个常量,一个立即值或一个复 杂的表达式。比如下面这个例子,所有对 display_title 的调用都是合法的: DECLARE happy_title CONSTANT VARCHAR2(30) := 'HAPPYBIRTHDAY'; changing_title VARCHAR2(30) := 'Happy Anniversary'; spc VARCHAR2(1) := CHR(32) --ASCII code for a single space; BEGIN display_titledisplay_titledisplay_titledisplay_title ('Happy('Happy('Happy('Happy Birthday');Birthday');Birthday');Birthday'); -------- aaaa literalliteralliteralliteral display_titledisplay_titledisplay_titledisplay_title (happy_title);(happy_title);(happy_title);(happy_title); -------- aaaa constantconstantconstantconstant changing_titlechanging_titlechanging_titlechanging_title :=:=:=:= happy_title;happy_title;happy_title;happy_title; display_titledisplay_titledisplay_titledisplay_title (changing_title);(changing_title);(changing_title);(changing_title); -------- aaaa variablevariablevariablevariable display_titledisplay_titledisplay_titledisplay_title ('Happy'('Happy'('Happy'('Happy' |||||||| spcspcspcspc |||||||| 'Birthday');'Birthday');'Birthday');'Birthday'); -------- anananan expressionexpressionexpressionexpression display_titledisplay_titledisplay_titledisplay_title (INITCAP(INITCAP(INITCAP(INITCAP(happy_title));(happy_title));(happy_title));(happy_title)); -------- anotheranotheranotheranother expressionexpressionexpressionexpression END; 2.2.2.2. OUT OUT OUT OUT 模式 一个 OUT模式参数的性质恰好与 IN 模式参数相反,所以你可能已经把它的 大概情况推想出来了。OUT模式参数就有点像一个函数的返回值,但是它是出 现在参数列表中的,而且你可以 在程序中,OUT模式参数就可以看成一个变量(只不过这个变量不需要初 始化)。实际上,OUT参数并不会保存任何数值,直到程序成功终止。在程序的 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 执行阶段,任何对 OUT参数的赋值动作,实际上都是在内部对 OUT参数进行 拷贝。当程序成功终止,并把控制权交还给调用代码时,存储在本地拷贝中的数 值才会传递给真正的 OUT参数。这样,OUT参数的值在调用代码中就可用了。 以下有一些关于 OUT模式参数的参考规则: 1、你不能把 OUT参数的值赋给任何其它的变量,甚至包括它自己本身。 2、你不能给 OUT参数设置一个默认值。你只能在模块内部的代码中给 OUT 参数赋值。 3、当在程序中发生异常时,任何给 OUT参数赋值的动作都会被回滚。因 为赋给 OUT参数的值必须等到程序成功终止,才能赋予,而其间的任何赋值行 为都可被忽略。除非异常被捕捉,并且在异常处理模块中,给 OUT参数进行了 赋值,不然的话,这个参数是不会被赋值的。这个变量将会保持在调用之前的数 值。 4、对应于 OUT模式参数的实参必须是一个变量。它不能是一个产量,立 即值,或表达式,因为这些都不能为传递出来的信息提供一个存储空间。 3.3.3.3. ININININ OUT OUT OUT OUT 模式 对于 INOUT参数,你既可以把数值传递给程序,也可以获取返回数值到调 用模块中。INOUT参数具有 OUT参数的两种限制: 1、一个 INOUT参数不能设置默认值。 2、一个 INOUT实参必须是一个变量。 除了以上两个限制外,就再没有其它的了。 你可以把 INOUT参数放在一个赋值表达式的两边,因为它像是一个初始化 了的变量。而且,PL/SQL 不会在程序的执行过程中,丢失 INOUT参数的值。 相反,它会在合适的地方使用这些参数。 下面的一个过程能够根据输入的姓名的模式,对姓名进行大小写变换,并且 输出完整的姓名。大家可以看到,这里用了四个参数,两个 INOUT类型,一个 OUT和一个 IN 类型。详细代码如下: PROCEDURE combine_and_format_names (first_name_inout INOUT VARCHAR2, last_name_inout INOUT VARCHAR2, 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com full_name_out OUT VARCHAR2, name_format_in INNUMBER:=0 ) IS BEGIN /*把姓名都转换成大写*/ first_name_inout := UPPER(first_name_inout); last_name_inout := UPPER(last_name_inout); /*根据输入的姓名模式,连接姓名*/ IF name_format_in = 0 THEN full_name_out := last_name_inout || '' || first_name_inout; ELSIF name_format_in = 1 THEN full_name_out := first_name_inout || '' || last_name_inout; ENDIF; END; 姓和名两个参数必须是“INOUT”类型,因为我还想要它们转换成大写之 后的信息,而 full_name_out 参数仅仅是 OUT类型就可以了。最后, name_format_in 参数(姓名组合格式)只要要是 IN 类型就行了,因为它仅仅是 用来决定姓名如何组合,而不需要做任何的改变。 每一种参数模式都有其特性和目的。在进行程序设计时,需要认真地考虑。 上述程序的执行结果如下图所示: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 17.4.4. Matching Actual and Formal Parameters in PL/SQL 4.1.4.4 在PL/SQL PL/SQL PL/SQL PL/SQL 中匹配实参和形参 当一个程序执行时,PL/SQL 是如何知道一个实参与那一个形参对应的呢? PL/SQL 提供两种关联方式。 1、位置对应 根据位置对应关系连接实参和形参。 2、名字对应 Associate the actual parameter explicitly (by name) with the formal parameter. 显式地根据形参的名字来连接实参。 1.1.1.1. 位置对应 一直以来,我们使用的都是位置对应法来连接实参和形参。PL/SQL 根据参 数的相对位置,从而把它们一一对应。 比如说,下面这个 tot_sales 的例子,PL/SQL 把第一个实参 “:order.company_id”和第一个形参“company_id_in”对应起来,根据的就是 这个两个参数的相对位置。 new_sales := tot_sales (:order.company_id:order.company_id:order.company_id:order.company_id,'N'); 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com FUNCTION tot_sales (company_id_incompany_id_incompany_id_incompany_id_in IN company.company_id%TYPE, status_in IN order.status_code%TYPE := NULL) RETURN std_types.dollar_amount; 通过下面的图形表示,你会有一个更直接的了解: 2.2.2.2. 名字对应 使用名字对应,你可以更直观地看到形参和实参的对应关系,在这里我们用 “=>”连接符来连接形参和实参。 一般的语法格式如下: formal_parameter_nameformal_parameter_nameformal_parameter_nameformal_parameter_name =>=>=>=> argument_valueargument_valueargument_valueargument_value 因为你显式地提供了形参的名字,所以 PL/SQL 就不会去根据参数间的相对 位来找对应关系。所以,如果你使用了名字对应方法,你就不必把每个实参按照 形参的排列顺序一一排好。如下所示,你也可以这样调用 tot_sales: new_sales := tot_sales (company_id_in => order_pkg.company_id, status_in =>'N'); new_sales := tot_sales (status_in =>'N', company_id_in => order_pkg.company_id); You can also mix named and positional notation in the same program call: 你还可以把这两种对应方法混合使用(不过,建议最好不要这样做)。 :order.new_sales := tot_sales (order_pkg.company_id, status_in =>'N'); 不过,你要混合使用的话,就必须把位置对应的参数放到前面,这样 PL/SQL 才能跟踪参数的位置。如下面两个例子,第一个会出现名字对应放前的错误,第 二个就会产生 PL/SQL 试图把’N’转换成 NUMBER 型(形参 company_id_in 的 类型)的情况,从而报错。 :order.new_sales:order.new_sales:order.new_sales:order.new_sales :=:=:=:= tot_salestot_salestot_salestot_sales (company_id_in(company_id_in(company_id_in(company_id_in =>=>=>=> 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com order_pkg.company_id,order_pkg.company_id,order_pkg.company_id,order_pkg.company_id, 'N');'N');'N');'N'); :order.new_sales:order.new_sales:order.new_sales:order.new_sales :=:=:=:= tot_salestot_salestot_salestot_sales ('N',('N',('N',('N', company_id_incompany_id_incompany_id_incompany_id_in =>=>=>=> order_pkg.company_id);order_pkg.company_id);order_pkg.company_id);order_pkg.company_id); 3.3.3.3. 名字对应的优势 现在你已经清楚了解了这两种参数对应方式。可能你会觉得奇怪,为什么你 以前一直没有使用名字对应方法。名字对应方法有两种特点: 1、名字对应是自我文档记录的。 当你使用了名字对应方法,你就会清楚看到形参的名字,如果形参的名字是 自我解释型的,那么对于程序的调用者来说,就很清楚它们的目的。并且你可以 很清楚看出哪些是输入参数,哪些是输出参数,从而了解整个程序的数据流向(这 是相当重要的一个方面)。 2、名字对应方法让你能够灵活地进行参数定义。 首先你可以把参数按照任意顺序排列(当然,这并不意味着你就可以随机型 地排列它们)。对于较复杂的应用,有时候要求一大堆参数,而有的参数是有默 认值的。使用名字对应方法的话,你就可以清楚看出哪些参数是用到的,而哪些 是使用了默认值的。 让我们来看看下面这些程序的首部: PROCEDURE business_as_usual ( advertising_budget_in INNUMBER , contributions_inout INOUTNUMBER , merge_and_purge_on_in INDATEDEFAULTSYSDATE , obscene_ceo_bonus_out OUTNUMBER , cut_corners_in IN VARCHAR2 DEFAULT'WHENEVER POSSIBLE'); 对于 business_as_usual 过程来说,最少的参数个数是 3(可以不算带有默 认值的输入参数)。 我可以使用名字对应方法来调用这个程序,需要 4个或 5个参数,因为最后 一个输入参数具有默认值。 你至少需要两个变量来存储 OUT和INOUT参数。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 根据以上的参数列表,有如下几种调用方式: DECLARE l_ceo_payoff NUMBER; l_lobbying_dollars NUMBER:= 100000; BEGIN /* All positional notation */ business_as_usual (50000000 , l_lobbying_dollars ,SYSDATE + 20 , l_ceo_payoff ,'PAYOFFOSHA' ); 1、完全使用位置对应的方式,如果实参仅仅是满足最少参数的需求,那么 这个调用就比较难理解: business_as_usual (50000000 , l_lobbying_dollars ,SYSDATE + 20 , l_ceo_payoff ); 2、所有参数据使用名字对应,而且保持了原始形参的次序。所以我可以认 为对 business_as_usual 的调用是自我归档的: business_as_usual (advertising_budget_in => 50000000 , contributions_inout => l_lobbying_dollars , merge_and_purge_on_in => SYSDATE , obscene_ceo_bonus_out => l_ceo_payoff , cut_corners_in => 'DISBANDOSHA' ); 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 3、跳过所有具有默认值的输入参数,这也是名字对应方法的一个特点: business_as_usual (advertising_budget_in => 50000000 , contributions_inout => l_lobbying_dollars , obscene_ceo_bonus_out => l_ceo_payoff ); 4、使用名字对应方法,但是把参数的顺序打乱了: business_as_usual (obscene_ceo_bonus_out => l_ceo_payoff , merge_and_purge_on_in => SYSDATE , advertising_budget_in => 50000000 , contributions_inout => l_lobbying_dollars ); 5、混合使用两种方法,但是你必须从位置对应开始,而且一旦你使用了名 字对应,就不能回退到位置对应方式了。 business_as_usual (50000000 , l_lobbying_dollars , merge_and_purge_on_in => SYSDATE , obscene_ceo_bonus_out => l_ceo_payoff ); 正如你所见,在PL/SQL 中传递参数有许多灵活的方法。作为一个一般准则, 名字对应是最好的方式,它能够让你的程序更易读,更易于维护。你要做的是仅 仅花点时间查找并写清楚参数的名字。 4.1.4.5 NOCOPY NOCOPY NOCOPY NOCOPY 参数模式提示 从Oracle8i 开始,PL/SQL 就提供了参数定义的可选项:NOCOPY 子句。 NOCOPY 是一个给编译器的提示,意味着你希望 PL/SQL 引擎如何传递 OUT 和INOUT模式参数。要理解 NOCOPY 和它的影响,我们就先来回顾一下在 PL/SQL 中,参数是如何通过引用或数值两种方式传递的。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 1、引用方式 当一个实参通过引用方式传递,那么就说明一个指向实参的指针被传递给相 应的形参。实参和形参都是引用的形式,都指向存储实际数值得内存区域。 2、数值方式 当一个实参是通过数值传递的,那么这个实参的数值就会拷贝给相应的形 参。如果程序能够顺利结束,那么形参的数值又会拷贝回给实参。如果中途程序 发生错误,那么改变了的数值就不会拷贝给实参。 在PL/SQL 中,如果没有使用 NOCOPY 提示的话,参数传递将遵循以下规 则: 参数模式 传递方式 IN 引用 OUT 数值 INOUT 数值 We can infer from these definitions and rules that when a large data structure (such as a collection, a record, or an instance of an object type) is passed as an OUT or INOUT parameter, that structure will be passed by value, and your application could experience performance and memory degradation as a result of all this copying. The NOCOPY hint is a way for you to attempt to avoid this. The syntax of this feature is as follows: 通过以上规则,我们可以注意到,如果程序的输出或输入输出参数的数据类 型是比较复杂的(比如说一个集合,一条记录,甚至一个对象的实例),那么在 参数传递的时候,这些参数的数值都将会被拷贝。那么你的程序无疑会消耗可观 的资源。所以,使用 NOCOPY 提示就能让你避免这种情况。语法如下所示: parameter_nameparameter_nameparameter_nameparameter_name [[[[ININININ |||| ININININOUTOUTOUTOUT |||| OUTOUTOUTOUT |||| ININININOUTOUTOUTOUTNOCOPYNOCOPYNOCOPYNOCOPY |||| OUTOUTOUTOUT NOCOPYNOCOPYNOCOPYNOCOPY]]]] parameter_datatypeparameter_datatypeparameter_datatypeparameter_datatype 你只能对 OUT或INOUT模式的参数使用 NOCOPY 提示,下面是一个范 例,它对其 INOUT参数(数组类型)都使用了 NOCOPY 提示。 PROCEDURE analyze_results ( date_in INDATE, 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com values INOUTNOCOPY numbers_varray, validity_flags INOUTNOCOPY validity_rectype ); 这里有两点关于 NOCOPY 的意见你应该放到脑子里: The corresponding actual parameter for an OUT parameter under the NOCOPY hint is set to NULL whenever the subprogram containing the OUT parameter is called. 1、任何时候,一个含有 OUT参数的子程序被调用,那么相应的实参,如 果是对应于被赋予 NOCOPY 的形参的话,就会被置为 NULL。 2、NOCOPY 只是一个提示,并不是一个命令。这就意味着编译器会做自 行判读,是否能够满足你的对参数 NOCOPY 的要求。下面的章节将会阐述 NOCOPY 的各种限制。 1.1.1.1. NOCOPY NOCOPY NOCOPY NOCOPY 的限制 在某些情况下,PL/SQL 引擎可能会忽略 NOCOPY 提示,而是采用传统的 数值拷贝传递方式来传递 OUT或INOUT参数。这些情况可能包括: 1)实参是一个联合数组(associative array,参看第二章内容)中的一个元 素时。 你可以对整个联合数组设定 NOCOPY,但是不能对其中的一个独立元素做 这样的设置。建议可以这样来做:把这个数据结构(单个元素)拷贝给一个独立 变量(不管是标量还是矢量的),然后把这个变量作为一个 NOCOPY 参数传递。 使用这种方法,至少你不用拷贝整个数据结构。 2)某些对实参有限制的情况下。 一些限制可能会使 NOCOPY 暗示失效。这些包括对一个数字型变量进行矢 量定义以及 NOT NULL 限制。但是你能够传递一个尺寸受限制的字符串型变量。 3)实参和形参都是记录型的数据结构。 其中一个参数或者两个参数都是用了%ROWTYPE 或%TYPE 来声明,而且 在相对应的域上所加的限制是不一致的。 4)在某些情况下,传递实参时,PL/SQL 引擎一定要进行隐式地数据类型 转换。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 我们建议这样做:最好把要进行数据类型转换的变量进行显式地转换,然后 再把转换后的变量传递出去。 5)使用了 NOCOPY 提示的子程序被外部或远程的过程调用。 在这种情况下,PL/SQL 总是用值传递的方式传递实参。 2.2.2.2. NOCOPY NOCOPY NOCOPY NOCOPY 的影响 根据你的应用,NOCOPY 能够提高你应用的性能。但是正如你所预料的那 样,收益也是由代价的,如果这些使用 NOCOPY 参数的程序因未捕捉的异常而 终止的话,你是不能信任那些 NOCOPY 实参里面的值的。 为何不能“信任”?让我们回顾一下 PL/SQL 在遇到异常终止程序时是如何 处理参数的。假设我给我的 calculate_totals 存储过程传递一个 INOUT纪录, PL/SQL 引擎就会先为这个实参创建一个拷贝,然后在程序的运行过程中,对这 个拷贝的内容进行实时的更新,最后在程序正常结束时,把这个拷贝的值传递给 实参,从而调用这个过程的程序就能够得到改变后的值。但是如果过程 calculate_totals 在运行过程中遇到没有被处理的异常,非正常结束的话,那么 调用这个过程的程序就能够确定实参的值没有被改变。 但是,当使用了 NOCOPY 之后,这种确定性就消失了。形参的值的任何改 变都回直接影响到实参。比如说,我传递给 calculate_totals 一个参数(集合类 型),内含有 10000 条记录,并且对这些记录进行处理。如果当处理到第 5000 条记录时,发生了一个致命的错误,而且传播到了 calculate_totals 程序的控制 范围之外,那么我的实参(集合),仅仅只有一般被处理。 一般而言,你应该清晰地使用 NOCOPY 提示。只有在你确定程序性能和参 数传递有必然关系的时,那么就可以考虑使用 NOCOPY 提示,同时也要做好对 付异常引发潜在问题的准备。 4.1.4.6 默认值(DefaultDefaultDefaultDefault ValueValueValueValue) 如果你看了前面的例子,你就能够给输入参数设置一个默认值。如果一个输 入参数有默认值,那么在调用这个程序时,可以不必包含这个参数。同样,如果 一个输入参数在程序被调用时没有被包含在参数列表里,那么它就会被赋予默认 值。当然,对于一个 INOUT类型的参数,你是必须在调用程序时把它包含进来 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 的。 输入参数默认值的工作原理其实和声明变量的默认值是一样的。这里有两种 方式来设置默认值,一种是关键字“DEFAULT”,另一种就是直接用赋值符号 “:=”,如下例子代码所示: PROCEDURE astrology_reading (sign_in IN VARCHAR2 := 'LIBRA', born_at_in INDATEDEFAULTSYSDATE)IS 通过使用默认值,你可以使用不同数目的参数来调用程序。调用程序时,没 有列在参数列表中的参数,就会被赋予事先设定的默认值。以下有几种调用过程 astrology_reading 的方法: BEGIN astrology_readingastrology_readingastrology_readingastrology_reading ('SCORPIO',('SCORPIO',('SCORPIO',('SCORPIO', TO_DATETO_DATETO_DATETO_DATE('12-24-2001('12-24-2001('12-24-2001('12-24-2001 17:56:10',17:56:10',17:56:10',17:56:10', 'MM-DD-YYYY'MM-DD-YYYY'MM-DD-YYYY'MM-DD-YYYY HH24:MI:SS');HH24:MI:SS');HH24:MI:SS');HH24:MI:SS'); astrology_readingastrology_readingastrology_readingastrology_reading ('SCORPIO');('SCORPIO');('SCORPIO');('SCORPIO'); astrology_reading;astrology_reading;astrology_reading;astrology_reading; END; 第一种方法显式地设置了两个参数的值。在第二种应用中,只有第一个参数 被包括进来,所以参数“born_at_in”将会被设置为默认值(当天日期和时间)。 在第三个调用中,没有参数被设置,我们不能使用圆括号,那么在过程体中使用 的都是默认值。 如果你想跳过第一个参数,让其使用默认值,而对第二个参数赋值的话,那 么你就必须使用“名字引用方式”,显式地写出形参的名称,然后使用“=>”符 号,指向实参,如下所示: BEGIN astrology_readingastrology_readingastrology_readingastrology_reading (((( born_at_inborn_at_inborn_at_inborn_at_in =>=>=>=> TO_DATETO_DATETO_DATETO_DATE('12-24-2001('12-24-2001('12-24-2001('12-24-2001 17:56:10',17:56:10',17:56:10',17:56:10', 'MM-DD-YYYY'MM-DD-YYYY'MM-DD-YYYY'MM-DD-YYYY HH24:MI:SS');HH24:MI:SS');HH24:MI:SS');HH24:MI:SS'); END; 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 4.1.5 本地模块 一个本地模块就是一个定义在某个 PL/SQL 块(可以是匿名的)的声明段中 的过程或函数。这种模块之所以被称为“本地”,是因为它在父模块中被定义, 而且不能被父模块以外的 PL/SQL 块所调用。 下图显示了这种“本地化模块”的调用情况: 定义一个过程或函数的本地模块,其语法与创建一个独立模块是一样的。 比如,下面这个匿名块就声明了一个本地模块。 DECLAREDECLAREDECLAREDECLARE PROCEDUREPROCEDUREPROCEDUREPROCEDURE show_dateshow_dateshow_dateshow_date (date_in(date_in(date_in(date_in ININININDATE)DATE)DATE)DATE)ISISISIS BEGINBEGINBEGINBEGIN DBMS_OUTPUT.PUT_LINEDBMS_OUTPUT.PUT_LINEDBMS_OUTPUT.PUT_LINEDBMS_OUTPUT.PUT_LINE(TO_CHAR(TO_CHAR(TO_CHAR(TO_CHAR(date_in,(date_in,(date_in,(date_in, 'Month'Month'Month'Month DD,DD,DD,DD, YYYY');YYYY');YYYY');YYYY'); END;END;END;END; BEGINBEGINBEGINBEGIN ............ END;END;END;END; 在声明段中,本地模块必须位于所有的声明语句之后。在你写第一 个 FUNCTION 或PROCEDURE 关键字之前,你应该声明你的变量,游标,异常, 类型,表以及其他。 下面一节就探讨了使用本地模块的优势,并且提供了一系列例子: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 4.1.5.1 本地模块化的优点 关于使用本地模块,主要有以下两个理由: 1、对于重复使用的代码,可以有效地减小模块的尺寸。 2、使用本地化模块最普遍的动机是——你可以在下面的例子中看到它的功 效。减小代码量能够使软件的质量得到提高,也减小了软件的维护费用。当你需 要做一些更改时,你只需要在某个地方的本地化模块里改就行了,而且你可以通 过父模块立即感受到变更带来的作用。 3、增强你的代码的可读性。 4、即使你不需要再一个模块中重复一段代码,你依旧可能需要把一组有关 联的语句提取出来,并把它们打包成一个本地模块。这样做可以较容易地跟踪父 模块主体的逻辑主线。 下面的一小节将显示这些优点: 1.1.1.1. 减小代码量 下面我们来看一个简单的减小代码量的例子。过程“calc_percentages”从 sales_pkg 中获得数字型数值,然后根据总的销售量计算每个销售类所占的百分 比,然后把结果进行格式化输出。下面的例子,你会看到仅仅有三个计算,但是 一般的生产实际中,会有比较多的计算。 PROCEDUREPROCEDUREPROCEDUREPROCEDURE calc_percentagescalc_percentagescalc_percentagescalc_percentages (tot_sales_in(tot_sales_in(tot_sales_in(tot_sales_in ININININNUMBER)NUMBER)NUMBER)NUMBER) ISISISIS l_profilel_profilel_profilel_profile sales_descriptors%ROWTYPE;sales_descriptors%ROWTYPE;sales_descriptors%ROWTYPE;sales_descriptors%ROWTYPE; BEGINBEGINBEGINBEGIN l_profile.food_sales_stgl_profile.food_sales_stgl_profile.food_sales_stgl_profile.food_sales_stg :=:=:=:= TO_CHARTO_CHARTO_CHARTO_CHAR((sales_pkg.food_sales((sales_pkg.food_sales((sales_pkg.food_sales((sales_pkg.food_sales //// tot_sales_intot_sales_intot_sales_intot_sales_in ))))**** 100,100,100,100, '$999,999');'$999,999');'$999,999');'$999,999'); l_profile.service_sales_stgl_profile.service_sales_stgl_profile.service_sales_stgl_profile.service_sales_stg :=:=:=:= TO_CHARTO_CHARTO_CHARTO_CHAR((sales_pkg.service_sales((sales_pkg.service_sales((sales_pkg.service_sales((sales_pkg.service_sales //// tot_sales_intot_sales_intot_sales_intot_sales_in ))))**** 100,100,100,100, '$999,999');'$999,999');'$999,999');'$999,999'); l_profile.toy_sales_stgl_profile.toy_sales_stgl_profile.toy_sales_stgl_profile.toy_sales_stg :=:=:=:= TO_CHARTO_CHARTO_CHARTO_CHAR((sales_pkg.toy_sales((sales_pkg.toy_sales((sales_pkg.toy_sales((sales_pkg.toy_sales //// tot_sales_intot_sales_intot_sales_intot_sales_in ))))**** 100,100,100,100, 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com '$999,999');'$999,999');'$999,999');'$999,999'); END;END;END;END; 这一段代码花费了很长的一段时间来写,我们可以发现有不少重复的部分。 如果我想变换一下输出格式,那么所有独立的计算表达式都要改变。 With local modules, I can concentrate all the common, repeated code into a single function, which is then called repeatedly in calc_percentages. The local module version of this procedure is shown here: 使用本地模块,我可以把所有普通,重复的代码放到一个单独函数中去,在 这里我们称之为“calc_percentages”。那么使用本地模块的版本如下所示: PROCEDUREPROCEDUREPROCEDUREPROCEDURE calc_percentagescalc_percentagescalc_percentagescalc_percentages (tot_sales_in(tot_sales_in(tot_sales_in(tot_sales_in ININININNUMBER)NUMBER)NUMBER)NUMBER) ISISISIS l_profilel_profilel_profilel_profile sales_descriptors%ROWTYPE;sales_descriptors%ROWTYPE;sales_descriptors%ROWTYPE;sales_descriptors%ROWTYPE; /*/*/*/* DefineDefineDefineDefine aaaa functionfunctionfunctionfunction rightrightrightright insideinsideinsideinside thethethethe procedure!procedure!procedure!procedure! */*/*/*/ FUNCTIONFUNCTIONFUNCTIONFUNCTION pct_stgpct_stgpct_stgpct_stg (val_in(val_in(val_in(val_in ININININNUMBER)NUMBER)NUMBER)NUMBER)RETURNRETURNRETURNRETURN VARCHAR2VARCHAR2VARCHAR2VARCHAR2 ISISISIS BEGINBEGINBEGINBEGIN RETURNRETURNRETURNRETURNTO_CHARTO_CHARTO_CHARTO_CHAR((val_in/tot_sales_in((val_in/tot_sales_in((val_in/tot_sales_in((val_in/tot_sales_in ))))**** 100,100,100,100, '$999,999');'$999,999');'$999,999');'$999,999'); END;END;END;END; BEGINBEGINBEGINBEGIN l_profile.food_sales_stgl_profile.food_sales_stgl_profile.food_sales_stgl_profile.food_sales_stg :=:=:=:= pct_stgpct_stgpct_stgpct_stg (sales_pkg.food_sales);(sales_pkg.food_sales);(sales_pkg.food_sales);(sales_pkg.food_sales); l_profile.service_sales_stgl_profile.service_sales_stgl_profile.service_sales_stgl_profile.service_sales_stg :=:=:=:= pct_stgpct_stgpct_stgpct_stg (sales_pkg.service_sales);(sales_pkg.service_sales);(sales_pkg.service_sales);(sales_pkg.service_sales); l_profile.toy_sales_stgl_profile.toy_sales_stgl_profile.toy_sales_stgl_profile.toy_sales_stg :=:=:=:= pct_stgpct_stgpct_stgpct_stg (sales_pkg.toy_sales);(sales_pkg.toy_sales);(sales_pkg.toy_sales);(sales_pkg.toy_sales); END;END;END;END; All of the complexities of the calculation, from the division by tot_sales_in to the multiplication by 100 to the formatting with TO_CHAR, have been transferred to the function pct_stg. This function is defined in the declaration section of the procedure. By calling this function from within the body of calc_percentages, the executable statements of the procedure are much more readable and maintainable. Now, if the formula for the calculation changes in any way, I make the change just once in the function and it takes effect in all 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com the assignments. 所有复杂的运算,包括求百分比和格式化结果都交给一个函数进行单独处 理,这个函数被定义在过程的声明段中。通过在 calc_percentages 主体中调用 这个函数,过程的执行语句变得更易读,以及更易维护。现在,如果结果的格式 需要做任何改变,我只需要在声明段对本地模块进行更改,就能在全局产生效果。 17.5.1.2 Improving readability 2.2.2.2. 提高程度的可读性 使用本地模块可以极大地提高程序代码的可读性和可维护性,因为本地模块 让你的代码更加符合由上至下或是逐步求精的设计方法。你也可以使用相同的技 术来分析或优化已存在的代码,使之能够更具有可读性。 使用本地代码的最终结果是让你的执行段极大地简化。通过保持执行段的轻 量化,你将会发现对于程序逻辑的理解变得更容易了。 建议:在这里,我强烈建议在你的 PL/SQL 块中,执行段不要超过 60 行(刚 好是一个屏幕或一页所能显示的文本数量)。这听起来有些疯狂,不过只要你认 真执行了本节所讲的原则和技术,那么你会发现这是不难实现而且有巨大优点 的。 假设我有一系列的 WHILE 循环(其中一些还是相互嵌套的),而且这些循 环体中还包含了一系列的复杂计算和逻辑分支。就算有外部的注释,它还是很难 理解和跟踪。特别是给定结构的 END IF 或END LOOP 语句与 IF 或LOOP 语句 不在同一页上。 相对而言,如果你把这些相关联的语句抽取出来,然后把它们放置到一个或 多个本地模块中去,然后再在程序主体中调用它们,那么这个程序将会有很好的 自述性。下面的一个例子就给给出了这样的典型场景: PROCEDURE assign_workload (department_in IN emp.deptno%TYPE) IS CURSOR emps_in_dept_cur (department_in IN emp.deptno%TYPE) IS SELECT*FROM emp WHERE deptno = department_in; 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com PROCEDURE assign_next_open_caseassign_next_open_caseassign_next_open_caseassign_next_open_case (emp_id_in INNUMBER, case_out OUTNUMBER) IS BEGIN... full implementation ...END; FUNCTION next_appointmentnext_appointmentnext_appointmentnext_appointment (case_id_in INNUMBER) RETURNDATE IS BEGIN... full implementation ...END; PROCEDURE schedule_caseschedule_caseschedule_caseschedule_case (case_in INNUMBER, date_in INDATE) IS BEGIN... full implementation ...END; BEGIN/* main */ FOR emp_rec IN emps_in_dept_cur (department_in) LOOP IF analysis.caseload (emp_rec.emp_id) < analysis.avg_cases (department_in); THEN assign_next_open_case (emp_rec.emp_id, case#); schedule_case (case#, next_appointment (case#)); ENDIF; ENDLOOP END assign_workload; 我们可以看到过程 assign_workload 有三个本地模块: assign_next_open_caseassign_next_open_caseassign_next_open_caseassign_next_open_case next_appointmentnext_appointmentnext_appointmentnext_appointment 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com schedule_caseschedule_caseschedule_caseschedule_case 它还依赖于两个已存在的包程序(这两个都能够容易地插入到这个程序中 去):analysis.caseloadanalysis.caseloadanalysis.caseloadanalysis.caseload 和 analysis.avg_casesanalysis.avg_casesanalysis.avg_casesanalysis.avg_cases。要明白整个主程序的逻辑 主线,并不需要对隐含在本地模块后面的逻辑进行了解。我只需要通过读取这些 本地模块的名字就能够明白它们大致的功能,从而读通整个主程序。甚至在没有 注释的情况下,一般的程序员也能对上述程序理解得八九不离十。当然,前提是 你必须为每一个模块和程序取上一个有意义的名字。 4.1.5.2 本地模块的作用范围 本地模块只能在定义它们的模块内调用。相比较而言,包模块能够被整个包 里的程序调用,而且如果包模块在包定义中被声明的话,它还能在你的应用的任 何地方被调用。 你应该把本地模块封装起来,而在当前程序以外就不能调用它们了。 4.1.5.3 使用本地模块把你的代码进行整合 一般来说,当一个程序超过 20 或30 行时,我们就应该考虑一下应该把它 组装成一个本地模块。这些可以帮助我们从一个较高的层次来理解我们的需求, 并且把由上至下和逐步求精的设计方法体现在代码上。最后,通过把代码进行模 块化并组织在一个单独程序中,可以大大地增强这段代码的重用性。 当然,对于一些功能性,独立很强的模块,我们建议你把这些模块从本地范 围中抽取出来,形成一个包级别的模块。使用这种方法,可以减少本地过程的嵌 套深度。一般的原则是尽可能地用一个元素的功能来定义它,这样就很自然地使 用到了本地模块。 I hope that as you read this, a program you have written comes to mind. Perhaps you can go back and consolidate some repetitive code, clean up the logic, and make the program actually understandable to another human being. Don't fight the urge. Go ahead, and modularize your code. 如果要更进一步聚合一些重复性代码,就需要清理逻辑,不要急功近利。更 高层次地模块化代码,可参看下一节“包”。 4.2 包 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 包是继过程和函数之后第三重类型的带名 PL/SQL 块。它们是 PL/SQL 中非 常有用的特性,它们提供了扩展语言的机制。 4.2.1 为什么要使用包 包有以下一些优点: 1、更易于升级和维护应用 对于一个 PL/SQL 应用来说,衡量其质量的一个重要指标就是维护费用。通 过对数据进行封装,对功能性相近的逻辑进行组合,以及使用“包驱动”设计, 从而减少了一个应用中的缺陷。 2、提高整个应用的性能 通过使用包,你能够提高代码的性能。通过缓存静态数据,持续性包数据能 够极大地增强查询的反应时间,并且避免了对相同信息的重复性查询。Oracle 的内存管理同样对代码的调用进行了优化,而这一切都可以写在包里。 3、减少代码的重编译 如果你往下看,你会发现包程序一般包含两个部分:规范和主体。外部程序 (不是定义在包里面)只能够调用在包规范中列举的程序。如果你改变了或重编 译了包主体,那些外部程序并不会失效。对于复杂逻辑的庞大程序来说,减少代 码重编译的数量是非常重要的。 从概念上讲,包是很简单的。难得是如何应用到实际中去。作为第一步,我 们先来看看一个简单的包是如何实现的,并且体会一下使用包的优势。 4.2.2 一个简单的包 我们先来看看一个简单的例子,读取一个雇员的全名,简单来说就是把一个 雇员的 first name 和last name 拼接起来(相同的应用还有拼接地区号,客户号 等等一大堆乱七八糟的号)。 如果你不知道包的概念,你可能会这样写,如下: CREATEORREPLACEPROCEDURE process_employee ( employee_id_in IN employee.employee_id%TYPE) IS l_fullname VARCHAR2(100); 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com BEGIN SELECT last_name || ',' || first_name INTO l_fullname FROM employee WHERE employee_id = employee_id_in; ... END; 让我们来看看这段代码的隐患: 1、 在过程中,定义了一个变量 l_fullname,类型为 VARCHAR2(100), 这是一个定长的变量(没有用到%type 方式来声明),如果以后字段的 长度发生变化,就有可能出现长度不够的情况。 2、 其次,这里使用了“硬代码”来设置组成完整名称的方法。但是, 如果下个星期老板对你说,我希望完整名字组成形式为:last_name+ 空格+first_name,你就只得老老实实那整个过程浏览一遍,然后在正确 地方修改原代码。 3、 最后,这是一个看起来经常使用的查询,这样的 SQL 冗余会使应用 程序的性能下降,而且妨碍进行下一步的优化。 下面,我们将用一个包的形式,把上述的功能模块组织起来。 首先,我们来看一下包规范(具体的语法我们放在后面再讲): 1 CREATEORREPLACEPACKAGE employee_pkg 2 AS 3 SUBTYPE fullname_t IS VARCHAR2 (200); 4 5 FUNCTION fullname ( 6 last_in employee.last_name%TYPE, 7 first_in employee.first_name%TYPE) 8 RETURN fullname_t; 9 10 FUNCTION fullname ( 11 employee_id_in IN employee.employee_id%TYPE) 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 12 RETURN fullname_t; 13 END employee_pkg; 首先,我在第三行那里定义了一个字类型:fullname_tfullname_tfullname_tfullname_t ,它的最大长度是 200 个字符,但是我们可以很容易修改它。 接下来,我声明了两个 fullname 函数。它们的名字是一样的,但是参数列 表不一样,所以系统还是认为它们是不同的函数。需要注意的是,通过包规范, 我们是不能看到这两个函数的内部实现细节。你很快就可以看到这样做的好处。 在阐述这个包的实现细节之前,我们先来看看,一般情况下是如何应用这些 个包元素的: DECLARE l_name employee_pkg.fullname_t; employee_id_in employee.employee_id%TYPE := 1; BEGIN l_name := employee_pkg.fullname (employee_id_in); ... END; 我首先使用了新的数据类型来声明一个变量,然后很简单地调用相应的函 数。组成全名的方式和 SQL 查询已经从我的应用中分离出来,放到了一个独立 的“容器”中去。所以看起来,这段应用代码显得简短和干净。如果我想修改全 名的组成方式或者是存放全名的数据类型的长度,我只需要到包规范或包主体中 去修改代码,然后重编译就可以了。 下面我们来看一下这个包的主体的实现细节: 1 CREATEORREPLACEPACKAGEBODY employee_pkg 2 AS 3 FUNCTION fullname ( 4 last_in employee.last_name%TYPE, 5 first_in employee.first_name%TYPE 6 ) 7 RETURN fullname_t 8 IS 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 9 BEGIN 10 RETURN last_in || ',' || first_in; 11 END; 12 13 FUNCTION fullname (employee_id_in IN employee.employee_id%TYPE) 14 RETURN fullname_t 15 IS 16 retval fullname_t; 17 BEGIN 18 SELECT fullname (last_name, first_name) INTO retval 19 FROM employee 20 WHERE employee_id = employee_id_in; 21 22 RETURN retval; 23 EXCEPTION 24 WHENNO_DATA_FOUNDTHENRETURNNULL; 25 26 WHENTOO_MANY_ROWSTHEN errpkg.record_and_stop; 27 END; 28 END employee_pkg; 3-11 行是一个封装了组成全名方法的函数,而 12-22 行显示了一个最典型 的单行查询,而且在查询中还使用了前面所定义的函数。 现在,我的老板突然有一天叫我把全名的组成方式转变成“first_space_last” 的形式,我只需要花费 5 秒钟来修改employee_pkg.fullname 的实现代码就行了, 而老板也会很满意地走开。 所以,到此为止,我想你应该对包这种 PL/SQL 块的形式有了一个新的认识。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 4.2.3 数据作用域 在这里,我们简单地谈一下有关包里数据的权限和作用域,如下图所示: 这里,我们可以得到以下一些结论: 1、外部程序是不能跨越从外到内的边界的。也就是说,外部程序是不能够 引用或调用在包主体内部定义的任何元素——它们对于包外是私有和不可见的。 2、在包规放中定义的元素(也就是在图中打上了“Public”的数据),是能 够刮越内部和外部的边界。这些元素能够被外部程序所调用,也能够被包内的(私 有的)程序所调用或引用;当然,它们也能够调用或引用任何包里的元素。 3、但是,包的公共元素只为包内部提供了一条路径。从这个角度看来,包 规范扮演者一个包的控制机的角色。 如果你发现一个私有对象(比如说一个模块或一个游标)必须修改成公共的, 只需要很简单地把这个元素加入到包规范中来,然后重编译。这样,它对于包的 外部就是可视的。 4.2.4 创建包的规则 表面上看,包的创建很简单,你可能只需要花费一点点的时间就能够掌握创 建所有包元素的语法和规则。但是,你可能需要花费数个星期甚至更多才能掌握 所有包结构的细微差别和微妙之处。在这一节,我们将重点阐述在创建包结构时 需要注意的一些规则,然后我们再看一下创建包结构的环境。 要创建一个包,首先肯定要创建一个包规范,然后就是一个包的主体。你必 须决定哪些元素放在规范中,而哪些隐藏在包主体里面。你也必须包含一段让 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com Oracle 知道如何初始化的代码。 4.2.4.1 包规范 一个包的规范列出了在包里可调用的所有元素,并且给开发者提供了所有这 些元素的说明信息。一个开发者可以不用去了解包主体中的实现细节,就能够通 过包规范的说明来正确调用所列出的元素。 在编写包规范时,有如下一些规则需要大家记住: 1、你可以在包级别上定义任何一种数据类型,比如说数字,异常,自定义 类型和集合。一般来说,你应该在包规范中避免声明变量,尽管常量总是“安全 的”。你不能在包规范或主体中,声明游标变量(就是根据 REF CURSOR 类型 定义的变量)。游标变量在会话级别中是不允许持久化的。 2、你能够定义任何一种数据结构类型,比如说一个集合类型,一个记录类 型,一个 REF CURSOR 类型。 3、你可以在包规范中声明过程或函数,但是你只能书写这些程序的首部(也 就是在 IS 或AS 关键字前面的东西)。 4、你可以在包规范中包含显式游标。显式游标有两种形式:一是把 SQL 查询作为游标的一部分,并且书写在规范中;二是把 SQL 查询“隐藏”在包主 体中,并且在游标的声明中仅提供一个 RETURN 子句。 5、如果你在包规范中定义了任何函数或过程或没有查询部分的游标,你都 必须在包主体部分提供实现它们的代码。 6、你可以在包规范中包含一条 AUTHID 语句,它决定了这个包的所有者能 引用那些数据。 7、在包的结束语句中,一般要加上包名(虽然它是可选的):END my_package; 下面是一个非常简单的包,演示了以上的这些原则: CREATEORREPLACEPACKAGE my_pkg AUTHIDCURRENT_USER IS -- 下面定义两个常量,不过我都给它们 -- 取了一个有意义的名字从而模糊它们的值 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com c_chocolate CONSTANTPLS_INTEGER:= 16; c_strawberry CONSTANTPLS_INTEGER:= 29; -- 定义了内嵌表(nested table)类型 TYPE codes_nt ISTABLEOFINTEGER; -- 根据上面的类型定义了一张内嵌表 my_favorites codes_nt; -- 一个 REFCURSOR,返回记录类型 TYPE fav_info_rct ISREFCURSORRETURN favorites%ROWTYPE; -- favorite information from that list. --定义了一个过程,其输入参数就是 --上面定义的类型。 PROCEDURE show_favorites (list_in IN codes_nt); --定义了一个函数,返回 favorites 表里的数据 FUNCTION most_popular RETURN fav_info_rct; END favorites_pkg; --给包的结束语句附上包名作为标签 正如你看到的那样,一个包规范,在结构上,本质上是和一个 PL/SQL 块的 声明段没有区别的。只有一个不同的地方就是,这个包规范不含任何实现代码。 4.2.4.2 包主体 一个包主体包含了所有实现包规范中定义元素的代码。不过,包主体并不是 必须的。只要当以下任意一个条件满足时,包主体才是必须的: 1、包规范中包含了一个具有 RETURN 子句的游标定义。那么你就必须在 包主体中定义这个 SELECT 语句。 2、这个包规范中包含了一个过程或函数的定义。你就必须在包主体中实现 这些模块。 3、如果你想在包主体的初始化段中执行代码:包规范不支持执行段(包含 BEGIN….END 的可执行语句组),你只能在包主体中做。 从结构上说,包主体和过程定义是非常接近的,这里有一些针对包主体的特 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 殊规则: 1)包主体包含了声明,执行和异常段。如果有一个初始化段的话,声明段 就可以为空。 2)像初始化段这样的可执行段:它会在包的初始化时执行。如果你设置了 初始化段,你就必须在包的底部设置异常处理段,而该段能够处理在初始化段发 生的异常。 3)一个包主体可能包含以下组合:只有一个声明段;只有一个执行段和异 常段;或声明段,执行段,和异常段。 4)在包主体中,你不能包含 AUTHID 子句。 5)对于包主体来说,针对数据定义的限制和包规范是一样的。 You can include an optional package name label after the END statement of the package, as in: 6)你也可以在包主体的结束语句后面加上包名作为结束标签:END my_package; 这里是上述 favorites_pkg 包的主体实现代码: CREATEORREPLACEPACKAGEBODY favorites_pkg IS --一个私有变量 g_most_popular PLS_INTEGER:= c_strawberry; --过程的实现代码 PROCEDURE show_favorites (list_in IN codes_nt) IS BEGIN FOR indx IN list_in.FIRST .. list_in.L* LOOP DBMS_OUTPUT.put_line (list_in (indx)); ENDLOOP; END show_favorites; 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com -- 函数的实现代码 FUNCTION most_popular RETURN fav_info_rct IS retval fav_info_rct; null_cv fav_info_rct; BEGIN OPEN retval FOR SELECT* FROM favorites WHERE code = g_most_popular; RETURN retval; EXCEPTION WHENNO_DATA_FOUNDTHENRETURN null_cv; END most_popular; END favorites_pkg; -- 包主体的结束标签 4.2.4.3 初始化包 报能够包含数据结构,而且对于整个会话期间,数据是持久的。当你的会话 第一次打开一个包时,Oracle 就会初始化这个包,这个过程包含以下几个步骤: 1)初始化报级别的数据(比如说数字型变量或一个字符串型常量)。 2)把默认值赋给相关的变量和常量。 3)调用包的初始化段,执行代码,完成前述的步骤。 Oracle 会对每一个会话执行一次以上步骤(换句话说,就是在对这个进行 “第一次接触”的时候执行)。 一个包的初始化段包含了所有在 BEGIN 关键字和包结束语句之间的代码。 下面是一个示范例子,通过这个例子,我们可以看到初始化段的大概位置: CREATEORREPLACEPACKAGEBODY favorites_pkg IS g_most_popular PLS_INTEGER; 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com PROCEDURE show_favorites (list_in IN codes_nt) ...END; FUNCTION most_popular RETURN fav_info_rct ...END; PROCEDURE analyze_favorites (year_in ININTEGER)...END; -- 以下是初始化段 BEGIN g_most_popular := c_chocolate; analyze_favorites (EXTRACT(YEARFROMSYSDATE)); END favorites_pkg; 初始化是一个非常强大的机制:PL/SQL 自动检测这些代码什么时候运行。 你不必显式地执行这些语句,而且你能够确定这些语句只会执行一次。为什么你 要使用一个初始化段?以下的章节将会对这一问题做解释: 1.1.1.1. 执行复杂的初始化逻辑 当然,你可以在声明段就对变量设置默认值,但是这样做的话,可能会有一 些潜在的问题: 1)如果设置默认值的过程是一个很复杂的逻辑,而且对于普通的赋值表达 式,较难触发。 2)如果默认值的赋值产生了一个异常,那么这个异常就无法在包中捕获。 使用初始化段来给变量赋默认值和传统的默认值赋值相比有许多优点。对于 这一点,你可以有充分的灵活性来定义,扩展甚至记录你的初始化步骤,而且, 一旦一个异常发生,你还可以在初始化段中对其进行处理。 2.2.2.2. 缓存静态会话数据 在你的包中包含一个初始化段的最大优点就是在你整个会话声明周期中,存 储静态信息。如果数据值没有改变,那么为何要重复执行相同的查询或数据处理 呢? 此外,如果你想确定在你的会话开始时,有哪些信息被提取出来,那么初始 化段就是一个理想的,自动管理的方法。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 当然,对于存储静态会话数据,这里又有一个经典的矛盾:内存和 CPU。 如果要把数据存放在包变量中,那么你在提高查询性能的同时,也消耗了相当的 内存。我们设想一下,如果这里有 1000 个会话发生,那么就有 1000 份缓存数 据的拷贝,消耗的内存还是相当可观的。 3.3.3.3. 当初始化时避免辩解效应 在一个初始化段中,一定要避免在给全局数据设定初始值时,影响了其他包 的数据(不管这个数据在其他包的什么地方)。这是为了防止一些意向不到的异 常产生,以及对容易对维护人员造成的混淆。要让初始化段的代码集中在当前包。 需要记住的是,一旦这个包被“接触”,那么初始化段中的代码就会被执行,你 也不希望当你的用户发呆时,而程序包却在执行一些复杂的,耗费高昂的,而且 是分配给应用中其他不同包的运算。 我们来看看以下这段代码: CREATEORREPLACEPACKAGEBODY company IS BEGIN //初始化段更新了全局变量 max_salary,但是这个变量 //是employee_pkg 包的,这是会产生严重问题。 SELECTSUM(salary) INTO employee_pkg.max_salary FROM employee; END company; 如果你发现你的初始化要求无法用初始化段来解决的话,我们建议你,把这 些初始化语句组合成一个过程(比如叫做“init_enviroment”之类的),放在包 中。然后在会话中调用这个过程。 4.4.4.4. 当初始化失败时 初始化一个包包含了以下几个步骤:声明数据,赋予默认值,运行初始化段。 如果在初始化期间发生一个异常或错误的话,怎么办呢?如果包在初始化过程中 出错,Oracle 就会把这个包标记为“已初始化”,然后在整个会话中就不再执行 这些初始化代码。我们可以使用以下的包程序来观察这一过程: 包规范如下: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com create or replace package Show_valerr is -- Purpose : 演示初始化段的异常 -- Public function and procedure declarations function get_val return varchar2; end Show_valerr; 包主体如下: create or replace package body Show_valerr is val varchar2(1) := 'ABC'; --私有变量设置默认值,带有明显错误 function get_val return varchar2 is begin --这个函数仅仅执行一条返回语句 return val; end; begin -- Initialization Dbms_Output.put_line('初始化开始...'); exception when others then Dbms_Output.put_line('抓住初始化错误了...'); end Show_valerr; 现在我使用 SQL*Plus 连接上 Oracle,并且运行这个包的 get_val 函数,我 们发现: 那么换句话说,我试图在包主体的声明段中对变量 val 进行赋初始值的行为 产生了一个 VALUE_ERROR 异常。而在这个包底部的异常段却无法捕捉这个异 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 常,它只能捕获那些在初始化段执行语句产生的异常。要注意的是,如果在同一 个会话中我第二次调用这个包的函数,我就会得不到任何错误,参看以下的情形: 这是多么神奇……。事实上,“Dbms_Output.put_line('初始化开始...');”这 条语句永远就不可能执行。如果这个包的函数在第一次就执行失败,那么第二次 甚至以后的 n次都不会成功。这里我们有一个经典的“不能繁殖的错误”,而在 PL/SQL 世界中,这个错误往往就是初始化错误。 这些错误是很难被跟踪的。要避免这些错误,最好的办法就是把声明段中的 一些默认值赋值语句挪到初始化段中去,在那里,异常段才能按照正常思路处理 错误,并且进行日志记录。比如以下代码所示(上面代码的修订版): create or replace package body Show_valerr is val varchar2(1); --私有变量设置默认值,带有明显错误 function get_val return varchar2 is begin --这个函数仅仅执行一条返回语句 return val; end; begin -- Initialization Dbms_Output.put_line('初始化开始...'); val := 'ABC'; --放到初始化段中进行赋值 exception when others then DBMS_OUTPUT.PUT_LINE('抓住初始化错误了...'); 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com DBMS_OUTPUT.PUT_LINE(SQLERRM); end Show_valerr; 关闭当前的会话连接,重新连接一个会话(不然的话会出现“ORA-04068: 已 丢弃程序包 的当前状态”)的错误,然后执行这个包的 get_val 函数,得到的 结果,如下所示: 当然,对于工业化编码来说,一个标准的模板是必不可少的,而通常这个模 板往往包含了一个初始化函数来提醒开发者,下面就是一个很不错的例子: CREATEORREPLACEPACKAGEBODY IS -- Place private data structures below. -- Avoid assigning default values here. -- Instead, assign in the initialization procedure and -- verify success in the verification program. -- Place private programs here. -- Initialization section (optional) PROCEDURE initialize IS BEGIN NULL; END initialize; PROCEDURE verify_initialization (optional) -- Use this program to verify the state of the package. -- Were default values assigned properly? Were all -- necessary steps performed? 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com IS BEGIN NULL; END verify_initialization; -- Place public programs here. BEGIN initialize; verify_initialization; END ; 4.2.5 调用包元素的规则 总的来说,调用包元素没有太多好说的,一般有以下两条(凑凑字数): 1、如果你在包外调用包规范中定义的元素,必须使用这样的形式: package_name.element_name。 2、当你在包中调用包规范定义的元素时,就可以不必写上包名,Oracle 会 自动解析。 4.2.6 包数据的使用 包数据包括在包级别定义的变量和常量,而不是在包中某个函数或过程里的 数据。包数据的作用域不局限在某个单个程序中,而是作为包的一个全局变量。 在PL/SQL 运行构架中,包数据在整个会话的运行过程中都是持久化的(保持它 们的数值)。 如果包数据在包主体中定义,那么它也会在整个会话过程中可被包里的其他 元素使用(私有变量数据)。 如果包数据在包规范里面声明了,那么数据对于整个会话是持久的,而且对 于有EXECUTE 权限的调用程序来说,是直接连接的(也就是说可读和可修改 其中的值),这样也带来一定的潜在危险性。 如果一个包过程打开了一个游标,那么这个游标就在整个会话期间保持打开 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 状态,并且可用。所以没有必要在每一个程序中都定义游标。一个程序可以打开 另一个程序正在读取的游标。额外地,包变量能够跨越事务边界,因为它们是和 会话紧密结合在一起而不是从属于某个单独的事务。 4.2.6.1 在单个 Oracle Oracle Oracle Oracle 会话中作为全局变量 作为 SGA 结果的产物之一,包数据结构在 PL/SQL 运行环境中扮演者一个 全局变量的角色。但是要记住的是,它们仅仅在单独的一个会话中可连接和可用, 包数据并不能跨会话共享。如果你需要在不同的 Oracle 会话间进行包数据的共 享,你就要使用到 DBMS_PIPE 包或 Oracle 高级队列(有关这两方面的内容, 我们可以去浏览 Oracle 官方文档)。 在你应用程序不同模块中,你都要时刻保持 Oracle 会话的单独性。某些时 候,可能一个工具建立了一个新的数据库连接,并且执行了一个动作。如果这种 情况发生,你在第一个会话中,存储在包中的数据就会变得不可用。 就像在包中有两种不同的数据结构一样(公共数据和私有数据),这里也有 两种全局包数据,全局公共数据和全局私有数据。下面的两小节将会探讨这些包 数据的不同之处。 4.2.6.2 全局公共数据 正如前面所述,任何在包规范中定义的数据都是全局公共数据结构,也就是 说任何一个外部程序都可以连接它们。比方说,你可以在包规范中定义一个内嵌 表,然后用它来保存一些特定的雇员信息。你还可以创建一个专门用来存储常量 的包,这样在你的应用程序中很方便使用。你也可以修改这些数据,除非它们在 声明时使用了“CONSTANT”关键字。 在编程界中,全局数据是有名的“散射加农炮”。定义和使用它们时非常方 便的,但是,如果代码的结构设计得不好的话,那么它们的边界效应也是很严重 的。 虽然我们都知道,全局数据结构都是写在包规范里面的,而你也能了解这些 信息。但是,我们并不知道那些包读取或写入了这些全局数据,你也不是很确定 在你的应用中,哪些程序修改了哪些数据。 所以,我们一直推荐使用输入输出参数的形式来修改全局数据。只有通过这 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 种方式,规范里的那些数据结构才变得可靠,而且也具备了清晰的含义。当然, 在另一方面,你也可以创建一个名为全局数据结构的类型来记录真正要在整个应 用范围内使用的常量,比如说那些配置信息。 最后,你也可以把这些全局数据都放到一个包中进行管理,这是最简便的方 式。但是要注意的是,这是一种“单点重编译”的设计模式,也就是说,如果你 改变这个包的规范中的数据结构,就很有可能引起你应用中许多程序的失效。 4.2.6.3 包游标 在包数据中,一个有趣的类型就是显式游标,这已在前面阐述过。我可以在 包规范或是包主体中定义一个游标。这个游标的状态对于整个会话来说是保持 的,就像任何一个包数据一样。这就意味着,你可以在第一个程序中打开它,然 后在第二个程序中读取数据,最后在第三个程序中关闭它。这种灵活性给我们带 来了很多编程的灵活性和问题。 让我们首先看看声明一个包游标的方法,并且是如何打开、提取和关闭这些 游标的。 1.1.1.1. 声明包游标 如果你在包规范中声明了一个显式游标,你就有两种选择: 1、定义整个游标,包括查询。这和你在本地 PL/SQL 块中定义游标的方式 是一样的。 2、仅仅定义游标的首部,而不包含查询本身。在这种情况下,查询就只能 在包主体中定义。所以,你可以有效地隐藏该游标的实现过程。 如果你仅仅声明了游标的首部,你就必须给这个游标的声明加上一 个 RETURN 子句,从而指示从这个游标提取出来的数据结构。当然,这些数据元 素是由针对这个游标的 SELECT 语句来决定的,但是这个 SELECT 语句只出现 在包主体中,而不在包规范里。 RETURN 可以是由以下的数据结构所组成: 1)一个使用了%ROWTYPE 属性的记录。 2)自定义记录。 如果你在包主体中声明了一个游标,语法和你在本地 PL/SQL 块是一样的。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 这里有一个单独的包规范来显示了这两种方法(HR 模式下): CREATEORREPLACEPACKAGE emp_info IS CURSOR byempID_cur ( empID_in IN employees.EMPLOYEE_ID%TYPE ) IS SELECT* FROM employees WHEREEMPLOYEE_ID = empID_in; --完整的游标声明 CURSOR bydept_cur ( department_id_in IN employees. DEPARTMENT_ID%TYPE )RETURN employees%ROWTYPE; -- 不带 SELECT 语句的游标声明 TYPE dept_summary_rt ISRECORD( dept_id employees. DEPARTMENT_ID%TYPE, total_emp_count PLS_INTEGER, total_ salary_count PLS_INTEGER); --自定义记录数据结构 CURSOR summary_cur ( dept_id_in IN employees. DEPARTMENT_ID%TYPE )RETURN dept_summary_rt; --不带 SELECT 语句,返回前面定义的记录类型 END emp_info; 现在让我们看看包主体是如何使用这些游标的: create or replace package body emp_info is CURSOR bydept_cur(department_id_in IN employees.DEPARTMENT_ID%TYPE) RETURN employees%ROWTYPE is 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com select * from employees t where t.department_id = department_id_in; --游标 bydept_cur 的实现细节 CURSOR summary_cur(dept_id_in IN employees.DEPARTMENT_ID%TYPE) RETURN dept_summary_rt is select t.department_id, count(t.employee_id), sum(t.salary) from employees t where t.department_id = dept_id_in; --游标 summary_cur 的实现细节 end emp_info; 因为在前面的包规范中,有两个游标都带有了 RETURN 子句,所以就必须 在包主体中完成它们的定义。但是,如果它们与规范中定义的 RETURN 子句中 的数据结构不相匹配,那么就会编译出错,并且报以下错误: PLS-00323: subprogram or cursor '' is declared in a package specification and must be defined in the package body PLS-00400: different number of columns between cursor SELECT statement and return value 2.2.2.2. 使用包游标 现在,让我们来体验一下包游标的巨大优势。首先,你不需要学习任何有关 打开,提取和关闭游标的新语法(人都是比较懒的),你只要记住把包的名字加 在游标名的前面就行了。如果我想要了解某个雇员编号所对应的雇员信息,我就 可以用如下的代码获取: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 从这里你可以看到,我可以对基于一个包游标使用%ROWTYPE 来定义变 量,并且检查这个游标的属性,就像前面的章节一样。 大家要注意的是上面代码的前半部分,我首先对包游标进行了检验,如果打 开的话,就关闭它,不然的话就会发生错误。因为包游标的状态在整个会话周期 都是维持不变的,所以如果前一段代码打开了游标没有关闭的话,就有可能引起 统一会话中程序的错误,看下面的例子: 第一次远行成功,但是代码有缺陷,很明显,我忘记把游标关上了,现在我 们再看看这样再运行一次会发生什么后果: 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 由于包游标在会话期间的持久性,你必须记住以下几条规则: 1、永远不要假设包游标是关闭的。 2、永远不要假设包游标是打开的。 记住,当你使用完一个游标之后,一定要显式地关闭游标。同时,你也可能 需要在异常处理段中,加入这一逻辑(不论这么着,都要确定游标已经被关闭)。 但是有的时候,我们还是很容易忘记这些必要的步骤,那么有什么办法呢? 这里有一个建议就是创建专门的过程来执行关闭和开启游标,从而完全区分游标 之间的细微差别。 下面的一个范例就演示了这种技术: CREATEORREPLACEPACKAGE personnel IS CURSOR emps_for_dept ( deptno_in IN employees.department_id%TYPE) IS SELECT*FROM employees WHERE department_id = deptno_in; 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com PROCEDURE open_emps_for_dept( deptno_in IN employee.department_id%TYPE, close_if_open INBOOLEAN:= TRUE ); PROCEDURE close_emps_for_dept; END personnel; 在上面的代码中,我设置了一个包游标,同时给这个游标配备了打开和关闭 过程。所以,如果我想对游标里的所有行进行遍历,我就会书写以下代码: DECLARE one_emp personnel.emps_for_dept%ROWTYPE; BEGIN personnel.open_emps_for_dept (1055); LOOP EXITWHEN personnel.emps_for_dept%NOTFOUND; FETCH personnel.emps_for_dept INTO one_emp; ... ENDLOOP; personnel.close_emps_for_dept; END; 在这里,我并没有显示地开启和关闭包游标;我使用了两个相应过程来替代, 从而规范化地处理了包游标与其他程序单元的关联性。 创建包游标,并且在整个项目中都提供给项目组的成员使用,这种做法具有 很多优点。如果你没有对你的游标进行“打包”,并且提供给所有的开发人员, 那么他/她们就会各自创建自己的游标,从而导致了多种性能和维护的问题。包 游标只是一个利用包的特性封装数据结构的例子,在后面的章节中还会进行更深 一步的探讨。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 4.2.6.4 序列化包 正如我们所见,包数据可以在你的整个会话周期中存在(除非这个包被重编 译)。这是一个非常有用的特性,但是它也有一些缺点: 1、全局性数据结构的持久化,可能会产生一些意想不到边界效应。在实际 操作上,如果某个程序打开了游标而顽疾关闭的话,就可能产生“already open” 的错误。 2、如果使用了过多的包数据,那么就会消耗比较多的物理内存(包数据是 在用户内存区或用户全局区中进行管理和存放的),而且在整个会话期间是不会 被释放的。 为了能够在包中也可以对内存的使用进行管理,PL/SQL 提供了一个叫 “SERIALLY_REUSABLE”的参数。这个参数必须同时出现在包规范和包主体 中,标志这个包是作为序列化重用的。对于这样的一个包,包状态(包含了包数 据的数值,包游标的开闭状态)的持续时间就可以从原来的整个会话周期缩减到 对包中某个程序的调用。 通过以下的例子代码,我们可以观察到这个参数的效果,我在这里创建了两 个独立的程序,一个是填充一系列部门信息,另一个是显示它们。该例子在 scott 模式下运行。 create or replace package dept_info is PRAGMASERIALLY_REUSABLE; PROCEDURE fill_list; PROCEDURE show_list; end dept_info; 下面是包主体,列表被声明为一个私有的,全局性的内嵌表 create or replace package body dept_info is PRAGMASERIALLY_REUSABLE; TYPE dept_list_t ISTABLEOF dept%ROWTYPE INDEXBYBINARY_INTEGER; my_depts dept_list_t; --创建一个全局,但是私有的包级别变量 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com procedure fill_list is begin FOR rec IN(SELECT*FROM dept) LOOP my_depts(NVL(my_depts.COUNT,0) + 1) := rec; ENDLOOP; END; Procedure show_list IS BEGIN IF my_depts.COUNT = 0 THEN DBMS_OUTPUT.put_line ('** No depts to show you...'); ELSE FOR indx IN my_depts.FIRST .. my_depts.COUNT LOOP DBMS_OUTPUT.put_line(my_depts(indx).Dname); ENDLOOP; ENDIF; END show_list; end dept_info; 下面我们来看看这个参数的功能。首先,我在一个 PL/SQL 块里调用这个包 里的两个过程,我们会发现,在同一个 PL/SQL 块里调用,包数据的状态是持久 的(持续到这个调用块结束): 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 下面,我分别在两个 PL/SQL 块中调用包里的两个过程,如下所示,我们会 发现,包级别的集合变量是空的: 在使用序列化包时,以下的一些东西是需要你记住的: 1、序列化包的全局数据存储,是放在系统全局区(SGA)的,而不是用户 全局区(UGA)。这种方法允许包的工作区能够被“重用”。每一次包被调用, 它的公共变量就会被初始化为默认值或 NULL 值,而且它的初始化段也会被重新 执行。 2、为序列化包开辟的工作区数目视由当前使用该包的用户数所决定的。所 以,UGA消耗量的减少其实被 SGA 增加的开销所抵消了。最后,如果 Oracle 需要回收 SGA 中的内存以满足新的请求,它就会删除那些不再使用的工作区。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 4.2.7 何时该使用包 现在,我们已经学会了创建一个包的规则,语法和一些技术细节。现在,让 我们回过头来看看,为什么要你使用 PL/sQL 包,并且更深入地探讨一下其中的 奥妙。一般来说,包的使用包含以下几个场景: 1、封装(隐含)数据操作 和开发者直接写 SQL 语句不一样,它提供了有关这些 SQL 语句的接口。 2、避免对字面值进行硬编码 使用包可以对一些常用的字面值命名,从而避免了在独立程序中对它们硬编 码。当然,你也可以在过程或者函数中声明常量。但是,在包中定义常量的优势 就是它可以被包外的程序调用。 3、增强内建功能 一些 Oracle 自己的内建工具,比如说 UTL_FILE 和 DBMS_OUTPUT包等, 留下了很多不完善的地方。你可以在这个基础上建立自己的工具包从而增加变成 环境的功能。 4、把功能性强的逻辑模块组合在一起 如果你有一打过程和函数,它们都围绕着你应用中一个特定的方面,那么把 它们都放到一个包里有助于你的代码管理。 5、缓存会话静态数据,从而提高应用的性能 通过缓存静态数据,能够很明显地加快应用的相应速度。 下面的章节将会对以上场景做更详细的描述。 4.2.7.1 封装数据操作 和直接写 SQL 语句,你为这些 SQL 语句提供了一个接口。这也是为什么要 创建包的一个最重要的理由,虽然它还不完全被开发者所接受。 通过这种方式,开发者不再在应用里直接写 SQL 语句,他们会使用经过验 证和优化后的代码模块。比如说,一个重载了“INSERT”命令的“加”过程(ADD), 不但具有 INSERT 的功能,还有异常处理的能力。 如果你采用了这种方式,那么项目组里的其他开发者就不必清楚了解这个模 块中的实现细节。他们甚至不需要知道在 INSERT 新记录之前表中是否已经存 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 在了相同的数据。这个过程有它自己的内部逻辑,能够很容易地升级,并且更进 一步地加入“MERGE”命令的功能。 对于这种方法的最大好处就是,当你的数据结构改变时,你可以用最低的开 销来对原程序进行升级和维护。而且这种维护是非常集中的。如果用户希望使用 的数据类型改变成表或者对象类型,那么你只需要做的就是更新某个处理数据的 包,从而使整个应用都具备了处理表或对象类型的能力。 下面的例子显示了数据操作封装的优势。 在HR模式下,我们要实现这样一个功能,对某个特定部门的员工发一笔奖 金,但是这些员工至少要在公司呆了半年以上。以下是用一个独立的过程来实现 这个功能,代码如下: create or replace procedure add_bonus( dept_in IN employees.department_id%type, bonus_in INNUMBER ) is CURSOR by_dept_cur is select * from employees where department_id = dept_in; begin For rec in by_dept_cur loop If employ_bonus.eligible_for_bonus(rec) then update employees t set t.salary = rec.salary+bonus_in where t.employee_id=rec.employee_id; End If; End Loop; commit; --以下还有一些其他数据处理,这里略…… end add_bonus; 其中引用到包 employ_bonus,该包的内容为: create or replace package employ_bonus is 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com CONDITION constant number := 180; --设置颁发奖金的条件,进入公司必须大于一定的天数 function eligible_for_bonus(rec IN employees%ROWTYPE) return boolean; --判断输入的员工记录,是否满足颁发奖金的条件:进入公司的时间大于 6个月 end employ_bonus; create or replace package body employ_bonus is function eligible_for_bonus(rec IN employees%ROWTYPE) return boolean is begin if (SYSDATE- rec.hire_date) < CONDITION then return false; else return true; end if; end; begin null; end employ_bonus; 下面我们再来看另外一个例子,就是把 UPDATE 操作进行封装之后的处理: 这个发放奖金的过程,为了不和上面的重复,改名为:give_bonus,代码如 下: create or replace procedure give_bonus( in_bonus in employee_tp.t_bonus, in_dept_id in employee_tp.t_dept_id) is l_empRecTab employee_tp.t_employee_recTab; begin l_empRecTab := EMPLOYEE_QP.getEmpRec_By_deptID(in_deptID => 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com in_dept_id); For cnt in 1 .. l_empRecTab.COUNT Loop If Employ_Bonus.eligible_for_bonus(rec => l_empRecTab(cnt)) then EMPLOYEE_MP.upd_oneCol_pky(colname => 'salary', new_value_in => l_empRecTab(cnt).salary+in_bonus, employee_id_in => l_empRecTab(cnt).employee_id); end if; End Loop; --以下是其他处理代码,这里略…… end give_bonus; 从这个例子,我们可以看到,封装得更进一步了,输入参数的类型被封装在 employee_tp 包里,本地变量的类型(元素类型为 EMPLOYEES 表记录的嵌套 表类型)同样也被封装在该包中。在实现过程中,使用了判断能够发放奖金的函 数和更新数据的过程(其他相关的源代码可参考培训包中的源程序)。 总的来说,SQL 语句已经成功地从主应用程序移除了,而被个各种函数和 过程的代用所替代。这样做的话,我们可以更容易地优化 SQL 语句,并且使用 更多效率高的方式来组织代码,从而提高应用的健壮性。 当然,对于某些人来说,要做到这样一个程度,还有些困难(事实上,封装 的颗粒度需要设计的支撑)。但是,不论怎么说,还是希望把多些精力投放到程 序设计中来,才能在后面的编码阶段收获更多的效益,而你实际上并不需要改变 多少编码技术。简而言之,有以下几条建议: 1、把你所有单条查询都用函数形式封装起来,只提供一个借口。通过这种 方式,你能够确定错误处理已经执行,从而选择最好的实现方法(例如显式或隐 式游标)。 2、确认哪一张表是最常使用的,把这些数据操作都封装起来。 3、对于复杂的事务,使用包程序来进行处理。例如,假如假如一条信息引 发以下操作:插入两条数据记录,更新其他 6条,以及其他。这个时候,就可以 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 把这些逻辑关联强的操作都合并到一个过程中来。 4.2.7.2 避免对字面值得硬编码 事实上,任何应用都会有一些“魔术值”,而且这些字面值对于一个应用系 统来说是非常重要的。这些值可能是对变量的类型约束。你的用户可能会告诉你 哪些“魔术值”是永远都不会变的(比如说,ICBCSDC 这几个字基本上是不会 变的)。不过,千万不要相信这些承诺,也永远不要把它们写进代码中来。看看 以下的 IF 语句。 IF footing_difference BETWEEN 1 and 100 THEN adjust_line_item; ENDIF; IF cust_status = 'C' THEN reopen_customer; ENDIF; 如果你这样写的话,那么你就是在自找麻烦。如果你想轻松一些,你最好使 用专门的一个包来存放这些常量,如下所示: CREATEORREPLACEPACKAGE config_pkg IS closed_status CONSTANT VARCHAR2(1) := 'C'; open_status CONSTANT VARCHAR2(1) := 'O'; active_status CONSTANT VARCHAR2(1) := 'A'; inactive_status CONSTANT VARCHAR2(1) := 'I'; min_difference CONSTANTPLS_INTEGER:= 1; max_difference CONSTANTPLS_INTEGER:= 100; earliest_date CONSTANTDATE:= SYSDATE; 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com latest_date CONSTANTDATE:= ADD_MONTHS(SYSDATE, 120); END config_pkg; 如果使用了以上的包,那么前面的 IF 语句就会变成以下这种样子: IF footing_difference BETWEEN config_pkg.min_difference and config_pkg.max_difference THEN adjust_line_item; ENDIF; IF cust_status = config_pkg.closed_status THEN reopen_customer; ENDIF; 如果任何一个“魔术值”改变了,那么我只需要到“配置包”中改一下就行 了。而我不再需要改变单个的源程序。我觉得,再怎么强调这种“配置包”思想 的重要性也不为过。 还有一种方式,就是把所有的魔术值放到包主体中去,而仅仅在保规范里提 供“get”和“set”函数来获取或设置这些常量。这样做的好处就是,如果这些 常量改变了,你只需要修改包主体里面的值,而不需要对包规范以及与其相依赖 的程序进行重编译。 4.2.7.3 增强内建工具包 我们知道有一些 Oracle 自带的工具包,比如 UTL_FILE 和 DBMS_OUTPUT,有许多不完善的地方。当然,你可以花点时间在它们之上编 写你自己的包程序,从而添加一些自己需要的功能。 在这里,我就不再列出这些源代码,大家可以到培训包查找这些工具包的源 程序文件。也许你会感到有些用处。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 4.2.7.4 把逻辑关联的功能模块组合起来 如果你有一堆函数或过程来完成某一个交易,那么你最好把它们用一个包封 装起来。这样使你的代码管理更容易一些。更进一步,对于一些商业规则的实现, 这里有一些比较重要的编程指南: 1、千万不要把它们进行硬编码放到一个单独的应用组件中。 2、千万把这些规则分解到不同的,难以管理的单个程序模块中去。 在你开始创建一个应用之前,你应该先规划一系列的包来封装这些规则。有 时候,这些规则可能是一个大包的一部分,比如说表的封装包。此外,你还需要 建立一个专门放置关键规则的包,比如下面这个例子: CREATEORREPLACEPACKAGE customer_rules IS FUNCTION min_balance RETURNPLS_INTEGER;/* Toronto */ FUNCTION eligible_for_discount (customer_in IN customer%ROWTYPE) RETURNBOOLEAN; FUNCTION eligible_for_discount (customer_id_in IN customer.customer_id%TYPE) RETURNBOOLEAN; END customer_rules; 我们可以看到函数“eligible_for_discount”是隐藏在包里的,所以管理起来 很容易。此外,我还使用重载给出了两个不同的接口:一个是根据顾客的记录, 另一个是根据客户的 ID。为什么要这样做呢?因为如果已经查询了用户信息, 那么就可以使用%ROWTYPE 来进行重载,避免第二次查询。 当然,不是所有的“逻辑相关功能件”都与商业规则有联系。有的时候,我 可能需要创建一个专门处理字符串的函数集。这样我就可以把这些函数都放到一 个包里面去。 4.2.7.5 缓存静态会话数据 通过缓存静态数据,可以提高程序反应速度。你可以在几个不同的级别上进 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 行缓存。 Cache a single value, such as the name of the session (returned by the USER function). Examples: thisuser.pkg and thisuser.tst. 1、缓存单独的一个数值,比如说会话的名字。 2、缓存单条记录或一个结果集,比如说给定用户的配置信息。 3、缓存整个数值列表,比如说一个静态的,可重复查询的表。 在你获得这种技术带来的好处同时,你要记住这些数据对于每个会话来说是 相互独立的。这就意味着,如果你把表中的一行记录保存起来要消耗 20K 内存 的话,1000 个用户就至少耗掉 2M 物理内存,对于很大的数据缓存,还是需要 仔细考虑的。不过总的来说,与应用程序的效率提高相比,一两条内存的代价是 很渺小的。 4.2.8 包和对象模型 Packages are containers that allow you to group together data and code elements. Object types are containers that allow you to group together data and code elements. Do we need both? Do object types supersede packages, especially now that Oracle has added support for inheritance? When should you use a package and when should you use an object type? All very interesting and pertinent questions. 从根本上说,包和对象都是一个让你能够把数据和代码组织起来的容器胆。 但是这两个容器的作用是一样的吗?什么时候应该使用包,而什么时候应该使用 对象呢? 包和对象类型都拥有一些相同的特性: 1、它们都包含一个或多个程序或数据结构。 2、它们都由一个规范和主体组成。 但是,它们之间有一些关键的不同之处: 一个对象类型就一个数据的一个模板,你能够为一个对象类型创建多个实 例。对于每一个实例,我们都可以使用它们的属性和方法。这些实例能够存放在 数据库中。另一方面,一个包是一个“静态”(从头到尾保持单一)的结构,你 不能对其进行实例化。 源码网资料下载:www.codepub.com OracleOracleOracleOracle PL/SQL PL/SQL PL/SQL PL/SQL 开发基础 下载源码就到源码网,www.codepub.com 从Oracle9i 开始,对象类型提供了“继承”功能。这就意味着我可以在一 个对象类型的基础上声明一个子对象类型,而它继承了父类的所有属性和方法。 但是对于包来说,就没有这样的继承功能。 通过包,你可以创建私有的、或隐藏的数据以及子程序。但是,在对象类型 中,这是不被支持,里面所有的东西都是公开的,对外界是可视的。 所以,什么时候应该使用对象类型,什么时候使用包呢?首先,很少有人会 使用对象类型,而更少的人会从 Oracle 的“对象关系”模型中获益。对于大部 分开发人员来说,包始终是 PL/SQL 程序的基石。 如果你尝试试用一下对象类型,那么我的建议就是你最好把复杂的逻辑代码 都放到包里,然后在对象类型的方法里调用它们。这样做的话,在设计和实现对 象类型的时候,你享有更大的灵活性,而且你还可以在你应用的其他地方共享这 些元素。 (下载源码就到源码网,www.codepub.com)
还剩166页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 8 金币 [ 分享pdf获得金币 ] 1 人已下载

下载pdf

pdf贡献者

aiilive

贡献于2012-12-09

下载需要 8 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf