MySQL 存储过程

最新推荐文章于 2022-05-10 19:04:31 发布

forverfly

最新推荐文章于 2022-05-10 19:04:31 发布

阅读量142

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql 文章标签：数据库 mysql

本文链接：https://blog.youkuaiyun.com/forverfly/article/details/98337175

mysql 专栏收录该内容

0 篇文章

订阅专栏

mysql存储过程

MySQL 5.0 版本开始支持存储过程。
存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。
存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用


DROP procedure IF EXISTS `getGameName`;#删除储存过程
 
#注意参数名不能与字段名相同
DELIMITER $$
CREATE PROCEDURE getGameName(
 IN gameid INT, #入参
 OUT g_name VARCHAR(45), #出参
 OUT pin_yin VARCHAR(45)) #出参
BEGIN
 SELECT gamename
 INTO g_name
 FROM cy_game
 WHERE id = gameid;
 
 SELECT pinyin
 INTO pin_yin
 FROM cy_game
 WHERE id = gameid;
END$$
DELIMITER ;
 
call getGameName(4,@g_name,@pin_yin);#调用储存过程
SELECT @g_name,@pin_yin;#返回值
 
SHOW PROCEDURE STATUS; #显示有哪些储存过程
 
SHOW CREATE PROCEDURE getGameInfo;#显示指定储存过程的信息，包括代码

优点

MySQL存储过程的优点
通常存储过程有助于提高应用程序的性能。当创建，存储过程被编译之后，就存储在数据库中。但是，MySQL实现的存储过程略有不同。 MySQL存储过程按需编译。在编译存储过程之后，MySQL将其放入缓存中。 MySQL为每个连接维护自己的存储过程高速缓存。如果应用程序在单个连接中多次使用存储过程，则使用编译版本，否则存储过程的工作方式类似于查询。
存储过程有助于减少应用程序和数据库服务器之间的流量，因为应用程序不必发送多个冗长的SQL语句，而只能发送存储过程的名称和参数。
存储的程序对任何应用程序都是可重用的和透明的。存储过程将数据库接口暴露给所有应用程序，以便开发人员不必开发存储过程中已支持的功能。
存储的程序是安全的。数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。

缺点

如果使用大量存储过程，那么使用这些存储过程的每个连接的内存使用量将会大大增加。此外，如果您在存储过程中过度使用大量逻辑操作，则CPU使用率也会增加，因为数据库服务器的设计不当于逻辑运算。
存储过程的构造使得开发具有复杂业务逻辑的存储过程变得更加困难。
很难调试存储过程。只有少数数据库管理系统允许您调试存储过程。不幸的是，MySQL不提供调试存储过程的功能。
开发和维护存储过程并不容易。开发和维护存储过程通常需要一个不是所有应用程序开发人员拥有的专业技能。这可能会导致应用程序开发和维护阶段的问题。

创建存储过程

#创建储存过程
DELIMITER //
 CREATE PROCEDURE GetAllProducts()
   BEGIN
   SELECT *  FROM products;
   END //
DELIMITER ;

声明变量

1.要在存储过程中声明一个变量，可以使用DECLARE语句，如下所示

DECLARE variable_name datatype(size) DEFAULT default_value;
下面来更详细地解释上面的语句：
	首先，在DECLARE关键字后面要指定变量名。变量名必须遵循MySQL表列名称的命名规则。
	其次，指定变量的数据类型及其大小。变量可以有任何MySQL数据类型，如INT，VARCHAR，DATETIME等。
	第三，当声明一个变量时，它的初始值为NULL。但是可以使用DEFAULT关键字为变量分配默认值。

例如，可以声明一个名为total_sale的变量，数据类型为INT，默认值为0，如下所示：DECLARE total_sale INT DEFAULT 0;
MySQL允许您使用单个DECLARE语句声明共享相同数据类型的两个或多个变量，如下所示：DECLARE x, y INT DEFAULT 0;我们声明了两个整数变量x和y，并将其默认值设置为0。

分配变量值

当声明了一个变量后，就可以开始使用它了。要为变量分配一个值，可以使用SET语句，例如

DECLARE total_count INT DEFAULT 0;
SET total_count = 10;

上面语句中，分配total_count变量的值为10。
除了SET语句之外，还可以使用SELECT INTO语句将查询的结果分配给一个变量。请参阅以下示例：

DECLARE total_products INT DEFAULT 0
SELECT COUNT(*) INTO total_products
FROM products

在上面的例子中：
首先，声明一个名为total_products的变量，并将其值初始化为0。
然后，使用SELECT INTO语句来分配值给total_products变量，从示例数据库(yiibaidb)中的products表中选择的产品数量。

