oracle 11g 的 PLSQL 基础

1. PL/SQL 简介

PL/SQL 是过程语言 (Procedural Language) 与结构化查询语言 (SQL) 结合而成的编程语言，PL/SQL 是对 SQL 的扩展。
PL/SQL 支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构，可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑。并且与 Oracle 服务器和 Oracle 工具紧密集成，具备可移植性、灵活性和安全性。

2. PL/SQL 优点

支持 SQL，在 PL/SQL 中可以使用：
- 数据操纵命令
- 事务控制命令
- 游标控制
- SQL 函数和 SQL 运算符
用户把PL/SQL块整个发送到服务器端，oracle服务器端编译、运行，再把结果返回给用户；
可移植性，可运行在任何操作系统和平台上的Oralce 数据库；
更佳的性能，PL/SQL 经过编译执行；
安全性，可以通过存储过程限制用户对数据的访问；
与 SQL 紧密集成，简化数据处理：
- 支持所有 SQL 数据类型
- 支持 NULL 值
- 支持 %TYPE 和 %ROWTYPE 属性类型（%TYPE 表示某一列的类型，%ROWTYPE 表示某一行的类型）

3. PL/SQL 体系结构

执行步骤：

首先用户将 PL/SQL 块发送给 oracle 服务器，并由 PL/SQL 引擎中的过程语句执行器执行过程语句，并转变称为 SQL 语句；
PL/SQL 引擎将 SQL 语句发往 SQL 引擎 SQL 语句执行器并执行 SQL 语句；
SQL 引擎将执行结果发给用户。

4. PL/SQL 块简介

PL/SQL 块是构成 PL/SQL 程序的基本单元，将逻辑上相关的声明和语句组合在一起。PL/SQL 分为三个部分，声明部分、可执行部分和异常处理部分，具体如下：

[DECLARE declarations] -- 声明部分，如声明变量（可选）
BEGIN
executable statements  -- 可执行部分
[EXCEPTION handlers]   -- 异常处理部分（可选）
END;

5. 变量和常量

PL/SQL 块中可以使用变量和常量，需注意：

在声明部分声明要使用的变量，且需要在使用前先声明；
声明时必须指定数据类型，每行声明一个标识符；
在可执行部分的 SQL 语句和过程语句中使用。

声明变量和常量的语法如下：

identifier [CONSTANT] datatype [NOT NULL]   
[:= | DEFAULT expr];

-- identifier 表示变量名称
-- CONSTANT 为常量标识
-- datatype 为变量数据类型
-- NOT NULL 为非空标识
-- := 为赋值符号
-- DEFAULT expr 为设置默认值（表达式）

给变量赋值有两种方法：

使用赋值语句 :=
使用 SELECT INTO 语句

示例如下所示：

DECLARE
  icode VARCHAR2(6);
  p_catg VARCHAR2(20);
  p_rate NUMBER;
  c_rate CONSTANT NUMBER := 0.10; -- 常量赋值为 0.10
BEGIN
  icode := 'i205';
  SELECT p_category, itemrate * c_rate
  INTO  p_catg, p_rate
  FROM itemfile WHERE itemcode = icode;
END;

6. 数据类型

PL/SQL 支持的内置数据类型有如下几种：

6.1 数字数据类型

6.2 字符数据类型

字符数据类型包括：CHAR、VARCHAR2、LONG、RAW 和 LONG RAW 五种。PL/SQL 的数据类型与 SQL数据类型的比较如下图所示：

6.3 日期时间和布尔数据类型

日期时间类型存储日期和时间数据，常用的两种日期时间类型有 DATE 和 TIMESTAMP；
布尔数据类型：
- 此类别只有一种类型，即BOOLEAN类型
- 用于存储逻辑值(TRUE、FALSE和NULL)
- 不能向数据库中插入BOOLEAN数据
- 不能将列值保存到BOOLEAN变量中
- 只能对BOOLEAN变量执行逻辑操作

6.4 LOB 数据类型

用于存储大文本、图像、视频剪辑和声音剪辑等非结构化数据。LOB 数据类型可存储最大 4GB的数据。LOB 类型包括：

