mysql必知必会中的存储过程和游标的案例（以及自己使用过的事件）

最新推荐文章于 2022-03-04 10:49:00 发布

莱克星顿的枪声

最新推荐文章于 2022-03-04 10:49:00 发布

阅读量311

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql 数据库

本文链接：https://blog.youkuaiyun.com/qq_37155959/article/details/80960407

mysql 同时被 2 个专栏收录

39 篇文章

订阅专栏

数据库

35 篇文章

订阅专栏

本文介绍MySQL存储过程的基础概念与实际应用案例，包括简单的存储过程创建、带有输出参数的存储过程及结合游标的复杂操作，并展示了如何通过事件调度定期执行特定任务。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.存储过程

-- 创建存储过程;
DROP PROCEDURE IF EXISTS ordertotal;

DELIMITER $$

CREATE DEFINER = 'root' @'%' PROCEDURE ordertotal (
  IN onumber INT,
  IN taxable BOOLEAN,
  OUT ototal DECIMAL (8, 2)
)
BEGIN
  DECLARE total DECIMAL (8, 2);
  DECLARE taxrate INT DEFAULT 6;
  SELECT
    SUM(item_price * quantity)
  FROM
    orderitems
  WHERE order_num = onumber INTO total;
  IF taxable
  THEN
  SELECT
    total + (total / 100 * taxrate) INTO total;
  END IF;
  SELECT
    total INTO ototal;
END $$

DELIMITER ;
-- 调用存储过程
CALL ordertotal(20005,0,@total);

-- 查看输出参数 ;
select -@total;

2.存储过程结合游标

-- 使用游标3--复杂;
 DROP PROCEDURE IF EXISTS cursordemo3;

DELIMITER $$

CREATE DEFINER = 'root' @'%' PROCEDURE cursordemo3 ()
BEGIN
  -- 声明局部变量;
   DECLARE done BOOLEAN DEFAULT 0;
  DECLARE o INT;
  DECLARE t DECIMAL (8, 2);
  -- 声明游标;`customers`
   DECLARE cursordemo3 CURSOR FOR
  SELECT
    order_num
  FROM
    `orders`;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  CREATE TABLE IF NOT EXISTS ordertotals (order_num INT, total DECIMAL (8, 2));
  OPEN cursordemo3;
  -- 遍历所有行;
   REPEAT
    FETCH cursordemo3 INTO o;
     CALL ordertotal(o, 0, t);
     SELECT t;
     INSERT INTO ordertotals (order_num, total) VALUE (o, t);
    -- 循环结束;
     UNTIL done
  END REPEAT;
  CLOSE cursordemo3;
END $$

DELIMITER ;
CALL cursordemo3 ();

3. 一个最简单的存储过程案例

DELIMITER $$

USE `flowbill_operation_platform` $$

DROP PROCEDURE IF EXISTS demo_test $$

CREATE DEFINER = 'root' @'%' PROCEDURE demo_test ()
BEGIN
  
  SELECT
    NOW();
  
END $$


DELIMITER ;

其中在整个创建存过过程的语句中，除了在语句结尾以及在begin和end之间可以用分号外，其余地方用$$ 代替分号;

mysql 存储过程使用2个输出参数

DROP PROCEDURE IF EXISTS test_out;

DELIMITER $$

CREATE DEFINER = 'root' @'%' PROCEDURE test_out (OUT nowTime VARCHAR(20)  ,OUT nowDate VARCHAR(20))
BEGIN
SELECT  TIME(NOW()) INTO nowTime;
SELECT  DATE(NOW()) INTO nowDate;

SELECT nowTime;
 
END $$

DELIMITER ;
CALL test_out(@nowTime,@nowDate);
-- 查看输出参数
SELECT @nowTime;
SELECT @nowDate;

mysql 事件

DELIMITER $$

DROP EVENT IF EXISTS  event_do_pps_staff_performance $$
CREATE DEFINER=`nm_wap`@`%` EVENT `event_do_pps_staff_performance` ON SCHEDULE EVERY 30 MINUTE STARTS '2018-05-08 00:00:01' ENDS '2018-12-30 00:00:01' ON COMPLETION PRESERVE ENABLE COMMENT '每隔30分钟,进行调用' DO BEGIN
       CALL do_pps_staff_performance();
       CALL do_pps_staff_performance_social_channel ();
    END$$

DELIMITER ;