mysql 存储过程

1.带游标的存储过程

DROP PROCEDURE  IF EXISTS `sp_message_his`; 

CREATE PROCEDURE `sp_message_his`(IN `msg_id` int) 

 

BEGIN 

  -- 用户id -- 

  declare v_userid bigint(20);

  -- 遍历数据结束标志

  DECLARE done INT DEFAULT FALSE;

  -- 查询推送方式为1的所有用户id

  declare cur cursor for SELECT t.userID from devicetoken t where t.pushtype=1;

  -- 将结束标志绑定到游标

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

 

  -- 打开游标

  OPEN cur;

   -- 开始循环

   read_loop: LOOP

    -- 提取游标里的数据，这里只有一个，多个的话也一样；

    FETCH cur INTO v_userid;

    -- 声明结束的时候

    IF done THEN

      LEAVE read_loop;

    END IF;

    -- 这里做你想做的循环的事件

    INSERT INTO suinfouser(userID,infoID,readed) values (v_userid,msg_id,0);

  END LOOP;

  -- 关闭游标

  CLOSE cur;

END

 

 

DROP PROCEDURE  IF EXISTS `sp_choose_his`; 

CREATE PROCEDURE `sp_choose_his`() 

BEGIN 

   DECLARE v_customerId int;

   DECLARE v_regtime date;

   DECLARE v_order_no VARCHAR(64);

 

   -- 遍历数据结束标志

   DECLARE done INT DEFAULT FALSE;

   -- 查询所有的VIP用户

   DECLARE cur cursor for SELECT t.customerId,left(t.RegTime,10) regtime from cus_customer t where t.GroupId=7

   and t.customerId not in (SELECT DISTINCT t.customer_id from choose_stock_record t where t.pay_status=1 and t.type=4);

   -- 将结束标志绑定到游标

   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

 

   -- 打开游标

   OPEN cur;

   -- 开始循环

   read_loop: LOOP

    -- 提取游标里的数据，这里只有一个，多个的话也一样；

    FETCH cur INTO v_customerId,v_regtime;

    -- 声明结束的时候

    IF done THEN

      LEAVE read_loop;

    END IF;

    select round(round(rand(),12)*10000000000) INTO v_order_no from dual;

    -- 这里做你想做的循环的事件

    INSERT into choose_stock_record(customer_id,order_no,stock_code,start_date,end_date,pay_amount,unit_name,unit_period,pay_time,pay_status,sub_status,type,channel,create_time,remark)

    VALUES(v_customerId,concat(168,v_order_no),'xg_zlzy',v_regtime,date_add(v_regtime, interval 365 day),0,'vip赠送',365,NOW(),1,1,4,1,NOW(),'手动开通选股');

 

    INSERT into choose_stock_record(customer_id,order_no,stock_code,start_date,end_date,pay_amount,unit_name,unit_period,pay_time,pay_status,sub_status,type,channel,create_time,remark)

    VALUES(v_customerId,concat(168,v_order_no),'xg_flzt',v_regtime,date_add(v_regtime, interval 365 day),0,'vip赠送',365,NOW(),1,1,4,1,NOW(),'手动开通选股');

 

    INSERT into choose_stock_record(customer_id,order_no,stock_code,start_date,end_date,pay_amount,unit_name,unit_period,pay_time,pay_status,sub_status,type,channel,create_time,remark)

    VALUES(v_customerId,concat(168,v_order_no),'xg_qlzy',v_regtime,date_add(v_regtime, interval 365 day),0,'vip赠送',365,NOW(),1,1,4,1,NOW(),'手动开通选股');

 

    INSERT into choose_stock_record(customer_id,order_no,stock_code,start_date,end_date,pay_amount,unit_name,unit_period,pay_time,pay_status,sub_status,type,channel,create_time,remark)

    VALUES(v_customerId,concat(168,v_order_no),'dz_xg_slqf',v_regtime,date_add(v_regtime, interval 365 day),0,'vip赠送',365,NOW(),1,1,4,1,NOW(),'手动开通选股');

 

    INSERT into choose_stock_record(customer_id,order_no,stock_code,start_date,end_date,pay_amount,unit_name,unit_period,pay_time,pay_status,sub_status,type,channel,create_time,remark)

    VALUES(v_customerId,concat(168,v_order_no),'dz_xg_ztyc',v_regtime,date_add(v_regtime, interval 365 day),0,'vip赠送',365,NOW(),1,1,4,1,NOW(),'手动开通选股');

 

  END LOOP;

  -- 关闭游标

  CLOSE cur;

   

END;

 

2.普通的存储过程

DROP PROCEDURE  IF EXISTS `sp_timeshar_his`; 

