PL/SQL详细总结

柔情只为你懂 2023-06-18 12:52 2阅读 0赞

### PL/SQL是什么 ###

![pl/sql是什么][pl_sql]  
简单来说PL/SQL是在原来的SQL上进行扩展，加入了判断、循环、以及“函数”等概念。

### PL/SQL Developer ###

![PL/SQL Developer][PL_SQL Developer]  
我们可以在PL/SQl Developer中进行PL/SQL开发，新建Test Window即可编写PL/SQL语句：  
![使用方法][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpMzQ1NTI3NzkyNQ_size_16_color_FFFFFF_t_70]  
点击齿轮状的按钮开始运行程序，点击书页形状的按钮可以美化代码：  
![运行程序和美化代码][20191202163905365.png]  
程序的输出在output中可以找到  
![程序输出][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpMzQ1NTI3NzkyNQ_size_16_color_FFFFFF_t_70 1]

### PL/SQL详细介绍 ###

基本PL/SQL块由定义部分、可执行部分、异常处理部分组成  
![PL/SQL语句块组成][PL_SQL]

*  定义部分: 定义在程序执行部分使用的常量,变量,游标和异常处理名称
 *  可执行部分：包括数据库操作语句和PL/SQL块控制语句，相当于c语言中的main函数
 *  异常处理部分：对执行部分的所有PL/SQL语句的执行进行监控,如执行发生异常,则程序跳到该部分执行

#### 变量定义 ####

在PLSQL中所使用的变量必须在变量定义部分明确定义，变量定义部分是包括在关键字`declare`和`begin`之间的部分,每条语句后用(;)结束  
定义格式: `变量标示符 [CONSTANT] 数据类型 [NOT NULL] [:=缺省值或PLSQL表达式];`

> 变量标示符命名规则应遵循SQL实体命名规则

##### 简单数据类型（标量数据类型） #####

![简单数据类型][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpMzQ1NTI3NzkyNQ_size_16_color_FFFFFF_t_70 2]

##### 赋值操作 #####

*  变量、常量、表达式的赋值都可以使用`:=`操作符，而`=`用来判断是否相等
 *  另外一种赋值方法为使用select语句：

select 列名 into 变量 from 表名 where 条件;

##### 引用型变量 #####

假设有一张员工表：

create table emp(
           eno int primary key,
           ename varchar(20),
           email varchar(20),
           salary float
    );

不用手动定义变量的长度和类型，使用表中的字段映射变量长度和类型  
一个例子（定义简单的类进行查询）：查询员工id为100的员工信息

declare
        v_sal emp.salary%type; --表示v_val的类型和emp.salary的类型相同
        v_email emp.email%type;
    begin
        select salary,email into v_val,v_email from emp where eid = 100;
        dbms_output.put_line(v_sal||','||v_email);
    end;

##### 记录型变量： #####

一个变量表示查询出来的一行数据，但是使用记录型变量会给数据库造成负担

declare
        v_emp emp%rawtype;
    begin
        select * into v_emp from empwhere eid = 100;
        dbms_output.put_line(v_emp.salary||','||v_emp.email);
    end;

##### 记录类型 #####

记录类型相当于java等高级语言中的类，可以用来封装多个数据

declare
        -- 声明一个记录类型
        type emp_record is record (
            v_sal employees.salary%type,
            v_email employees.email%type
        );
        -- 定义一个记录类型的成员变量
        v_emp_record emp_record;
    begin
        select salary,email into v_emp_record from employees where eid = 100;
        dbms_output.put_line(v_emp_record.v_sal||','||v_emp_record.v_email);
    end;

#### PL/SQL中的SQL语句 ####

在PL/SQL块中,所有对数据库的访问和操作还是要经由SQL语言进行，在PL/SQL块中可以使用数据查询语言，数据操纵语言和数据控制语言，但不能使用数据定义语言具体地说可以使用select，insert，update，delete，commit，rollback，但不能使用create，alter，drop，grant，revoke。

#### 条件控制 ####

##### 判断 #####

1.  if\_then语句语法:

if 条件 then
    	语句;	
    end if;

条件可为is null或not is null以及and，or，not，逻辑运算符  
示例代码：

declare
      v_i number;
    begin
      v_i := 2;
      if v_i = 2 then
        dbms_output.put_line('i的值为2');
      end if;
    exception
      when others then
        dbms_output.put_line('捕获一个异常！');
    end;

运行结果：  
![运行结果][20191202163714447.png]  
2. if\_then\_else语句语法：

if 条件 then
    	语句;
    else
    	语句；	
    end if;

示例代码：

