PL/SQL编程

       前言

        结合了Oracle过程语言的结构化查询语言的一种扩展语言。使用PL/SQL可以具有很多高级功能的程序，虽然通过多个SQL语句也能实现，但相比而言，PL/SQL具有更为明显的优点。

---

PL/SQL编程的特点

1. PL/SQL具有编程语言的特点，能把一组SQL语句放到一个模块中，使其更具模块化程序的特点。
2. PL/SQL可以采用过程性语言控制程序的结构，也就是，我们可以在PL/SQL中增加逻辑结构。如判断、循环等结构。
3. 同其他的编程语言一样，PL/SQL可以对中的错误进行自动处理，使程序能够遇到错误时不会中断，即它的异常处理机制。
4. PL/SQL程序块具有更好的可移植性，可以移植到另一个Oracle数据库中。
5. PL/SQL程序减少了网络的交互，有助于提高程序性能。

---

PL/SQL体系结构

       PL/SQL引擎用来编译和执行PL/SQL块或子程序，该引擎驻留在Oracle服务器中。PL/SQL引擎仅执行过程语句，而将SQL语句发送给Oracle服务器上的SQL语句执行器，有SQL语句执行器执行这些SQL语句。

• PL/SQL块简介

       PL/SQL是一种块结构的语言，它将一组语句放在一个块中。PL/SQL块将逻辑上相关的声明和语句组合在一起。匿名块是一个未在数据库中命名的PL/SQL块，在运行时被传递到PL/SQL引擎以便执行。在PL/SQL块中可以使用SELECT、INSERT、UPDATE、DELETE等DML语句。事务控制器语句及SQL函数等。PL/SQL块中不允许直接使用CREATE、DROP或ALTER等DDL语句，但可以通过动态SQL来执行它们。

一个PL/SQL块由三部分组成

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

• 运算符和表达式

  • 关系运算符

    = --等于
    <>,!=,~=,^= --不等于
    < --小于
    > --大于
    <= --小于或等于
    >= --大于或等于

  • 一般运算符

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

  • 逻辑运算符

    is null --是空值
    between and --介于两者之间
    in --在一列值中间
    and --逻辑与
    or --逻辑或
    not --取反、is not null、not in

• 常量和变量声明

       在PL/SQL块的可执行部分引用变量和常量前，必须先对其进行声明。变量和常量在PL/SQL块的部分声明，在PL/SQL块的可执行部分被使用。

语法：

variable_name data_type[(size)][:=init_value];

variable_name：变量名称

data_type：变量的SQL或PL/SQL数据类型

size：变量的范围

init_value：变量的初始化

variable_name constant data_type :=value;

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

标识符名不能超过30个字符。

1. 第一个字符必须为字母
2. 不区分大小写
3. 不能用”-“(减号)
4. 不能是SQL保留字

---

PL/SQL数据类型

• 标量数据类型
         包含单个值，没有内部组件。标量数据类型包括数字、字符、布尔值和日期时间。

  • 属性类型

    属性用于引用变量或数据库列的数据类型，以及表示表中一行的记录类型。PL/SQL支持两种属性类型。

    1) %TYPE

    定义一个变量，其数据类型与已经定义的某个数据变量(尤其是表的某一列)的数据类型相一致，可以使用%TYPE。

    优点：

    1. 可以不必知道所引用的数据库列的数据类型
    2. 所引用的数据库列的数据类型可以实时改变，容易保持一致，不用修改PL/SQL程序。

    2) %ROWTYPE

    返回一个记录类型，其数据类型和数据库表的数据结构相一致，这时可以使用%ROWTYPE。

    优点：

    1. 可以不必知道所引用的数据库列的个数和数据类型
    2. 所引用的数据库列列的个数和数据类型可以实时改变，容易保持一致，不用修改PL/SQL程序。

• PL/SQL控制语句

       PL/SQL提供了丰富的流程控制语句。控制结构供有三种类型、条件控制、循环控制和顺序控制

1. 条件控制

   if<布尔表达式> then
       PL/SQL 和SQL语句
   end if;
   -----------------------
   if<布尔表达式> then
       PL/SQL 和SQL语句
   else
       其他语句
   end if;
   -----------------------
   if<布尔表达式> then
       PL/SQL 和SQL语句
   elsif<其他布尔表达式> then
       其他语句
   else
       其他语句
   end if;
   
   case 条件表达式
       when 表达式结果1 then
           语句段1
       when 表达式结果2 then
           语句段2
       ……
       when 表达式结果n then
           语句段n
         [else 语句段]
   end case;
   -------------------------
   case
       when 表达式结果1 then
           语句段1
       when 表达式结果2 then
           语句段2
       ……
       when 表达式结果n then
           语句段n
         [else 语句段]
   end case;

2.循环控制

       循环控制用于重复执行一系列语句。包括LOOP和EXIT语句，使用EXIT语句可以立即退出循环;使用EXIT WHEN语句可以根据条件结束循环。三种：LOOP、WHILE、FOR

LOOP循环语法
loop
    要执行的语句;
    exit when<条件语句> --条件满足退出循环
