什么是PL/SQL

就算你不知道PL/SQL，但是作为开发人员，你应该知道SQL。这就够了，PL/SQL和SQL是一路货色，都是一种数据库语言，而PL/SQL只不过是Oracle公司对SQL扩展的结果。好了，我想你已经知道什么是PL/SQL了——SQL的加强版，只对Oracle数据库好使，对MySQL等其它数据库不好使。不要问我为什么varchar2在MySQL中不好用这样的问题。

PL/SQL基本要素

作为一门开发语言，基本要素无外乎就是下面的几个方面：

• 字符单元
  其它语言能使用的合法字符，在PL/SQL中几乎都可以使用；

  请记住，在PL/SQL中，不区分大小写。

• 标识符
  标识符用于命名PL/SQL单元，比如：常量、变量、异常、存储过程等。

  • 标识符的最小长度是1，最大长度是30。但是，标识符的第一个字符必须是字母
  • 和其它语言一样，不能使用系统保留的关键字

• 数据类型
  基本分为以下几种：
  • 数字值，如：10、+2、-13等；
  • 字符值，字符值是单个字符用单引号括起来，如：’J’、’d’、’%’等。字符值是大小写敏感；
  • 字符串值，字符串值是把多个字符用单引号括起来，如：’JellyThink’、’http://www.jellythink.com‘、’果冻想’等。字符串大小写敏；
  • 布尔值，布尔值有三个取值：TRUE、FALSE和NULL；
• 注释
  • 单行注释

    -- 获得特殊号码
    select * from ac.sys_spec_tel;
    

  • 多行注释

    /**
     * 获得特殊号码
     * @author 果冻想
     * @date 20150713
     */
    select * from ac.sys_spec_tel;
    

PL/SQL块

写PL/SQL程序，就需要知道PL/SQL程序是以块为单位的，块其实就是一段程序，基本格式如下：

-- Created on 2015-7-13 by 果冻想 
[declare]
    -- declaration statements
    i integer;
begin
    -- executable statements

[exception]
    -- exception statements
end;

• declare声明部分，声明部分以declare开始，用于定义变量（常量），包括变量的名字、数据类型、初始值。声明部分是可选的；
• begin语句执行部分，以begin开始，该部分包含了所有的语句和表达式。在该部分可以执行SQL代码和SQL语句。这部分是必须的；
• exception异常处理部分，以exception开始，它也包含一系列代码，用于处理块执行中出现的异常。只有执行部分出现异常时，才会执行异常处理部分。这部分也是可选的；
• end标志块的结束。

常量和变量

• 常量与变量定义
  • 定义常量

    variable_name constant datatype [not null] [:=|default expression]
    

  • 定义变量

    variable_name datatype [not null] [:=|default expression]
    

• 变量作用范围

来，看一段代码，说说变量定义与变量作用范围：

declare 
    jellythink constant varchar2(40) := 'http://www.jellythink.com'; -- 定义一个常量
    site varchar2(40); -- 定义一个变量
begin
    site := 'http://www.jellythink.com';

    -- 嵌套块
    declare
        site varchar2(40); -- 覆盖了外部定义的site
    begin
        site := 'http://www.google.com';
        dbms_output.put_line(site); -- 输出:http://www.google.com
    end;
    dbms_output.put_line(site); -- 输出:http://www.jellythink.com
    dbms_output.put_line(jellythink); -- 输出:http://www.jellythink.com
end;

这个变量的作用域和C++的有几分相似，可以拿来比较一下。

数据类型

• 预定义PL/SQL数据类型
  《ORACLE数值类型详解–NUMBER、FLOAT、BINARY_FLOAT、BINARY_DOUBLE、BINARY_INTEGER》
  《ORACLE字符类型详解—-char、nchar、varchar、varchar2、nvarchar2》
• 用户定义PL/SQL子类型
  我们可以定义PL/SQL子类型，我们定义的子类型是其它类型的子集。自定义PL/SQL子类型的格式如下：

  SUBTYPE 子类型的名字 IS 基本数据类型 [(constraint)] [NOT NULL]
  

  看个简短的代码，就啥都明白了。

  declare 
      SUBTYPE AGE IS NUMBER(3); -- 用户自定义的类型
      v AGE; -- 使用自定义类型定义变量
  begin
      v := 20;
      dbms_output.put_line(v);
  end;
  

• CHAR、VARCHAR和VARCHAR2的区别
  《ORACLE字符类型详解—-char、nchar、varchar、varchar2、nvarchar2》
• NULL值的问题
  请不要忘了NULL值的存在，以后在做判断的时候，要想一想NULL值的问题。
