Mysql 创建存储过程

表

CREATE TABLE `m_car` (

   `cid` bigint(30) unsigned NOT NULL AUTO_INCREMENT,
   `seats` int(10) NOT NULL,
   `price` int(10) NOT NULL,
   `color` varchar(200) NOT NULL,
   `brand` varchar(200) NOT NULL,
   `rent` varchar(20) NOT NULL,
   `cts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (`cid`),
   UNIQUE KEY `cid` (`cid`)

 ) ENGINE=InnoDB AUTO_INCREMENT=5938701 DEFAULT CHARSET=utf8

------------------------------------------------------FullCar-----------------------------------------------------------------

DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `FullCar`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `FullCar`( IN cut BIGINT )
BEGIN
DECLARE _size BIGINT ;
DECLARE _pk BIGINT ;
DECLARE _seats INT ;
DECLARE _price INT ;
DECLARE _rent VARCHAR(20) ;
DECLARE _color VARCHAR(20) ;
DECLARE _brand VARCHAR(20) ;
DECLARE _var INT DEFAULT 1;  
IF cut IS NOT NULL THEN
SET _size = cut ;
ELSE
SET _size = 10;
END IF;
SET _var = 0;
myloop:LOOP
IF _var >= _size THEN 
LEAVE myloop;
END IF;

SELECT (MAX(cid)  + 1) INTO _pk FROM m_car  ;
SELECT get_color( _pk % 7) INTO _color ;
SELECT get_brand(_pk % 4) INTO _brand ;
SET _seats = get_random(1);
SET _price = get_random(8);
SET _rent = CASE _pk % 2 WHEN 0 THEN 'FALSE' WHEN 1 THEN 'true' ELSE 'unknow' END;
INSERT INTO m_car (cid, seats , price , color , brand , rent ) VALUES ( _pk , _seats , _price , _color , _brand ,  _rent) ;
SET _var = _var + 1;
END LOOP myloop;
END$$
DELIMITER ;

-----------------------------------------------------------UpdateCar------------------------------------------------------------

DELIMITER $$

USE `test`$$
DROP PROCEDURE IF EXISTS `UpdateCar`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `UpdateCar`( IN cid_limit INT )
BEGIN
DECLARE _cid INT;
IF cid_limit IS NULL THEN
SET _cid = 0;
ELSE
SET _cid = cid_limit;
    END IF;
#select * from m_car where cid <= cid_limit;
UPDATE m_car 
SET price = get_random(7)
WHERE cid <= _cid;
    END$$
DELIMITER ;