end loop;

while循环语法
while<布尔表达式> loop
    要执行的语句;
end loop;

for循环语法
for 循环计数器 in [REVERSE] 下限 ... 上限 loop
    要执行的语句;
end loop;

3.顺序控制

       用于按顺序执行语句。包括null语句和goto语句。goto语句不推荐使用。

       null语句：是一个可执行语句，相当于一个占位符或不执行任何操作的空语句，可以使某些语句变得更有意义，提高程序的可读性保证其他语句结构的完整性、正确性

---

异常处理

• 预定义异常

       预定义异常大约24个。对于这种异常处理无须再程序中定义可由Oracle自动引发。

ACCESS_INTO_NULL - 在未初始化对象时出现
CASE_NOT_FOUND - CASE语句中的选项与用户输入的数据不匹配时出现
COLLECTION_IS_NULL - 给尚未初始化的表或数据赋值时出现
CURSOR_ALREADY_OPEN - 在用户试图重新打开已经打开的游标时出现
DUP_VAL_ON_INDEX - 在用户试图将重复的值存储在使用唯一索引的数据库列中出现
INVALID_CURSOR - 在执行非法游标(如打开一个尚未打开的游标)运算时出现
INVALID_NUMBER - 在将字符串转换成数字时出现
LOGIN_DENIED - 在输入的用户名或密码无效时出现
NO_DATA_FOUND - 在表中不存在请求的行时出现。
STORAGE_ERROR - 在内存损坏或PL/SQL耗尽内存时出现
TOO_MANY_ROWS - 在执行SELECT INTO语句后返回多行时出现
VALUE_ERROR - 在产生大小限制错误时出现
ZERO_DIVIDE - 以零作为除数时出现

语法：

BEGIN
    sequence_of_statements;
EXCEPTION
    WHEN <exception_name> then
        sequence_of_statements;
    WHEN OTHERS THEN
        sequence_of_statements;
END;

       其中，OTHERS处理程序确保不会漏过任何异常，如果没有在前面的异常处理部分显示获取命名的异常，他就可以获取其余的异常。PL/SQL块只能有一个OTHERS异常处理程序。可以使用函数SQLCODE和SQLLERRM来返回错误代码和错误文本信息。

• 处理用户自定义异常

       程序在执行过程中，出现编程人员认为的非正常情况。对于这种异常情况的处理，需要用户在程序中定义，然后显式的在程序中将其引发。用户定义的异常错误通过显式使用RAISE语句来触发。当引发一个异常错误时，控制就转到EXCEPTION块异常错误部分，执行错误处理代码。

1) 在PL/SQL块的定义部分定义异常情况：
    <异常情况> EXCEPTION;
2) 抛出异常情况:
    RAISE <异常情况>;

---

游标

• 游标的基本原理

       在Oracle中，在执行一个有select、insert、update、delete语句的PL/SQL块时，Oracle会在内存中为其分配一个缓冲区、将执行结果放在这个缓冲区中，而游标是指向该去的一个指针。游标为应用程序提供了一种对多行数据查询结果集中的每行数据进行单独处理的方法，是设计嵌入式SQL语句的应用程序的常用编程方式。

• 游标分类

       在Oracle中提供了两种游标类型，即静态游标和动态游标。静态游标是在编译时知道明确的select语句的游标。静态游标又分为两种类型，即隐式游标和显示游标。

• 显示游标

  声明游标 -> 打开游标 -> 提取游标 -> 空?是(关闭游标):否

  • 声明游标

    cursor cursor_name [(parameter[,parameter]...)]
    [return return_type] is select_statement;

    cursor_name：游标的名称

    parameter：由于为游标指定输入参数。在指定数据类型时，不能使用长度约束。

    return_type：用于定义游标提取的行的类型。

    select_statement：游标定义的查询语句

  • 打开游标

    open cursor_name[(parameters)];

  • 提取游标

    fetch cursor_name into variables;

    cursor_name：游标的名称

    variables：变量名

  • 关闭游标

    close cursor_name;

  • 显示游标属性

    %found：只有在DML语句影响一行或多行时，返回true;

    %notfound：与%found相反

    %rowcount：返回DML语句影响的行数。没有影响任何行数返回0；

    %isopen：返回游标是否打开

  • 使用显式游标删除或更新

    cursor cursor_name is
    select_statement for update [of columns];

    for update [of columns];为更新查询，锁定选择的行。
    当选择单表更新查询时，可以省略of子句。
    当选择多个表更新查询，被锁定的行来源于of子句后声明的行。

  • 使用循环游标简化游标的读取

    循环游标隐式打开游标，自动从活动集获取行，后再处理完所有行时关闭游标。

for record_index in cursor_name
loop
    executable_statements
end loop;

       record_index：PL/SQL声明的记录变量。%rowtype类型。作用域在for之内。

特性：

1. 在从游标中提取了所有记录之后自动终止
2. 提取和处理游标中的每一条记录
3. 如果在提取记录之后%notfound属性返回true终止循环
4. 未返回行，不进入循环