BLOB 将大型二进制对象存储在数据库中
CLOB 将大型字符数据存储在数据库中
NCLOB 存储大型UNICODE字符数据
BFILE 将大型二进制对象存储在操作系统文件中

LOB 类型的数据库列仅存储定位符，该定位符指向大型对象的存储位置。可以使用DBMS_LOB程序包来操纵 LOB 数据。下面我们尝试来操作 LOB 数据类型数据：

1）首先先创建一张 my_book_text 表，并插入测试数据：

create table my_book_text (
    chapter_id number(8) primary key,
    chapter_text clob
);
-- 插入测试数据
insert into my_book_text values (5, 'LOB 类型的数据库列仅存储定位符，该定位符指向大型对象的存储位置。可以使用DBMS_LOB程序包来操纵 LOB 数据。下面我们尝试来操作 LOB 数据类型数据');

（2）编写如下的 PL/SQL 执行块：

set serveroutput on; -- 开启命令行输出
declare
 clob_var clob;
 amount integer;
 offset integer;
 output_var varchar2(100);
begin
 select chapter_text into clob_var from my_book_text where chapter_id = 5;
 amount := 24; -- 要读取的字符数
 offset := 1; -- 起始位置
 DBMS_LOB.READ(clob_var, amount, offset, output_var); -- 读取 clob_var 指定位置的字符赋值到 output_var 中
 DBMS_OUTPUT.PUT_LINE(output_var);
END;
/

7. 属性类型

属性类型用于引用数据库列的数据类型，以及表示表中一行的记录类型。属性类型有两种：

%TYPE - 引用变量和数据库列的数据类型
%ROWTYPE - 提供表示表中一行的记录类型

使用属性类型的优点：不需要知道被引用的表列的具体类型，且如果被引用对象的数据类型发生改变，PL/SQL 变量的数据类型也随之改变，健壮性强。

示例如下所示：

1）首先查看 student 表的数据如下所示：

SQL> select * from student;

                                    SNO SNAME                                         SAGE
--------------------------------------- ---------- ---------------------------------------
                                      1 Tom                                             21
                                      2 Kite                                            22
                                      3 Bob                                             23
                                      4 Mike                                            24

2）然后执行如下的 PL/SQL 语句，并输出学号为 1 的学生的年龄。可以发现即便不知道 student 表字段的类型，我们依旧可以声明对应类型的 my_sage 的变量：

set serveroutput on; -- 开启打印输出
declare
 my_sage student.sage%type; -- 此时不需要知道 student 表 sage 字段的类型
begin
 select sage into my_sage from student where sno = 1;
 dbms_output.put_line('学号为 1 的学生的年龄为 ' || my_sage);
end;
/
学号为 1 的学生的年龄为 21 -- 执行结果

8. Oracle 11g 的 PL/SQL 序列改进

在PL/SQL中取出序列的nextval、currval时，可以不使用 select语句，如 select seq1.nextval from dual;，在 PL/SQL 可以简化如下：

declare
    id int;
begin
    id := seq1.nextval; -- 直接赋值，而不用通过 select 获取
    dbms_output.put_line('当前序列值：' || id);
end;
/

9. 逻辑比较

逻辑比较用于比较变量和常量的值，这些表达式称为布尔表达式。布尔表达式由关系运算符与变量或常量组成，布尔表达式的结果为TRUE、FALSE或NULL，通常由逻辑运算符AND、OR和NOT连接。

布尔表达式有三种类型：

数字布尔型
字符布尔型
日期布尔型

10. 控制结构

PL/SQL 支持的流程控制结构：

条件控制
- IF 语句
- CASE 语句
循环控制
- LOOP 循环
- WHILE 循环
- FOR 循环
顺序控制
- GOTO 语句
- NULL 语句

10.1条件控制

10.1.1 IF 语句

IF 语句根据条件执行一系列语句，有三种形式：IF-THEN、IF-THEN-ELSE 和 IF-THEN-ELSIF-THEN。示例如下：

SQL> select * from person;

        ID NAME                 ADDRESS
---------- -------------------- --------------------
         1 张三                 北京
         2 李四                 广州
         3 王五                 南宁

-- 1. IF-THEN 语句
declare
    my_add person.address%type;