CREATE PROCEDURE `sp_timeshar_his`

BEGIN 

   DECLARE v_cur_Date date;

   DECLARE v_cur_num_sh int;

   DECLARE v_minDate_sh date; 

   DECLARE v_cur_num_sz int;

   DECLARE v_minDate_sz date; 

 

   set v_cur_Date = CURDATE();

   set v_cur_num_sh =0;

   set v_cur_num_sz =0;

   

   -- 查询上海当天是否有数据 -- 

   SELECT count(1) into v_cur_num_sh from stk_timesharing_sh where date=DATE_FORMAT(v_cur_Date,'%Y-%m-%d');

   -- 把数据写入历史表 --

   if v_cur_num_sh>0 THEN 

       INSERT INTO stk_timesharing_sh_bak(ts_id,asset_id,price,avg_price,volume,date,time,create_time) 

       select t.ts_id,t.asset_id,t.price,t.avg_price,t.volume,t.date,t.time,CURTIME() from stk_timesharing_sh t 

       where t.date=DATE_FORMAT(v_cur_Date, '%Y-%m-%d');

   end if ;

   -- 查询最小的日期 --

   select min(a.date) into v_minDate_sh from 

   (SELECT DISTINCT t.date from stk_timesharing_sh t ORDER BY t.date desc LIMIT 8)a;

   -- 删除数据--

   DELETE from stk_timesharing_sh where date<=DATE_FORMAT(v_minDate_sh, '%Y-%m-%d');

 

   -- 查询深圳当天是否有数据 -- 

   SELECT count(1) into v_cur_num_sz from stk_timesharing_sz  where date=DATE_FORMAT(v_cur_Date,'%Y-%m-%d');

    -- 把数据写入历史表 --

   if v_cur_num_sz>0 THEN 

       INSERT INTO stk_timesharing_sz_bak(ts_id,asset_id,price,avg_price,volume,date,time,create_time) 

       select t.ts_id,t.asset_id,t.price,t.avg_price,t.volume,t.date,t.time,CURTIME() from stk_timesharing_sz t 

       where t.date=DATE_FORMAT(v_cur_Date, '%Y-%m-%d');

   end if ;

   -- 查询最小的日期 --

   select min(a.date) into v_minDate_sz from 

   (SELECT DISTINCT t.date from stk_timesharing_sz t ORDER BY t.date desc LIMIT 8)a;

   -- 删除数据--

   DELETE from stk_timesharing_sz where date<=DATE_FORMAT(v_minDate_sz, '%Y-%m-%d');

   

 

END

 

3.有多个循环语句的存储过程

DROP PROCEDURE  IF EXISTS `sp_register_his`; 

CREATE PROCEDURE `sp_register_his`() 

BEGIN 

   DECLARE v_i int unsigned DEFAULT 1;

   DECLARE v_reg_date date;

   DECLARE v_next_date date;

   DECLARE v_cur_date date;

   DECLARE v_j int unsigned DEFAULT 1;

   DECLARE v_first_week VARCHAR(20) DEFAULT '2019-05-06';

   DECLARE v_next_week VARCHAR(20);

   DECLARE v_cur_week  VARCHAR(20);

DECLARE v_m int unsigned DEFAULT 1;

   DECLARE v_first_month VARCHAR(20) DEFAULT '2019-05-01';

   DECLARE v_next_month VARCHAR(20);

   DECLARE v_cur_month VARCHAR(20);

   DECLARE temp_mm VARCHAR(20);

 

SELECT min(left(t.reg_time,10)) into v_reg_date from policy.cus_customer t;

   SELECT date_format(now(),'%y-%m-%d') into v_cur_date from dual;

 

   WHILE v_i < 365 DO

    IF v_reg_date<=v_cur_date THEN 

    -- 注册用户 

    INSERT INTO cus_reg_pool(reg_date,android_reg,ios_reg,total_reg,mobile_reg,wx_reg,qq_reg,ding_reg,facebook_reg,trade_reg,apple_reg,created_time,update_time)

    SELECT

    v_reg_date as regDate,

IFNULL(SUM(CASE WHEN reg_from = 'android' THEN 1 ELSE '0' END ),0) as androidReg,

IFNULL(SUM(CASE WHEN reg_from = 'ios' THEN 1 ELSE '0' END ),0) as iosReg,

    count(1) as totalReg,

IFNULL(SUM(CASE WHEN reg_type = 'mobile' THEN 1 ELSE '0' END ),0) as mobileReg,

IFNULL(SUM(CASE WHEN reg_type ='wx' THEN 1 ELSE '0' END ),0) as wxReg,

