MYSQL 存储过程、函数、临时表、游标

创建函数

因为我们平时经常需要创建不同日期的数据，以模拟的场景，覆盖更多的用例，所以这里写了一个返回随机日期的demo。大家可以自行扩展。

DROP FUNCTION IF EXISTS milan_get_rand_time;
CREATE FUNCTION milan_get_rand_time(type VARCHAR(50)) RETURNS VARCHAR(20)
BEGIN
DECLARE _day INT DEFAULT (FLOOR(RAND() * 60)); -- 随机天数 60天以内随机天数
DECLARE _hour INT DEFAULT (FLOOR(RAND() * 24)); -- 随机小时
DECLARE _minute INT DEFAULT (FLOOR(RAND() * 60));-- 随机分
DECLARE _second INT DEFAULT (FLOOR(RAND() * 60));-- 随机秒
DECLARE _myday VARCHAR(20);
IF type = 'before' THEN
        set _day =_day;
ELSEIF type='after' THEN
        SET _day =-1*_day;
ELSE
        SET _day=0;
END IF;

SET _myday = DATE_FORMAT(DATE_SUB(NOW(),INTERVAL _day DAY) ,'%Y-%m-%d'); -- 获取一个随机的日期
-- SET _myday = DATE_SUB(CURRENT_DATE(),INTERVAL _day DAY); -- 获取一个随机的日期
SET _myday = CONCAT(_myday,' ',LPAD(_hour,2,0),':',LPAD(_minute,2,0),':',LPAD(_second,2,0)); -- 在这个日期上加上时分秒
   RETURN _myday;
END

调用

SELECT milan_get_rand_time('after');  -- 返回 2018-03-02 22:07:14
SELECT milan_get_rand_time('before'); -- 2018-02-02 03:17:10

-- 补位函数
-- 向前补位
select LPAD(1,3,0); -- 001
-- 向后补位
select RPAD(1,3,0); -- 100
 

 创建存储过程

每次获取一条随机数据，插入到表中。用select。。。into。语句。因为milan_table的game_id是主键，为了防止重复写入报错，所以用了ignore忽略错误。

DROP PROCEDURE IF EXISTS milan_test;
CREATE PROCEDURE milan_test(in add_count int,OUT data_count INT) --  in 入参  out 出参
BEGIN
DECLARE _game_id BIGINT;
DECLARE _game_name VARCHAR(50);
-- DELETE FROM milan_table;
WHILE add_count > 0 DO
    SELECT game_id,game_name INTO _game_id,_game_name from t_game ORDER BY RAND() LIMIT 1;
    INSERT IGNORE INTO milan_table VALUES (_game_id,_game_name,milan_get_rand_time('before'));
  SET add_count=add_count-1;
END WHILE;
    SET data_count =(select count(*) from milan_table);
  SELECT * from milan_table;
end

-- 调用：
SET @count =NULL;
CALL milan_test(5,@count);
SELECT @count;

根据游标写入：

DROP PROCEDURE if EXISTS curtest;
CREATE PROCEDURE curtest()
BEGIN
DECLARE _uid INT;
DECLARE _account VARCHAR(20);
DECLARE done INT DEFAULT FALSE;
 -- 遍历数据结束标志
DECLARE cur CURSOR FOR SELECT uid,account FROM tmm_tmp_testaccount ;
  -- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO _uid,_account;
   IF done THEN
      LEAVE read_loop;
    END IF;
    UPDATE tmm_tmp_testaccount set `desc` = NULL where uid =_uid;
  END LOOP;
  CLOSE cur;
END

跳出存储过程，相当于return语句：
label:BEGIN
DECLARE _uid int DEFAULT (select uid from tmm_tmp_testaccount where flag =_flag limit 1);
IF  ISNULL(_uid)  THEN
 leave label;
END IF;
END

临时表

DROP PROCEDURE if EXISTS temp_table;
CREATE PROCEDURE temp_table()
BEGIN
DROP TABLE if EXISTS temp_table;
CREATE TEMPORARY TABLE temp_table(
 uid INT NULL,
 n VARCHAR(10) NULL
);
INSERT INTO temp_table  (uid,n) VALUES ('1','milan1');
INSERT INTO temp_table  (uid,n) VALUES ('2','milan2');
select * from temp_table;
END