Oracle PL/SQL 编程


目 录 ORACLE PL/SQL 编程详解之一: ................................................................................................................................. 3 PL/SQL 程序设计简介(千里之行,始于足下) ............................................................................................................... 3 1.1 SQL 与 PL/SQL ....................................................................................................................................... 3 1.2 PL/SQL 的优点或特征 .............................................................................................................................. 3 1.3 PL/SQL 可用的 SQL 语句 ....................................................................................................................... 4 1.4 运行 PL/SQL 程序 ..................................................................................................................................... 4 ORACLE PL/SQL 编程详解之二: ................................................................................................................................. 5 PL/SQL 块结构和组成元素(为山九仞,岂一日之功) .................................................................................................... 5 2.1 PL/SQL 块................................................................................................................................................ 5 2.2 PL/SQL 结构............................................................................................................................................ 5 2.3 标识符 ....................................................................................................................................................... 5 2.4 PL/SQL 变量类型 ................................................................................................................................... 6 2.5 运算符和表达式(数据定义) ................................................................................................................... 16 2.6 变量赋值 ................................................................................................................................................. 17 2.7 变量作用范围及可见性 ........................................................................................................................ 19 2.8 注释 ......................................................................................................................................................... 20 2.9 简单例子 ................................................................................................................................................. 20 ORACLE PL/SQL 编程详解之三: ............................................................................................................................... 22 PL/SQL 流程控制语句(不给规则,不成方圆) .............................................................................................................. 22 3.1 条件语句 ................................................................................................................................................... 22 3.2 CASE 表达式 ........................................................................................................................................... 23 3.3 循环 ........................................................................................................................................................... 24 3.4 标号和 GOTO ........................................................................................................................................... 26 3.5 NULL 语句 .............................................................................................................................................. 27 ORACLE PL/SQL 编程之四: ....................................................................................................................................... 29 把游标说透(不怕做不到,只怕想不到) ......................................................................................................................... 29 4.1 游标概念 ..................................................................................................................................................... 29 4.2 游标变量 ..................................................................................................................................................... 36 ORACLE PL/SQL 编程之五: ....................................................................................................................................... 40 异常错误处理(知已知彼、百战不殆) ............................................................................................................................. 40 5.1 异常处理概念 ............................................................................................................................................. 40 5.2 异常错误传播 ............................................................................................................................................. 44 5.3 异常错误处理编程 ..................................................................................................................................... 45 5.4 在 PL/SQL 中使用 SQLCODE, SQLERRM 异常处理函数 ................................................................ 45 ORACLE PL/SQL 编程之六: ....................................................................................................................................... 47 把过程与函数说透(穷追猛打,把根儿都拔起!) ............................................................................................................. 47 6.1 引言 .............................................................................................................................................................. 47 6.2 创建函数 ...................................................................................................................................................... 47 6.3 存储过程 ...................................................................................................................................................... 51 ORACLE PL/SQL 编程详解之七: ............................................................................................................................... 63 程序包的创建与应用(聪明在于学习,天才在于积累!) .............................................................................................. 63 7.1 程序包简介 ............................................................................................................................................... 63 7.2 程序包的定义 ........................................................................................................................................... 64 7.3 包的开发步骤 ........................................................................................................................................... 65 7.4 包定义的说明 ........................................................................................................................................... 65 7.5 子程序重载 ............................................................................................................................................... 77 7.6 加密实用程序 ........................................................................................................................................... 79 7.7 删除包 ....................................................................................................................................................... 79 7.8 包的管理 ................................................................................................................................................... 79 ORACLE PL/SQL 编程之八: ....................................................................................................................................... 81 把触发器说透 .................................................................................................................................................................... 81 8.1 触发器类型 .................................................................................................................................................. 81 编写触发器时,需要注意以下几点: ............................................................................................................ 82 8.2 创建触发器 .................................................................................................................................................. 82 8.3 删除和使能触发器 ..................................................................................................................................... 93 8.4 触发器和数据字典 ..................................................................................................................................... 94 8.5 数据库触发器的应用举例 ......................................................................................................................... 94 8.6 数据库触发器的应用实例 ....................................................................................................................... 103 ORACLE PL/SQL 编程详解之一: PL/SQL 程序设计简介(千里之行,始于足下) SQL 语言只是访问、操作数据库的语言,并不是一种具有流程控制的程序设计语言,而只有程序设计语 言才能用于应用软件的开发。PL /SQL 是一种高级数据库程序设计语言,该语言专门用于在各种环境下对 ORACLE 数据库进行访问。由于该语言集成于数据库服务器中,所以 PL/SQL 代码可以对数据进行快速高 效的处理。除此之外,可以在 ORACLE 数据库的某些客户端工具中,使用 PL/SQL 语言也是该语言的一个 特点。本章的主要内容是讨论引入 PL/SQL 语言的必要性和该语言的主要特点,以及了解 PL/SQL 语言的 重要性和数据库版本问题。还要介绍一些贯穿全书的更详细的高级概念,并在本章的最后就我们在本书案 例中使用的数据库表的若干约定做一说明。 1.1 SQL 与 PL/SQL 1.1.1 什么是 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 内容。 1.2 PL/SQL 的优点或特征 1.2.1 有利于客户/服务器环境应用的运行 对于客户/服务器环境来说,真正的瓶颈是网络上。无论网络多快,只要客户端与服务器进行大量的数据交 换。应用运行的效率自然就回受到影响。如果使用 PL/SQL 进行编程,将这种具有大量数据处理的应用放 在服务器端来执行。自然就省去了数据在网上的传输时间。 1.2.2 适合于客户环境 PL/SQL 由于分为数据库 PL/SQL 部分和工具 PL/SQL。对于客户端来说,PL/SQL 可以嵌套到相应的工具 中,客户端程序可以执行本地包含 PL/SQL 部分,也可以向服务发 SQL 命令或激活服务器端的 PL/SQL 程 序运行。 1.2.3 过程化 PL/SQL 是 Oracle 在标准 SQL 上的过程性扩展,不仅允许在 PL/SQL 程序内嵌入 SQL 语句,而且允许使 用各种类型的条件分支语句和循环语句,可以多个应用程序之间共享其解决方案。 1.2.4 模块化 PL/SQL 程序结构是一种描述性很强、界限分明的块结构、嵌套块结构,被分成单独的过程、函数、触发器, 且可以把它们组合为程序包,提高程序的模块化能力。 1.2.5 运行错误的可处理性 使用 PL/SQL 提供的异常处理(EXCEPTION),开发人员可集中处理各种 ORACLE 错误和 PL/SQL 错误, 或处理系统错误与自定义错误,以增强应用程序的健壮性。 1.2.6 提供大量内置程序包 ORACLE 提供了大量的内置程序包。通过这些程序包能够实现 DBS 的一些低层操作、高级功能,不论对 DBA 还是应用开发人员都具有重要作用。 当然还有其它的一些优点如:更好的性能、可移植性和兼容性、可维护性、易用性与快速性等。 1.3 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 的开发工具中使用(如:SQL Developer 或 Procedure Builder 等)。 其它开发工具也可以调用 PL/SQL 编写的过程和函数,如 Power Builder 等都可以调用服务器端的 PL/SQL 过程。 1.4 运行 PL/SQL 程序 PL/SQL 程序的运行是通过 ORACLE 中的一个引擎来进行的。这个引擎可能在 ORACLE 的服务器端, 也可能在 ORACLE 应用开发的客户端。引擎执行 PL/SQL 中的过程性语句,然后将 SQL 语句发送给数据 库服务器来执行。再将结果返回给执行端。 ORACLE PL/SQL 编程详解之二: PL/SQL 块结构和组成元素(为山九仞,岂一日之功) 2.1 PL/SQL 块 PL/SQL 程序由三个块组成,即声明部分、执行部分、异常处理部分。 PL/SQL 块的结构如下: DECLARE --声明部分: 在此声明 PL/SQL 用到的变量,类型及游标,以及局部的存储过程和函数 BEGIN -- 执行部分: 过程及 SQL 语句 , 即程序的主要部分 EXCEPTION -- 执行异常部分: 错误处理 END; 其中:执行部分不能省略。 PL/SQL 块可以分为三类: 1. 无名块或匿名块(anonymous):动态构造,只能执行一次,可调用其它程序,但不能被其它程序调用。 2. 命名块(named):是带有名称的匿名块,这个名称就是标签。 3. 子程序(subprogram):存储在数据库中的存储过程、函数等。当在数据库上建立好后可以在其它程序 中调用它们。 4. 触发器 (Trigger):当数据库发生操作时,会触发一些事件,从而自动执行相应的程序。 5. 程序包(package):存储在数据库中的一组子程序、变量定义。在包中的子程序可以被其它程序包或子 程序调用。但如果声明的是局部子程序,则只能在定义该局部子程序的块中调用该局部子程序。 2.2 PL/SQL 结构  PL/SQL 块中可以包含子块;  子块可以位于 PL/SQL 中的任何部分;  子块也即 PL/SQL 中的一条命令; 2.3 标识符 PL/SQL 程序设计中的标识符定义与 SQL 的标识符定义的要求相同。要求和限制有:  标识符名不能超过 30 字符;  第一个字符必须为字母;  不分大小写;  不能用‟-„(减号);  不能是 SQL 保留字。 提示: 一般不要把变量名声明与表中字段名完全一样,如果这样可能得到不正确的结果. 例如:下面的例子将会删除所有的纪录,而不是‟EricHu‟的记录; DECLARE ename varchar2(20) :='EricHu'; BEGIN DELETE FROM scott.emp WHERE ename=ename; END; 复制代码 变量命名在 PL/SQL 中有特别的讲究,建议在系统的设计阶段就要求所有编程人员共同遵守一定的要求, 使得整个系统的文档在规范上达到要求。下面是建议的命名方法: 标识符 命名规则 例子 程序变量 V_name V_name 程序常量 C_Name C_company_name 游标变量 Cursor_Name Cursor_Emp 异常标识 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 2.4 PL/SQL 变量类型 在前面的介绍中,有系统的数据类型,也可以自定义数据类型。下表给出 ORACLE 类型和 PL/SQL 中的变 量类型的合法使用列表: 2.4.1 变量类型 在 ORACLE8i 中可以使用的变量类型有: 类型 子类 说 明 范 围 ORACLE 限制 CHAR Character String Rowid Nchar 定长字符串 民族语言字符集 032767 可选,确省=1 2000 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 变长字符串 0->2147483647 32,767 字节 DATE 日期型 公元前 4712 年 1 月 1 日至公元后 4712 年 12 月 31 日 BOOLEAN 布尔型 TRUE, FALSE, NULL 不使用 ROWID 存放数据库行号 UROWID 通用行标识符,字符类型 例 1. 插入一条记录并显示; DECLARE Row_id ROWID; info VARCHAR2(40); BEGIN INSERT INTO scott.dept VALUES (90, '财务室', '海口') 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. 修改一条记录并显示 DECLARE Row_id ROWID; info VARCHAR2(40); BEGIN UPDATE dept SET deptno=100 WHERE DNAME='财务室' 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 子句的限制相同。 例 3. 删除一条记录并显示 DECLARE Row_id ROWID; info VARCHAR2(40); BEGIN DELETE dept WHERE DNAME='办公室' 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 子句用于检索被删除行的信息:当 DELETE 语句删除单行数据时,RETURNING 子句可以检 索被删除行的 ROWID 和 REF 值,以及被删除列的列表达式,并可将他们存储到 PL/SQL 变量或复合变量中; 当 DELETE 语句删除多行数据时,RETURNING 子句可以将被删除行的 ROWID 和 REF 值,以及列表达式值 返回到复合变量数组中。在 DELETE 中使用 RETURNING 子句的限制与 INSERT 语句中对 RETURNING 子句 的限制相同。 2.4.2 复合类型 ORACLE 在 PL/SQL 中除了提供象前面介绍的各种类型外,还提供一种称为复合类型的类型---记录和表. 2.4.2.1 记录类型 记录类型类似于 C 语言中的结构数据类型,它把逻辑相关的、分离的、基本数据类型的变量组成一个整体 存储起来,它必须包括至少一个标量型或 RECORD 数据类型的成员,称作 PL/SQL RECORD 的域(FIELD), 其作用是存放互不相同但逻辑相关的信息。在使用记录数据类型变量时,需要先在声明部分先定义记录的组成、 记录的变量,然后在执行部分引用该记录变量本身或其中的成员。 定义记录类型语法如下: TYPE record_name IS RECORD( v1 data_type1 [NOT NULL] [:= default_value ], v2 data_type2 [NOT NULL] [:= default_value ], ...... vn data_typen [NOT NULL] [:= default_value ] ); 复制代码 例 4 : DECLARE TYPE test_rec IS RECORD( Name VARCHAR2(30) NOT NULL := '胡勇', Info VARCHAR2(100)); rec_book test_rec; BEGIN rec_book.Name :='胡勇'; rec_book.Info :='谈 PL/SQL 编程;'; DBMS_OUTPUT.PUT_LINE(rec_book.Name||' ' ||rec_book.Info); END; 复制代码 可以用 SELECT 语句对记录变量进行赋值,只要保证记录字段与查询结果列表中的字段相配即可。 例 5 : DECLARE --定义与 hr.employees 表中的这几个列相同的记录数据类型 TYPE RECORD_TYPE_EMPLOYEES IS RECORD( f_name hr.employees.first_name%TYPE, h_date hr.employees.hire_date%TYPE, j_id hr.employees.job_id%TYPE); --声明一个该记录数据类型的记录变量 v_emp_record RECORD_TYPE_EMPLOYEES; BEGIN SELECT first_name, hire_date, job_id INTO v_emp_record FROM employees WHERE employee_id = &emp_id; DBMS_OUTPUT.PUT_LINE('雇员名称:'||v_emp_record.f_name ||' 雇佣日期:'||v_emp_record.h_date ||' 岗位:'||v_emp_record.j_id); END; 复制代码 一个记录类型的变量只能保存从数据库中查询出的一行记录,若查询出了多行记录,就会出现错误。 2.4.2.2 数组类型 数据是具有相同数据类型的一组成员的集合。每个成员都有一个唯一的下标,它取决于成员在数组中的位置。 在 PL/SQL 中,数组数据类型是 VARRAY。 定义 VARRY 数据类型语法如下: TYPE varray_name IS VARRAY(size) OF element_type [NOT NULL]; varray_name 是 VARRAY 数据类型的名称,size 是下整数,表示可容纳的成员的最大数量,每个成员的数据类 型是 element_type。默认成员可以取空值,否则需要使用 NOT NULL 加以限制。对于 VARRAY 数据类型来说, 必须经过三个步骤,分别是:定义、声明、初始化。 例 6 : DECLARE --定义一个最多保存 5 个 VARCHAR(25)数据类型成员的 VARRAY 数据类型 TYPE reg_varray_type IS VARRAY(5) OF VARCHAR(25); --声明一个该 VARRAY 数据类型的变量 v_reg_varray REG_VARRAY_TYPE; BEGIN --用构造函数语法赋予初值 v_reg_varray := reg_varray_type ('中国', '美国', '英国', '日本', '法国'); DBMS_OUTPUT.PUT_LINE('地区名称:'||v_reg_varray(1)||'、' ||v_reg_varray(2)||'、' ||v_reg_varray(3)||'、' ||v_reg_varray(4)); DBMS_OUTPUT.PUT_LINE('赋予初值 NULL 的第 5 个成员的值:'||v_reg_varray(5)); --用构造函数语法赋予初值后就可以这样对成员赋值 v_reg_varray(5) := '法国'; DBMS_OUTPUT.PUT_LINE('第 5 个成员的值:'||v_reg_varray(5)); END; 复制代码 2.4.2.3 使用%TYPE 定义一个变量,其数据类型与已经定义的某个数据变量(尤其是表的某一列)的数据类型相一致,这时可以使 用%TYPE。 使用%TYPE 特性的优点在于:  所引用的数据库列的数据类型可以不必知道;  所引用的数据库列的数据类型可以实时改变,容易保持一致,也不用修改 PL/SQL 程序。 例 7: DECLARE -- 用%TYPE 类型定义与表相配的字段 TYPE T_Record IS RECORD( 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; 复制代码 例 8: DECLARE v_empno emp.empno%TYPE :=&no; Type t_record is record ( v_name emp.ename%TYPE, v_sal emp.sal%TYPE, v_date emp.hiredate%TYPE); Rec t_record; 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; 复制代码 2.4.3 使用%ROWTYPE PL/SQL 提供%ROWTYPE 操作符, 返回一个记录类型, 其数据类型和数据库表的数据结构相一致。 使用%ROWTYPE 特性的优点在于:  所引用的数据库中列的个数和数据类型可以不必知道;  所引用的数据库中列的个数和数据类型可以实时改变,容易保持一致,也不用修改 PL/SQL 程序。 例 9: DECLARE v_empno emp.empno%TYPE :=&no; 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.4 LOB 类型 ORACLE 提供了 LOB (Large OBject)类型,用于存储大的数据对象的类型。ORACLE 目前主要支持 BFILE, BLOB, CLOB 及 NCLOB 类型。 BFILE (Movie) 存放大的二进制数据对象,这些数据文件不放在数据库里,而是放在操作系统的某个目录里,数据库的表里 只存放文件的目录。 BLOB(Photo) 存储大的二进制数据类型。变量存储大的二进制对象的位置。大二进制对象的大小<=4GB。 CLOB(Book) 存储大的字符数据类型。每个变量存储大字符对象的位置,该位置指到大字符数据块。大字符对象的大小 <=4GB。 NCLOB 存储大的 NCHAR 字符数据类型。每个变量存储大字符对象的位置,该位置指到大字符数据块。大字符对象 的大小<=4GB。 2.4.5 BIND 变量 绑定变量是在主机环境中定义的变量。在 PL/SQL 程序中可以使用绑定变量作为他们将要使用的其它变量。 为了在 PL/SQL 环境中声明绑定变量,使用命令 VARIABLE。例如: VARIABLE return_code NUMBER VARIABLE return_msg VARCHAR2(20) 复制代码 可以通过 SQL*Plus 命令中的 PRINT 显示绑定变量的值。 例如: PRINT return_code PRINT return_msg 复制代码 例 10: VARIABLE result NUMBER; BEGIN SELECT (sal*10)+nvl(comm, 0) INTO :result FROM emp WHERE empno=7844; END; --然后再执行 PRINT result 复制代码 2.4.6 PL/SQL 表(TABLE) 定义记录表(或索引表)数据类型。它与记录类型相似,但它是对记录类型的扩展。它可以处理多行记录, 类似于高级中的二维数组,使得可以在 PL/SQL 中模仿数据库中的表。 定义记录表类型的语法如下: TYPE table_name IS TABLE OF element_type [NOT NULL] INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARRAY2]; 复制代码 关键字 INDEX BY 表示创建一个主键索引,以便引用记录表变量中的特定行。 方法 描述 EXISTS(n) 如果集合的第 n 个成员存在,则返回 true COUNT 返回已经分配了存储空间即赋值了的成员数量 FIRST LAST FIRST:返回成员的最低下标值 LAST: 返回成员的最高下标值 PRIOR(n) 返回下标为 n 的成员的前一个成员的下标。如果没有则返回 NULL NEXT(N) 返回下标为 n 的成员的后一个成员的下标。如果没有则返回 NULL TRIM TRIM:删除末尾一个成员 TRIM(n) :删除末尾 n 个成员 DELETE DELETE:删除所有成员 DELETE(n) :删除第 n 个成员 DELETE(m, n) :删除从 n 到 m 的成员 EXTEND EXTEND:添加一个 null 成员 EXTEND(n):添加 n 个 null 成员 EXTEND(n,i):添加 n 个成员,其值与第 i 个成员相同 LIMIT 返回在 varray 类型变量中出现的最高下标值 例 11: DECLARE TYPE dept_table_type IS TABLE OF dept%ROWTYPE INDEX BY BINARY_INTEGER; my_dname_table dept_table_type; 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; END LOOP; 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); END LOOP; END; 复制代码 例 12:按一维数组使用记录表 DECLARE --定义记录表数据类型 TYPE reg_table_type IS TABLE OF varchar2(25) INDEX BY BINARY_INTEGER; --声明记录表数据类型的变量 v_reg_table REG_TABLE_TYPE; BEGIN v_reg_table(1) := 'Europe'; v_reg_table(2) := 'Americas'; v_reg_table(3) := 'Asia'; v_reg_table(4) := 'Middle East and Africa'; v_reg_table(5) := 'NULL'; DBMS_OUTPUT.PUT_LINE('地区名称:'||v_reg_table (1)||'、' ||v_reg_table (2)||'、' ||v_reg_table (3)||'、' ||v_reg_table (4)); DBMS_OUTPUT.PUT_LINE('第 5 个成员的值:'||v_reg_table(5)); END; 复制代码 例 13:按二维数组使用记录表 DECLARE --定义记录表数据类型 TYPE emp_table_type IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER; --声明记录表数据类型的变量 v_emp_table EMP_TABLE_TYPE; BEGIN SELECT first_name, hire_date, job_id INTO v_emp_table(1).first_name,v_emp_table(1).hire_date, v_emp_table(1).job_id FROM employees WHERE employee_id = 177; SELECT first_name, hire_date, job_id INTO v_emp_table(2).first_name,v_emp_table(2).hire_date, v_emp_table(2).job_id FROM employees WHERE employee_id = 178; DBMS_OUTPUT.PUT_LINE('177 雇员名称:'||v_emp_table(1).first_name ||' 雇佣日期:'||v_emp_table(1).hire_date ||' 岗位:'||v_emp_table(1).job_id); DBMS_OUTPUT.PUT_LINE('178 雇员名称:'||v_emp_table(2).first_name ||' 雇佣日期:'||v_emp_table(2).hire_date ||' 岗位:'||v_emp_table(2).job_id); END; 复制代码 2.5 运算符和表达式(数据定义) 2.5.1 关系运算符 运算符 意义 = 等于 <> , != , ~= , ^= 不等于 < 小于 > 大于 <= 小于或等于 >= 大于或等于 2.5.2 一般运算符 运算符 意义 + 加号 - 减号 * 乘号 / 除号 := 赋值号 => 关系号 .. 范围运算符 || 字符连接符 2.5.3 逻辑运算符 运算符 意义 IS NULL 是空值 BETWEEN AND 介于两者之间 IN 在一列值中间 AND 逻辑与 OR 逻辑或 NOT 取返,如 IS NOT NULL, NOT IN 2.6 变量赋值 在 PL/SQL 编程中,变量赋值是一个值得注意的地方,它的语法如下: variable := expression ; variable 是一个 PL/SQL 变量, expression 是一个 PL/SQL 表达式. 2.6.1 字符及数字运算特点 空值加数字仍是空值:NULL + < 数字> = NULL 空值加(连接)字符,结果为字符:NULL || <字符串> = < 字符串> 2.6.2 BOOLEAN 赋值 布尔值只有 TRUE, FALSE 及 NULL 三个值。如: DECLARE bDone BOOLEAN; BEGIN bDone := FALSE; WHILE NOT bDone LOOP Null; END LOOP; END; 复制代码 2.6.3 数据库赋值 数据库赋值是通过 SELECT 语句来完成的,每次执行 SELECT 语句就赋值一次,一般要求被赋值的变量 与 SELECT 中的列名要一一对应。如: 例 14: 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; 复制代码 提示:不能将 SELECT 语句中的列赋值给布尔变量。 2.6.4 可转换的类型赋值  CHAR 转换为 NUMBER: 使用 TO_NUMBER 函数来完成字符到数字的转换,如: v_total := TO_NUMBER('100.0') + sal;  NUMBER 转换为 CHAR: 使用 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') ; 2.7 变量作用范围及可见性 在 PL/SQL 编程中,如果在变量的定义上没有做到统一的话,可能会隐藏一些危险的错误,这样的原因主 要是变量的作用范围所致。变量的作用域是指变量的有效作用范围,与其它高级语言类似,PL/SQL 的变量 作用范围特点是:  变量的作用范围是在你所引用的程序单元(块、子程序、包)内。即从声明变量开始到该块的结 束。  一个变量(标识)只能在你所引用的块内是可见的。  当一个变量超出了作用范围,PL/SQL 引擎就释放用来存放该变量的空间(因为它可能不用了)。  在子块中重新定义该变量后,它的作用仅在该块内。 例 15: DECLARE Emess char(80); BEGIN DECLARE V1 NUMBER(4); BEGIN SELECT empno INTO v1 FROM emp WHERE LOWER(job)='president'; DBMS_OUTPUT.PUT_LINE(V1); EXCEPTION When TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE ('More than one president'); END; DECLARE V1 NUMBER(4); BEGIN SELECT empno INTO v1 FROM emp WHERE LOWER(job)='manager'; EXCEPTION When TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE ('More than one manager'); END; EXCEPTION When others THEN Emess:=substr(SQLERRM,1,80); DBMS_OUTPUT.PUT_LINE(emess); END; 复制代码 2.8 注释 在 PL/SQL 里,可以使用两种符号来写注释,即:  使用双 „-„ ( 减号) 加注释 PL/SQL 允许用 – 来写注释,它的作用范围是只能在一行有效。如: V_Sal NUMBER(12,2); -- 人员的工资变量。  使用 /* */ 来加一行或多行注释,如: /***********************************************/ /* 文件名: department_salary.sql */ /* 作 者: EricHu */ /* 时 间: 2011-5-9 */ /***********************************************/ 提示:被解释后存放在数据库中的 PL/SQL 程序,一般系统自动将程序头部的注释去掉。只有在 PROCEDURE 之后的注释才被保留;另外程序中的空行也自动被去掉。 2.9 简单例子 2.9.1 简单数据插入例子 例 16: /***********************************************/ /* 文件名: test.sql */ /* 说 明: 一个简单的插入测试,无实际应用。*/ /* 作 者: EricHu */ /* 时 间: 2011-5-9 */ /***********************************************/ DECLARE v_ename VARCHAR2(20) := 'Bill'; v_sal NUMBER(7,2) :=1234.56; v_deptno NUMBER(2) := 10; v_empno NUMBER(4) := 8888; BEGIN INSERT INTO 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; 复制代码 2.9.2 简单数据删除例子 例 17: /***********************************************/ /* 文件名: test_deletedata.sql */ /* 说 明: 简单的删除例子,不是实际应用。 */ /* 作 者: EricHu */ /* 时 间: 2011-5-9 */ /***********************************************/ DECLARE v_ename VARCHAR2(20) := 'Bill'; v_sal NUMBER(7,2) :=1234.56; v_deptno NUMBER(2) := 10; v_empno NUMBER(4) := 8888; BEGIN INSERT INTO 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; DECLARE v_empno number(4) := 8888; BEGIN DELETE FROM emp WHERE empno=v_empno; COMMIT; END; 复制代码 ORACLE PL/SQL 编程详解之三: PL/SQL 流程控制语句(不给规则,不成方圆) 介绍 PL/SQL 的流程控制语句, 包括如下三类: 控制语句: IF 语句 循环语句: LOOP 语句, EXIT 语句 顺序语句: GOTO 语句, NULL 语句 3.1 条件语句 IF <布尔表达式> THEN PL/SQL 和 SQL 语句 END IF; ----------------------- IF <布尔表达式> THEN PL/SQL 和 SQL 语句 ELSE 其它语句 END IF; ----------------------- IF <布尔表达式> THEN PL/SQL 和 SQL 语句 ELSIF < 其它布尔表达式> THEN 其它语句 ELSIF < 其它布尔表达式> THEN 其它语句 ELSE 其它语句 END IF; 提示: ELSIF 不能写成 ELSEIF 例 1: DECLARE v_empno employees.employee_id%TYPE :=&empno; V_salary employees.salary%TYPE; V_comment VARCHAR2(35); BEGIN SELECT salary INTO v_salary FROM employees WHERE employee_id = v_empno; IF v_salary < 1500 THEN V_comment:= '太少了,加点吧~!'; ELSIF v_salary <3000 THEN V_comment:= '多了点,少点吧~!'; ELSE V_comment:= '没有薪水~!'; END IF; DBMS_OUTPUT.PUT_LINE(V_comment); exception when no_data_found then DBMS_OUTPUT.PUT_LINE('没有数据~!'); when others then DBMS_OUTPUT.PUT_LINE(sqlcode || '---' || sqlerrm); END; 例 2: DECLARE v_first_name VARCHAR2(20); v_salary NUMBER(7,2); BEGIN SELECT first_name, salary INTO v_first_name, v_salary FROM employees WHERE employee_id = &emp_id; DBMS_OUTPUT.PUT_LINE(v_first_name||'雇员的工资是'||v_salary); IF v_salary < 10000 THEN DBMS_OUTPUT.PUT_LINE('工资低于 10000'); ELSE IF 10000 <= v_salary AND v_salary < 20000 THEN DBMS_OUTPUT.PUT_LINE('工资在 10000 到 20000 之间'); ELSE DBMS_OUTPUT.PUT_LINE('工资高于 20000'); END IF; END IF; END; 例 3: DECLARE v_first_name VARCHAR2(20); v_hire_date DATE; v_bonus NUMBER(6,2); BEGIN SELECT first_name, hire_date INTO v_first_name, v_hire_date FROM employees WHERE employee_id = &emp_id; IF v_hire_date > TO_DATE('01-1 月-90') THEN v_bonus := 800; ELSIF v_hire_date > TO_DATE('01-1 月-88') THEN v_bonus := 1600; ELSE v_bonus := 2400; END IF; DBMS_OUTPUT.PUT_LINE(v_first_name||'雇员的雇佣日期是'||v_hire_date ||'、奖金是'||v_bonus); END; 3.2 CASE 表达式 ---------格式一--------- CASE 条件表达式 WHEN 条件表达式结果 1 THEN 语句段 1 WHEN 条件表达式结果 2 THEN 语句段 2 ...... WHEN 条件表达式结果 n THEN 语句段 n [ELSE 条件表达式结果] END; ---------格式二--------- CASE WHEN 条件表达式 1 THEN 语句段 1 WHEN 条件表达式 2 THEN 语句段 2 ...... WHEN 条件表达式 n THEN 语句段 n [ELSE 语句段] END; 例 4: 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; 例 5: DECLARE v_first_name employees.first_name%TYPE; v_job_id employees.job_id%TYPE; v_salary employees.salary%TYPE; v_sal_raise NUMBER(3,2); BEGIN SELECT first_name, job_id, salary INTO v_first_name, v_job_id, v_salary FROM employees WHERE employee_id = &emp_id; CASE WHEN v_job_id = 'PU_CLERK' THEN IF v_salary < 3000 THEN v_sal_raise := .08; ELSE v_sal_raise := .07; END IF; WHEN v_job_id = 'SH_CLERK' THEN IF v_salary < 4000 THEN v_sal_raise := .06; ELSE v_sal_raise := .05; END IF; WHEN v_job_id = 'ST_CLERK' THEN IF v_salary < 3500 THEN v_sal_raise := .04; ELSE v_sal_raise := .03; END IF; ELSE DBMS_OUTPUT.PUT_LINE('该岗位不涨工资: '||v_job_id); END CASE; DBMS_OUTPUT.PUT_LINE(v_first_name||'的岗位是'||v_job_id ||'、的工资是'||v_salary ||'、工资涨幅是'||v_sal_raise); END; 3.3 循环 3.3.1. 简单循环 LOOP 要执行的语句; EXIT WHEN <条件语句> --条件满足,退出循环语句 END LOOP; 例 6: DECLARE int NUMBER(2) :=0; BEGIN LOOP int := int + 1; DBMS_OUTPUT.PUT_LINE('int 的当前值为:'||int); EXIT WHEN int =10; END LOOP; END; 3.3.2. WHILE 循环 WHILE <布尔表达式> LOOP 要执行的语句; END LOOP; 例 7. DECLARE x NUMBER :=1; BEGIN WHILE x<=10 LOOP DBMS_OUTPUT.PUT_LINE('X 的当前值为:'||x); x:= x+1; END LOOP; END; 3.3.3. 数字式循环 [<<循环标签>>] FOR 循环计数器 IN [ REVERSE ] 下限 .. 上限 LOOP 要执行的语句; END LOOP [循环标签]; 每循环一次,循环变量自动加 1;使用关键字 REVERSE,循环变量自动减 1。跟在 IN REVERSE 后面的 数字必须是从小到大的顺序,而且必须是整数,不能是变量或表达式。可以使用 EXIT 退出循环。 例 8. BEGIN FOR int in 1..10 LOOP DBMS_OUTPUT.PUT_LINE('int 的当前值为: '||int); END LOOP; END; 例 9. CREATE TABLE temp_table(num_col NUMBER); DECLARE V_counter NUMBER := 10; BEGIN INSERT INTO temp_table(num_col) VALUES (v_counter ); FOR v_counter IN 20 .. 25 LOOP INSERT INTO temp_table (num_col ) VALUES ( v_counter ); END LOOP; INSERT INTO temp_table(num_col) VALUES (v_counter ); FOR v_counter IN REVERSE 20 .. 25 LOOP INSERT INTO temp_table (num_col ) VALUES ( v_counter ); END LOOP; END ; DROP TABLE temp_table; 例 10: DECLARE TYPE jobids_varray IS VARRAY(12) OF VARCHAR2(10); --定义一个 VARRAY 数据类型 v_jobids JOBIDS_VARRAY; --声明一个具有 JOBIDS_VARRAY 数据类型的变量 v_howmany NUMBER; --声明一个变量来保存雇员的数量 BEGIN --用某些 job_id 值初始化数组 v_jobids := jobids_varray('FI_ACCOUNT', 'FI_MGR', 'ST_CLERK', 'ST_MAN'); --用 FOR...LOOP...END LOOP 循环使用每个数组成员的值 FOR i IN v_jobids.FIRST..v_jobids.LAST LOOP --针对数组中的每个岗位,决定该岗位的雇员的数量 SELECT count(*) INTO v_howmany FROM employees WHERE job_id = v_jobids(i); DBMS_OUTPUT.PUT_LINE ( '岗位'||v_jobids(i)|| '总共有'|| TO_CHAR(v_howmany) || '个雇员'); END LOOP; END; 例 11 在 While 循环中嵌套 loop 循环 /*求 100 至 110 之间的素数*/ DECLARE v_m NUMBER := 101; v_i NUMBER; v_n NUMBER := 0; BEGIN WHILE v_m < 110 LOOP v_i := 2; LOOP IF mod(v_m, v_i) = 0 THEN v_i := 0; EXIT; END IF; v_i := v_i + 1; EXIT WHEN v_i > v_m - 1; END LOOP; IF v_i > 0 THEN v_n := v_n + 1; DBMS_OUTPUT.PUT_LINE('第'|| v_n || '个素数是' || v_m); END IF; v_m := v_m + 2; END LOOP; END; 3.4 标号和 GOTO PL/SQL 中 GOTO 语句是无条件跳转到指定的标号去的意思。语法如下: GOTO label; ...... <
还剩106页未读

继续阅读

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

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

需要 10 金币 [ 分享pdf获得金币 ] 4 人已下载

下载pdf

pdf贡献者

十月天

贡献于2013-01-08

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