declare
      v_i number;
    begin
      v_i := 1;
      if v_i = 2 then
        dbms_output.put_line('i的值为2');
      else
        dbms_output.put_line('i的值不为2');
      end if;
    exception
      when others then
        dbms_output.put_line('捕获一个异常！');
    end;

运行结果：  
![运行结果][20191202164445391.png]  
3. if\_then\_elsif语句语法：

if 条件 then
    	语句;		
    elsif 条件 then
    	语句;		
    [elsif 条件 then  语句;]
    [else 语句;]		
    end if;

**这里需要注意是`elsif`不是`elseif`**  
示例代码：

declare
      v_i number;
    begin
      v_i := 1;
      if v_i = 2 then
        dbms_output.put_line('i的值为2');
      elsif v_i = 3 then
        dbms_output.put_line('i的值为3');
      else
        dbms_output.put_line('i的值不是2或3');
      end if;
    exception
      when others then
        dbms_output.put_line('捕获一个异常！');
    end;

运行结果：  
![运行结果][20191202165028960.png]

##### 循环 #####

1.  loop循环语法：

loop 
    	语句;
    exit when 条件;
    end loop;

1.  for循环语法：

for 计数器 in [reverse] 下界...上界
    loop
    	语句;
    end loop;

示例代码：

declare
      s number := 0;
    begin
      for i in 1 .. 5 loop
        s := s + i;
      end loop;
      dbms_output.put_line('s=' || s);
    end;

1.  循环控制语句语法

while 条件 
    loop
    	语句;
    end loop;

示例代码：

declare
      s number := 0;
    begin
      while i < 3 loop
        s := s + i;
      end loop;
      dbms_output.put_line('s=' || s);
    end;

#### 游标 ####

##### 为什么要使用游标 #####

*  SQL语言与主语言具有不同数据处理方式
 *  SQL语言是面向集合的，一条SQL语句原则上可以产生或处理多条记录
 *  主语言是面向记录的，一组主变量一次只能存放一条记录
 *  仅使用主变量并不能完全满足SQL语句向应用程序输出数据的要求
 *  引入游标的概念，用来协调这两种不同的处理方式

##### 游标的特点 #####

*  游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果
 *  每个游标区都有一个名字
 *  用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理

##### 定义游标 #####

语法：

cursor 游标名称 is select语句;

*  定义游标应写在PL/SQL语句的declare变量定义部分
 *  定义游标时select语句中不可有into子语句
 *  在select语句中使用的变量必须在定义游标前定义

##### 打开游标 #####

语法：

open 游标名;

*  在begin语句之后,可以打开游标,在打开游标之前,必须对游标所涉及到的变量赋值

##### 利用游标提取数据 #####

语法：

fetch 游标名 into 变量1,变量2,...;

*  游标每次只能取到一条数据,同时游标指针下移,等待取下一条数据.该条语句变量列表应与定义游标时的参数列表一致

##### 关闭游标 #####

语法：

close 游标名;

*  关闭游标，释放资源，游标关闭后不能再提取数据。

##### 游标的属性 #####

![游标的属性][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpMzQ1NTI3NzkyNQ_size_16_color_FFFFFF_t_70 3]

示例代码：

1.  使用fetch提取游标中数据（需要显示的打开游标）：

declare
      v_job emp.job%type;
      cursor cur_emp is select job from emp;
    begin
      open cur_emp;
      loop
        fetch cur_emp into v_job;
        dbms_output.put_line('' || v_job);
        exit when cur_emp%notfound;
      end loop;
      close cur_emp;
    exception
      when others then
        dbms_output.put_line('捕获一个异常');
    end;

1.  利用for循环遍历游标：

> 当使用游标for循环时，orcale会隐含的打开游标，提取数据并关闭游标，不用手动打开游标或关闭游标

declare
      v_job emp.job%type;
      cursor cur_emp is select job from emp;
    begin
      for i in cur_emp loop
        dbms_output.put_line('job is ' || i.job);--注意这里是i.job
      end loop;
    exception
      when others then
        dbms_output.put_line('捕获一个异常');
    end;

declare
      cursor c_clazz is
        select * from clazz;
    begin
      for i in c_clazz loop
        dbms_output.put_line(i.cname);
      end loop;
    exception
      when others then
        raise_application_error(-20001, '出现异常！');
    end;

##### 带参数的游标 #####

游标可以带有参数，当打开游标的时候传入参数。

declare
      cursor c_emp(v_deptno emp.deptno%type) is
        select ename, sal from emp where deptno = v_deptno;
      v_ename emp.ename%type;
      v_sal   emp.sal%type;
    begin
      open c_emp(10);
      loop
        fetch c_emp
          into v_ename.v_sal;
        dbms_output.put_line(v_ename || '-' || v_sal);
        exit when c_emp%notfound;
      end loop;
      close c_emp;
    end;

