存储过程的例子

举例1：
 

 创建存储过程：
   DELIMITER $$
    CREATE
        PROCEDURE `zyh`.`if_else_`(
           IN num INT(11),
           OUT total VARCHAR(225)   
        )     
        BEGIN
           DECLARE num_ INT;
           SELECT COUNT(*) INTO num_ FROM goods WHERE pre_price > num;
           IF num_ > 1000 THEN 
             SET total = '大于1000条';
           ELSEIF (num_ > 1 AND num_ < 10) THEN 
             SET total = '大于1条 小于 10 条';
           ELSEIF (num_> 10 AND num_ < 1000) THEN 
             SET total ='大于10条小于 1000'; 
           END IF;      
         END$$
    DELIMITER ;
    
    调用存储过程：
       CALL if_else_(10,@total);
        SELECT @total;

 

举例2： 游标在存储过程中的使用：

   创建存储过程：
   

DELIMITER $$
CREATE  PROCEDURE `zyh`.`you_biao`()
    
    BEGIN
    DECLARE str VARCHAR(225) DEFAULT '';
    DECLARE finished INT DEFAULT 0;
    DECLARE v VARCHAR(20);
    DECLARE bonus_value CURSOR  FOR SELECT `value` FROM bonus;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
    OPEN bonus_value;
    get_value : LOOP
       FETCH bonus_value INTO v;
       IF finished = 1 THEN 
          LEAVE get_value;
       END IF;
       IF str != '' THEN   
          SET str= CONCAT(str,';',v);  
       ELSE  
          SET str = v;
       END IF;       
    END LOOP get_value;
    CLOSE  bonus_value;
    SELECT str;     
        
    END$$

DELIMITER ;  

调用存储过程： 
  call you_biao();

举例3：存储过程的时间的比较

DELIMITER $$

CREATE
    
    PROCEDURE `zyh`.`refreshGoods`()
    BEGIN
      DECLARE str VARCHAR(225) DEFAULT '';
      DECLARE finished INT DEFAULT FALSE;
      DECLARE good_id VARCHAR(225) DEFAULT '';
      DECLARE refresh_start_Time VARCHAR(225) DEFAULT '';
      DECLARE refresd_id VARCHAR(225) DEFAULT '';
      DECLARE refresh_end_Time VARCHAR(225) DEFAULT '';
      DECLARE goodsid VARCHAR(225) DEFAULT '';
      DECLARE isnotor INT(10);
      DECLARE nowtime VARCHAR(225) DEFAULT (SELECT  CONCAT(EXTRACT(HOUR FROM NOW()),':',EXTRACT(MINUTE FROM NOW())));
      DECLARE refreshPoint CURSOR  FOR SELECT rp.start_time,rp.refresh_goods_id,rp.end_time FROM refresh_point rp,refresh_goods rg WHERE rg.id = rp.refresh_goods_id AND rg.status=1;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = TRUE;
      OPEN  refreshPoint;
      get_refreshPoint : LOOP
         FETCH refreshPoint INTO refresh_start_Time,refresd_id,refresh_end_Time;
         IF  finished  THEN 
           LEAVE get_refreshPoint;
         END IF;
         IF (refresh_start_Time < nowtime && nowtime < refresh_end_Time)  THEN 
           SELECT rg.goods_id  INTO goodsid  FROM  refresh_goods rg WHERE rg.id = refresd_id;    

    SET good_id = goodsid ; 
    UPDATE goods SET datecreated = NOW() WHERE id=good_id;
    INSERT INTO refresh_history(id,goods_refresh_id,start_time,end_time,datecreated) VALUES(
    ROUND(ROUND(RAND(),5)*10000),refresd_id,refresh_start_Time,refresh_end_Time,NOW());
           
     END IF;
     END LOOP  get_refreshPoint;
     CLOSE refreshPoint;
    END$$

DELIMITER ;

4 存储过程的时间的比较：

DELIMITER $$

CREATE
     PROCEDURE `zyh`.`zhiding_expirydate`()
    BEGIN
    DECLARE str VARCHAR(225) DEFAULT '';
    DECLARE finished INT DEFAULT FALSE;
    DECLARE topgoodsid VARCHAR(50) DEFAULT '';
      DECLARE my_cursor CURSOR FOR(SELECT tg.expiry_date AS expiry_date,tg.id AS id FROM top_goods tg WHERE  tg.status !=2);
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = TRUE;
      OPEN my_cursor;
         get_cursor : LOOP 
            FETCH my_cursor INTO  str,topgoodsid;
            IF finished THEN
          LEAVE get_cursor    ;
        END IF;
        IF(NOW() > str) THEN 
          UPDATE top_goods t SET t.status=2 WHERE t.id= topgoodsid;
        END IF ; 
        END LOOP get_cursor;
        CLOSE my_cursor;
                    
    END$$

DELIMITER ;