mysql 存储过程

存储过程案例：

-- 使用存储过程查询每个门店的数据

-- 使用 DELIMITER $$ 命令将存储过程语句的结束符号从分号 ; 临时改为两个 $$，
-- 使得存储过程体中使用的分号被直接传递到服务器，而不会被mysql客户端解释
DELIMITER $$
DROP PROCEDURE IF EXISTS `query_total_buy_amount_by_shop_no`$$
CREATE  PROCEDURE `query_total_buy_amount_by_shop_no`(IN p_shop_no_array varchar(255), IN p_data_date varchar(50),IN p_start_time_min varchar(50),IN p_start_time_max varchar(50))
-- 存储过程开始
BEGIN
-- 变量赋值：将入参赋值给变量
SET @array_content = p_shop_no_array;
SET @i=1;
SET @count=CHAR_LENGTH(@array_content)-CHAR_LENGTH(REPLACE(@array_content,' ','')) + 1;

WHILE @i <= @count
DO
-- 将 入参 p_shop_no_array 截取为每个shop_no（SUBSTRING_INDEX 按照关键字截取）
set @shop_no = SUBSTRING_INDEX(SUBSTRING_INDEX(@array_content,' ',@i),' ',-1);

-- ############## 查询语句主体 ↓↓↓ ##############
select p.shop_no,
       p.shop_name,
       p.total_buy_index,
       p.buy_amount_total,
       p.buy_completion_rate_total,
       p.statistical_date
from
(
  select  t.shop_no as shop_no,
          t.shop_name as shop_name,
          t.total_buy_amount/t.total_buy_index as buy_completion_rate_total,
          t.buy_amount/t.buy_index as buy_completion_rate_selfemployed,
          t.expense_amount/t.expense_index as expense_completion_rate_selfemployed,
          t.total_buy_amount as buy_amount_total,
          t.buy_amount as buy_amount_selfemployed,
          t.expense_amount as expense_amount_selfemployed,
          t.total_buy_index as total_buy_index,
          t.buy_index as buy_index,
          t.expense_index as expense_index,
          t.data_date as statistical_date
from
(
      select t1.shop_no,
             t1.shop_name,
             t1.data_date,
             t1.total_buy_index,
             t1.buy_index,
             t1.expense_index,
             ifnull(t2.total_amount_daily,0.0) + ifnull(t4.total_amount_monthly,0.0) + ifnull(t5.total_amount_yearly,0.0) as total_buy_amount,
             ifnull(t2.buy_amount_daily,0.0) + ifnull(t4.buy_amount_monthly,0.0) + ifnull(t5.buy_amount_yearly,0.0) as buy_amount,
             ifnull(t3.expense_amount_daily,0.0) + ifnull(t4.expense_amount_monthly,0.0) + ifnull(t5.expense_amount_yearly,0.0) as expense_amount
      from
      (
          select shop_no,
                 shop_name,
                 data_date,
                 total_buy_index,
                 buy_index,
                 expense_index
          from test_shop_index_dim
          where shop_no = @shop_no
          and data_date = p_data_date
          and index_type != 'H'
          and is_business = 'true'
      ) t1
      left join
      (
          select shop_no,
                 data_date,
                 sum(total_buy_amount) as total_amount_daily,
                 sum(buy_amount) as buy_amount_daily
          from test_shop_buy_real_time
          where
          index_type = 'D'
          and shop_no = @shop_no
          and start_time >= p_start_time_min
          and start_time <= p_start_time_max
          group by shop_no,data_date
      ) t2
      on t1.shop_no = t2.shop_no
      left join
      (
          select shop_no,
                 data_date,
                 sum(expense_amount) as expense_amount_daily
          from test_shop_self_expense_real_time
          where data_date = p_data_date
          and index_type = 'D'
          and shop_no = @shop_no
          group by shop_no,data_date
      ) t3
      on t1.shop_no = t3.shop_no
      left join
      (
          select shop_no,
                 data_date,
                 total_buy_amount as total_amount_monthly,
                 buy_amount as buy_amount_monthly,
                 expense_amount as expense_amount_monthly
          from test_shop_finish_dim
          where data_date = p_data_date
          and index_type = 'M'
          and shop_no = @shop_no
      ) t4
      on t1.shop_no = t4.shop_no and substring(t1.data_date,1,7) = t4.data_date
      left join
      (
          select shop_no,
                 data_date,
                 total_buy_amount as total_amount_yearly,
                 buy_amount as buy_amount_yearly,
                 expense_amount as expense_amount_yearly
          from test_shop_finish_dim
          where data_date = p_data_date
          and index_type = 'Y'
          and shop_no = @shop_no
      ) t5
      on t1.shop_no = t5.shop_no and substring(t1.data_date,1,4) = t5.data_date
  ) t
) p
;
-- ############## 查询语句主体 ↑↑↑ ##############

SET @i=@i+1;
END WHILE; -- 结束while循环

END$$ -- 存储过程结束，$$为存储过程语句的结束符
DELIMITER ; -- 将语句的结束符号恢复为分号


-- ############## 存储过程调用 ##############
-- 入参1： shop_no ，多个以空格分割
-- 入参2： data_date
-- 入参3： start_time_min（任一天某个时间段的数据核实需要此参数）
-- 入参4： start_time_max（任一天某个时间段的数据核实需要此参数）

-- 当前时间为 01-22，查询 01-21全天回款数据
CALL query_total_buy_amount_by_shop_no("6222 3142 5468 6493","2020-01-21","2020-01-21 00:00:00","2020-01-21 23:59:59");

-- 当前时间为 01-22，查询 01月 回款数据，计算22号当天的
CALL query_total_buy_amount_by_shop_no("6222 3142 5968 6099","2020-01","2020-01-22 00:00:00","2020-01-22 23:59:59");

 

存储过程示例：

DROP PROCEDURE IF EXISTS `Test_Procedure01`;
DELIMITER $$
CREATE PROCEDURE `Test_Procedure01`()
BEGIN
    -- 定义变量
    DECLARE v_i int unsigned DEFAULT 0;
    WHILE v_i < 5 DO
        SELECT v_i;
        SET v_i = v_i+1;
    END WHILE;
END $$
DELIMITER ;

call Test_Procedure01();