begin
    select address into my_add from person where id = 1;
    if my_add = '北京'
    	then dbms_output.put_line('Beijing');
    end if;
end;
/
Beijing -- 输出结果

-- 2. IF-THEN-ELSE 语句
declare
    my_add person.address%type;
begin
    select address into my_add from person where id = 1;
    if my_add = '广州'
    	then dbms_output.put_line('Guangzhou');
    else
    	dbms_output.put_line('其他');
    end if;
end;
/
其他 -- 输出结果

-- IF-THEN-ELSIF-THEN 语句
declare
    my_add person.address%type;
begin
    select address into my_add from person where id = 1;
    if my_add = '广州'
    	then dbms_output.put_line('Guangzhou');
    elsif my_add = '北京'
    	then dbms_output.put_line('Beijing');
    end if;
end;
/

10.1.2 CASE 语句

示例如下所示：

declare
    my_add person.address%type;
    out_add varchar2(10);
begin
    select address into my_add from person where id = 1;
    out_add := case my_add
        when '北京' then 'Beijing'
        when '广州' then 'Guangzhou'
        when '天津' then 'Tianjin'
        else 'other'
    end;
    dbms_output.put_line(out_add);
end;
/
Beijing -- 输出结果

10.2 循环控制

循环控制用于重复执行一系列语句，循环控制语句包括：LOOP、EXIT 和 EXIT WHEN 、FOR 、WHILE。

10.2.1 LOOP 循环

语法如下所示：

1
2
3

LOOP
 sequence_of_statements
END LOOP;

由于 LOOP 循环是无条件循环，所以一般情况下我们需要在循环内部使用 EXIT WHEN 进行满足指定条件退出循环。示例如下：

declare
    j number := 0;
begin
    j := 1;
    loop
        dbms_output.put_line(j || '---');
        exit when j > 7; -- j 大于 7 时退出循环
        j := j + 1;
    end loop;
    dbms_output.put_line('结束');
end;
/
-- 输出结果如下所示
1---
2---
3---
4---
5---
6---
7---
8---
结束

10.2.2 WHILE 循环

语法如下所示：

1
2
3

WHILE condition LOOP
    sequence_of_statements
END LOOP;

示例如下：

declare
    j number := 0;
begin
    j := 1;
    while j <= 7 -- j 大于 7 时退出循环
    loop
        dbms_output.put_line(j || '---');
        j := j + 1;
    end loop;
    dbms_output.put_line('结束');
end;
/
-- 输出结果如下所示
1---
2---
3---
4---
5---
6---
7---
8---
结束

10.2.3 FOR 循环

语法如下所示：

FOR counter IN [REVERSE] value1..value2
LOOP
    sequence_of_statements
END LOOP;

示例如下：

declare
    j number := 0;
begin
    j := 1;
    for j in 1..8 -- 默认对 j 进行 j++
    loop
        dbms_output.put_line(j || '---');
    end loop;
    dbms_output.put_line('结束');
end;
/
-- 输出结果
1---
2---
3---
4---
5---
6---
7---
8---
结束

declare
    j number := 8;
begin
    for j in REVERSE 1..8 -- 默认对 j 进行 j--
    loop
        dbms_output.put_line(j || '---');
    end loop;
    dbms_output.put_line('结束');
end;
/
-- 输出结果
8---
7---
6---
5---
4---
3---
2---
1---
结束

10.3 oracle 11g 的 continue

oracle11g新增了continue语句，可在循环中使用。该语句可将逻辑移到循环结尾，然后再移到循环开头。例如：

declare
  j number:=1;
begin
  loop
      j:=j+1;
      exit      when j>8;
      continue  when j>4; -- 开始新的循环
      dbms_output.put_line(to_char(j)||'---');
  end loop;
end;
/
-- 输出结果
2---
3---
4---

10.3 顺序结构

顺序控制用于按顺序执行语句，顺序控制语句包括：

GOTO 语句 - 无条件地转到标签指定的语句
NULL 语句 - 什么也不做的空语句

示例如下，使用 goto 语句来实现循环的效果：

declare
    j number := 0;
