PL/SQL基础知识

1. PL/SQL块简介
  块（Block）是PL/SQL的基本程序单元，编写PL/SQL程序实际上就是编写PL/SQL程序块，要完成相对简单的应用功能，可能只需要编写一个PL/SQL块；而如果要实现复杂的应用功能，那么可以在一个PL/SQL快中嵌套其他PL/SQL块。块的嵌套层次没有限制。

1.1 PL/SQL块结构
  PL/SQL块由三个部分组成：定义部分、执行部分、例外处理部分。

• 定义部分用于定义常量、变量、游标、例外、复杂数据类型等。
• 执行部分用于实现应用模块功能，该部分包含了要执行的PL/SQL语句和SQL语句。
• 例外处理部分用于处理执行部分可能出现的错误。

结构如下：

Declare
/*
 *  定义部分 -- 定义常量、变量、复杂数据类型、游标、例外。
 */
beging
/*
 *  执行部分 -- PL/SQL和SQL语句
 */
exception
/*
 *  例外运行部分 -- 处理运行错误
 */
end;  /* 块结束标记 */

其中declare（定义）部分和exception(例外)部分是可选的，beging、end执行部分是必须的。

1.2 PL/SQL块分类
  PL/SQL块可划分为：匿名块、命名块、子程序和触发器等四种类型。
1.匿名块
  匿名块是指没有名称的PL/SQL块，匿名块既可以内嵌到应用程序中，也可以在SQL*PLUS中直接使用。

示例如下：

DECLARE
  v_avgsal NUMBER(6,2);
BEGIN
  SELECT avg(sal) INTO v_argsal FROM emp WHERE deptno = &no;
  dbms_output.put_line('平均工资' || v_avgsal);
END;  /* 块结束标记 */

如上所示，该PL/SQL块直接以DECLARE开始，没有给出任何名称，所以该PL/SQL块属于匿名块。
2.命名块
  是指具有特定名称标识的PL/SQL块，命名块与匿名块非常相似，只不过在PL/SQL块前使用<<>>来标记，当使用嵌套块时，为了区分多级嵌套层次关系，可以使用命名块加以区分。
示例如下：

<<outer>>
DECLARE
  v_deptno NUMBER(2);
  v_dname  VARCHAR2(10);
BEGIN
  <<inner>>
  BEGIN
    SELECT dname INTO v_dname FROM emp WHERE lower(ename) = lower('&name');
  END;  --<<inner>>
  
  SELECT deptno INTO v_deptno FROM emp WHERE deptno = v_deptno;
  dbms_output.put_line('部门名称' || v_dname);

END;  /* outer块结束标记 */

  如例所示，<<outer>>和<<inner>>分别是主块（外层块）和子块（内层块）的标记，这种PL/SQL块被称为命名块。
3.子程序
  子程序包括过程、函数和包。当开发PL/SQL块时，既可以开发客户端的子程序，也可以开发服务器端的子程序。将业务逻辑集成到PL/SQL子程序中，可以简化客户端程序的开发和维护，并提高应用程序的性能。
[list]

过程

  过程用于执行特定的操作，在建立过程时，既可以制定输入参数（IN），也可以指定输出参数（OUT）。通过在过程中指定输入参数，达到将应用程序中的数据传递到执行部分；通过使用输出参数将执行部分的数据传递到应用程程序中。
  CREATE PROCEDURE命令就是用来创建过程的。示例如下：

CREATE PROCEDURE update_sal(name VARCHAR2, newsal NUMBER)
IS
BEGIN
  UPDATE emp SET sal = newsal WHERE lower(ename) = lower(name);
END;

如示例所示，过程update_sal用于更新雇员工资。当在SQL*PLUS中调用该过程时，可以使用execute命令或者call命令。示例如下：

SQL > exec update_sal('scott', 3000);
SQL > call update_sal('scott', 4000);

函数

  函数用于返回特定的数据。当建立函数时，在函数的头部必须包含RETURE子句，而在函数体内必须要包含RETURN语句返回数据。CREATE FUNCTION命令就是用来创建函数的。示例如下：

CREATE FUNCTION annual_income(name VARCHAR2)
RETURN NUMBER IS 
  annual_salary NUMBER(7,2);
BEGIN
  SELECT sal*12 + nvl(comm, 0) INTO annual_salary FROM emp WHERE lower(ename) = lower(name);
  RETURN annual_salary;
