Oracle学习(七) --- PL/SQL(二) 游标、储存过程、自定义函数、触发器

1、PL/SQL -- 游标

1.1、什么是游标

当在PL/SQL中使用SQL语句时，Oracle会为其分配上下文区域，这是一段私有的内存区域，用于暂时保存SQL语句影响到的数据。游标是指向这段内存区域的指针。游标并不是一个数据库对象，只是留存在内存中。

• 游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。我们可以把游标理解为PL/SQL中的结果集。

• 游标就是将查询结果存放到缓冲区，可以通过游标依次获得数据

1.2、语法

declare
	--声明游标
	cursor 游标名称 is 查询语句;
begin
	open 游标名称;
	loop;
		-- 给游标指向当前行进行命名，方便获得当前行的数据
		fetch 游标名称 into 变量;
		-- 使用当前行数据
		-- 结束信息
		exit when 游标名称%notfound;
	end loop;
	close 游标名称;
end;

1.3、使用

• 需求：打印业主类型为1的价格表的价格

---需求：打印业主类型为1的价格表
-- 方式1：游标基本操作
declare
   --声明游标
   cursor cur_pricetable is select * from t_pricetable where ownertypeid = 1;
   --声明变量，可以存放一条记录
   v_pricetable t_pricetable%rowtype;
begin
   --开启
   open cur_pricetable;
   
   loop
     -- 游标指向有数据，打印价格
     fetch cur_pricetable into v_pricetable;
     -- 没有数据，退出
     exit when cur_pricetable%notfound;
     -- 打印
     dbms_output.put_line(v_pricetable.price);
     
   end loop;
   
   --关闭
   close cur_pricetable;
end;

• 方式2：使用for循环，简化游标操作

-- 方式2：for循环
declare
   -- 声明游标
   cursor cur_pricetable is select * from t_pricetable where ownertypeid = 2;
   -- 变量
   v_pricetable t_pricetable%rowtype;
begin
   -- for遍历
   for v_pricetable in cur_pricetable
     loop
       dbms_output.put_line(v_pricetable.price);
     end loop;
end;

1.4、有参数游标

--- 具有参数的游标
declare
   -- 声明游标
   cursor cur_pricetable(v_ownertypeid number) is select * from t_pricetable where ownertypeid = v_ownertypeid;
   -- 变量
   v_pricetable t_pricetable%rowtype;
begin
   -- for遍历
   for v_pricetable in cur_pricetable(3)
     loop
       dbms_output.put_line(v_pricetable.price);
     end loop;
end;

2、储存过程

储存过程是Oracle开发者在数据转换或查询报表时，最经常用的方式之一。储存过程是一种命名 PL/SQL 程序块，它将一些相关的 SQL 语句、流程控制语句组合在一起，用于执行某些特定的操作或任务，可以将经常需要执行的特定的操作写成过程。通过过程名，就可以多次调用过程，从而实现程序的模块化设计，这种方式极大地节省了用户的时间，也提高了程序的效率。

2.1、概述

在 Oracle 中，可以在数据库中定义子程序，在子程序中将一些固定的操作集中起来，由Oracle数据库服务器完成，以完成某个特定的功能。这种子程序称为储存过程(Proce-Dure)。存储过程可以通俗地理解为是储存在数据库服务器中的封装了一段或多段SQL语句的PL/SQL代码块。在数据库中有一些是系统默认的储存过程，那么可以直接通过储存过程的名称进行调用。

• 储存过程是被命名的PL/SQL块，储存于数据库中，是数据库对象的一种。应用程序可以调用储存过程，执行相应的逻辑。

使用储存过程具有如下一些优点：

• (1) 储存过程在服务器端运行，执行速度快。
• (2) 储存过程执行一次后驻留在 Oracle 数据库服务器的高速 Cache 中，以后在此执行储存过程时，只需要从高速 Cache 中调用已经编译好的代码即可，从而提升了系统性能。
• (3) 储存过程确保了数据库的安全。使用储存过程，可以在禁止用户直接访问应用程序中的某些数据表的情况下，授权执行访问这些数据表的储存过程。
• (4) 自动完成需要预先执行的任务。储存过程可以设置为系统启动时自动执行，而不必在系统启动后再手动操作，从而方便了用户的使用，可以自动完成一些需要预先执行的任务。

2.2、语法

• 语法

create [or replace] procedure 存储过程名称
(参数名 参数模式 类型,参数名2 参数模式 类型,....)
is|as
  --变量声明
begin
  --逻辑代码
end;
--参数模式：in、out、 in out

• 没有返回值(in 传入参数)

  • 创建存储过程

    -- 1) 创建存储过程
    create procedure add_area
    (v_name in varchar2)
    is
     -- 声明变量
     v_id t_area.id%type;
    begin
      -- 从序列查询id，并赋值给v_id
      select seq_stuno.nextval into v_id from dual;
      --插入语句
      insert into t_area(id,name) values(v_id,v_name);
      --提交
      commit;
    end;
    

  • 调用存放过程

    --- 方式1：直接调用
    call add_area("小兰");
    --- 范式2：PLSQL编程
    begin
      add_area("小兰666");
    end;
    