#### 存储过程 ####

##### 创建存储过程 #####

语法：

create procedure
     过程名([参数1 in/out 参数类型,参数2 in/out 参数类型.....])
    	<定义变量>;
    as/is
    begin
    	<PL/SQL语句块>;
    end;

*  过程名：数据库服务器合法的对象标识
 *  参数列表：用名字来标识调用时给出的参数值，必须指定值的数据类型。参数也可以定义输入参数、输出参数或输入/输出参数。默认为输入参数。
 *  过程体：是一个<PL/SQL块>。包括声明部分和可执行语句部分 ;不用 declare 语句  
    实例代码：

create or replace procedure p4(i_empno in emp.empno%type) is
      v_job emp.job%type;
      cursor cur_emp is
        select job from emp where empno = i_empno;
    begin
      for i in cur_emp loop
        dbms_output.put_line('job is ' || i.job);
      end loop;
    exception
      when others then
        dbms_output.put_line('捕获一个异常');
    end;

示例代码2：银行转账的例子

CREATE PROCEDURE TRANSFER(inAccount INT， outAccount INT， amount FLOAT) AS
      totalDeposit FLOAT;
    BEGIN
      /* 检查转出账户的余额 */
      SELECT total
        INTO totalDeposit
        FROM ACCOUNT
       WHERE ACCOUNTNUM = outAccount;
      IF totalDeposit IS NULL THEN
        /* 账户不存在或账户中没有存款 */
        ROLLBACK;
        RETURN;
      END IF;
      IF totalDeposit < amount THEN
        /* 账户账户存款不足 */
        ROLLBACK;
        RETURN;
      END IF;
      UPDATE account /* 修改转出账户，减去转出额 */
         SET total = total - amount
       WHERE ACCOUNTNUM = outAccount;
      UPDATE account /* 修改转入账户，增加转出额 */
         SET total = total + amount
       WHERE ACCOUNTNUM = inAccount;
      COMMIT; /* 提交转账事务 */
    END;

##### 执行存储过程 #####

*  执行存储过程语法：call/perform procedure 过程名(［参数1，参数2，…］)；
 *  在PL/SQL中，数据库服务器支持在过程体中调用其他存储过程
 *  使用CALL或者PERFORM等方式激活存储过程的执行。
 *  调用时”()”是不可少的，无论是有参数还是无参数。

##### 删除存储过程 #####

drop procedure 存储过程名;

#### 异常处理 ####

*  为了提高存储过程的健壮性，避免运行错误，当建立存储过程时应包含异常处理部分。
 *  异常(EXCEPTION)是一种PL/SQL标识符，包括预定义异常、非预定义异常和自定义异常；
 *  异常可以分为：
    
     *  预定义异常是指由PL/SQL提供的系统异常；
     *  非预定义异常用于处理与预定义异常无关的Oracle错误(如完整性约束等)；
     *  自定义异常用于处理与Oracle错误的其他异常情况。
 *  RAISE\_APPLICATION\_ERROR用于自定义错误消息，并且消息号必须在－20000～－20999之间

代码实例：预定义异常

create or replace procedure stu_proc6(pno   in student.sno%type,
                                          pname out student.sname%type) is
    begin
      select sname into pname from student where sno = pno;
    EXCEPTION
      when NO_DATA_FOUND then
        RAISE_APPLICATION_ERROR(-20011, 'ERROR：不存在！');
    end;

常见预定义异常：  
![常见预定义异常][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpMzQ1NTI3NzkyNQ_size_16_color_FFFFFF_t_70 4]

代码实例：（自定义异常）

create or replace procedure stu_proc7(pno in student.sno%type,
                                          pon in student.sno%type) is
      v_raise exception;
      v_name student.sname%type;
    begin
      if pno = 101 then
        raise v_raise;
      end if;
      select sname into v_name from student where sno = 111111;
    exception
      when v_raise then
        RAISE_APPLICATION_ERROR(-20010, 'ERROR:not existed!');
      when no_data_found then
        RAISE_APPLICATION_ERROR(-20011, 'ERROR：不存在！');
    end;

#### 触发器 ####

##### 特点 #####

*  触发器是一种特殊类型的存储过程。也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。 触发器和存储过程的区别：触发器主要是通过事件触发而执行的，而存储过程是通过调用存储过程名称而执行的。
 *  它与表紧密相连，不可以脱离表单独存在。
 *  不允许使用参数，没有返回值。
 *  不允许用户调用，当对表进行插入、删除、修改操作时由系统自动调用并执行（相当于事件方法）
 *  作用：
    
     *  就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性
     *  用于完整性检查
     *  提供审计和日志记录
     *  启用复杂的业务逻辑
 *  触发器可以实现比较复杂的完整性约束：
    
     *  扩展约束、默认值和规则对象的完整性检查。
     *  自动生成数据。
     *  检查数据的修改，防止对数据不正确的修改，保证数据表之间数据的正确性和一致性。
     *  自定义复杂的安全权限。

