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