END;

如上例所示，函数annual_income用于返回雇员的全年收入（包括工资和奖金）。当调用该函数时，可以使用多种方法。在这里使用SQL*PLUS绑定变量存放输出结果，示例如下：

SQL > VAR income NUMBER
SQL > call annual_income('scott') INTO :income;
SQL > PRINT income
    INCOME
-----------
    24000

包

  包用于逻辑组合相关的过程和函数，它由包规范和包体两部分组成。包规范用于定义公用的常量、变量、过程和函数。CREATE PACKAGE命令就是用来创建包的。示例如下：

CREATE PACKAGE emp_pkg IS
  PROCEDURE update_sal(anme varchar2, newsal NUMBER);
  FUNCTION annual_income(anme VARCHAR2) RETURN NUMBER;
END;

包规范只包含了过程和函数的说明，而没有过程和函数的实现代码。包体用于实现包规范中的过程和函数，建立包体可以使用CREATE PACKAGE BODY命令，示例如下：

CREATE PAKAGE BODY emp_pkg IS
  PROCEDURE udapte_sal(name VARCHAR2, newsal NUMBER)
  IS
  BEGIN
    UPDATE emp SET sal=newsal
    WHERE lower(ename) = lower(name)
  END;
  FUNCTION annual_income(name VARCHAR2) RETURN NUMBER
  IS
    annual_salary NUMBER(7,2);
  BEGIN
    SELECT sal*12+nvl(comm,0) INTO annual_salary
    FROM emp WHERE lower(ename) = lower(name);
    RETURN annual_salary;
  END;
END;

  当调用包的过程和函数时，在过程和函数前必须要有包名作为前缀（包名.子程序名），而如果要访问其他方案的包，还必须要加方案名称作为前缀（方案名.包名.子程序名）。示例如下：

SQL > call emp_pkg.update_sal('scott',1500);
......
SQL > VAR income NUMBER
SQL > call emp_pkg.annual_income('scott') INTO :income;
SQL > PRINT income
    INCOME
-----------
    18000

触发器

  触发器是指隐含执行的存储过程。当定义触发器时，必须要指定触发器事件以及触发器操作，常用的触发器事件包括INSERT，UPDATE和DELETE时语句，而触发器操作是执行一段PL/SQL块。在SQL*PLUS中创建触发器是使用CREATE TRIGGER命令来完成的。示例如下：

CREATE TRIGGER update_cascade
  ALTER UPDATE OF deptno ON dept
  FOR EACH ROW
BEGIN
  UPDATE emp SET deptno =:new.deptno
  WHERE deptno=:old.deptno;
END;

  如上例所示，触发器update_cascade用于实现级联更新；如果不建立该触发器，那么更新dept表的deptno列数据时，就会显示错误“ORA-02292：违反完整约束条件（SCOTT.FK_DEPTNO）- 已找到子记录日志”；而在建立该触发器后，当更新deptno时，就会级联更新emp表的deptno列的相关数据。
[/list]

2. 定义并使用变量
  编写PL/SQL程序时，若临时存储数值，并需要定义变量和常量；若要在应用程序和子程序之间传递数据，那么必须为子程序指定参数。而在PL/SQL中指定变量、常量、参数时，则必须为他们指定PL/SQL中的类型。在编写PL/SQL程序时，可以使用标量（Scalar）类型、复合（Composite）类型、参照（Reference）类型和LOB（Large Object）类型等四种类型。
  其中在标量（Scalar）类型中，Oracle 10g 时新增了BINARY_DOUBLE和BINARY_FLOAT两个类型。另外在Oracle 9i 时，还增加了一些日期时间类型，包括TIMESTAMP,TIMESTAMP WITH ZONE,TIMESTAMP WITH LOCAL ZONE,INTERVAL DAY TO SECOND,INTERVAL YEAR TO MONTH等。这些数据类型都可以在PL/SQL块中引用。

2.1 标量变量
  标量变量是指能存放单个数值的变量。当编写PL/SQL块时，最常用的变量就是标量变量。当定义标量变量时，必须要指定标量数据类型。标量数据类型包括数字类型，字符类型，日期类型和布尔类型。每种类型又包括相应的子类型，例如：NUMBER类型就包括INTEGER，POSITIVE等子类型。

1.常用标量类型

