PL/SQL Step By Step（一）

1.概述

    PL/SQL（Procedural Language/Structured Query Language）是Oracle对标准数据库语言SQL的过程化扩充，它将数据库技术和过程化程序设计语言联系起来,是一种应用开发语言，可使用循环，分支处理数据，将SQL的数据操纵功能与过程化语言数据处理功能结合起来。 PL/SQL的使用,使SQL成为一种高级程序设计语言，支持高级语言的块操作，条件判断，循环语句，嵌套等，与数据库核心的数据类型集成，使SQL 的程序设计效率更高。（更加详细的介绍，大家可以Google一下）

    PL/SQL程序主要分为两大类：匿名程序和命名程序。下面先讲匿名程序。一个典型的匿名块结构分为以下几个部分：

DECLARE--可选部分
    --变量、常量、游标、用户定义异常的声明
BEGIN--必要部分
    --SQL语句和PL/SQL语句构成的执行程序
EXCEPTION--可选部分
    --程序出现异常时，捕捉异常并处理异常
END；--必须部分，注意END后面的分号

    按照上面的格式，我们先写一个简单完整的匿名块： 

DECLARE
  v_counter NUMBER(3);
  v_user ALL_USERS.USERNAME%TYPE;
  v_today DATE;
BEGIN
  SELECT  SYSDATE,USER
  INTO    v_today,v_user
  FROM DUAL;
  
  DBMS_OUTPUT.PUT_LINE(
       'today:' ||
       TO_CHAR(v_today,'YYYY-MM-DD'));
  DBMS_OUTPUT.PUT_LINE('Schema:' || v_user);
  
  v_counter:=0;
  LOOP
    v_counter:=v_counter + 1;
    EXIT WHEN v_counter > 10;
    DBMS_OUTPUT.PUT_LINE('line:' || v_counter);
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    --Do log operations  
    --COMMIT;
END;

    下面逐一解释：

• 声明部分

    定义了三个变量：v_counter，number类型；v_user，ALL_USERS视图中USERNAME字段的类型（XX%TYPE的意思就是表示XX的类型）；v_today，DATE类型。

• 执行部分

    将当前日期和当前用户查询出来分别赋值给v_today和v_user变量，并将二者打印输出；

    打印从0到9这10个数字。v_counter充当迭代用的变量。

• 异常处理

    有异常出现时怎么处理。

• 结束    

    结束部分就是按格式写上就好，没什么好说的。

    上面的例子中还出现了单行注释，也就是以--开头的一行都会被视为注释。此外，PL/SQL还支持多行注视/* */。此外，对于PL/SQL的标识符来说，必须以字符开头，并且最大不超过30个字符，这一点与很多语言略有不同。还要特别说的一点是，PL/SQL的字符串字面量是用单引号'括起来的。

2.常量与变量

    PL/SQL的变量定义的格式如下：

VARIABLE_NAME DATATYPE;

    变量的类型必须给出，可以是所有PL/SQL支持的类型，具体有哪些我们后面会继续讲。此外，在定义变量的时候可以给变量给出初始值，像下面这样：

VARIABLE_NAME DATATYPE := INIT_VALUE;

    PL/SQL的赋值操作符是:= ，如果没有给出初始值，那么变量默认的初始值是NULL。

    定义常量的方法与定义变量的方法基本类似，只是需要加上关键字CONSTANT，并且必须给定初始值，如下：

VARIABLE_NAME CONSTANT DATATYPE := INIT_VALUE;

    定义常量时必须给出常量值，并且一旦赋值，之后就不允许再改变了。下面是一个例子： 

--常量值不可变例子
DECLARE
  v_name CONSTANT VARCHAR2(8) := 'Tom';
BEGIN
  v_name := 'Jerry';
  DBMS_OUTPUT.put_line(v_name);
END;
/

    运行上面的程序，Oracle会报错：

ORA-06550: 第 5 行, 第 3 列: 
PLS-00363: 表达式 'V_NAME' 不能用作赋值目标
ORA-06550: 第 5 行, 第 3 列: 
PL/SQL: Statement ignored

    PL/SQL中，常量、变量支持的所有类型以及详细的解释建议大家结合你使用的Oracle版本参考Oracle官方文档，版本之间会有一些微小调整。这里简要的列举一些：

