Oracle数据库之PL/SQL程序基础设计

一、PL/SQL块结构 前边我们已经介绍了PL/SQL块的结构， http://www.linuxidc.com/Linux/2015-01/112351.htm 再来回顾一下： DECL

一、PL/SQL块结构

前边我们已经介绍了PL/SQL块的结构， 再来回顾一下：

DECLARE /* * 声明部分——定义常量、变量、复杂数据类型、游标、用户自定义异常 */ BEGIN /* * 执行部分——PL/SQL语句和SQL语句 */ EXCEPTION /* * 异常处理部分——处理运行异常 */ END; /*块结束标记 */

要实现PL/SQL程序设计，先介绍如下的基本内容：

二、标识符

PL/SQL程序设计中的标识符定义与SQL的标识符定义的要求相同：

注意: 一般不要把变量名声明与表中字段名完全一样，如果这样可能得到不正确的结果。

三、变量

1. 变量定义

声明：

variable_name Type;

示例：

my_var NUMBER(4);

赋值：

variable := expression ;

示例：

my_var := 32 * 8 + 79; -- 声明并赋值 cnt NUMBER(2) := 3;

2. 变量作用域

在PL/SQL编程中，如果在变量的定义上没有做到统一的话，可能会隐藏一些危险的错误，这样的原因主要是变量的作用域所致。变量的作用域是指变量的有效作用范围，与其它高级语言类似，PL/SQL的变量作用范围特点是：

四、运算符

1. 关系运算符

运算符说明

= 等于

<> , != , ~= , ^= 不等于

< 小于

> 大于

<= 小于等于

>= 大于等于

2. 逻辑运算符

运算符说明

AND 逻辑与

OR 逻辑或

NOT 逻辑非，取反

3. 算术运算符

运算符说明

+ 加

- 减

* 乘

/ 除

4. 其它运算符

运算符说明

IS NULL 是空值

BETWEEN AND 介于两者之间

IN 在一列值中间

:= 赋值运算符

=> 关系号

.. 范围运算符

|| 字符连接符

五、数据类型

前面已经介绍过Oracle中的基本数据类型，除了这些基本数据类型外，Oracle中还有其它的数据类型，我们一一来分析。

1. BOOLEAN

该数据用于定义布尔变量，其变量的值为TRUE、FALSE或NULL。注意此类型只能在PL/SQL中使用，表列是没有此类型的。

2. %TYPE

通常用于指定表的某个列的数据类型，可以理解为“的类型”(小技巧：%读“的”)。

示例：

emp_id employee.id%TYPE;

使用%TYPE特性的优点在于：

所引用的数据库列的数据类型可以不必知道。

所引用的数据库列的数据类型可以实时改变，容易保持一致，也不用修改PL/SQL程序。

3. %ROWTYPE

%ROWTYPE操作符，返回一个记录类型，其数据类型和数据库表的数据结构相一致。

示例：

rec employee%ROWTYPE;

使用%ROWTYPE特性的优点在于：

所引用的数据库中列的个数和数据类型可以不必知道。

所引用的数据库中列的个数和数据类型可以实时改变，容易保持一致，也不用修改PL/SQL程序。

4. 复合数据类型

4.1. 记录（Record）

记录类型类似于C语言中的结构数据类型，它把分离的、逻辑相关的、基本数据类型的变量组成一个整体存储起来，它必须包括至少一个标量型或RECORD数据类型的成员，称作PL/SQL RECORD的域(FIELD)，其作用是存放互不相同但逻辑相关的信息。

语法：

TYPE record_name IS RECORD( varable1 data_type1 [NOT NULL] [:= default_value ], varable2 data_type2 [NOT NULL] [:= default_value ], ......, varablen data_typen [NOT NULL] [:= default_value ] );

在使用记录数据类型变量时，需要先在声明部分先定义记录的组成、记录的变量，然后在执行部分引用该记录变量本身或其中的成员。

示例1：

DECLARE -- 定义记录类型 TYPE TEST_REC IS RECORD( rec_name VARCHAR2(30) NOT NULL := '匿名', info VARCHAR2(100) ); -- 声明记录变量 rec_emp TEST_REC; BEGIN rec_emp.rec_name :='刘强东'; rec_emp.info :='谈京东的业务发展'; DBMS_OUTPUT.PUT_LINE(rec_emp.rec_name||' ' ||rec_emp.info); END;

说明：我们可以使用DBMS_OUTPUT.PUT_LINE(outdata)或DBMS_OUTPUT.PUT(outdata)将处理结果输出到屏幕上。

运行结果：

刘强东 谈京东的业务发展

示例2（示例中所用到的表及数据见文末）：

DECLARE --定义与employee表中的几个列相同的记录数据类型 TYPE RECORD_TYPE_EMPLOYEES IS RECORD( e_id employee.id%TYPE, e_name employee.name%TYPE, e_birth employee.birthday%TYPE, e_address employee.address%TYPE, e_did employee.did%TYPE ); --声明一个该记录数据类型的记录变量 emp_record RECORD_TYPE_EMPLOYEES; BEGIN SELECT id, name, birthday, address, did INTO emp_record FROM employee WHERE id = &emp_id; DBMS_OUTPUT.PUT_LINE('雇员名称：'||emp_record.e_name ||' 出生日期：'||emp_record.e_birth ||' 地址：'||emp_record.e_address); END;

说明：可以用SELECT语句对记录变量进行赋值，只要保证记录字段与查询结果列表中的字段相配即可。&emp_id表示接收用户的输入。

紧跟着&符号后面的变量被称为替换变量。替换变量对数据库引擎是“不可见的”。其实PL/SQL会用用户输入的文本内容来替换变量引用。

如果你想在代码的不同地方引用相同的替换变量，那就需要在第一个替换变量前面放两个&符号，这样才会为你保留这个值以便后续使用。

输入2，输出结果：

雇员名称：刘兰 出生日期：03-9月 -85 地址：四川成都

4.2. 表(TABLE)

4.2.1. 索引表

索引表也称为PL/SQL表，它是Oracle早期版本用于处理PL/SQL数组的数据类型。索引表的元素个数没有限制，并且下标可以为负值。

它与记录类型相似，但它是对记录类型的扩展。它可以处理多行记录，使得可以在PL/SQL中模仿数据库中的表。

注意，索引表只能作为PL/SQL复合数据类型使用，而不能作为表列的数据类型使用。

语法结构：

TYPE table_name IS TABLE OF element_type [NOT NULL] INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2];

关键字INDEX BY表示创建一个主键索引，，以便引用记录表变量中的特定行。

表是集合类型，集合类型常用方法有：

方法说明

EXISTS(n) 如果集合的第n个成员存在，则返回true

COUNT 返回已经分配了存储空间即赋值了的成员数量

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类型变量中出现的最高下标值

集合方法调用语法为：

collection_name.method_name[(parameters)]