1.1 VARCHAR2(n)
  该数据类型用于定义可变长度的字符串，其中n用于指定字符串的最大长度，其最大字节为32767。在使用该数据类型定义变量时，必须要指定长度。需要注意，当PL/SQL块中使用该数据类型操作VARCHAR2表列时，其数值长度不应该操作4000个字节。
1.2 CHAR(n)
  该数据类型用于定义固定长度的字符串，其中n用于指定字符串的最大长度，其最大长度为32767字节。当使用该数据类型时，如果没有指定n，其默认值为1。需要注意的是，在PL/SQL块中使用该数据类型定义变量时，其最大数值长度不应该超过2000字节。
1.3 NUMBER(p,s)
  该数据类型用于定义固定长度的整数和浮点数，其中p代表精度，用于指定数值的总位数；s标识标度，用于指定小数点后的数字位数。例如指定了NUMBER（6,2），那么证书位数最大值应该是4位。
1.4 DATE
  该数据类型用于定义日期和时间数据。其数据长度为固定长度。但需要注意，在给日期DATE变量赋值时，数据必须要与日期格式和日期语言匹配。
1.5 TIMESTAMP
  该数据类型是Oracle9i新增的数据类型。它也用于定义日期和时间类型。给TIMESTAMP数据类型赋值的方法与给DATE类型赋值的方法一致。但当现实TIMESTAMP变量数据时，不仅会显示日期，而且还会现实时间和上午下午的标记。
1.6 LONG和LONG RAW
  LONG数据类型用于定义变长字符串，类似于VARCHAR2数据类型，但其字符串的最大长度为32760字节；LONG RAW数据类型用于定义变长的二进制数据，其数据的最大长度为32760个字节。
1.7 BOOLEAN
  该数据类型用于定义布尔变量，其变量值为TRUE,FALSE和NULL。需要注意的是该数据类型是PL/SQL的数据类型。表列不能采用该数据类型。
1.8 BINARY_INTEGER
  该数据类型用于定义整数，其值范围在-2147483647和2147483647之间，在Oracle9i之前，当在PL/SQL中定义PL/SQL表时，必须使用该数据类型作为下标的数据类型。需要注意的是该数据类型是PL/SQL的数据类型。表列不能采用该数据类型。
1.9 BINARY_FLOAT和BINARY_DOUBLE
  BINARY_FLOAT和BINARY_DOUBLE是Oracle10g新增的数据类型，分别用于定义单精度的浮点数和双精度的浮点数。这两种数据类型主要用于高速的科学计算，当为BINARY_FLOAT变量赋值时，应该带有后缀f（例如：1.5f）；当为BINARY_DOUBLE变量赋值时，应该带有后缀d（例如：3.000095d）。
2.定义标量变量
  当编写PL/SQL程序时，如果要引用标量变量，必须首先在定义部分定义标量变量，然后才能在执行部分或例外处理部分中使用这些标量变量。
2.1 语法
在PL/SQL中定义变量和常量的语法如下：
  identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr]

• identifier：用于指定变量或常量的名称。
• CONSTANT：用于指定常量；当定义常量时，必须指定它的初始值，并且其数值不能变。
• datatype：用于指定变量或常量的数据类型。
• NOT NULL：用于强制初始化变量（不能为NULL）。当指定NOT NULL时，必须要为变量提供数值。
• := >> 用于指定变量和常量的初始值。
• DEFAULT：用于为变量和常量指定初始值。
• expr：用于指定初始值的PL/SQL表达式；可以是文本值、其他变量、函数等。

2.2 定义标量变量示例
当定义标量变量时，必须要使用标量数据类型。示例如下：
  v_ename   VARCHAR2(10);
  v_sal   NUMBER(6,2);
  v_balance   BINARY_FLOAT; --Oracle10g新的数据类型
  c_tax_rate   CONTANT NUMBER(3,2):=5.5;
  v_hiredate   DATE;
  v_valid   BOOLEAN NOT NULL DEFAULT FALSE;
如例所示：以上语句定义了5个变量和1个常量，并且为v_valid提供了默认值。需要注意的是，当定义变量时，没有指定初始值，那么变量的初始值为NULL；
2.3 使用标量变量
  当在定义部分定义了标量变量之后，在执行部分和例外处理部分可以引用这些标量变量。需要注意的是，在PL/SQL块中为变量赋值时，不同与其他的变成语言，必须要在等号前加上冒号（:=）。下面以输入雇员号显示雇员名称