1. 代码段小站首页
  2. 编程
  3. 中间件/工具/框架

MySQL回顾-存储过程与触发器

彭晨涛 • • 中间件/工具/框架 • 阅读 9

存储过程

概述

存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时，只需调用“CALL存储过程名字”即可自动完成。

一个存储过程是一个可编程的函数，它在数据库中创建并保存，一般由 SQL 语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的特定功能时，存储过程尤为合适。

优点

存储过程通常有如下优点：
1. 封装性
存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的 SQL 语句，并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码。
2. 可增强 SQL 语句的功能和灵活性
存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
3. 可减少网络流量
由于存储过程是在服务器端运行的，且执行速度快，因此当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载。
4. 高性能
存储过程执行一次后，产生的二进制代码就驻留在缓冲区，在以后的调用中，只需要从缓冲区中执行二进制代码即可，从而提高了系统的效率和性能。
5. 提高数据库的安全性和数据的完整性
使用存储过程可以完成所有数据库操作，并且可以通过编程的方式控制数据库信息访问的权限。

编写

基本的存储过程

CREATE PROCEDURE productpricing()
    BEGIN
        SELECT AVG(prod_price) as priceaverage FROM products;
    END;

创建名为 productpricing 的储存过程。如果存储过程中需要传递参数，则将他们在括号中列举出来即可。括号必须有。BEGIN 和 END 关键字用来限制存储过程体。上述存储过程体本身是一个简单的 select 语句。注意这里只是创建存储过程并没有进行调用。

储存过程的使用：
Call productpring();

使用参数的存储过程

一般存储过程并不显示结果，而是把结果返回给你指定的变量上。

变量：内存中一个特定的位置，用来临时存储数据。

CREATE PROCEDURE prod(
    OUT pl DECIMAL(8,2),
    OUT ph DECIMAL(8,2),
    OUT pa DECIMAL(8,2)
    )
    BEGIN
        SELECT MIN(prod_price) INTO pl FROM products;
        SELECT MAX(prod_price) INTO ph FROM products;
        SELECT AVG(prod_price) INTO pa FROM products;
    END;

关键字 OUT 指出相应的参数用来从存储过程传出一个值(返回给调用者)。

MySQL 支持 in(传递给存储过程)、out(从存储过程传出，这里所用)和 inout(对存储过程传入和传出)类型的参数。存储过程的代码位于 begin 和 end 语句内。他们是一系列 select 语句，用来检索值。然后保存到相对应的变量(通过 INTO 关键字)。存储过程的参数允许的数据类型与表中使用的类型相同。注意记录集是不被允许的类型，因此，不能通过一个参数返回多个行和列，这也是上面为什么要使用 3 个参数和 3 条 select语句的原因。

使用：

call PROCEDURE(@pricelow,@pricehigh,@priceaverage);

select @pricelow;
select @pricehigh;
select @pricelow,@pricehigh,@priceaverage;

为调用此存储过程，必须指定 3 个变量名。如上所示。3 个参数是存储过程保存结果的 3 个变量的名字。调用时，语句并不显示任何数据，它返回以后可以显示的变量(或在其他处理中使用)。

注意：所有的 MySQL 变量都是以@开头。

使用IN的示例：

CREATE PROCEDURE ordertotal(
    IN innumber int,
    OUT outtotal decimal(8,2)
    )
    BEGIN
        SELECT Sum(item_price * quantity) FROM orderitems WHERE order_num =
        innumber INTO outtotal;
    END; 


CALL ordertotal(20005,@total);
select @total; // 得到 20005 订单的合计

CALL ordertotal(20009,@total);
select @total; //得到 20009 订单的合计

带有控制语句的存储过程

CREATE PROCEDURE ordertotal(
    IN onumber INT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8,2)
    ) COMMENT 'Obtain order total, optionally adding tax'
    BEGIN
        -- declear variable for total
        DECLARE total DECIMAL(8,2);
        -- declear tax percentage
        DECLARE taxrate INT DEFAULT 6;
        -- get the order total
        SELECT Sum(item_price * quantity) FROM orderitems WHERE order_num =
        onumber INTO total;
        -- IS this taxable?
        IF taxable THEN
            -- yes ,so add taxrate to the total
            SELECT total+(total/100*taxrate)INTO total;
        END IF;
        -- finally ,save to out variable
        SELECT total INTO ototal;
    END;

在存储过程中我们使用了 DECLARE 语句，他们表示定义两个局部变量，DECLARE 要求指定变量名和数据类型。它也支持可选的默认值(taxrate 默认 6%)，因为后期我们还要判断要不要增加税，所以，我们把 SELECT 查询的结果存储到局部变量 total 中，然后在 IF 和 THEN 的配合下，检查 taxable 是否为真，然后在真的情况下，我们利用另一条 SELECT 语句增加营业税到局部变量 total 中，然后我们再利用 SELECT 语句将 total(增加税或者不增加税的结果)保存到总的 ototal 中。

