oracle中sql语句详解

Oracle PLSQL 开发基础开发基础 前前 言言 课程简介 本教材主要针对需要在平台数据上查询数据和察看代码的测试人员 要求 学员已经掌握 Oracle 的基本概念和 Oracle SQL 语言 本教材由质控办廖昊星编写 在编写过程中得到了同事的大力支持 特此 鸣谢 目目 录录 课程目标 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 课程目标课程目标 学员学完本课程之后 应该掌握如下内容 1 了解 Oracle 数据库表的结构和数据存储方式 2 能够使用 SQL 编写查询语句 获取所需的测试数据 3 能够看懂一般的 PL SQL 代码 并能找编写规范上的错误 4 能够编写简单的 PL SQL 程序 第第 1 章章 PL SQLPL SQL 语言基础语言基础 PL SQL 是 ORACLE 对标准数据库语言的扩展 ORACLE 公司已经将 PL SQL 整合到 ORACLE 服务器和其他工具中了 近几年中更多的开发人员和 DBA 开始使用 PL SQL 本文将讲述 PL SQL 基础语法 结构和组件 以及如 何设计并执行一个 PL SQL 程序 1 1 什么是什么是 PL 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 块的不同部分 1 2 1 声明部分声明部分 Declaration section 声明部分包含了变量和常量的数据类型和初始值 这个部分是由关键字 DECLARE 开始 如果不需要声明变量或常量 那么可以忽略这一部分 需要 说明的是游标的声明也在这一部分 1 2 2 执行部分执行部分 Executable section 执行部分是 PL SQL 块中的指令部分 由关键字 BEGIN 开始 所有的可执 行语句都放在这一部分 其他的 PL SQL 块也可以放在这一部分 异常处理部 分 Exception section 这一部分是可选的 在这一部分中处理异常或错误 对 异常处理的详细讨论我们在后面进行 1 2 3 异常处理部分异常处理部分 Exception section 这一部分是可选的 在这一部分中处理异常或错误 对异常处理的详细讨 论我们在后面进行 1 2 4 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 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 1 2 5 3 包包 package 包其实就是被组合在一起的相关对象的集合 当包中任何函数或存储过程 被调用 包就被加载入内存中 包中的任何函数或存储过程的子程序访问速度 将大大加快 包由两个部分组成 规范和包主体 body 规范描述变量 常量 游标 和子程序 包体完全定义子程序和游标 1 2 5 4 触发器触发器 trigger 触发器与一个表或数据库事件联系在一起的 当一个触发器事件发生时 定义在表上的触发器被触发 因为中心的 数据库开发规范 中规定不能使用 触发器 所以本教材将不作阐述 1 3 PL SQL 的数据与数据类型的数据与数据类型 1 3 1 变量变量 变量存放在内存中以获得值 能被 PL SQL 块引用 你可以把变量想象成 一个可储藏东西的容器 容器内的东西是可以改变的 1 3 1 1 声明变量声明变量 变量一般都在 PL SQL 块的声明部分声明 PL SQL 是一种强壮的类型语言 这就是说在引用变量前必须首先声明 要在执行或异常处理部分使用变量 那 么变量必须首先在声明部分进行声明 声明变量的语法如下 Variable name CONSTANT databyte NOT NULL DEFAULT expression 注意 可以在声明变量的同时给变量强制性的加上 NOT NULL 非空 约束 条件 此时变量在初始化时必须赋值 1 3 1 2 给变量赋值给变量赋值 给变量赋值有两种方式 1 直接给变量赋值 X 200 Y Y X 20 2 通过 SQL SELECT INTO 或 FETCH INTO 给变量赋值 SELECT SUM SALARY SUM SALARY 0 1 INTO TOTAL SALARY TATAL COMMISSION FROM EMPLOYEE WHERE DEPT 10 1 3 2 常量常量 常量与变量相似 但常量的值在程序内部不能改变 常量的值在定义时赋予 它的声明方式与变量相似 但必须包括关键字 CONSTANT 常量和变量都可 被定义为 SQL 和用户定义的数据类型 ZERO VALUE CONSTANT NUMBER 0 这个语句定了一个名叫 ZERO VALUE 数据类型是 NUMBER 值为 0 的常量 1 3 3 标量标量 scalar 数据类型数据类型 标量 scalar 数据类型没有内部组件 它们大致可分为以下四类 1 number 2 character 3 date time 4 boolean 1 3 3 1 数字型数字型 几种主要的数字类型 见下表 数据类型范围子类型描述 number1 0E 130 9 99E125DEC DECIMAL DOUBLE PRECISION FLOAT 存储数字值 包括整数和 浮点数 可以选择精度和 刻度方式 语法 number 缺省的 精度是 38 小数位数是 0 INTEGERIC 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 数据类型相 似 同样他也不能在 字符集之间进行转换 ROWID18 个字节与数据库 ROWID 伪列 类型相同 能够存储 一个行标示符 可以 将行标示符看作数据 库中每一行的唯一键 值 VARCHAR 2 最大长度最大长度 32767 字节字节 STRINGVARCHA R 存储可变长度的字符存储可变长度的字符 串 声明变量时需要串 声明变量时需要 附带变量的长度附带变量的长度 字符类型变量使用得最多的就是 VARCAHR2 类型 同事也是 PL SQL 程 序中使用得最频繁的数据类型 下面是两个变量的声明例子 remark varchar2 400 备注 accNo varchar2 18 18 位帐号 1 3 3 3 日期和布尔类型日期和布尔类型 数据类型范围子类型描述 BOOLEANTRUE FALSE存储逻辑值 TRUE 或 FALSE 无参 数 DATE01 01 4712 BC存储固定长的日期和时间值 日期 值中包含时间 1 4 PL SQL 的操作符的操作符 与其他程序设计语言相同 PL SQL 有一系列操作符 操作符分为下面几 类 算术操作符 关系操作符 比较操作符 逻辑操作符 1 4 1 算术操作符算术操作符 操作符操作 加 减 除 乘 乘方 1 4 2 关系操作符关系操作符 关系操作符主要用于条件判断语句或用于 where 子句中 关系操作符检查 条件和结果是否为 true 或 false 下表是 PL SQL 中的关系操作符 操作符操作 小于操作符 大于操作符 大于或等于操作符 等于操作符 不等于操作符 不等于操作符 赋值操作符 1 4 3 比较操作符比较操作符 操作符操作 IS NULL如果操作数为 NULL 返回 TRUE LIKE比较字符串值 BETWEEN验证值是否在范围之内 IN验证操作数在设定的一系列值中 1 4 4 逻辑操作符逻辑操作符 操作符操作 AND两个条件都必须满足 OR只要满足两个条件中的一个 NOT取反 1 5 PL SQL 流程控制流程控制 PL SQL 支持条件控制和循环控制结构 以下是详细的说明 1 5 1 条件控制条件控制 1 5 1 1 IF THEN 语句语句 语法 IF condition THEN Statements 1 Statements 2 END IF IF 语句判断条件 condition 是否为 TRUE 如果是 则执行 THEN 后面的 语句 如果 condition 为 false 或 NULL 则跳过 THEN 到 END IF 之间的语句 执行 END IF 后面的语句 1 5 1 2 IF THEN ELSE 语句语句 语法 IF condition THEN Statements 1 Statements 2 ELSE Statements 1 Statements 2 END IF 如果条件 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 END IF END IF 1 5 1 3 IF ELSIF ELSE THEN 语句语句 语法 IF condition1 THEN statement1 ELSIF condition2 THEN statement2 ELSIF condition3 THEN statement3 ELSE statement4 END IF 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 和 END LOOP 之间的语句将 无限次的执行 LOOP 语句的语法如下 LOOP statements END LOOP LOOP 和 END LOOP 之间的语句无限次的执行显然是不行的 那么在使 用 LOOP 语句时必须使用 EXIT 语句 强制循环结束 例如 X 100 LOOP X X 10 IF X 1000 THEN EXIT END IF END LOOP Y X 最后得到 Y 的值就是 1010 上机练习示范 如果不使用 IF THEN 语句的话 也可以使用 EXIT WHEN condition 语句 同样结束循环 如果条件 condition 为 TRUE 则结束循环 上机示范练习 1 5 2 2 WHILE LOOP 循环循环 WHILE condition LOOP 有一个条件与循环相联系 如果条件为 TRUE 则 执行循环体内的语句 如果结果为 FALSE 则结束循环 X 100 WHILE X 1000 LOOP X X 10 END LOOP Y X 上机示范 1 5 2 3 FOR LOOP 循环循环 语法 FOR counter IN REVERSE start range end range LOOP statements END LOOP 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 语句 这里只作 简单的介绍 1 5 3 1 标记程序块标记程序块 BEGIN INSERT INTO catalog VALUES EXCEPTION WHEN DUP VAL ON INDEX THEN NULL END insert but ignore dups 从上面的代码可以看出 虽然 BEGIN 开始的是一个匿名块 实际上 我们 用标签 insert but ignore dups 对它进行了命令 这对于增加程序易读性是非 常有帮助的 1 5 3 2 标记循环标记循环 BEGIN LOOP LOOP Statements1 WHEN condition1 EXIT outer loop END LOOP Statements2 WHEN condition2 exit END LOOP Statements3 END 可以用标签来标记一个循环 特别是在内嵌多层循环的时候 可以用 EXIT 加标签的形式使循环推出到想要的地方 比如上面的例子代码 如果 condition1 条件为 TRUE 的话 那么就会跳出外层循环 执行到 Statements3 上机练习示范 1 5 4 PL SQL 嵌套块嵌套块 一般来说 我们只要在 PL SQL 程序中看到 begin 关键字就可以认为是 一个 PL SQL 块的开始 在 块 里面 还可以继续书写 块 这就是嵌套块 一个典型嵌套块实例如下图所示 这是一个三层的嵌套块 用不同深浅的颜色标记了出来 嵌套块要注意的是变量 定义在最外部程序块中的变量可以在所有子块中 使用 如果在子块中定义了与外部程序块变量相同的变量名 在执行子块时将 使用子块中定义的变量 子块中定义的变量不能被父块引用 1 6 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 错误检测新的运行时异常处理器 你只需简单的 在程序中使用下面的语句 RAISE TIMEOUT ON RESOUCE 这种自定义异常抛出的方法 在记录错误日志的时候非常有用 比如当输 入参数为空的时候 我们就可以定义一个统一异常 INPUT NULL ERR 这 样每个 PL SQL 程序块都可以声明并抛出这个异常 当发生相应状况时 就会 自动进入处理模块 可以记录日志等 下面是一个订单输入系统 当库存小于订单时抛出一个 inventory too low 异常 DECLARE inventory too low EXCEPTION 其他声明语句 BEGIN IF order rec qty inventory rec qty THEN RAISE inventory too low END IF 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 WHEN others THEN Code for handing any other exception 用户必须在独立的 WHEN 子串中为每个异常设计异常处理代码 WHEN OTHERS 子串必须放置在最后面作为缺省处理器处理没有显式处理的异常 当 异常发生时 控制转到异常部分 ORACLE 查找当前异常相应的 WHEN THEN 语句 捕捉异常 THEN 之后的代码被执行 如果错误陷阱代码 只是退出相应的嵌套块 那么程序将继续执行内部块 END 后面的语句 如果没 有找到相应的异常陷阱 那么将执行 WHEN OTHERS 在异常部分 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 WHEN OTHERS THEN 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 WHEN OTHERS THEN 控制不会从 bad credit 异常转到这里 因为 bad credit 已被处理 END 当异常发生时 在块的内部没有该异常处理器时 控制将转到或传播到上 一层块的异常处理部分 BEGIN DECLARE 内部块开始 bad credit Exception BEGIN RAISE bad credit 发生异常 控制转向 EXCEPTION WHEN ZERO DIVIDE THEN 不能处理 bad credite 异常 dbms output put line divide by zero error END 结束内部块 控制不能到达这里 因为异常没有解决 异常部分 EXCEPTION WHEN OTHERS THEN 由于 bad credit 没有解决 控制将转到这里 END 1 6 3 异常传递异常传递 没有处理的异常将沿检测异常调用程序传播到外面 当异常被处理并解决 或被传递到程序的最外层 执行部分抛出的异常将首先传递到同一块的异常部 分 如果在同一块的异常部分没有处理这个异常的处理器 那么异常将会传播 到上一层的异常部分中 一直到最外层 注意 在异常部分抛出的异常将控制转到上一层的异常部分 处理异常将停止异常的传播和解决 有时用户希望在错误发生时 程序仍 然能执行一些动作 要达到这个目的 可以把希望执行的动作放在异常处理器 中 然后执行不带参数的 RAISE 语句 RAISE 语句将重新抛出出现的异常 允许他继续传播 DECLARE order too old EXCEPTION 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 子串处理 ORACLE 包含了一系列已命名的异常 这些异常都声明在 STANDARD 包中 这些内建异常在这里就不一一讲述 有兴趣的读者可以查阅有关资料 第第 2 章章 复合数据复合数据 PL SQL 有两种复合数据结构 记录和集合 记录由不同的域组成 集合 由不同的元素组成 在本文中我们将讨论记录和集合的类型 怎样定义和使用 记录和集合 2 1 PL SQL 记录记录 记录是 PL SQL 的一种复合数据结构 标量数据类型和其他数据类型只是 简单的在包一级进行预定义 但复合数据类型在使用前必须被定义 记录之所 以被称为复合数据类型是因为它由域这种由数据元素的逻辑组所组成 域可以 是标量数据类型或其他记录类型 它与 c 语言中的结构相似 记录也可以看成表中的数据行 域则相当于表 中的列 在表和虚拟表 视图或查询 中非常容易定义和使用 行或记录中的 每一列或域都可以被引用或单独赋值 也可以通过一个单独的语句引用记录所 有的域 在存储过程或函数中记录也可能有参数 2 1 1 创建记录创建记录 在 PL SQL 中有两种定义方式 显式定义和隐式定义 一旦记录被定义后 声明或创建定义类型的记录变量 然后才是使用该变量 隐式声明是在基于表 的结构或查询上使用 TYPE 属性 隐式声明是一个更强有力的工具 这是因 为这种数据变量是动态创建的 2 1 1 1 显示定义 显示定义 显式定义记录是在 PL SQL 程序块中创建记录变量之前在声明部分定义 使用 type 命令定义记录 然后在创建该记录的变量 语法如下 TYPE record type IS RECORD field definition list field definition list 是由逗号分隔的列表 域定义的语法如下 field name data type and size NOT NULL DEFAULT default value 域名必须服从与表或列的命名规则相同的命名规则 下面我们看一个例子 DELCARE TYPE stock quote rec IS RECORD symbol stock symbol TYPE bid NUMBER 10 4 ask NUMBER 10 4 volume NUMBER NOT NULL 0 exchange VARCHAR2 6 DEFAULT NASDAQ real time quote stock quote rec 声明一个叫 real time quote 的 stock quote rec 类型的变量 上面代码中的阴影部分就是这个纪录类型的域列表 域定义时的 TYPE 属性用于引用数据库中的表或视图的数据类型和大小 而在此之前程序不知道类型和大小 在上面的例子记录中的 symbol 域在编译 时将被定义为与表 stock 的 SYMBOL 列相同的数据类型和大小 当代码中要使 用来自数据库中的数据时 在变量或域定义中最好使用 TYPE 来定义 上机练习 2 1 1 2 隐式定义记录隐式定义记录 隐式定义记录中 我们不用描述记录的每一个域 这是因为我们不需要定 义记录的结构 不需要使用 TYPE 语句 相反在声明记录变量时使用 ROWTYPE 命令定义与数据库表 视图 游标有相同结构的记录 与 TYPE 命 令一样 它也是一种定义获得数据库数据记录的好方法 DECLARE accounter info accounts ROWTYPR 有一些 PL SQL 指令在使用隐式定义记录时没有使用 ROWTYPE 属性 比如游标 FOR 循环或触发器中的 old 和 new 记录 触发器在中心数据库开发 规范中不允许使用 所以本文不作阐述 DELCARE CURSOR c1 IS SELECT DISTINCT tm200001012 dqh FROM TM200001 BEGIN FOR rec1 IN c1 LOOP END LOOP 上述代码中的 rec1 就是一个隐式声明的纪录类型 虽然在声明段中没有对 它进行声明 但是在程序运行中 仍然可以把它当成一个变量来用 上机练习 2 1 2 使用纪录使用纪录 用户可以给记录赋值 将值传递给其他程序 记录作为一种复合数据结构 意味作他有两个层次可用 用户可以引用整个记录 使用 select into 或 fetch 转移所有域 也可以将整个记录传递给一个程序或将所有域的值赋给另一个记 录 在更低的层次 用户可以处理记录内单独的域 用户可以给单独的域赋值 或者在单独的域上运行布尔表达式 也可以将一个或更多的域传递给另一个程 序 2 1 2 1 引用记录引用记录 记录由域组成 访问记录中的域使用点 符号 在前面的上机练习中 我们已经接触到了这种表示方式 如 empName emp name 和 rec ename 2 1 2 2 给记录赋值给记录赋值 给记录或记录中的域赋值的方法有几种 可以使用 SELECT INTO 或 FETCH 给整个记录或单独的域赋值 可以将整个记录的值赋给其他记录 也 可以通过给每个域赋值来得到记录 以下我们通过实例讲解每一种赋值方法 1 使用 使用 SELECT INTO 使用 SELECT INTO 给记录赋值要将记录或域放在 INTO 子句中 需要注 意的是 INTO 子句中的变量与 SELECT 中列的位置相对应 上机练习 2 使用 使用 FETCH 如果 SQL 语句返回多行数据或者希望使用带参数的游标 那么就要使用游 标 这种情况下使用 FETCH 语句代替 SELECT INTO 是一个更简单 更有效 率的方法 上机练习 2 1 2 3 注意事项注意事项 使用赋值语句将整个记录复制给另一个记录是一项非常有用的技术 不过 记录必须精确地被声明为相同的类型 不能是基于两个不同的 TYPE 语句来获 得相同的结构 实例代码 第二个要注意的是 记录与纪录之间不能直接比较 比如下面的代码就是 错误的 IF emp1 emp1 too THEN 此外 还要注意的是 虽然有的纪录变量声明为 ROWTYPE 类型 但是 不能用在 INSERT 语句里面 比如说下面的 SQL 语句 Emp Info EMP ROWTYPE INSERT INTO EMP VALUES Emp Info 错误 2 2 PL SQL 集合集合 在 PL SQL 中 集合 类型和其他编程语言 如 C 里的数组很类似 集 合有以下三种类型 Index by 表 也就是所谓的 联合数组 你可以使用一个数字或字 符串作为索引来直接查找表里的数据 好比其他语言中的哈希表类型 嵌套表 可以存储任意数量的元素 以连续的数字作为下标索引 和 Oracle 中的其他表一样 你可以使用 DML 语言对其进行操纵 VARRAY 和嵌套表类似 但是存储元素的数量必须是指定的 而 且灵活性也要比嵌套表差很多 所以比较少用 Index by 表不能存储在数据库中 但嵌套表和 VARRAY 可以被存储在数 据库中 2 2 1 集合定义集合定义 2 2 1 1 Index by 表 在表 在 Oracle9i 中叫联合数组 中叫联合数组 Index by 表集合的定义语法如下 TYPE type name IS TABLE OF element type NOT NULL INDEX BY BINARY INTEGER PLS INTEGER VARCHAR2 size limit INDEX BY key type 这里面重要的关键字是 INDEX BY BINARY INTERGET 没有这个关键字 那么集合将是一个嵌套表 element type 可以是任何合法的 PL SQL 数据类型 包括 PLS INTEGER SIGNTYPE 和 BOOLEAN 其他的集合类型对数据 库的数据类型都有限制 但 Index by 表不能存储在数据库中 所以没有这些限 制 一旦定义了 index by 表 就可以向创建其他变量那样创建 index by 表的 变量 DECLARE TYPE symbol tab typ IS TABLE OF VARCHAR2 5 INDEX BY BINARY INTEGER symbol tab symbol tab typ BEGIN 2 2 1 2 嵌套表嵌套表 嵌套表非常类似于 Index by 表 创建的语法也非常相似 使用 TYPE 语 句 只是没有 INDEX BY BINARY INTEGER 子串 TYPE type name IS TABLE OF element type NOT NULL NOT NULL 这里的选项要求集合所有的元素都要有值 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 VARRAY VARRAY 或数据变量都有元素的限制 像其他集合一样 VARRAY 定义仍 然使用 TYPE 语句 但关键字 VARRAY 或 VARRYING ARRAY 告诉 ORACLE 这是一个 VARRAY 集合 TYPE type name IS VARRAY VARYING ARRAY max size OF element type NOT NULL max size 是一个整数 用于标示 VARRAY 集合拥有的最多元素数目 VARRAY 集合的元素数量可以低于 max size 但不能超过 max size element type 是一维元素的数据类型 如果 element type 是记录 那么这个记录只能使用标量数据字段 与嵌套表相似 NOT NULL 子串表示 集合中的每一个元素都必须有值 与嵌套表相似 VARRAY 能够自动为 NULL 可以使用 IS NULL 操作符进行检测 与嵌套表不同的是 当 VARRAY 存储在数据库中时与表中的其他数据存放在同一个数据块中 正象列的排序保 存在表的 SELECT 中一样 元素的顺序保存在 VARRAY 中 同样由于集合是 在线存储的 VARRAY 很适合于小型集合 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 IS TABLE OF VARCHAR2 16 my courses CourseList BEGIN my courses CourseList Econ 2010 Acct 3401 Mgmt 3100 END 上面是一个嵌套表的初始化函数 初始化的同时给表赋值 由于嵌套表对 元素的个数没有限制 所以你可以根据需要在初始化阶段给变量 my courses 赋予任意多的值 VARRAY 初始化示例 初始化示例 DECLARE 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 表 联合数组 下标是两位的整数 如果定义了 INDEX BY BINARY INTERGET 的话 可以为正也可以为负 范围是 2147483647 2147483647 在引用集合之前 对于嵌套表和 VARRAY 类型 必须初始化了才能引用其 中的元素 如下例所示 上机练习 正确的应该是 可见在初始化之后 就能够引用嵌套表和 VARRAY 了 对于 INDEX BY 表 只要对其中一个元素进行赋值 就是初始化了整个集 合变量 上机练习 2 2 2 3 给集合赋值给集合赋值 给集合赋值的方法和普通变量相类似 我们可以通过 DML SQL 语句来对 它们进行赋值 也可以使用以下形式 Collection subscript expression 对集合赋值需要注意的是 必须保证所赋的值的类型必须和定义集合的类 型相匹配 不然的话 就会出现异常 下面举一个给集合赋空值的例子 2 2 2 4 比较集合比较集合 对于比较两个集合变量是否相同 首先要确定这两个集合变量是同意类型 的 然后就得逐个比较这些集合变量中的元素个数和每一个元素的值是否相等 下面是一个错误比较的例子 不过 从 Oracle10 1 开始 这样的比较是允许的 如下图所示 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 非法 FIRST返回集合中的第一个元素的下标号 对于 VARRAY 集合始 终返回 1 LAST返回集合中最后一个元素的下标号 对于 VARRAY 返回值 始终等于 COUNT LIMIT返回 VARRY 集合的最大的元素个数 对于嵌套表和对于嵌 套表和 Index by 为 null 对 Index by 无用 NEXT 返回在元素 x 之后及紧挨着它的元素的值 如果该元素是最 后一个元素 则返回 null PRIOR 返回集合中在元素 x 之前紧挨着它的元素的值 如果该元素 是第一个元素 则返回 null TR