IFNULL(SUM(CASE WHEN reg_type ='qq' THEN 1 ELSE '0' END ),0) as qqReg,

IFNULL(SUM(CASE WHEN reg_type ='ding' THEN 1 ELSE '0' END ),0) as dingReg,

IFNULL(SUM(CASE WHEN reg_type ='facebook' THEN 1 ELSE '0' END ),0) as facebookReg,

IFNULL(SUM(CASE WHEN reg_type = 'trade' THEN 1 ELSE '0' END ),0) as tradeReg,

IFNULL(SUM(CASE WHEN reg_type = 'apple' THEN 1 ELSE '0' END ),0) as appleReg,

    NOW(),

    NOW()

from policy.cus_customer t

where left(t.reg_time,10)=v_reg_date;

   

    -- 累计注册用户

    INSERT INTO cus_grand_pool(reg_date,android_reg,ios_reg,total_reg,created_time,update_time)

    SELECT

    v_reg_date,

IFNULL(SUM(CASE WHEN reg_from = 'android' THEN 1 ELSE '0' END ),0) as androidReg,

IFNULL(SUM(CASE WHEN reg_from = 'ios' THEN 1 ELSE '0' END ),0) as iosReg,

    count(1) as totalReg,

    NOW(),

    NOW()

from policy.cus_customer t

    where left(t.reg_time,10)<=v_reg_date;

 

    -- 注册用户日表

    INSERT INTO cus_day_report(login_date,total_login,android_login,ios_login,type,created_time,update_time)

    SELECT r.reg_date,r.total_reg,r.android_reg,r.ios_reg,1,now(),now() from cus_reg_pool r where r.reg_date=v_reg_date;

 

    -- 累计注册用户日表

    INSERT INTO cus_day_report(login_date,total_login,android_login,ios_login,type,created_time,update_time)

    SELECT g.reg_date,g.total_reg,g.android_reg,g.ios_reg,2,now(),now() from cus_grand_pool g where g.reg_date=v_reg_date;

   

    END IF;

    -- 日期加1 

    SELECT date_add(v_reg_date, interval 1 day) into v_next_date from dual;

    SET v_i = v_i+1;

    set v_reg_date=v_next_date;

   END WHILE;

 

   -- 获取最小的月份 

   SELECT MONTH(min(t.login_date)) into temp_mm from cus_day_report t where t.type=1;

   IF temp_mm = 7  THEN  

      set v_first_week='2019-07-01';

      set v_first_month='2019-07-01';

   ELSEIF temp_mm = 8  THEN  

      set v_first_week='2019-07-29';

      set v_first_month='2019-08-01'; 

   ELSEIF temp_mm = 9  THEN

      set v_first_week='2019-09-02';

      set v_first_month='2019-09-01';

ELSEIF temp_mm = 10  THEN

      set v_first_week='2019-09-30';

      set v_first_month='2019-10-01';

   ELSEIF temp_mm = 11  THEN

      set v_first_week='2019-10-28';

      set v_first_month='2019-11-01';

   ELSE  

      set v_first_week='2019-11-25';

      set v_first_month='2019-12-01';  

   END IF;

 

  -- 周表 

  WHILE v_j <60 DO

     -- 获取当前时间的周一

     select subdate(curdate(),date_format(curdate(),'%w')-1) into v_cur_week from dual;

     SELECT DATE_ADD(v_first_week, INTERVAL 1 WEEK) into v_next_week FROM dual;

     IF v_first_week<=v_cur_week THEN 

        -- 注册用户

INSERT INTO cus_week_report(login_date,total_login,android_login,ios_login,type,created_time,update_time)

select v_first_week,IFNULL(sum(t.total_login),0),IFNULL(sum(t.android_login),0),IFNULL(sum(t.ios_login),0),1,NOW(),NOW()

from cus_day_report t where t.login_date>=v_first_week and t.login_date<v_next_week and t.type=1;

 

-- 累计注册用户

        INSERT INTO cus_week_report(login_date,total_login,android_login,ios_login,type,created_time,update_time)

select v_first_week,t.total_login,t.android_login,t.ios_login,2,NOW(),NOW()

from cus_day_report t where t.login_date>=v_first_week and t.login_date<v_next_week and t.type=2 ORDER BY t.id DESC LIMIT 1;

 

END IF;

     SET v_j = v_j+1;

     SET v_first_week=v_next_week;

  END WHILE;

 

  -- 月表

  WHILE v_m < 14 DO

   -- 获取当前时间的月初第一天

   select date_add(curdate(), interval - day(curdate()) + 1 day) into v_cur_month from dual;

   SELECT DATE_ADD(v_first_month, INTERVAL 1 MONTH) into v_next_month FROM dual;

   IF v_first_month<=v_cur_month THEN 

      -- 注册用户