上面的 COMMENT 关键字可以给出或者不给出，如果给出，将在 SHOW PROCEDURE STATUS 的结果中显示。

所有语法：

变量相关：

  • DECLARE var_name[,...] type [DEFAULT value]：声明变量
  • SET var_name = expr [, var_name = expr] ...：变量赋值
  • select ... into var_name：将查询的结果赋值给变量

分支循环：

if_then elseif_then else_then

if search_condition then statement_list

    [elseif search_condition then statement_list] ...

    [else statement_list]

end if;

case

CASE case_value

  WHEN when_value THEN statement_list

  [WHEN when_value THEN statement_list] ...

  [ELSE statement_list]

END CASE;

while

while search_condition do

    statement_list

end while;

repeat

while 是满足条件才执行，repeat 是满足条件就退出循环。

REPEAT

  statement_list

  UNTIL search_condition

END REPEAT;

loop

通常配合leave语句退出循环，也可以死循环

[begin_label:] LOOP

  statement_list

END LOOP [end_label]

leave

退出循环

CREATE PROCEDURE pro_test11(n int)
BEGIN
  declare total int default 0;

  ins: LOOP

    IF n <= 0 then
      leave ins;
    END IF;

    set total = total + n;
    set n = n - 1;

  END LOOP ins;

  select total;
END$

使用游标的存储过程

游标是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下。

声明光标：

DECLARE cursor_name CURSOR FOR select_statement ;

代表这个变量是一个cursor类型，对应的是select_statement语句的结果集。

OPEN 光标：

OPEN cursor_name ;

FETCH 光标：

FETCH cursor_name INTO var_name [, var_name] ...

CLOSE 光标：

CLOSE cursor_name ;

实例：

-- 查询emp表中数据, 并逐行获取进行展示
DELIMITER $

create procedure pro_test12()
begin
  DECLARE id int(11);
  DECLARE name varchar(50);
  DECLARE age int(11);
  DECLARE salary int(11);
  DECLARE has_data int default 1;

  DECLARE emp_result CURSOR FOR select * from emp;
  DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;

  open emp_result;

  repeat
    fetch emp_result into id , name , age , salary;
    select concat('id为',id, ', name 为' ,name , ', age为 ' ,age , ', 薪水为: ', salary);
    until has_data = 0
  end repeat;

  close emp_result;
end$

DELIMITER ;

通过DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;相当于在光标获取不到下一行的时候给has_data赋值为0。

修改存储过程

MySQL 中修改存储过程的语法格式如下：

ALTER PROCEDURE 存储过程名 [ 特征 ... ]

特征指定了存储过程的特性，可能的取值有：

  • CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句。
  • NO SQL 表示子程序中不包含 SQL 语句。
  • READS SQL DATA 表示子程序中包含读数据的语句。
  • MODIFIES SQL DATA 表示子程序中包含写数据的语句。
  • SQL SECURITY { DEFINER |INVOKER } 指明谁有权限来执行。
    • DEFINER 表示只有定义者自己才能够执行。
    • INVOKER 表示调用者可以执行。
  • COMMENT 'string' 表示注释信息。
ALTER PROCEDURE showstuscore MODIFIES SQL DATA SQL SECURITY INVOKER;

提示：ALTER PROCEDURE 语句用于修改存储过程的某些特征。如果要修改存储过程的内容，可以先删除原存储过程，再以相同的命名创建新的存储过程；如果要修改存储过程的名称，可以先删除原存储过程，再以不同的命名创建新的存储过程。

删除存储过程

MySQL 中使用 DROP PROCEDURE 语句来删除数据库中已经存在的存储过程。语法格式如下：

DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <过程名>

IF EXISTS：指定这个关键字用于防止因删除不存在的存储过程而引发的错误。

触发器

概述

MySQL 数据库中触发器是一个特殊的存储过程，不同的是执行存储过程要使用 CALL 语句来调用，而触发器的执行不需要使用 CALL 语句来调用，也不需要手工启动，只要一个预定义的事件发生就会被 MySQL自动调用。

触发程序的优点如下：
+ 触发程序的执行是自动的，当对触发程序相关表的数据做出相应的修改后立即执行。
+ 触发程序可以通过数据库中相关的表层叠修改另外的表。
+ 触发程序可以实施比 FOREIGN KEY 约束、CHECK 约束更为复杂的检查和操作。

在 MySQL 中，只有执行 INSERT、UPDATE 和 DELETE 操作时才能激活触发器。

创建触发器

在创建触发器时，需要给出 4 条语句（规则）：
1. 唯一的触发器名；
2. 触发器关联的表；
3. 触发器应该响应的活动；
4. 触发器何时执行(处理之前或者之后)

CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT
'Product added' INTO @info;

CREATE TRIGGER 用来创建名为 newproduct 的新触发器。触发器可以在一个操作发生前或
者发生后执行，这里 AFTER INSERT 是指此触发器在 INSERT 语句成功执行后执行。这个触
发器还指定 FOR EACH ROW ， 因此代码对每个插入行都会执行。文本 Product added 将
对每个插入的行显示一次