使用游标

MySQL5添加了对游标的支持
只能用于存储过程
由前几章可知，mysql检索操作返回一组称为结果集的行。都与mysql语句匹配的行（0行或多行），使用简单的SELECT语句，没有办法得到第一行、下一行或前10行，也不存在每次行地处理所有行的简单方法（相对于成批处理他们）
有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。游标（cursor）是一个存储在MYSQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。
游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。
使用游标
    使用游标涉及几个明确的步骤：
        1 在能够使用游标前，必须声明（定义）它，这个过程实际上没有检索数据，它只是定义要使用的SELECT语句
        2 一旦声明后，必须打开游标以供使用。这个过程用钱吗定义的SELECT语句吧数据实际检索出来
        3 对于填有数据的游标，根据需要取出（检索）的各行
        4 在接受游标使用时，必须关闭它 如果不明确关闭游标，MySQL将会在到达END语句时自动关闭它
创建游标
    游标可用DECLARE 语句创建。 DECLARE命名游标，并定义相应的SELECT语句。根据需要选择带有WHERE和其他子句。如：下面第一名为ordernumbers的游标，使用了检索所有订单的SELECT语句
        CREATE PROCEDURE processorders()
        BEGIN
        DECLARE ordernumbers CURSOR
        FOR
        SELECT order_num FROM orders ;
        END;
        存储过程处理完成后，游标就消失，因为它局限于存储过程
打开和关闭游标
        CREATE PROCEDURE processorders()
        BEGIN
        DECLAREordernumbers CURSOR
        FOR
        SELECT order_num FROM orders ;
        Open ordernumbers ;
        Close ordernumbers ;  //CLOSE释放游标使用的所有内部内存和资源，因此，每个游标不需要时都应该关闭
        END;
使用游标数据
    在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。FETCH指定检索什么数据（所需的要列），检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行，相当于PHP中的each()函数

循环检索数据，从第一行到最后一行

        CREATE PROCEDURE processorders()
        BEGIN
        -- 声明局部变量
        DECLARE done BOOLEAN DEFAULT 0;
        DECLARE o INT;
        DECLAREordernumbers CURSOR
        FOR
        SELECT order_num FROM orders ;
        -- 当SQLSTATE为02000时设置done值为1
        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
        --打开游标
        Open ordernumbers ;
        -- 开始循环
        REPEAT
        -- 把当前行的值赋给声明的局部变量o中
        FETCH ordernumbers INTO o;
        -- 当done为真时停止循环
        UNTIL done END REPEAT;
        --关闭游标
        Close ordernumbers ;  //CLOSE释放游标使用的所有内部内存和资源，因此，每个游标不需要时都应该关闭
        END;
    语句中定义了CONTINUE HANDLER ,它是在条件出现时被执行的代码。这里，它指出当SQLSTATE '02000'出现时，SET done=1。SQLSTATE '02000'是一个未找到条件，当REPEAT没有更多的行供循环时，出现这个条件。
DECLARE 语句次序  用DECLARE语句定义局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标之后定义。不遵守此规则就会出错

重复和循环除这里使用REPEAT语句外，MySQL还支持循环语句，它可用来重复执行代码，直到使用LEAVE语句手动退出为止。通常REPEAT语句的语法使它更适合于对游标进行的循环。
为了把这些内容组织起来，这次吧取出的数据进行某种实际的处理

    CREATE PROCEDURE processorders()
    BEGIN
    -- 声明局部变量
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
    DECLARE t DECIMAL(8,2)
    DECLAREordernumbers CURSOR
    FOR
    SELECT order_num FROM orders ;
    -- 当SQLSTATE为02000时设置done值为1
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
    -- 创建一个ordertotals的表
    CREATE TABLE IF NOT EXISTS ordertotals( order_num INT , total DECIMAL(8,2))
    --打开游标
    Open ordernumbers ;
    -- 开始循环
    REPEAT
    -- 把当前行的值赋给声明的局部变量o中
    FETCH ordernumbers INTO o;
    -- 用上文讲到的ordertotal存储过程并传入参数，返回营业税计算后的合计传给t变量
    CALL ordertotal(o , 1 ,t)
    -- 把订单号和合计插入到新建的ordertotals表中
    INSERT INTO ordertotals(order_num, total) VALUES(o , t);
    -- 当done为真时停止循环
    UNTIL done END REPEAT;
    --关闭游标
    Close ordernumbers ;  //CLOSE释放游标使用的所有内部内存和资源，因此，每个游标不需要时都应该关闭
    END;
    最后SELECT * FROM ordertotals就能查看结果了

使用触发器

