第二章 PL/SQL 块结构和组成元素

PL/SQL 块

PL/SQL 程序由三个块组成，即声明部分、执行部分、异常处理部分

PL/SQL 块的结构如下：

DECLARE   
/* 声明部分 : 在此声明 PL/SQL用到的变量 , 类型及游标，以及局部的存储过程和函数 */ 
BEGIN     
/*  执行部分 :  过程及 SQL 语句  , 即程序的主要部分  */ 
EXCEPTION    
/* 执行异常部分 : 错误处理  */ 
END;

其中执行部分是必须的。

PL/SQL 块可以分为三类：

无名块：动态构造，只能执行一次。

子程序：存储在数据库中的存储过程、函数及包等。当在数据库上建立好后可以在其它程序中调用它们。

触发器：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序。

PL/SQL 结构

PL/SQL 块中可以包含子块；

子块可以位于 PL/SQL 中的任何部分；

子块也即 PL/SQL 中的一条命令；

标识符

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

标识符名不能超过 30 字符；

第一个字符必须为字母；

不分大小写；

不能用’-‘(减号);

不能是 SQL 保留字。提示 : 一般不要把变量名声明与表中字段名完全一样 , 如果这样可能得到不正确的结果 .

例如：下面的例子将会删除所有的纪录，而不是 KING 的记录；
DECLARE    
 Ename varchar2(20) := ’KING’; 
BEGIN  
 DELETE FROM emp WHERE ename=ename; 
END; 
变量命名在 PL/SQL 中有特别的讲究，建议在系统的设计阶段就要求所有编程人员共同遵守一定的要求，使得整个系统的文档在规范上达到要求。下面是建议的命名方法：

标识符命名规则例子

程序变量 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

标识符	命名规则	例子
程序变量	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

PL/SQL 变量类型

在前面的介绍中，有系统的数据类型，也可以自定义数据类型。下表是 ORACLE 类型和 PL/SQL 中的变量类型的合法使用列表：

变量类型

在 ORACLE8i 中可以使用的变量类型有：

复合类型

ORACLE 在 PL/SQL 中除了提供象前面介绍的各种类型外,还提供一种称为复合类型的类型---记录和表.

记录类型
记录类型是把逻辑相关的数据作为一个单元存储起来，称作 PL/SQL RECORD 的域(FIELD)，其作用是存放互不相同但逻辑相关的信息。
定义记录类型语法如下:
 TYPE record_type IS RECORD(    
   Field1 type1  [NOT NULL]  [:= exp1 ],    
   Field2 type2  [NOT NULL]  [:= exp2 ],    
   . . .   . . .    
   Fieldn typen  [NOT NULL]  [:= expn ] ); 
例 1 ：
declare
     type test_rec is record(          
          l_name varchar2(30),
          d_id number(4));
     v_emp test_rec;
begin
     v_emp.l_name := 'Tom';
     v_emp.d_id :=1234;
     dbms_output.put_line(v_emp.l_name || ',' || v_emp.d_id);
end;
declare
     type test_rec is record(          
          l_name varchar2(30),
          d_id number(4));
     v_emp test_rec;
begin
     select last_name,department_id into v_emp
     from employees
     where employee_id = 200;
     dbms_output.put_line(v_emp.l_name || ',' || v_emp.d_id);
end;
提示
1.DBMS_OUTPUT.PUT_LINE 过程的功能类似于 Java 中的 System.out.println() 直接将输出结果送到标准输出中 .
2.在使用上述过程之前必须将 SQL * PLUS 的环境参数 SERVEROUTPUT
设置为 ON, 否则将看不到输出结果 : set serveroutput on
3.可以用 SELECT 语句对记录变量进行赋值,只要保证记录字段与查询结果列表中的字段相配即可。

使用%TYPE
定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用%TYPE。
使用%TYPE 特性的优点在于：
1.所引用的数据库列的数据类型可以不必知道；

2.所引用的数据库列的数据类型可以实时改变。

declare
     type test_rec is record(          
          l_name employee.last_name%type,
          d_id employee.department_id%type);
     v_emp test_rec;
begin
     select last_name,
     department_id into v_emp
     from employees  where employee_id = 200;
     dbms_output.put_line(v_emp.l_name || ',' || v_emp.d_id);
