plsql_oracle9i编程讲义(编辑修改稿)

plsql_oracle9i编程讲义(编辑修改稿)内容摘要：

e)和 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 内容。 167。 PL/SQL 的好处 167。 有利于客户 /服务器环境应用的运行 对于客户 /服务器环境来说，真正的瓶颈是网络上。 无论网络多快，只要客户端与服务器进行大量的数据交换。 应用运行的效率自然就 会 受到影响。 如果使用 PL/SQL 进行编程，将这种具有大量数据处理的应用放在服务器端来执行。 自然就省去了数据在网上的传输时间。 5 5 167。 适合于客户环境 PL/SQL 由于分为数据库 PL/SQL 部分和工具 PL/SQL。 对于客户端来说， PL/SQL 可以嵌套到相应的工具中，客户端程序可 以执行本地包含 PL/SQL部分，也可以向服务发 SQL命令或激活服务器端的 PL/SQL 程序运行。 167。 PL/SQL 可用的 SQL 语句 PL/SQL是 ORACLE系统的核心语言，现在 ORACLE的许多部件都是由 PL/SQL写成。 在 PL/SQL 中可以使用的 SQL 语句有： INSERT， UPDATE， DELETE， SELECT INTO， COMMIT， ROLLBACK， SAVEPOINT。 提示：在 PL/SQL 中只能用 SQL 语句中的 DML 部分，不能用 DDL 部分，如果要在PL/SQL 中使用 DDL(如 CREATE table 等 )的话，只能以动态的方式来使用。  ORACLE 的 PL/SQL 组件在对 PL/SQL 程序进行解释时，同时对在其所使用的表名、列名及数据类型进行检查。  PL/SQL 可以在 SQL*PLUS 中使用。  PL/SQL 可以在高级语言中使用。  PL/SQL 可以 在 ORACLE 的 开发工具中使用。  其它开发工具也可以调用 PL/SQL 编写的过程和函数，如 Power Builder 等都可以调用服务器端的 PL/SQL 过程。 167。 运行 PL/SQL 程序 PL/SQL 程序的运行是通过 ORACLE 中的一个引擎来进行的。 这个引擎可能在ORACLE 的服务器端，也可能在 ORACLE 应用开发的客户端。 引擎执行 PL/SQL 中的过程性语句，然后将 SQL 语句发送给数据库服务器来执行。 再将结果返回给执行端。 6 6 第二章 PL/SQL 块结构和组成元素 167。 PL/SQL 块 PL/SQL 程序由三个块组成，即声明部分、执行部分、异常处理部分。 PL/SQL 块的结构如下： DECLARE /* 声明部分 : 在此声明 PL/SQL 用到的变量 ,类型及游标， 以及局部的存储过程和函数 */ BEGIN /* 执行部分 : 过程及 SQL 语句 , 即程序的主要部分 */ EXCEPTION /* 执行异常部分 : 错误处理 */ END。 其中 执行部分是必须的。 PL/SQL 块可以分为三类： 1. 无名块：动态构造，只能执行一次。 2. 子程序：存储在数据库中的存储过程、函数及包等。 当在数据库上建立好后可以在其它程序中调用它们。 3. 触发器：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。 167。 PL/SQL 结构  PL/SQL 块中 可以包含子块；  子块可以位于 PL/SQL 中的任何部分；  子块也即 PL/SQL 中的一条命令； 167。 标识符 PL/SQL 程序设计中的标识符定义与 SQL 的标识符定义的要求相同。 要求和限制有：  标识符名不能超过 30 字符；  第一个字符必须为字母；  不分大小写；  不能用 ‘‗(减号 )。  不能是 SQL 保留字。 提示 : 一般不要把变量名声明与表中字段名完全一样 ,如果这样可能得到不正确的结果 . 7 7 例如：下面的例子将会删除所有的纪录，而不是 KING 的记录； DECLARE Ename varchar2(20) :=‘KING‘。 BEGIN DELETE FROM emp WHERE ename=ename。 END。 变量命名在 PL/SQL 中有特别的讲究，建议在系统的设计阶段就要求所有编程人员共同遵守一定的要求，使得整个系统的文档在规范上达到要求。 下面是建议的命名方法： 标识符 命名规则 例子 程序变量 V_name V_name 程序常量 C_Name C_pany_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 167。 PL/SQL 变量类型 在前面的介绍中，有系统的数据类型，也可以自定义数据类型。 下表是 ORACLE类型和 PL/SQL 中的变量类型的合法使用列表： 167。 变量类型 在 ORACLE8i 中可以使用的变量类型有 ： 类型 子类 说 明 范 围 ORACLE 限制 CHAR Character String Rowid Nchar 定长字符串 民族语言字符集 032767 可选 ,确省 =1 2020 VARCHAR2 Varchar, String NVARCHAR2 可变字符串 民族语言字符集 032767 4000 4000 BINARY_INTEGER 带符号整数 ,为整数计算优化性能 NUMBER(p,s) Dec Double precision Integer Int Numeric Real Small int 小数 , NUMBER 的子类型 高精度实数 整数 , NUMBER 的子类型 整数 , NUMBER 的子类型 与 NUMBER 等价 与 NUMBER 等价 整数 , 比 integer 小 LONG 变长字符串 021474836 32,767 字节 8 8 47 DATE 日期型 公元前 4712 年 1 月1 日至公元后 4712年 12 月 31 日 BOOLEAN 布尔型 TRUE, FALSE,NULL 不使用 ROWID 存放数据库行号 UROWID 通用行标识符 ，字符类型 例 1. 插入一条记录并显示； DECLARE Row_id ROWID。 info VARCHAR2(40)。 BEGIN INSERT INTO dept VALUES (90, ‗SERVICE‘, ‗BEIJING‘) RETURNING rowid, dname||‘:‘||to_char(deptno)||‘:‘||loc INTO row_id, info。 (‗ROWID:‘||row_id)。 (info)。 END。 其中： RETURNING 子句用于检索 INSERT 语句中所影响的数据行数，当 INSERT 语句使用 VALUES 子句插入数据时， RETURNING 子 句还可将列表达式、 ROWID 和 REF 值返回到输出变量中。 在使用 RETURNING 子句是应注意以下几点限制： 1． 不能并行 DML 语句和远程对象一起使用； 2． 不能检索 LONG 类型信息； 3． 当通过视图向基表中插入数据时，只能与单基表视图一起使用。 例 2. 修改一条记录并显示 DECLARE Row_id ROWID。 info VARCHAR2(40)。 BEGIN UPDATE dept SET deptno=80 WHERE DNAME=‗SERVICE‘ RETURNING rowid, dname||‘:‘||to_char(deptno)||‘:‘||loc INTO row_id, info。 (‗ROWID:‘||row_id)。 (info)。 END。 其中： RETURNING 子句用于检索被修改行信息：当 UPDATE 语句修改单行数据时，RETURNING 子句可以检索被修改行的 ROWID和 REF值，以及行中被修改列的列表达式，并可将他们存储到 PL/SQL 变量或复合变量中；当 UPDATE 语句修改多行数据时， 9 9 RETURNING 子句可以将被修改行的 ROWID 和 REF 值，以及列表达式值返回到复合变量数组中。 在 UPDATE 中使用 RETURNING 子句的限制与 INSERT 语句中对 RETURNING子句的限制相同。 例 3. 删除一条记录并显示 DECLARE Row_id ROWID。 info VARCHAR2(40)。 BEGIN DELETE dept WHERE DNAME=‗SERVICE‘ RETURNING rowid, dname||‘:‘||to_char(deptno)||‘:‘||loc INTO row_id, info。 (‗ROWID:‘||row_id)。 (info)。 END。 其中： RETURNING 子句用于检索被修改行信息 ：当 UPDATE 语句修改单行数据时，RETURNING 子句可以检索被修改行的 ROWID和 REF值，以及行中被修改列的列表达式，并可将他们存储到 PL/SQL 变量或复合变量中；当 UPDATE 语句修改多行数据时，RETURNING 子句可以将被修改行的 ROWID 和 REF 值，以及列表达式值返回到复合变量数组中。 在 UPDATE 中使用 RETURNING 子句的限制与 INSERT 语句中对 RETURNING子句的限制相同。 167。 复合类型 ORACLE 在 PL/SQL 中除了提供象前面介绍的各种类 型外 ,还提供一种称为复合类型的类型 记录和表 . 167。 记录类型 记录类型是把逻辑相关的数据作为一个单元存储起来，它必须包括至少一个标量型或RECORD 数据类型的成员，称作 PL/SQL RECORD 的域 (FIELD)，其作用是存放互不相同但逻辑相关的信息。 定义记录类型语法如下 : TYPE record_type IS RECORD( Field1 type1 [NOT NULL] [:= exp1 ], Field2 type2 [NOT NULL] [:= exp2 ], . . . . . . Fieldn typen [NOT NULL] [:= expn ] )。 例 4 ： DECLARE 10 10 TYPE test_rec IS RECORD( Code VARCHAR2(10), Name VARCHAR2(30) NOT NULL :=‘a book‘)。 V_book test_rec。 BEGIN :=‘123‘。 :=‘C++ Programming‘。 (||)。 END。 可以用 SELECT 语句对记录变量进行赋值 ,只要保证记录字段与查询结果列表中的字段相配即可。 167。 使用 %TYPE 定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用 %TYPE。 使用 %TYPE 特性的优点在于：  所引用的数据库列的数据类型可以不必知道；  所引用的数据库列的数据类 型可以实时改变。 例 5： DECLARE 用 %TYPE 类型定义与表相配的字段 TYPE t_Record IS RECORD( T_no %TYPE, T_nam。