• 有返回值(out)

  • 创建存储过程

    --- 有返回值：使用存储过程向t_area添加数据 id、name，并调用存放过程时，打印id
    create procedure add_area2
    (
    v_id out number,
    v_name in varchar2
    )
    is
      -- 声明变量
    begin
      --查询序列，并将结果赋值v_id
      select seq_stuno.nextval into v_id from dual;
      --插入数据
      insert into t_area(id,name) values(v_id,v_name);
      --提交
      commit;
    end;
    

  • 调动存储过程

    --- 调用,定义变量，用于存放返回值
    declare
     v_id t_area.id%type;
    begin
      add_area2(v_id,"赐儿");
      dbms_output.put_line(v_id);
    end;
    

2.3、创建存放过程排错

• 创建成功和失败效果图

• 检查错误原因：储存过程右键/View

• 根据提示排错

3、自定义函数

3.1、概述

• 函数分为内置函数和自定义函数。可以接收一个或多个参数，返回一个结果。
  • 在函数中我们可以使用PL/SQL进行逻辑的处理。

3.2、语法

-- 语法
create function 函数名
(参数名 参数类型, ....)
	--注意：return 没有分号
	return 返回值类型
is
	--变量
begin
	--代码
	return 返回值;
end;

• 实例：

--需求： 创建函数，根据地址ID查询地址名称。
create or replace function getaddressname
(v_id number)
    return varchar2
is
  v_name t_address.name%type;  
begin
  -- 查询
  select name into v_name from t_address where id = v_id;
  -- 返回
  return v_name;
end;

-- 测试
select getaddressname(1) from dual;

4、触发器

触发器是一种在发生数据库事件时能够自动运行的PL/SQL代码块，它与特定表或视图上的操作相关联。注意储存过程是由用户直接调用的，而对于触发器的执行用户则不能直接调用，Oracle会在相应的事件发生时，自动调用触发器。触发器是许多关系数据库系统都提供的一项技术。

4.1、触发器概述

触发器是一种特殊的储存过程，它与数据表紧密联系，用于保护表中的数据，当一个定义了特定类型触发器的基表执行插入、修改或删除表中数据的操作时，讲自动触发触发器中定义的操作，以实现数据的一致性和完整性。

• 数据库触发器时一个与表相关联的、储存在PL/SQL程序。每当一个特定的数据操作语句(Insert，update，delete)在指定的表上发出时，Oracle自动地执行触发器中定义的语句。

4.2、触发器的作用

• (1) 在安全性方面，触发器可以基于数据库的值使用户具有操作数据库的某种权利。
• (2) 在审计方面，触发器可以跟踪用户对数据库的操作。
• (3) 实现复杂的数据完整性规则。
• (4) 实现复杂的非标准的数据库相关完整性规划。触发器可以对数据库中相关的表进行连环更新。
• (5) 同步实时地复制表中的数据。
• (6) 自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。

4.3、触发器的分类

按照触发事件的不同，触发器可以分为不同的类型。

(1) 触发事件不同，可分为：

• INSERT：也称插入型触发器，当指定的表发生插入(INSERT)操作时，触发器将自动触发执行。
• UPDATE：也称更新型触发器，当指定的表发生修改(UPDATE)操作时，触发器将自动触发执行。
• DELETE：也称删除型触发器，当指定的表发生删除(DELETE)操作时，触发器将自动触发执行。

(2) 按触发时间(根据指定的事件和触发器执行的先后次序)不同，可分为：

• BEFORE：在指定的事件发生之前执行触发器。
• AFTER：在指定的事件发生之后执行触发器。

(3) 按触发级别不同，可分为：

• 行触发。对触发事件影响的每一行执行触发器。
• 语句触发。对于触发事件只能触发一次，而且不能访问受触发器影响的每一行的值。

在Oracle系统里，触发器类似过程和函数，它们都有声明、执行逻辑处理部分和异常处理部分，并且被存储在数据库中。

4.4、触发器的执行顺序

触发器的执行，是由触发事件激活的，并由数据库服务器自动执行的。一个数据表上可能定义了多个触发器，比如多个BEFORE 触发器，多个AFTER 触发器等。同一个表上的多个触发器激活时遵循如下的执行顺序：

• (1) 执行该表上的 BEFORE 触发器
• (2) 激活触发器的 SQL 语句。
• (3) 执行该表上的 AFTER 触发器。

对于同一个表上的多个 BEFORE(AFTER)触发器，遵循【谁先创建谁先执行】的原则，即按照触发器创建的时间先后顺序执行。

4.5、创建触发器的语法

create trigger 触发器名称
before | after 			--前置触发器、后置触发器
update of 列名			--更新时触发，取值：insert/update/delete
on 表					--触发器检测的表
for each row			 -- 检测表中的每一行数据
declare
	--声明
begin
	--可以通过规定的关键字，获得对应数据
		-- :old 之前的数据（一行）
		-- :new 现在的数据（一行）
end;

• 实例：当用户输入本月累计表数后，自动计算出本月使用数 。

---需求：当用户输入本月累计表数后，自动计算出本月使用数 。
---  当 t_account 表 num1 发生更新时，将自动更新 usenum列
create or replace trigger tri_account_usenum
  before
  update of num1
  on t_account
  for each row
declare
  -- 声明
begin
  -- 获得最新的数据 num1 - num0 结果赋值 usenum
  :new.usenum  :=  :new.num1 - :new.num0 ;
end;

--测试
update t_account set num1 = 13000 where id = 43;
commit;
-- 查看 usenum 是否自动更新
select * from t_account where id = 43;