end;
使用%ROWTYPE
PL/SQL 提供%ROWTYPE 操作符, 返回一个记录类型, 其数据类型和数据库表的数据结构相一致。
使用%ROWTYPE 特性的优点在于：
1.所引用的数据库中列的个数和数据类型可以不必知道；
2.所引用的数据库中列的个数和数据类型可以实时改变。
declare
     v_emp employee%rowtype;
begin
     select * into v_emp
     from employees where employee_id = 200;
     dbms_output.put_line(v_emp.last_name || ',' || v_emp.department_id || ',' || v_emp.hire_date);
end;
PL/SQL 表(嵌套表)
PL/SQL 程序可使用嵌套表类型创建具有一个或多个列和无限行的变量, 这很像数据库中的表. 声明嵌套表类型的一般语法如下:
TYPE type_name IS TABLE OF  
{datatype | {variable | table.column} % type | table%rowtype}; 
declare
     type dep_table_type is table of departments%rowtype;
     my_dep_table dep_table_type := dep_table_type();
begin
     my_dep_table.extend(5);
    
     for i in 1 .. 5 loop
     select * from departments
     where department_id = 200 + 10 * i;
     end loop;

     dbms_output.put_line(my_dep_table.count());
     dbms_output.put_line(my_dep_table(1).department_id);
end;
说明
1.在使用嵌套表之前必须先使用该集合的构造器初始化它. PL/SQL 自动提供一个带有相同名字的构造器作为集合类型.
2.嵌套表可以有任意数量的行. 表的大小在必要时可动态地增加或减少: extend(x) 方法添加 x 个空元素到集合末尾; trim(x) 方法为去掉集合末尾的 x 个元素.

运算符和表达式(数据定义)

关系运算符

运算符意义

= 等于

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

< 小于

> 大于

<= 小于或等于

>= 大于或等于

运算符	意义
=	等于
<> , != , ~= , ^=	不等于
<	小于
>	大于
<=	小于或等于
>=	大于或等于

一般运算符

运算符意义

+ 加号

- 减号

* 乘号

/ 除号

:= 赋值号

=> 关系号

.. 范围运算符

｜｜字符连接符

运算符	意义
+	加号
-	减号
*	乘号
/	除号
:=	赋值号
=>	关系号
..	范围运算符
｜｜	字符连接符

逻辑运算符

运算符意义

IS NULL 是空值

BETWEEN AND 介于两者之间

IN 在一列值中间

AND 逻辑与

OR 逻辑或

NOT 取返,如 IS NOT NULL, NOT IN

运算符	意义
IS NULL	是空值
BETWEEN AND	介于两者之间
IN	在一列值中间
AND	逻辑与
OR	逻辑或
NOT	取返,如 IS NOT NULL, NOT IN

变量赋值

在 PL/SQL 编程中，变量赋值是一个值得注意的地方，它的语法如下：
variable  := expression ;    
variable 是一个 PL/SQL 变量, expression 是一个 PL/SQL 表达式.

字符及数字运算特点

空值加数字仍是空值：NULL + < 数字> = NULL
空值加（连接）字符，结果为字符：NULL || <字符串> = < 字符串>

BOOLEAN 赋值

布尔值只有 TRUE, FALSE 及 NULL 三个值。

数据库赋值

数据库赋值是通过 SELECT语句来完成的，每次执行 SELECT语句就赋值一次，一般要求被赋值的变量与 SELECT中的列名要一一对应。如：
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 语句中的列赋值给布尔变量

可转换的类型赋值

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') ;

变量作用范围及可见性

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

变量的作用范围是在你所引用的程序单元（块、子程序、包）内。即从声明变量开始到该块的结束。

一个变量（标识）只能在你所引用的块内是可见的。

当一个变量超出了作用范围，PL/SQL 引擎就释放用来存放该变量的空间（因为它可能不用了）。

在子块中重新定义该变量后，它的作用仅在该块内。

注释

在PL/SQL里，可以使用两种符号来写注释，即：

使用双 ‘-‘ ( 减号) 加注释 PL/SQL允许用 – 来写注释，它的作用范围是只能在一行有效。
如： V_Sal NUMBER(12,2); -- 工资变量。

使用 /* */ 来加一行或多行注释，如： /***********************************************/
/* 文件名： department_salary.sql */ /***********************************************/

提示：被解释存放在数据库中的 PL/SQL 程序，一般系统自动将程序头部的注释去掉。只有在 PROCEDURE 之后的注释才被保留；另外程序中的空行也自动被去掉

简单例子

简单数据插入例子

/* 本例子仅是一个简单的插入，不是实际应用。  */ 
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;