CHAR(N)：N位字符，不足N位，自动用空格补全。N必须要指定。
VARCHAR2(N)：最多N位字符，N可以不指定，默认为1。不足N位时，不会用空格补全N位。因此，用的比较多。
DATE:日期时间类型，默认按照数据库要求的格式显示。
NUMBER(N,M)：数字型。N位数字，其中M位为小数的位数。
BOOLEAN：SQL中不支持这个类型，但是PL/SQL支持，有TRUE和FALSE两种。
LONG：可以接收很长的字符数据，多达32760字节。
RAW：接收二进制数据，长度达到32767字符。PL/SQL无法解析其内容。
LONG RAW：比RAW更大而已。
MLSLABEL：安全操作系统标签，Trusted Oracle中使用。
ROWID：用来唯一表示记录在数据库中的物理存储地址的一种类型。Oracle会自动为每条记录添加这个伪列。
BLOB：最大4GB的二进制数据。
CLOB：最大4GB的文本数据。
NCLOB：多字节字符的BLOB。比如存储汉字。
BFILE：一个指向OS文件的指针。
BINARY_INTEGER
INT/INTEGER：整数
SMALLINT：小一点的整数。
POSITIVE：正数
NATURAL：自然数
NUMERIC/DEC/DECIMAL：类似于NUMBER
REAL/FLOAT：浮点数
TABLE：符合类型，类似于数组
RECORD：复合类型，表示一条记录

    在声明部分，不仅可以定义像上面那种的常量或者变量，还可以声明显式游标或者用户自定义的异常。关于游标和异常后面的文章还会讨论。

3.执行部分

    这个部分实际上是我们真正处理任务的一些操作，可以说是PL/SQL块中最重要的部分。主要包括表达式、赋值语句、条件语句、比较语句、循环、游标控制语句：

• 表达式

    表达式并不是单独的语句，而是包含在单独语句中的小的代码单元。表达式一般主要用在赋值语句和条件语句中。表达式又分为算术表达式和比较表达式。这个和很多第三代语言是类似的，这里就不赘述。

    几乎所有的SQL函数都可以以用作表达式，例如函数SUBSTR：

SUBSTR(v_str,m,n);

    这就是一个合法的表达式。在PL/SQL中，唯一不能作为表达式的SQL函数是DECODE函数，因为，在PL/SQL中，已经有了IF-THEN-END语句可以替代它。

    表达式之间还可以通过逻辑运算符连接起来。这一点也是比较简单的。

• 赋值语句

    赋值语句上面已经见到了，通过赋值运算符:=实现赋值操作。因此，PL/SQL中的等于使用=实现的，不同于C语言中的==。

• 条件语句

    条件语句无非是if语句及其各种变种。PL/SQL的格式稍微有点不同，最简单的形式如下：

IF(...) THEN
  --DO STH
ELSE
  --DO STH ELSE
END IF;

    这是最简单的一种形式。C语言中还有else if的形式，PL/SQL中也有，如下：

IF () THEN
  --A
ELSIF () THEN
  --B
ELSE
  --C
END IF;

    使用上就和别的语言没有什么差别，只是初学的时候需要注意一下写法格式

• 循环

    循环是编程语言中非常重要的一种结构，它用于对一组操作对象执行某一组相同的操作。循环有很多种不同的形式，下面一一介绍：

    形式1：

LOOP
  --做若干操作
  EXIT WHEN ...;--什么时候退出循环
END LOOP;

    第一部分的例子中就用到了这种形式的循环了。

    形式2：

LOOP
  --做若干操作
  IF ...--什么时候退出循环
  THEN
    EXIT;
  END IF;
END LOOP;

    和形式1其实是等价的

    形式3：while循环

WHILE(v_i < 10)
LOOP
  ----做若干操作
  v_i := v_i + 1;
END LOOP;

   形式4：数值FOR循环

FOR v_i IN 1..10
LOOP
  --做一些操作
END LOOP;

    形式5：游标FOR循环

    这一部分，我们在后面讲到游标的时候再讲。

    下面，我们先用四种形式打印数字1-10，代码如下：

--形式1
DECLARE
  v_limit number(2) := 1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE(v_limit);
    v_limit := v_limit + 1;
    EXIT WHEN v_limit > 10;
  END LOOP;
END;
/

--形式2
DECLARE
  v_limit number(2) := 1;
BEGIN
  LOOP
    IF v_limit > 10 
    THEN
      EXIT;
    END IF;
    DBMS_OUTPUT.PUT_LINE(v_limit);
    v_limit := v_limit + 1;    
  END LOOP;
END;
/

--形式3
DECLARE
  v_limit number(2) := 1;
BEGIN
  WHILE v_limit <= 10
  LOOP
    DBMS_OUTPUT.PUT_LINE(v_limit);
    v_limit := v_limit + 1;    
  END LOOP;
END;
/

--形式4
DECLARE
  v_limit number(2) := 1;
BEGIN
  FOR v_limit IN 1..10
  LOOP
    DBMS_OUTPUT.PUT_LINE(v_limit); 
  END LOOP;
END;
/

PS：

    1.运行上面的PL/SQL程序可能会没有输出，这是因为Oracle默认没有打开serveroutput选项，只需要在SQL PLUS中执行：

SET SERVEROUTPUT ON；

    2.上面代码片段后面的/不是代码本身的内容，而是让SQLPLUS立刻运行这段代码的一个操作。