INSERT 触发器

是在 insert 语句执行之前或者执行之后被执行的触发器。
1. 在 insert 触发器代码中，可引入一个名为 new 的虚拟表，访问被插入的行（普通插入语句之后也存在这个表）
2. 在 before insert 触发器中，new 中的值也可以被更新(允许更改被插入的值)
3. 对于 auto_increment 列，new 在 insert 执行之前包含 0，在 insert 执行之后包含新的自动生成值

CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT
NEW.order_num;

该语句创建一个名为 neworder 的触发器，按照 AFTER INSERT ON orders 执行。在插入一个新订单到 orders 表时，MySQL 生成一个新的订单号并保存到 order_num 中。触发器从NEW.order_num 取得这个值并返回它。此触发器必须按照 AFTER INSERT 执行，因为在BEFORE INSERT 语句执行之前，新 order_num 还没有生成。 对于 orders 的每次插入使用这个触发器总是返回新的订单号。

DELETE 触发器

Delete 触发器在 delete 语句执行之前或者之后执行。
1. 在 delete 触发器的代码内,可以引用一个名为 OLD 的虚拟表，用来访问被删除的行。
2. OLD 中的值全为只读，不能更新。

CREATE TRIGGER deleteorder BEFORE DELETE ON orders FOR EACH ROW
BEGIN
    INSERT INTO archive_orders(order_num,order_date,cust_id) values
    (OLD.order_num,OLD.order_date,OLD.cust_id);
END;

在任意订单被删除前将执行此触发器，它使用一条 INSERT 语句将 OLD 中的值(要被删除的订单) 保存到一个名为 archive_orders 的存档表中(为实际使用这个例子，我们需要用与orders 相同的列创建一个名为 archive_orders 的表)

使用 BEFORE DELETE 触发器的优点(相对于 AFTER DELETE 触发器来说)为，如果由于某种原因，订单不能存档，delete 本身将被放弃。

我们在这个触发器使用了 BEGIN 和 END 语句标记触发器体。这在此例子中并不是必须的，只是为了说明使用BEGIN END 块的好处是触发器能够容纳多条SQL 语句(在BEGIN END块中一条挨着一条)。

UPDATE 触发器

在 update 语句执行之前或者之后执行

  1. 在 update 触发器的代码内,可以引用一个名为 OLD 的虚拟表，用来访问以前(UPDATE 语句之前)的值，引用一个名为 NEW 的虚拟表访问新更新的值。
  2. 在 BEFORE UPDATE 触发器中，NEW 中的值可能也被用于更新(允许更改将要用于 UPDATE语句中的值)
  3. OLD 中的值全为只读，不能更新。
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET
NEW.vend_state = Upper(NEW.vemd_state);

保证州名缩写总是大写(不管 UPFATE 语句中是否给出了大写)，每次更新一行时，NEW.vend_state 中的值(将用来更新表行的值)都用 Upper(NEW.vend_state)替换。

总结

  1. 与其他 DBMS 相比，MySQL 5 中支持的触发器相当初级，未来的 MySQL 版本中估计会存在一些改进和增强触发器的支持。
  2. 通常 before 用于数据的验证和净化(为了保证插入表中的数据确实是需要的数据) 也适用于 update 触发器。
  3. 创建触发器可能需要特殊的安全访问权限，但是触发器的执行时自动的，如果 insert，update，或者 delete 语句能够执行，则相关的触发器也能执行。
  4. 用触发器来保证数据的一致性(大小写，格式等)。在触发器中执行这种类型的处理的优点就是它总是进行这种处理，而且透明的进行，与客户机应用无关。
  5. 触发器的一种非常有意义的使用就是创建审计跟踪。使用触发器，把更改(如果需要，甚至还有之前和之后的状态)记录到另外一个表是非常容易的。
  6. MySQL 触发器不支持 call 语句，无法从触发器内调用存储过程。

总体来说，触发器使用的过程中，MySQL 会按照以下方式来处理错误。

若对于事务性表，如果触发程序失败，以及由此导致的整个语句失败，那么该语句所执行的所有更改将回滚；对于非事务性表，则不能执行此类回滚，即使语句失败，失败之前所做的任何更改依然有效。

若 BEFORE 触发程序失败，则 MySQL 将不执行相应行上的操作。

若在 BEFORE 或 AFTER 触发程序的执行过程中出现错误，则将导致调用触发程序的整个语句失败。

仅当 BEFORE 触发程序和行操作均已被成功执行，MySQL 才会执行AFTER触发程序。

原创文章，作者：彭晨涛，如若转载，请注明出处：https://www.codetool.top/article/mysql%e5%9b%9e%e9%a1%be-%e5%ad%98%e5%82%a8%e8%bf%87%e7%a8%8b%e4%b8%8e%e8%a7%a6%e5%8f%91%e5%99%a8/

赞 (0)
彭晨涛彭晨涛管理者
0 0
上一篇 2020年3月2日
下一篇 2020年3月2日

相关推荐

发表回复

登录后才能评论