• 数据类型转换
  和其它语言一样，分为以下两种转换（转换有风险，转换需谨慎）：
  • 隐士转换
    比如字符’1314′自动转成数字1314，没有任何问题；
  • 显示转换
    需要开发人员借助函数完成数据类型的转换；

  看以下这段关于数据类型转换的代码：

  declare 
      str varchar2(20) := '1314';
      i number;
      i2 number := 520;
  begin
      i := str; -- 隐士转换
      str := i2; -- 隐士转换
      dbms_output.put_line(i);
      dbms_output.put_line(str);
  
      -- 显示转换，将日期转换成指定格式的字符串
      dbms_output.put_line(to_char(sysdate, 'YYYY-MM-DD'));
  end;
  

PL/SQL表达式和运算符

• 算术运算符
  

  运算符	含义
  +	加
  -	减
  *	乘
  /	除
  **	乘方

• 逻辑运算符
  

  运算符	含义
  AND	两者同时为真，结果才为真（TRUE）
  OR	只要其中一个为真，结果就为真（TRUE）
  NOT	取反

• 比较运算符
  

  运算符	含义
  IS NULL	如果测试值为NULL，则返回TRUE；如果测试值不为NULL，则返回FALSE
  LIKE	比较字符串值，结合’%’进行模糊匹配
  BETWEEN	测试值是否在指定的范围之内
  IN	测试操作数是否在一系列的值中

• 关系运算符
  

  运算符	含义
  =	等于
  <>、!=、~=	不等于
  <	小于
  >	大于
  <=	小于等于
  >=	大于等于

• 连接运算符
  

  运算符	含义
  ||	连接符用于连接两个字符串

控制结构

这里的控制结构的含义与其它语言中的是一致的，所以只总结语法结构。

• 顺序结构
• 条件结构
  • IF

    IF 条件1 THEN
        执行语句1;
        执行语句2;
    END IF;
    

  • IF ELSE

    IF 条件1 THEN
        执行语句1;
        执行语句2;
    ELSIF 条件2 THEN
        执行语句3;
        执行语句4;
    ELSE
        执行语句5;
        执行语句6;
    END IF;
    

• CASE结构
  当分支比较多时，使用IF ELSE结构就使代码显的比较杂乱，不易阅读。这个时候使用CASE应该是最佳的选择。CASE结构如下：

  CASE [ expression ]
      WHEN condition_1 THEN result_1
      WHEN condition_2 THEN result_2
      ...
      WHEN condition_n THEN result_n
      ELSE result
  END
  

• 循环结构
  • LOOP
    Oracle会一直循环LOOP之间的语句，如果你不控制的话，就是死循环。

    LOOP
        执行循环语句;
    END LOOP;
    

  • FOR

    FOR counter IN 1..10 LOOP
        -- 测试一下，输出counter值
        dbms_output.put_line(counter);
    END LOOP;
    

  • WHILE

    WHILE i <= 10 LOOP
        dbms_output.put_line(i);
        i := i + 1;
    END LOOP;
    

• EXIT循环退出
  • 无条件退出

    LOOP
        执行语句1;
        EXIT;
    END LOOP;
    

    执行到EXIT就立刻退出循环。

  • 有条件退出

    LOOP
        执行语句1;
        EXIT WHEN 条件1;
    END LOOP;
    

    当执行到EXIT时，要对条件1进行判断，如果条件1返回真，将退出循环，跳到END LOOP之后接着执行代码；如果条件1返回假，将继续循环。

• CONTINUE循环继续
  • 无条件继续

    LOOP
        执行语句1;
        CONTINUE;
        执行语句2;
    END LOOP;
    

    当执行到CONTINUE语句时，将无条件终止本次循环的执行，也就是说跳过执行语句2，继续执行循环。

  • 有条件继续

    LOOP
        执行语句1;
    CONTINUE WHEN 条件1;
        执行语句2;
    END LOOP;
    

    当执行到CONTINUE WHEN语句时，将堆条件1进行判断，如果条件1结果为真，将终止本次循环，执行语句2将被跳过，开始下一次循环；如果测试结果为假，将继续执行CONTINUE WHEN语句后面的代码。

GOTO语句

臭名昭著的GOTO，本来不想讲，但是为了这篇文章的完整性，就使用下面这段代码，足以。

declare
    m varchar2(20);
begin
    m := 'JellyThink';
    <<Label1>>
    dbms_output.put_line(m);
    goto Label1;
end;

异常

内容太多，独立成文，请参见这篇文章《Oracle学习笔记——异常处理》。

2015年7月9日 于包头。