Oracle PL/SQL基础教程

kawaiimoon

贡献于2015-04-25

字数:0 关键词: SQL

PL/SQL PL/SQL PL/SQL PL/SQL 程序设计 尔行网络 http://www.2xnet.net 1 - 1 - 目目目目 录录录录 第一 章 PL/SQL 程序 设计 简介 ...................................................................................... 4 §1.2 SQL 与PL/SQL..................................................................................................................... 4 §1.2.1 什么是 PL/SQL?...............................................................................................................4 §1.2.1 PL/SQL 的好处 .................................................................................................................4 §1.2.2 PL/SQL 可用的 SQL 语句 ................................................................................................ 5 §1.3 运行 PL/SQL 程序 ................................................................................................................. 5 第二 章 PL/SQL 块结 构和 组成 元素 ..................................................................................6 §2.1 PL/SQL 块.............................................................................................................................. 6 §2.2 PL/SQL 结构 .......................................................................................................................... 6 §2.3 标识符 ..................................................................................................................................... 6 §2.4 PL/SQL 变量类型 ..................................................................................................................7 §2.4.1 变量类型 .............................................................................................................................7 §2.4.2 复合类型 ...........................................................................................................................9 §2.4.3 使用 %ROWTYPE...............................................................................................................11 §2.4.4 LOB 类型 *........................................................................................................................ 11 §2.4.5 Bind 变量 ......................................................................................................................... 11 §2.4.6 INDEXBYTABLES........................................................................................................ 12 §2.4.7 数据类型的转换 *.............................................................................................................13 §2.5 运算符和表达式 (数据定义 ).................................................................................................13 §2.5.1 关系运算符 .......................................................................................................................13 §2.5.2 一般运算符 .......................................................................................................................13 §2.5.3 逻辑运算符 .......................................................................................................................13 §2.6 变量赋值 ............................................................................................................................... 13 §2.6.1 字符及数字运算特点 .......................................................................................................13 §2.6.2 BOOLEAN 赋值 ..............................................................................................................13 §2.6.3 数据库赋值 .......................................................................................................................13 §2.6.4 可转换的类型赋值 ...........................................................................................................13 §2.7 变量作用范围及可见性 ....................................................................................................... 13 §2.8 注释 ....................................................................................................................................... 13 §2.9 简单例子 ............................................................................................................................... 13 §2.9.1 简单数据插入例子 .........................................................................................................13 §2.9.2 简单数据删除例子 .........................................................................................................13 第三 章 PL/SQL 流程 控制 语句 .........................................................................................13 §3.1 条件语句 ............................................................................................................................... 13 §3.2 CASE 表达式 .......................................................................................................................13 §3.3 循环 ....................................................................................................................................... 13 §3.3 标号和 GOTO.........................................................................................................................13 §3.4 NULL 语句 ............................................................................................................................. 13 第四 章 游标 的使 用 ..........................................................................................................13 尔行网络 http://www.2xnet.net 2 - 2 - §4.1 游标概念 ................................................................................................................................... 13 §4.1.1 处理显式游标 .....................................................................................................................13 §4.1.2 处理隐式游标 .....................................................................................................................13 §4.1.3 游标修改和删除操作 .......................................................................................................13 第五 章 异常 错误 处理 ...................................................................................................13 §5.1 异常处理概念 ........................................................................................................................... 13 §5.1.1 预定义的异常处理 .............................................................................................................13 §5.1.2 非预定义的异常处理 .........................................................................................................13 §5.1.3 用户自定义的异常处理 .....................................................................................................13 §5.1.4 用户定义的异常处理 .......................................................................................................13 §5.2 异常错误传播 ........................................................................................................................... 13 §5.2.1 在执行部分引发异常错误 .................................................................................................13 §5.2.2 在声明部分引发异常错误 .................................................................................................13 §5.3 异常错误处理编程 ................................................................................................................... 13 §5.4 在PL/SQL 中使用 SQLCODE,SQLERRM....................................................................13 第六 章 存储 函数 和过 程 ...................................................................................................13 §6.1 引言 ......................................................................................................................................... 13 §6.2 创建函数 ................................................................................................................................. 13 §6.3 存储过程 ................................................................................................................................. 13 §6.3.1 创建过程 ...........................................................................................................................13 §6.3.2 调用存储过程 ...................................................................................................................13 §6.3.3 开发存储过程步骤 ...........................................................................................................13 §6.3.4 与过程相关数据字典 .......................................................................................................13 第七 章 包的 创建 和应 用 ...................................................................................................13 §7.1 引言 ......................................................................................................................................... 13 §7.2 包的定义 ................................................................................................................................. 13 §7.3 包的开发步骤 ......................................................................................................................... 13 §7.4 包定义的说明 ......................................................................................................................... 13 §7.5 子程序重载 ............................................................................................................................. 13 §7.6 删除过程、函数和包 ............................................................................................................. 13 §7.7 包的管理 ................................................................................................................................. 13 第八 章 触发 器 ................................................................................................................. 13 §8.1 触发器类型 ............................................................................................................................. 13 §8.1.1 DML 触发器 ........................................................................................................................ 13 §8.1.2 替代触发器 .........................................................................................................................13 §8.1.3 系统触发器 .........................................................................................................................13 §8.2 创建触发器 ............................................................................................................................. 13 §8.2.1 触发器触发次序 .................................................................................................................13 §8.2.2 创建 DML 触发器 .............................................................................................................. 13 §8.2.3 创建替代 (Instead_of)触发器 ............................................................................................. 13 §8.2.3 创建系统事件触发器 .........................................................................................................13 尔行网络 http://www.2xnet.net 3 - 3 - §8.2.4 系统触发器事件属性 .........................................................................................................13 §8.2.5 使用触发器谓词 .................................................................................................................13 §8.2.6 重新编译触发器 .................................................................................................................13 §8.3 删除和使能触发器 ................................................................................................................. 13 §8.4 触发器和数据字典 ................................................................................................................. 13 §8.5 数据库触发器的应用举例 ................................................................................................... 13 尔行网络 http://www.2xnet.net 4 - 4 - 第一 章 PL/SQLPL/SQLPL/SQLPL/SQL 程序 设 计简 介 PL/SQL是一 种高级数据库程序设计语言,该语言专门用于在各种环境下对ORACLE 数据 库进 行访 问。 由于 该语 言集 成于 数据 库服 务器 中, 所以 PL/SQL代码 可以 对数 据进 行快 速高 效的 处理 。 除 此之 外, 可 以 在 ORACLE数据 库的 某些 客户 端工 具中 , 使 用 PL/SQL语言 也是 该语 言的 一个 特点 。本 章的 主要 内容 是讨 论引 入 PL/SQL语言 的必 要性 和该 语言 的主 要 特点 ,以 及了 解 PL/SQL语言 的重 要性 和数 据库 版本 问题 。还 要介 绍一 些贯 穿全 书的 更详 细 的高 级概 念, 并在 本章 的最 后就 我们 在本 书案 例中 使用 的数 据库 表的 若干 约定 做一 说明 。 本 章主要重点: ���� PL/SQL 概述 ���� PL/SQL 块结 构 � PL/SQL 流程 � 运算 符和 表达 式 � 游标 � 异常 处理 � 数据 库存 储过 程和 函数 � 包 � 触发 器 §§§§1111....2222 SQL SQL SQL SQL 与PL/SQLPL/SQLPL/SQLPL/SQL §§§§1111....2.12.12.12.1 什么是PL/SQL?PL/SQL?PL/SQL?PL/SQL? PL/SQL 是Procedure Language & Structured Query Language 的缩 写 。 ORACLE 的SQL 是支持ANSI(American national Standards Institute) 和ISO92 (International Standards Organization)标准的产品。PL/SQL 是对SQL 语言存储过程语言的扩展。从 ORACLE6 以后, ORACLE 的RDBMS 附带了 PL/SQL。它现 在已 经成 为一 种过 程处 理语 言, 简称 PL/SQL。 目 前的 PL/SQL包括 两部 分, 一 部 分是 数据 库引 擎部 分; 另 一部 分是 可 嵌入 到许 多产 品 (如 C语言 , JAVA 语言 等) 工 具 中的 独立 引擎 。 可 以 将这 两部 分称 为: 数 据库 PL/SQL 和工具 PL/SQL。两者的编程非常相似。都具有编程结构、语法和逻辑机制。 工具 PL/SQL 另外 还增 加了 用于 支持 工具 (如 ORACLE Forms)的 句法 ,如 :在 窗体 上设 置按 钮等 。本 章主 要介 绍数 据库 PL/SQL 内容 。 §§§§1111....2.12.12.12.1 PLPLPLPL////SQL SQL SQL SQL 的 好处 §1.2.1.11.2.1.11.2.1.11.2.1.1 有 利于客户 ////服 务器环境应用的运 行 对于客户 /服务器环境来说,真正的瓶颈是网络上。无论网络多快,只要客户端与服务 器进 行大 量的 数据 交换 。应 用运 行的 效率 自然 就回 受到 影响 。如 果使用 PL/SQL 进行编程, 将这种 具有 大量 数据 处理 的应 用放 在服 务器 端来 执行 。自 然就 省去 了数 据在 网上 的传 输时 间。 尔行网络 http://www.2xnet.net 5 - 5 - §1.2.1.21.2.1.21.2.1.21.2.1.2 适 合于客户环境 PL/SQL 由于分为数据库 PL/SQL 部分和工具 PL/SQL。对于客户端来说, PL/SQL 可 以嵌 套到 相应 的工 具中 , 客 户 端程 序可 以执 行本 地包含 PL/SQL 部分 , 也 可 以向 服务发 SQL 命令 或激 活服 务器 端的 PL/SQL 程序 运行 。 §1.2.21.2.21.2.21.2.2 PL/SQLPL/SQLPL/SQLPL/SQL 可用的SQL SQL SQL SQL 语句 PL/SQL 是ORACLE系统 的核 心语 言, 现在 ORACLE 的许 多部 件都 是由 PL/SQL 写成。 在PL/SQL 中可 以使 用的 SQL 语句 有: INSERT,UPDATE,DELETE,SELECTINTO,COMMIT,ROLLBACK,SAVEPOINT。 提示:在PL/SQL PL/SQL PL/SQL PL/SQL 中只能用SQL SQL SQL SQL 语句中的DMLDMLDMLDML 部分,不能用DDLDDLDDLDDL 部分,如果要在 PL/SQL PL/SQL PL/SQL PL/SQL 中使用DDL(DDL(DDL(DDL(如CREATECREATECREATECREATE tabletabletabletable 等))))的 话,只能以动态的 方 式来使用 。 � ORACLE 的PL/SQL 组件在对 PL/SQL 程序进行解释时,同时对在其所使用的 表名 、列 名及 数据 类型 进行 检查 。 � PL/SQL 可以在 SQL*PLUS 中使 用。 � PL/SQL 可以 在高 级语 言中 使用 。 � PL/SQL 可以 在ORACLE 的开发 工具 中使 用。 � 其它 开发 工具 也可 以调用 PL/SQL 编写 的过 程和 函数 , 如 Power Builder 等都 可 以 调用 服务 器端的 PL/SQL 过程 。 §§§§1111....3333 运行PLPLPLPL////SQL SQL SQL SQL 程序 PL/SQL 程序的运行是通过ORACLE 中的一个引擎来进行的。这个引擎可能在 ORACLE 的服 务器 端, 也可 能在 ORACLE 应用 开发 的客 户端 。引 擎执行 PL/SQL 中的 过 程性 语句 ,然 后将 SQL 语句 发送 给数 据库 服务 器来 执行 。再 将结 果返 回给 执行 端。 尔行网络 http://www.2xnet.net 6 - 6 - 第二 章 PLPLPLPL////SQL SQL SQL SQL 块结 构 和组 成 元 素 §§§§2.12.12.12.1 PLPLPLPL////SQL SQL SQL SQL 块 PL/SQL PL/SQL PL/SQL PL/SQL 程序 由三 个块 组成 ,即 声明 部分 、执 行部 分、 异常 处理 部分 。 PL/SQL PL/SQL PL/SQL PL/SQL 块 的结构如下: DECLAREDECLAREDECLAREDECLARE /* 声明 部分 : 在此 声明 PL/SQL 用到 的变 量 ,类型 及游 标, 以 及 局部 的存 储过 程和 函数 */ BEGINBEGINBEGINBEGIN /* 执行 部分 : 过程及 SQL 语句 , 即程 序的 主要 部分 */ EXCEPTIONEXCEPTIONEXCEPTIONEXCEPTION /* 执行 异常 部分 : 错误 处理 */ END;END;END;END; 其中 执行 部分 是必 须的 。 PL/SQL PL/SQL PL/SQL PL/SQL 块 可以分为三类: 1. 无名 块: 动态 构造 ,只 能执 行一 次。 2. 子程 序: 存 储在 数据 库中 的存 储过 程、 函 数及 包等 。 当 在 数据 库上 建立 好后 可以 在其 它 程序 中调 用它 们。 3. 触发 器: 当数 据库 发生 操作 时, 会触 发一 些事 件, 从而 自动 执行 相应 的程 序。 §§§§2.22.22.22.2 PLPLPLPL////SQL SQL SQL SQL 结构 � PL/SQL 块中 可以 包含 子块 ; � 子块 可以 位于 PL/SQL 中的 任何 部分 ; � 子块 也即 PL/SQL 中的 一条 命令 ; §§§§2.32.32.32.3 标识 符 PL/SQL 程序 设计 中的 标识 符定 义与 SQL 的标 识符 定义 的要 求相 同。 要求 和限 制有 : � 标识 符名 不能 超过 30字符 ; � 第一 个字 符必 须为 字母 ; � 不分 大小 写; � 不能 用 ’-‘(减号 ); � 不能是 SQL 保留 字。 提示:::: 一 般不要把变量名声 明 与表中字段 名 完全一样 ,,,,如 果这样可能得到 不 正确的结果 .... 尔行网络 http://www.2xnet.net 7 - 7 - 例如 :下 面的 例子 将会 删除 所有 的纪 录, 而不是 KING 的记 录; DECLARE Ename varchar2(20) :=’KING’; BEGIN DELETEFROM emp WHERE ename=ename; END; 变量 命名在 PL/SQL 中有 特别 的讲 究, 建 议 在系 统的 设计 阶段 就要 求所 有编 程人 员共 同 遵守 一定 的要 求, 使得 整个 系统 的文 档在 规范 上达 到要 求。 下面 是建 议的 命名 方法 : §§§§2222....4444 PL/PL/PL/PL/SQLSQLSQLSQL 变量 类型 在前 面的 介绍 中, 有 系 统的 数据 类型 , 也 可以 自定 义数 据类 型。 下 表是 ORACLE 类型 和PL/SQL 中的 变量 类型 的合 法使 用列 表: §2.4.12.4.12.4.12.4.1 变 量类型 在ORACLE8i 中可 以使 用的 变量 类型 有: 标识 符 命名 规则 例子 程序 变量 V_name V_name 程序 常量 C_Name C_company_name 游标 变量 Name_cursor Emp_cursor 异常 标识 E_name E_too_many 表类 型 Name_table_type Emp_record_type 表 Name_table Emp 记录 类型 Name_record Emp_record SQL*Plus 替代 变量 P_name P_sal 绑定 变量 G_name G_year_sal 类型 子类 说明范围ORACLE 限制 CHAR Character String Rowid Nchar 定长字符串 民族语言字符集 0�32767 可选 ,确省 =1 2000 VARCHAR2 Varchar, String NVARCHAR2 可变字符串 民族语言字符集 0�32767 4000 4000 BINARY_INT EGER 带符号整数,为整数计算优 化性能 NUMBER(p, s) Dec Double precision Integer Int Numeric Real Small int 小数 ,NUMBER 的子类型 高精度实数 整数 ,NUMBER 的子类型 整数 ,NUMBER 的子类型 与NUMBER 等价 与NUMBER 等价 整数 , 比integer 小 尔行网络 http://www.2xnet.net 8 - 8 - 例1. 插入 一条 记录 并显 示; DECLARE Row_id UROWID; info VARCHAR2(40); BEGIN INSERTINTO dept VALUES(90, ‘SERVICE’,‘BEIJING’) RETURNING rowid, dname||’:’||to_char(deptno)||’:’||loc INTO row_id, info; DBMS_OUTPUT.PUT_LINE(‘ROWID:’||row_id); DBMS_OUTPUT.PUT_LINE(info); END; 其中 : RETURNING 子句 用于 检索 INSERT 语句 中所 影响 的数 据行 数,当 INSERT 语句 使 用VALUES 子句插入数据时, RETURNING 字句还可将列表达式、 ROWID 和REF 值返 回到 输出 变量 中。 在使用 RETURNING 子句 是应 注意 以下 几点 限制 : 1.不能 并行 DML 语句 和远 程对 象一 起使 用; 2.不能 检索 LONG 类型 信息 ; 3.当通 过视 图向 基表 中插 入数 据时 ,只 能与 单基 表视 图一 起使 用。 例2.2.2.2. 修改 一条 记录 并显 示 DECLARE Row_id UROWID; info VARCHAR2(40); BEGIN UPDATE dept SET deptno=80 WHERE DNAME=‘SERVICE’ RETURNING rowid, dname||’:’||to_char(deptno)||’:’||loc INTO row_id, info; DBMS_OUTPUT.PUT_LINE(‘ROWID:’||row_id); DBMS_OUTPUT.PUT_LINE(info); END; 其中:RETURNING 子句用于检索被修改行信息:当UPDATE 语句修改单行数据时, RETURNING 子句 可以 检索 被修 改行的 ROWID 和REF值, 以 及行 中被 修改 列的 列表 达 式, LONG 变长字符串 0- >2147483647 32,767 字节 DATE 日期型 公元前 4712年1月1 日至 公 元 后 4712 年 12 月31 日 BOOLEAN 布尔型 TRUE, FALSE,NULL 不使用 ROWID 存放数据库行号 UROWID 通用行标识符,字符类型 尔行网络 http://www.2xnet.net 9 - 9 - 并可将他们存储到PL/SQL 变量或复合变量中;当UPDATE 语句修改多行数据时, RETURNING 子句 可以 将被 修改 行的 ROWID 和REF 值, 以 及 列表 达式 值返 回到 复合 变 量 数组 中。在 UPDATE 中使用 RETURNING 子句 的限 制与 INSERT 语句 中对 RETURNING 子句 的限 制相 同。 例3.3.3.3. 删除 一条 记录 并显 示 DECLARE Row_id UROWID; info VARCHAR2(40); BEGIN DELETE dept WHERE DNAME=‘SERVICE’ RETURNING rowid, dname||’:’||to_char(deptno)||’:’||loc INTO row_id, info; DBMS_OUTPUT.PUT_LINE(‘ROWID:’||row_id); DBMS_OUTPUT.PUT_LINE(info); END; 其中:RETURNING 子句用于检索被修改行信息:当UPDATE 语句修改单行数据时, RETURNING 子句 可以 检索 被修 改行的 ROWID 和REF值, 以 及行 中被 修改 列的 列表 达 式, 并可将他们存储到PL/SQL 变量或复合变量中;当UPDATE 语句修改多行数据时, RETURNING 子句 可以 将被 修改 行的 ROWID 和REF 值, 以 及 列表 达式 值返 回到 复合 变 量 数组 中。在 UPDATE 中使用 RETURNING 子句 的限 制与 INSERT 语句 中对 RETURNING 子句 的限 制相 同。 §§§§2222....4.24.24.24.2 复 合类型 ORACLE 在PL/SQL 中除了提供象前面介绍的各种类型外 ,还提供一种称为复合类型 的类 型 ---记录 和表 . §§§§2222....4.2.14.2.14.2.14.2.1 记 录类型 记录类型是把逻辑相关的数据作为一个单元存储起来,它必须包括至少一个标量型或 RECORD 数据类 型的 成员 ,称 作 PL/SQLRECORD 的域 (FIELD),其作 用是 存放 互不相 同但 逻辑 相关 的信 息。 定 义记录类型语法如下 :::: TYPE record_type ISRECORD( Field1 type1 [NOTNULL][:= exp1 ], Field2 type2 [NOTNULL][:= exp2 ], ...... Fieldn typen [NOTNULL][:= expn ]); 例4444 : 尔行网络 http://www.2xnet.net 10 - 10 - DECLARE TYPE test_rec ISRECORD( Code VARCHAR2(10), Name VARCHAR2(30) NOTNULL:=’a book’); V_book test_rec; BEGIN V_book.code :=’123’; V_book.name :=’C++ Programming’; DBMS_OUTPUT.PUT_LINE(v_book.code||v_book.name); END; 可以用 SELECT 语句对 记录 变量 进行 赋值 ,只要保 证记 录字 段与 查询 结果 列表 中的字 段相 配即 可。 §§§§2222....4.2.24.2.24.2.24.2.2 使用%TYPE%TYPE%TYPE%TYPE 定义 一个 变量 , 其 数 据类 型与 已经 定义 的某 个数 据变 量的 类型 相同 , 或 者 与数 据库 表的 某 个 列的 数据 类型 相同 ,这 时可 以使 用 %TYPE。 使用 %TYPE 特性 的优 点在 于: � 所引 用的 数据 库列 的数 据类 型可 以不 必知 道; � 所引 用的 数据 库列 的数 据类 型可 以实 时改 变。 例5555: DECLARE -- 用%TYPE 类型 定义 与表 相配 的字 段 TYPE t_Record ISRECORD( T_no emp.empno%TYPE, T_name emp.ename%TYPE, T_sal emp.sal%TYPE); -- 声明 接收 数据 的变 量 v_emp t_Record; BEGIN SELECT empno, ename, sal INTO v_emp FROM emp WHERE empno=7788; DBMS_OUTPUT.PUT_LINE (TO_CHAR(v_emp.t_no)||v_emp.t_name||TO_CHAR(v_emp.t_sal)); END; 例6666: DECLARE v_empno emp.empno%TYPE :=&empno; Type r_record is record ( v_name emp.ename%TYPE, v_sal emp.sal%TYPE, v_date emp.hiredate%TYPE); Rec r_record; 尔行网络 http://www.2xnet.net 11 - 11 - BEGIN SELECT ename, sal, hiredate INTO Rec FROM emp WHERE empno=v_empno; DBMS_OUTPUT.PUT_LINE(Rec.v_name||'---'||Rec.v_sal||'--'||Rec.v_date); END; §§§§2222....4.34.34.34.3 使用%ROWTYPE%ROWTYPE%ROWTYPE%ROWTYPE PL/SQL 提供 %ROWTYPE 操作 符 , 返回 一 个 记 录 类 型 , 其数 据类型和数据库表的数 据结 构相 一致 。 使用 %ROWTYPE 特性 的优 点在 于: � 所引 用的 数据 库中 列的 个数 和数 据类 型可 以不 必知 道; � 所引 用的 数据 库中 列的 个数 和数 据类 型可 以实 时改 变。 例7777: DECLARE v_empno emp.empno%TYPE :=&empno; rec emp%ROWTYPE; BEGIN SELECT*INTO rec FROM emp WHERE empno=v_empno; DBMS_OUTPUT.PUT_LINE('姓名 :'||rec.ename||'工资 :'||rec.sal||'工作 时间 :'||rec.hiredate); END; §2.4.42.4.42.4.42.4.4 LOB LOB LOB LOB 类型**** ORACLE 提供了 LOB(Large OBject)类型 ,用 于存 储大 的数 据对 象的 类型 。 ORACLE 目前 主要 支持 BFILE,BLOB,CLOB 及NCLOB 类型 。 BFILEBFILEBFILEBFILE(Movie)(Movie)(Movie)(Movie) 存放 大的 二进 制数 据对 象, 这 些 数据 文件 不放 在数 据库 里, 而 是 放在 操作 系统 的某 个 目 录里 ,数 据库 的表 里只 存放 文件 的目 录。 BLOB(Photo)BLOB(Photo)BLOB(Photo)BLOB(Photo) 存储大的二进制数据类型。变量存储大的二进制对象的位置。大二进制对象的大小 <=4GB。 CLOB(Book)CLOB(Book)CLOB(Book)CLOB(Book) 存储 大的 字符 数据 类型 。每 个变 量存 储大 字符 对象 的位 置, 该位 置指 到大 字符 数据 块。 大字 符对 象的 大小 <=4GB。 NCLOBNCLOBNCLOBNCLOB 存储 大的 NCHAR字符 数据 类型 。 每个 变量 存储 大字 符对 象的 位置 ,该 位置 指到 大字 符 数据 块。 大字 符对 象的 大小 <=4GB。 §2.4.52.4.52.4.52.4.5 BindBindBindBind 变量 绑定 变量 是在 主机 环境 中定 义的 变量 。 在 PL/SQL 程序 中可 以使 用绑 定变 量作 为他 们 将 要使 用的 其它 变量 。为 了在 PL/SQL 环境 中声 明绑 定变 量, 使用 命令 VARIABLE。例 如: 尔行网络 http://www.2xnet.net 12 - 12 - VARIABLE return_code NUMBER VARIABLE return_msg VARCHAR2(20) 可以 通过 SQL*Plus 命令 中的 PRINT 显示 绑定 变量 的值 。例 如: PRINT return_code PRINT return_msg 例7777: VARIABLE result NUMBER BEGIN SELECT(sal*12)+nvl(comm, 0) INTO:result FROM emp WHERE empno=7788; END; PRINT result §2.4.62.4.62.4.62.4.6 INDEXINDEXINDEXINDEXBYBYBYBYTABLESTABLESTABLESTABLES 包括 两个 基本 成分 : .数 据处 理类 型为 BINARY_INTEGER 主键 ; .标 量或 记录 数据 类型 的列 . TYPE type_name ISTABLEOF {column_type | variable%TYPE | table.column%TYPE }[NOTNULL] | table%ROWTYPE [INDEXBYBINARY_INTEGER]; 例8888: DECLARE TYPE dept_table_type ISTABLEOF dept%ROWTYPE INDEXBYBINARY_INTEGER; my_dname_table dept_table_type; 方法 描述 EXISTS(n) Return TRUE if the nth element in a PL/SQL table exists; COUNT Returns the number of elements that a PL/SQL table currently contains; FIRST LAST Return the first and last (smallest and lastest) index numbers in a PL/SQL table. Returns NULL if the PL/SQL table is empty. PRIOR(n) Returns the index number that precedes index n in a PL/SQL table; NEXT(N) Returns the index number that succeeds index n in a PL/SQL table; TRIMTRIM removes one element from the end of a PL/SQL table. TRIM(n) removes n element from the end of a PL/SQL table. DELETEDELETE removes all elements from a PL/SQL table. DELETE(n) removes the nth elements from a PL/SQL table. DELETE(m, n) removes all elements in the range m to n from a PL/SQL table. 尔行网络 http://www.2xnet.net 13 - 13 - v_count number(2) :=4; BEGIN FOR int IN 1 .. v_count LOOP SELECT*INTO my_dname_table(int) FROM dept WHERE deptno=int*10; ENDLOOP; FOR int IN my_dname_table.FIRST .. my_dname_table.LAST LOOP DBMS_OUTPUT.PUT_LINE(‘Department number: ‘||my_dname_table(int).deptno); DBMS_OUTPUT.PUT_LINE(‘Department name: ‘|| my_dname_table(int).dname); ENDLOOP; END; §2.4.2.4.2.4.2.4.7777 数 据类型的转换 **** 隐式 类型 转换 §§§§2222....5555 运算 符和 表达 式 ((((数据 定义 )))) §2.5.12.5.12.5.12.5.1 关 系运算符 §2.5.22.5.22.5.22.5.2 一 般运算符 BIN_INTCHARDATELONGNUMBERPLS_INTUROWID VARCHAR2 BIN_INT       CHAR   DATE        LONG         NUMBER      RAW        UROWID         VARCHAR2   运算 符 意义 = 等于 <> ,!= , ~= , ^= 不等 于 < 小于 > 大于 <= 小于 或等 于 >= 大于 或等 于 运算 符 意义 + 加号 - 减号 * 乘号 / 除号 := 赋值 号 => 关系 号 尔行网络 http://www.2xnet.net 14 - 14 - §2.5.32.5.32.5.32.5.3 逻 辑运算符 §§§§2222....6666 变量 赋值 在PL/SQL 编程 中, 变量 赋值 是一 个值 得注 意的 地方 ,它 的语 法如 下: variable := expression ; variable 是一个 PL/SQL 变量 , expression 是一个 PL/SQL 表达 式 . §2.6.12.6.12.6.12.6.1 字 符及数字运算特点 空值 加数 字仍 是空 值: NULL + < 数字 > = NULL 空值 加( 连接 )字 符, 结果 为字 符: NULL || <字符 串 > = < 字符 串 > §2.6.22.6.22.6.22.6.2 BOOLEANBOOLEANBOOLEANBOOLEAN 赋值 布尔 值只有 TRUE, FALSE 及NULL 三个 值。 如: DECLARE done BOOLEAN; /* the following statements are legal: */ BEGIN done := FALSE; WHILENOT done LOOP Null; ENDLOOP; END; §2.6.32.6.32.6.32.6.3 数 据库赋值 数据 库赋 值是 通过 SELECT语句 来完 成的 , 每 次 执行 SELECT语句 就赋 值一 次, 一 般 要求 被赋 值的 变量 与 SELECT中的 列名 要一 一对 应。 如: 例9999: .. 范围 运算 符 || 字符 连接 符 运算 符 意义 ISNULL 是空 值 BETWEEN 介于 两者 之间 IN 在一 列值 中间 AND 逻辑 与 OR 逻辑 或 NOT 取返 ,如ISNOTNULL,NOTIN 尔行网络 http://www.2xnet.net 15 - 15 - DECLARE emp_id emp.empno%TYPE :=7788; emp_name emp.ename%TYPE; wages emp.sal%TYPE; BEGIN SELECT ename, NVL(sal,0) + NVL(comm,0) INTO emp_name, wages FROM emp WHERE empno = emp_id; Dbms_output.put_line(emp_name||’----‘||to_char(wages)); END; 提 示:不能将 SELECTSELECTSELECTSELECT语 句中的列赋值给布尔变量。 §2.6.42.6.42.6.42.6.4 可 转换的类型赋值 � CHARCHARCHARCHAR 转 换为 NUMBERNUMBERNUMBERNUMBER: 使用 TO_NUMBER 函数 来完 成字 符到 数字 的转 换, 如: v_total := TO_NUMBER(‘100.0’) + sal; ���� NUMBERNUMBERNUMBERNUMBER 转换为CHARCHARCHARCHAR: 使用 TO_CHAR 函数 可以 实现 数字 到字 符的 转换 ,如 : v_comm := TO_CHAR(‘123.45’) || ’元’; ���� 字 符转换为日期: 使用 TO_DATE 函数 可以 实现 字符 到日 期的 转换 ,如 : v_date := TO_DATE('2001.07.03','yyyy.mm.dd'); ���� 日 期转换为字符 使用 TO_CHAR 函数 可以 实现 日期 到字 符的 转换 ,如 : v_to_day := TO_CHAR(SYSDATE,'yyyy.mm.dd hh24:mi:ss') ; §§§§2222....7777 变量 作用 范围 及可 见性 在PL/SQL 编程 中, 如 果 在变 量的 定义 上没 有做 到统 一的 话, 可 能 会隐 藏一 些危 险的 错 误, 这 样的 原因 主要 是变 量的 作用 范围 所致 。 与 其 它高 级语 言类 似, PL/SQL 的变 量作 用 范 围特 点是 : � 变量 的作 用范 围是 在你 所引 用的 程序 单元 ( 块 、 子 程 序、 包 ) 内 。 即 从 声明 变量 开 始到 该块 的结 束。 � 一个 变量 (标 识) 只能 在你 所引 用的 块内 是可 见的 。 � 当一 个变 量超 出了 作用 范围 , PL/SQL 引擎 就释 放用 来存 放该 变量 的空 间( 因为 它 可能不用了)。 � 在子 块中 重新 定义 该变 量后 ,它 的作 用仅 在该 块内 。 尔行网络 http://www.2xnet.net 16 - 16 - 例10101010: DECLARE Emess char(80); BEGIN DECLARE V1 NUMBER(4); BEGIN SELECT empno INTO v1 FROM emp WHERELOWER(job)=’president’; DBMS_OUTPUT.PUT_LINE(V1); EXCEPTION When TOO_MANY_ROWSTHEN DBMS_OUTPUT.PUT_LINE(‘More than one president’); END; DECLARE V1 NUMBER(4); BEGIN SELECT empno INTO v1 FROM emp WHERELOWER(job)=’manager’; EXCEPTION When TOO_MANY_ROWSTHEN DBMS_OUTPUT.PUT_LINE(‘More than one manager’); END; EXCEPTION When others THEN Emess:=substr(SQLERRM,1,80); DBMS_OUTPUT.PUT_LINE(emess); END; §§§§2222....8888 注释 在PL/SQL里, 可以 使用 两种 符号 来写 注释 ,即 : � 使用 双 ‘-‘( 减号 ) 加注 释 PL/SQL允许 用 – 来写 注释 ,它 的作 用范 围是 只能 在一 行有 效。 如: V_Sal NUMBER(12,2); -- 工资 变量 。 � 使用 /**/ 来加 一行 或多 行注 释, 如: /***********************************************/ /* 文件 名: statistcs_sal.sql */ /***********************************************/ 提 示: 被解释 存 放在数据库 中 的 PL/SQLPL/SQLPL/SQLPL/SQL 程 序, 一般系统 自 动将程序头 部 的注释去 掉 。 只 有在PROCEDUREPROCEDUREPROCEDUREPROCEDURE 之 后的注释才被保留; 另 外程序中的 空 行也自动 被 去掉。 尔行网络 http://www.2xnet.net 17 - 17 - §§§§2222....9999 简单 例子 §§§§2222....9.19.19.19.1 简 单数据插入例子 例11111111: /* 本例 子仅 是一 个简 单的 插入 ,不 是实 际应 用。 */ DECLARE v_ename VARCHAR2(20) := ‘Bill’; v_sal NUMBER(7,2) :=1234.56; v_deptno NUMBER(2) := 10; v_empno NUMBER(4) := 8888; BEGIN INSERTINTO emp ( empno, ename, JOB, sal, deptno , hiredate ) VALUES( v_empno, v_ename, ‘Manager’, v_sal, v_deptno, TO_DATE(’1954.06.09’,’yyyy.mm.dd’)); COMMIT; END; §§§§2222....9.29.29.29.2 简 单数据删除例子 例12121212: /* 本例 子仅 是一 个简 单的 删除 例子 ,不 是实 际应 用。 */ DECLARE v_empno number(4) := 8888; BEGIN DELETEFROM emp WHERE empno=v_empno; COMMIT; END; 尔行网络 http://www.2xnet.net 18 - 18 - 第三 章 PL/SQL PL/SQL PL/SQL PL/SQL 流程 控 制语 句 介绍 PL/SQL 的流 程控 制语 句 , 包括 如下 三类 : � 控制 语句 :IF 语句 � 循环 语句 : LOOP 语句 , EXIT 语句 � 顺序 语句 : GOTO 语句 , NULL 语句 §§§§3333....1111 条件 语句 IF <布尔 表达 式 > THEN PL/SQL 和SQL 语句 ENDIF; IF <布尔 表达 式 > THEN PL/SQL 和SQL 语句 ELSE 其它 语句 ENDIF; IF <布尔 表达 式 > THEN PL/SQL 和SQL 语句 ELSIF < 其它 布尔 表达 式 > THEN 其它 语句 ELSIF < 其它 布尔 表达 式 > THEN 其它 语句 ELSE 其它 语句 ENDIF; 提示::::ELSIFELSIFELSIFELSIF 不 能写成 ELSEIFELSEIFELSEIFELSEIF 例1:1:1:1: DECLARE v_empno emp.empno%TYPE :=&empno; V_salary emp.sal%TYPE; V_comment VARCHAR2(35); BEGIN SELECT sal INTO v_salary FROM emp WHERE empno=v_empno; IF v_salary<1500 THEN V_comment:= ‘Fairly less’; ELSIF v_salary <3000 THEN V_comment:= ‘A little more’; 尔行网络 http://www.2xnet.net 19 - 19 - ELSE V_comment:= ‘Lots of salary’; ENDIF; DBMS_OUTPUT.PUT_LINE(V_comment); END; §§§§3333.2.2.2.2 CASECASECASECASE 表达 式 CASE selector WHEN expression1 THEN result1 WHEN expression2 THEN result2 WHEN expressionN THEN resultN [ELSE resultN+1] END; 例2:2:2:2: DECLARE V_grade char(1) := UPPER(‘&p_grade’); V_appraisal VARCHAR2(20); BEGIN V_appraisal := CASE v_grade WHEN‘A’THEN‘Excellent’ WHEN‘B’THEN‘Very Good’ WHEN‘C’THEN‘Good’ ELSE‘No such grade’ END; DBMS_OUTPUT.PUT_LINE(‘Grade:‘||v_grade||’ Appraisal: ‘|| v_appraisal); END; §§§§3333.3.3.3.3 循环 1.1.1.1. 简 单循环 LOOP 要执 行的 语句 ; EXITWHEN <条件 语句 > /*条件 满足 ,退 出循 环语 句 */ ENDLOOP; 尔行网络 http://www.2xnet.net 20 - 20 - 例3.3.3.3. DECLARE int NUMBER(2) :=0; BEGIN LOOP int := int + 1; DBMS_OUTPUT.PUT_LINE('int 的当 前值 为 :'||int); EXITWHEN int =10; ENDLOOP; END; 2.2.2.2. WHILEWHILEWHILEWHILE 循环 WHILE <布尔 表达 式 > LOOP 要执 行的 语句 ; ENDLOOP; 例4.4.4.4. DECLARE x NUMBER; BEGIN x:= 1; WHILE x<10 LOOP DBMS_OUTPUT.PUT_LINE('X的当 前值 为 :'||x); x:= x+1; ENDLOOP; END; 3.3.3.3. 数 字式循环 FOR 循环 计数 器 IN[REVERSE] 下限 .. 上限 LOOP 要执 行的 语句 ; ENDLOOP; 每循 环一 次, 循环 变量 自动加 1;使 用关 键字 REVERSE,循 环变 量自 动减 1。跟在 IN REVERSE 后面 的数 字必 须是 从小 到大 的顺 序, 而且 必须 是整 数, 不能 是变 量或 表达 式。 可以 使用 EXIT 退出 循环 。 例5.5.5.5. BEGIN FOR int in 1..10 LOOP DBMS_OUTPUT.PUT_LINE('int 的当 前值 为 :'||int); ENDLOOP; END; 例6.6.6.6. 尔行网络 http://www.2xnet.net 21 - 21 - CREATETABLE temp_table(num_col NUMBER); DECLARE V_counter NUMBER:= 10; BEGIN INSERTINTO temp_table(num_col) VALUES(v_counter ); FOR v_counter IN 20 .. 25 LOOP INSERTINTO temp_table (num_col )VALUES( v_counter ); ENDLOOP; INSERTINTO temp_table(num_col) VALUES(v_counter ); FOR v_counter INREVERSE 20 .. 25 LOOP INSERTINTO temp_table (num_col )VALUES( v_counter ); ENDLOOP; END; DROPTABLE temp_table; §§§§3333....3333 标号 和 GOTOGOTOGOTOGOTO PL/SQL 中GOTO 语句 是无 条件 跳转 到指 定的 标号 去的 意思 。语 法如 下: GOTOGOTOGOTOGOTO label;label;label;label; ........................ <

下载文档,方便阅读与编辑

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

需要 8 金币 [ 分享文档获得金币 ] 0 人已下载

下载文档

相关文档