##### 概述 #####

1.  触发器自动执行

在对表的数据作了任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活。

1.  触发器能够对数据库中的相关表实现级联更改

触发器是基于一个表创建的，但是可以针对多个表进行操作，实现数据库中相关表的级联更改。

1.  触发器可以实现更为复杂的数据完整性约束

在数据库中为了实现数据完整性约束，可以使用CHECK约束或触发器。CHECK约束不允许引用其它表中的列来完成检查工作，而触发器可以引用其它表中的列。

##### 语法 #####

##### 触发器类型 #####

![触发器类型][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpMzQ1NTI3NzkyNQ_size_16_color_FFFFFF_t_70 5]

##### 触发器的执行次序 #####

Oracle 对事件的触发共有16种，但是它们的触发是有次序的，基本触发次序如下：

*  执行 BEFORE语句级触发器;
 *  对于受语句影响的每一行：
    
     *  a) 执行 BEFORE语句行级触发器
     *  b) 执行 DML语句
     *  c) 执行 AFTER行级触发器
 *  执行 AFTER语句级触发器

> 行级触发器与语句级触发器的区别  
> 根据进行一个操作时触发器的触发次数，来决定是创建一个语句级触发器，还是创建一个行级触发器。  
> 注意当某操作只影响到表中的一行数据时，语句级触发器与行级触发器的效果相同。

##### 触发器谓词 #####

ORACLE 提供三个参数 INSERTING，UPDATEING，DELETING 用于判断触发了哪些操作。 谓词的行为如下：

*  INSERTING如果触发语句是 INSERT 语句，则为TRUE,否则为FALSE
 *  UPDATING如果触发语句是 UPDATE语句，则为TRUE,否则为FALSE
 *  DELETING如果触发语句是 DELETE 语句，则为TRUE,否则为FALSE

DML触发器的限制：

*  触发器中不能使用控制语句(commit,rollback,savepoint)
 *  由触发器所调用过程或函数也不能使用控制语句
 *  触发器中不能使用long, long raw类型

当触发器被触发时,要使用被插入,更新或删除的记录中的列值,有时要使用操作前、后列的值.实现:

*  :new 修饰符访问操作完成后列的值
 *  :old 修饰符访问操作完成前列的值

##### 其他操作 #####

禁用触发器：

ALTER TRIGGER aiu_itemfile DISABLE;

启用触发器：

ALTER TRIGGER aiu_itemfile ENABLE;

删除触发器：

DROP TRIGGER aiu_itemfile;

查看触发器信息：

SELECT * FROM USER_TRIGGERS WHERE TABLE_NAME='EMP';

![查看触发器信息][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpMzQ1NTI3NzkyNQ_size_16_color_FFFFFF_t_70 6]

[pl_sql]: https://img-blog.csdnimg.cn/20191202160856670.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpMzQ1NTI3NzkyNQ==,size_16,color_FFFFFF,t_70
[PL_SQL Developer]: https://img-blog.csdnimg.cn/20191202161143573.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpMzQ1NTI3NzkyNQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpMzQ1NTI3NzkyNQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2019120216135815.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpMzQ1NTI3NzkyNQ==,size_16,color_FFFFFF,t_70
[20191202163905365.png]: https://img-blog.csdnimg.cn/20191202163905365.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpMzQ1NTI3NzkyNQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191202164037134.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpMzQ1NTI3NzkyNQ==,size_16,color_FFFFFF,t_70
[PL_SQL]: https://img-blog.csdnimg.cn/20191202162227839.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpMzQ1NTI3NzkyNQ_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/2019120216264865.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpMzQ1NTI3NzkyNQ==,size_16,color_FFFFFF,t_70
[20191202163714447.png]: https://img-blog.csdnimg.cn/20191202163714447.png
[20191202164445391.png]: https://img-blog.csdnimg.cn/20191202164445391.png
[20191202165028960.png]: https://img-blog.csdnimg.cn/20191202165028960.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpMzQ1NTI3NzkyNQ_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20191202185735339.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpMzQ1NTI3NzkyNQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpMzQ1NTI3NzkyNQ_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20191202195227374.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpMzQ1NTI3NzkyNQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpMzQ1NTI3NzkyNQ_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20191202202729793.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpMzQ1NTI3NzkyNQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpMzQ1NTI3NzkyNQ_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20191202203758451.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpMzQ1NTI3NzkyNQ==,size_16,color_FFFFFF,t_70