INSERT INTO cus_month_report(login_date,total_login,android_login,ios_login,type,created_time,update_time)

select v_first_month,IFNULL(sum(t.total_login),0),IFNULL(sum(t.android_login),0),IFNULL(sum(t.ios_login),0),1,NOW(),NOW()

from cus_day_report t where t.login_date>=v_first_month and t.login_date<v_next_month and t.type=1;

      

      -- 累计注册用户

      INSERT INTO cus_month_report(login_date,total_login,android_login,ios_login,type,created_time,update_time)

select v_first_month,t.total_login,t.android_login,t.ios_login,2,NOW(),NOW()

from cus_day_report t where t.login_date>=v_first_month and t.login_date<v_next_month and t.type=2 ORDER BY t.id DESC LIMIT 1;

END IF;

   SET v_m = v_m+1;

   SET v_first_month=v_next_month;

  END WHILE;

 

  -- 注册用户年表

  INSERT INTO cus_year_report(login_date,total_login,android_login,ios_login,type,created_time,update_time)

  select '2019-01-01',sum(t.total_login),sum(t.android_login),sum(t.ios_login),1,NOW(),NOW()

  from cus_month_report t where t.login_date>='2019-01-01' and t.login_date<'2020-01-01' and t.type=1;

    

  INSERT INTO cus_year_report(login_date,total_login,android_login,ios_login,type,created_time,update_time)

  select '2020-01-01',sum(t.total_login),sum(t.android_login),sum(t.ios_login),1,NOW(),NOW()

  from cus_month_report t where t.login_date>='2020-01-01' and t.type=1;

 

  -- 累计注册用户年表

  INSERT INTO cus_year_report(login_date,total_login,android_login,ios_login,type,created_time,update_time)

  select '2019-01-01',t.total_login,t.android_login,t.ios_login,2,NOW(),NOW()

  from cus_month_report t where t.login_date='2019-12-01' and t.type=2;

 

  INSERT INTO cus_year_report(login_date,total_login,android_login,ios_login,type,created_time,update_time)

  select '2020-01-01',t.total_login,t.android_login,t.ios_login,2,NOW(),NOW()

  from cus_month_report t where DATE_FORMAT(t.login_date,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') and t.type=2;

 

  -- 初始化其他类型周报

  INSERT INTO `cus_week_report`(login_date,total_login,android_login,ios_login,type,created_time,update_time)

  select subdate(curdate(),date_format(curdate(),'%w')-1),0,0,0,3,NOW(),NOW();

 

  INSERT INTO `cus_week_report`(login_date,total_login,android_login,ios_login,type,created_time,update_time)

  select subdate(curdate(),date_format(curdate(),'%w')-1),0,0,0,4,NOW(),NOW();

 

  INSERT INTO `cus_week_report`(login_date,total_login,android_login,ios_login,type,created_time,update_time)

  select subdate(curdate(),date_format(curdate(),'%w')-1),0,0,0,5,NOW(),NOW();

 

  -- 初始化其他类型月报

  INSERT INTO `cus_month_report`(login_date,total_login,android_login,ios_login,type,created_time,update_time)

  select date_add(curdate(), interval - day(curdate()) + 1 day),0,0,0,3,NOW(),NOW();

 

  INSERT INTO `cus_month_report`(login_date,total_login,android_login,ios_login,type,created_time,update_time)

  select date_add(curdate(), interval - day(curdate()) + 1 day),0,0,0,4,NOW(),NOW();

 

  INSERT INTO `cus_month_report`(login_date,total_login,android_login,ios_login,type,created_time,update_time)

  select date_add(curdate(), interval - day(curdate()) + 1 day),0,0,0,5,NOW(),NOW();

 

  -- 初始化其他类型年报

  INSERT INTO `cus_year_report`(login_date,total_login,android_login,ios_login,type,created_time,update_time)

  VALUES ('2020-01-01', '0', '0', '0', '3', '2020-05-01 00:00:01', '2020-05-01 23:00:05');

 

  INSERT INTO `cus_year_report`(login_date,total_login,android_login,ios_login,type,created_time,update_time)

  VALUES ('2020-01-01', '0', '0', '0', '4', '2020-05-01 00:00:01', '2020-05-01 23:00:05');

 

  INSERT INTO `cus_year_report`(login_date,total_login,android_login,ios_login,type,created_time,update_time)

  VALUES ('2020-01-01', '0', '0', '0', '5', '2020-05-01 00:00:01', '2020-05-01 23:00:05');

END;