MySQL5版本后支持触发器
只有表支持触发器，视图不支持触发器
MySQL语句在需要的时被执行，存储过程也是如此，但是如果你想要某条语句（或某些语句）在事件发生时自动执行，那该怎么办呢：例如：
    1 每增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确，区的缩写是否为大写
    2 每当订购一个产品时，都从库存数量中减少订购的数量
    3 无论何时删除一行，都在某个存档中保留一个副本
这写例子的共同之处是他们都需要在某个表发生更改时自动处理。这就是触发器。触发器是MySQL响应一下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END语句之间的一组语句）
1 DELETE
2 INSERT
3 UPDATE
其他的MySQL语句不支持触发器

创建触发器

    创建触发器需要给出4条信息
    1 唯一的触发器名；  //保存每个数据库中的触发器名唯一
    2 触发器关联的表；
    3 触发器应该响应的活动（DELETE、INSERT或UPDATE）
    4 触发器何时执行（处理前还是后,前是BEFORE 后是AFTER）
    创建触发器用CREATE TRIGGER
    CREATE TRIGGER newproduct AFTER INSERT ON products
    FOR EACH ROW SELECT'Product added'
   创建新触发器newproduct ，它将在INSERT语句成功执行后执行。这个触发器还镇定FOR EACH ROW,因此代码对每个插入的行执行。这个例子作用是文本对每个插入的行显示一次product added
    FOR EACH ROW 针对每个行都有作用，避免了INSERT一次插入多条语句

触发器定义规则

    触发器按每个表每个事件每次地定义，每个表每个事件每次只允许定义一个触发器，因此，每个表最多定义6个触发器（每条INSERT UPDATE 和DELETE的之前和之后）。单个触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT 和UPDATE存储执行的触发器，则应该定义两个触发器

触发器失败如果BEFORE(之前)触发器失败，则MySQL将不执行SQL语句的请求操作，此外，如果BEFORE触发器或语句本身失败，MySQL将不执行AFTER(之后)触发器

删除触发器

    DROP TRIGGER newproduct；
    触发器不能更新或覆盖，所以修改触发器只能先删除再创建

使用触发器

    我们来看看每种触发器以及它们的差别
INSERT 触发器
    INSERT触发器在INSERT语句执行之前或之后执行。需要知道以下几点：
1 在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行
2 在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改插入的值）
3 对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值
   提示：通常BEFORE用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据）。本提示也适用于UPDATE触发器

DELETE 触发器

    DELETE触发器在语句执行之前还是之后执行，需要知道以下几点：
1 在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；
2 OLD中的值全部是只读的，不能更新
    例子演示适用OLD保存将要除的行到一个存档表中
    CREATE TRIGGERdeleteorder 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;
    //此处的BEGIN  END块是非必需的，可以没有
在任何订单删除之前执行这个触发器，它适用一条INSERT语句将OLD中的值（将要删除的值）保存到一个名为archive_orders的存档表中
BEFORE DELETE触发器的优点是（相对于AFTER DELETE触发器），如果由于某种原因，订单不能被存档，DELETE本身将被放弃执行。

多语言触发器正如上面所见，触发器deleteorder 使用了BEGIN和END语句标记触发器体。这在此例中并不是必需的，不过也没有害处。使用BEGIN END块的好处是触发器能容纳多条SQL语句。

UPDATE触发器

    UPDATE触发器在语句执行之前还是之后执行，需要知道以下几点：
    1 在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问（UPDATE语句前）的值，引用一名为NEW的虚拟表访问新更新的值
    2 在BEFORE UPDATE触发器中，NEW中的值可能被更新，（允许更改将要用于UPDATE语句中的值）
    3 OLD中的值全都是只读的，不能更新
        例子：保证州名的缩写总是大写（不管UPDATE语句给出的是大写还是小写）
        CREATE TRIGGER updatevendor BEFORE UPDATE ON vendores FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state)
触发器的进一步介绍
1 与其他DBMS相比，MySQL5中支持的触发器相当初级。以后可能会增强
2 创建触发器可能需要特殊的安全访问权限，但是触发器的执行时自动的.如果INSERT UPDATE DELETE能执行，触发器就能执行
3 应该用触发器来保证数据的一致性（大小写、格式等）。在触发器中执行这种类型的处理的优点是它总是进行这个处理，而且是透明地进行，与客户机应用无关
4 触发器的一种非常有意义的使用创建审计跟踪。使用触发器把更改（如果需要，甚至还有之前和之后的状态）记录到另一表非常容易
5 遗憾的是，MySQL触发器中不支持CALL语句，这表示不能从触发器中调用存储过程。所需要的存储过程代码需要复制到触发器内