数据库复习10——PL/SQL

数据库复习

---

CH10 PL/SQL

10.1 PL/SQL简介

PL/SQL是Oracle对SQL的过程化的扩展，PL/SQL可以实现SQL相关的过程化程序，并且能够以存储过程和函数的方式让一段SQL业务逻辑驻留在SQL服务器中，以便减少客户机计算任务并减少网络I/O

10.2 PL/SQL编程基础

（1）简介

PL/SQL编程框架为：

DECLARE 
    <Variable List>
BEGIN
    <Extented SQL Execution>
EXCEPTION
    <Exception Handler>
END

若是存储过程或函数，首部换成Create Procedure/Function ... IS <Variable List>（见后例）

先看一个完整的PL/SQL程序例子：

DECLARE
    name varchar2(20);
BEGIN
    Select sname Into name From Student Where s#='001';     DBMS_OUTPUT.PUT_LINE('学号001的学生姓名是:' || sname)
EXCEPTION
    When NO_DATA_FOUND Then
        DBMS_OUTPUT.PUT_LINE('学号为001的学生不存在');
    When others Then
        DBMS_OUTPUT.PUT_LINE('发生了其它错误');
END;

其中：

• name varchar2(20)声明一个类型为varchar2长度为20的变量name
• select S into V是扩展的select语句，把表的字段S赋值给变量V
• DBMS_OUTPUT.PUT_LINE()是PL/SQL标准输出语句
• when <Exception> then <Extented SQL Execution>是PL/SQL异常处理的分支判断

（2）变量声明

变量声明分成普通声明、表字段类型声明和记录类型声明

普通声明name varchar2(20);等价于表字段类型声明name Student.sname%TYPE，后者利用了某表的某字段的类型来声明变量的类型

记录类型相当于结构体，提高程序可读性，如定义stu记录类型：

TYPE stu IS RECORD (
    S# varchar2(10),
    name varchar2(20),
    age number
);

val stu;

引用时用点运算：val.name

（3）分支与循环

1.if分支

If <Condition> Then
    <Statement>
Elseif <Condition> Then
    <Statement>
Else
    <Statement>
End If;

注意=是相等，:=是赋值

2.while循环

While <Condition> Loop
    <Statement>
End LOOP;

3.for循环

For <Loop Variable> In [Reverse] <begin>..<end> Loop
    <Statement>
End LOOP;

4.loop循环

无条件的Loop，必须在循环体内部加入退出语句Exit;或Exit When <Condition>才能退出该循环

（4）异常处理

标准格式：

Exception
    When <exception_name_1> Or <exception_name_2> Then
        <Statement>
    ...
    When Others Then
        <Statement>

系统定义的exception_name包括：

• NO_DATA_FOUND：select into语句未找到匹配元组
• TOO_MANY_ROWS：select into返回多行数据
• VALUE_ERROR：赋值错误（类型不匹配、长度过长）
• ZERO_DIVIDE：除零
• TIMEOUT_ON_RESOURCE：等待资源超时

可以通过Raise <exception>生成一个自定义的异常，然后在Exception段捕捉它；也可以raise_application_error生成一个程序错误，由外部捕捉

10.3 游标

PL/SQL每次只能处理单个元组，为了使其和SQL**多元组处理的特性保持一致，引入游标**

游标是客户机上用来存放SQL语句返回中间结果的一块内存，目的是为了协调PL/SQL和SQL间数据处理数目的矛盾

（1）声明与打开关闭

PL/SQL中游标只能存储select语句的中间结果，其声明如下：

Declare
    Cursor <cursor_name> IS <Select Statement>;

游标声明时不会立即执行，需要显式调用open语句（对应关闭游标的close语句）：

Begin
    Open <cursor_name>;

（2）循环读取

游标中有一下几个参数用于辅助控制游标读取数据：

• %FOUND：布尔，当前Fetch返回一行时为真
• %NOTFOUND：布尔，当前Fetch没有返回一行时为真
• %ISOPEN：布尔，游标已打开为真
• %ROWCOUNT：数值，返回已从游标中取出的元组数目

那么利用Fetch into语句（返回一条元组）有一下两种方式完成游标数据访问：

1.While循环版本
Begin
    Open <cursor_name>;
    Fetch <cursor_name> into <record_type_variable>;
    While <cursor_name>%FOUND Loop
        DBMS_OUTPUT.PUT_LINE(...);
        Fetch <cursor_name> into <record_type_variable>;
    End Loop;
    Close <cursor_name>;
    ...

2.For循环版本

Begin
    For <Loop_variable> in <cursor_name> Loop
        DBMS_OUTPUT.PUT_LINE(...);
    End Loop;
    ...

其中有：

• Fetch into语句返回一条元组，cursor自动指向下一个元组
• For in可以简洁的遍历游标内数据，循环开始前自动打开游标读取数据，循环结束自动关闭游标，并且自动为<Loop_variable>声明记录类型存放

（3）带参数的游标

游标可以添加参数，如可以在参数中限定where子句的条件：

Cursor cs_s(val Number(3)) IS Select * from stu where age = val;

10.4 存储过程和函数

一般匿名的PL/SQL程序每次运行时都需要编译，而命名PL/SQL程序如存储过程、函数和触发器等则是编译好驻留在数据库中，可以随时被SQL或其他PL/SQL程序调用

过程和匿名的PL/SQL程序只是声明格式上些许不同：

Create [or Replace] Procedure <procedure_name> (
    <param_name> In|Out|In Out <type> [:= <initial_value>],
    ...
)
AS|IS
    <Variable Declaration>
Begin
    <PL/SQL statement>
Exception
    <Exception Handler>
End;
)

其中有：

• In参数不能修改，Out参数只能赋值
• 参数不能指定长度，但可以用%TYPE
• 参数传递默认按位置顺序传递，也可以显式地用<param_name> => <param_value>按名称乱序传递
• 函数是有返回类型的存储过程，在AS前加上返回类型声明Return <return_type>然后再函数体中用return <return_value>返回对应类型的值，在其他的PL/SQL中按函数名传参调用，并用:=赋值给其他变量

10.5 触发器

触发器是一类绑定在表上，由特定DML语句（Update、Insert、Delete）触发自动执行的一段PL/SQL**存储过程**，一个表能有多个触发器但一个触发器只能绑定在一个表上

触发器的概念在先前的SQL复习中已经详细讲解，这里列出PL/SQL中触发器声明规则：

Create [Or Replace] Trigger <trigger_name>
    // 声明触发动作与触发时间
    Before|After Delect|Insert|Update [Of <attribute_name>]
    // Or 连接多个触发动作时间声明
    {Or Before|After Delect|Insert|Update [Of <attribute_name>]}*
On <table_name>
    // 不写表示语句触发，写For Each Row表示行触发
    [For Each Row]
Declare
...