存储过程的例子

深碍√TFBOYSˉ_ 2022-05-17 09:30 283阅读 0赞

**举例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 ;