begin
    j := 1;
    <<aa>>
        dbms_output.put_line(j || '---');
        j := j + 1;
        if j <= 7 then goto aa; end if;
        if j > 7 then goto bb; end if;
    <<bb>> null; -- 空语句，什么都不做
    dbms_output.put_line('结束');
end;
/
-- 执行结果
1---
2---
3---
4---
5---
6---
7---
结束

11. 动态 SQL

动态 SQL 是指在PL/SQL程序执行时生成的 SQL 语句。编译程序对动态 SQL 不做处理，而是在程序运行时动态构造语句、对语句进行语法分析并执行。DDL 语句命令和会话控制语句不能在 PL/SQL 中直接使用，但是可以通过动态 SQL 来执行。

执行动态 SQL 的语法：

1
2
3

EXECUTE IMMEDIATE dynamic_sql_string
[INTO  define_variable_list] -- 上述 sql 指定结果赋值到指定 define_variable_list 变量中
[USING bind_argument_list]; -- 绑定变量

示例如下所示：

begin
    execute immediate 'create table T(t1 int)'; -- create 为 ddl 语句，普通 plsql 无法做到
end;
/

12. 错误处理

在运行程序时出现的错误叫做异常。在发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分。异常有两种类型：

预定义异常 - 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发
用户定义异常 - 用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发

12.1 预定义异常

示例表数据如下：

SQL> select * from student;

                                    SNO SNAME                                         SAGE
--------------------------------------- ---------- ---------------------------------------
                                      1 Tom                                             21
                                      2 Kite                                            22
                                      3 Bob                                             23
                                      4 Mike                                            24

查询学号为 5 的学生，若找不到该学生则进行相应的异常处理和提示，具体示例如下所示：

declare
  out_sname student.sname%type;
begin
  select sname into out_sname from student where sno = 5;
  dbms_output.put_line(out_sname);
exception
  when no_data_found then
     dbms_output.put_line('该学生不存在！');
end;
/
该学生不存在！-- 输出结果

需要说明的是 no_data_found 是 oracle 预定义异常，类似于 Java 中的 ClassNotFoundException。对应的异常编号为 ORA-01403，具体说明可查阅官方文档。

12.2 用户自定义异常

示例如下：

DECLARE 
  invalidCATEGORY EXCEPTION; -- 自定义 无效类别异常
  category VARCHAR2(10);
BEGIN
  category := '&Category'; -- 控制台输入值
  IF category NOT IN ('附件','顶盖','备件') THEN
    RAISE invalidCATEGORY; -- 抛出异常
  ELSE
    DBMS_OUTPUT.PUT_LINE('您输入的类别是'|| category);
  END IF;
EXCEPTION
  WHEN invalidCATEGORY THEN
    DBMS_OUTPUT.PUT_LINE('无法识别该类别');
END;
/
无法识别该类别 -- 输出结果，当控制台输入的类型不在指定集合时

12.3 引发应用程序错误

我们可以使用 RAISE_APPLICATION_ERROR 来引发程序发生错误。该语句可以用于创建用户定义的错误信息，且可以在可执行部分和异常处理部分使用。RAISE_APPLICATION_ERROR 抛出的异常可带有自定义异常编号和异常信息。

修改上述用户自定义异常的示例如下：

DECLARE 
  invalidCATEGORY EXCEPTION; -- 自定义 无效类别异常
  category VARCHAR2(10);
BEGIN
  category := '&Category'; -- 控制台输入值
  IF category NOT IN ('附件','顶盖','备件') THEN
    RAISE invalidCATEGORY; -- 抛出异常
  ELSE
    DBMS_OUTPUT.PUT_LINE('您输入的类别是'|| category);
  END IF;
EXCEPTION
  WHEN invalidCATEGORY THEN
    DBMS_OUTPUT.PUT_LINE('无法识别该类别');
    raise_application_error(-20001, '无法识别的类别！'); -- 抛出异常，程序停止，带有异常编号和异常信息
END;
/
ORA-20001: 无法识别的类别！ -- 输出结果，当控制台输入的类型不在指定集合时

需要说明的，异常错误编号必须介于 –20000 和 –20999 之间，且错误消息的长度最大可长达 2048 个字节。