存储过程 mysql

 存储过程循环

DELIMITER $$ -- 设置定界符
CREATE PROCEDURE test22() 
BEGIN
declare i int;
SET i=1;
WHILE i<5 DO
	SELECT i;
	set i = i + 1;
END WHILE;
END;$$

DELIMITER; -- 还原定界符

call test22()　

随机数

cast(floor(rand()*10) as int) 　

随机时间

SET @dt1 = '2010-01-01 10:20:11'; -- 开始日期
SET @dt2 = '2010-06-30 11:45:23'; -- 结束日期
 
set  @a = unix_timestamp(@dt1);  -- 1262312411
set  @b = unix_timestamp(@dt2)-@a; -- 362712312
SELECT from_unixtime(@a+floor(rand()*@b));　-- 2011-01-27 09:18:27

时间格式转换 

date_format('2010-06-30 11:45:23', '%Y%m%d')

判断

DROP PROCEDURE IF EXISTS test3;
DELIMITER $$
CREATE PROCEDURE test3(out m3 int)
BEGIN
declare i int;
set i = 5;
IF i = 3 THEN
   set m3 = 3;
ELSEIF i = 4 THEN
   set m3 = 4;
ELSE
	 set m3 = 4;
END IF;
END;$$
DELIMITER $$

call test3(@m3);

SELECT @m3;

返回值待参数

DROP PROCEDURE IF EXISTS test2;
DELIMITER $$
CREATE PROCEDURE test2(out m3 int)
BEGIN
declare m int;
declare start_tm VARCHAR(30);
declare end_tm VARCHAR(30);
SET start_tm = '2011-05-01'; -- 开始日期
SET end_tm = '2021-06-30';  -- 结束日期
SET m3 =  CAST(substring_index(substring_index(start_tm,'-',-2),'-',1) as INT);
END;$$
DELIMITER $$

call test2(@m3);

SELECT @m3;

给定一个起始日期，获取之后每个月份的月底日期

DROP PROCEDURE IF EXISTS test2;
DELIMITER $$
CREATE PROCEDURE test2(out m3 VARCHAR(15))
BEGIN
	declare m4 VARCHAR(15);
	declare y int;
	declare y2 int;
	declare m int;
	declare d int;
	declare i int;
	declare start_tm VARCHAR(30);
	declare end_tm VARCHAR(30);
	SET start_tm = '2016-10-11'; -- 开始日期
	SET end_tm = '2021-06-30';  -- 结束日期
	SET i = 1;
	WHILE i < 20 DO
		SET y = substring_index(start_tm,'-',1);
		SET m = CAST(substring_index(substring_index(start_tm,'-',-2),'-',1) as INT);
		SET d = substring_index(start_tm,'-',-1);
		IF m = 12 THEN
			set m3 = '01';
			set y2 = y + 1;
			set m3 = CONCAT(y2,'-',m3,'-','01');
		ELSEIF m < 9  THEN
			set m3 = m + 1;
			set m3 = CONCAT('0',m3);
			set m3 = CONCAT(substring_index(start_tm,'-',1),'-',m3,'-','01');
		ELSE
			set m3 = m + 1;
			set m3 = CONCAT(substring_index(start_tm,'-',1),'-',m3,'-','01');
		END IF;
		
-- 		SELECT m3;
		
		SET y = substring_index(m3,'-',1);
		SET m = CAST(substring_index(substring_index(m3,'-',-2),'-',1) as INT);
		SET d = substring_index(m3,'-',-1);
			IF m = 12 THEN
			set m4 = '01';
			set y2 = y + 1;
			set m4 = CONCAT(y2,'-',m4,'-','01');
		ELSEIF m < 9  THEN
			set m4 = m + 1;
			set m4 = CONCAT('0',m4);
			set m4 = CONCAT(substring_index(m3,'-',1),'-',m4,'-','01');
		ELSE
			set m4 = m + 1;
			set m4 = CONCAT(substring_index(m3,'-',1),'-',m4,'-','01');
		END IF;
-- 		SELECT m4;
	-- 	select DATEDIFF(m4,m3);
		set m3 = CONCAT(substring_index(m3,'-',2),'-',DATEDIFF(m4,m3));
		set i = i +1;
		set start_tm = m3;
		SELECT m3;
	END WHILE;
END;$$
DELIMITER $$

call test2(@m3);
SELECT @m3;

记录

DROP PROCEDURE IF EXISTS test2;
DELIMITER $$
CREATE PROCEDURE test2(IN start_tm VARCHAR(15),out m3 VARCHAR(15))
BEGIN
    declare m4 VARCHAR(15);
    declare y int;
    declare y2 int;
    declare m int;
    declare d int;
    declare i int;
--     declare start_tm VARCHAR(30);
    declare end_tm VARCHAR(30);
--     SET start_tm = '2016-10-11'; -- 开始日期
    SET end_tm = '2021-06-30';  -- 结束日期
		
		SET y = substring_index(start_tm,'-',1);
		SET m = CAST(substring_index(substring_index(start_tm,'-',-2),'-',1) as INT);
		SET d = substring_index(start_tm,'-',-1);
		IF m = 12 THEN
				set m3 = '01';
				set y2 = y + 1;
				set m3 = CONCAT(y2,'-',m3,'-','01');
		ELSEIF m < 9  THEN
				set m3 = m + 1;
				set m3 = CONCAT('0',m3);
				set m3 = CONCAT(substring_index(start_tm,'-',1),'-',m3,'-','01');
		ELSE
				set m3 = m + 1;
				set m3 = CONCAT(substring_index(start_tm,'-',1),'-',m3,'-','01');
		END IF;
		 
--      SELECT m3;
		 
		SET y = substring_index(m3,'-',1);
		SET m = CAST(substring_index(substring_index(m3,'-',-2),'-',1) as INT);
		SET d = substring_index(m3,'-',-1);
				IF m = 12 THEN
				set m4 = '01';
				set y2 = y + 1;
				set m4 = CONCAT(y2,'-',m4,'-','01');
		ELSEIF m < 9  THEN
				set m4 = m + 1;
				set m4 = CONCAT('0',m4);
				set m4 = CONCAT(substring_index(m3,'-',1),'-',m4,'-','01');
		ELSE
				set m4 = m + 1;
				set m4 = CONCAT(substring_index(m3,'-',1),'-',m4,'-','01');
		END IF;
--      SELECT m4;
--  select DATEDIFF(m4,m3);
		set m3 = CONCAT(substring_index(m3,'-',2),'-',DATEDIFF(m4,m3));

--         SELECT m3;

END;$$
DELIMITER;
 
-- call test2(,@m3);
-- SELECT @m3;

-- - 生成姓名---
DROP PROCEDURE IF EXISTS generateName;
DELIMITER $$
CREATE PROCEDURE generateName(out nam VARCHAR(5))
	BEGIN
		set nam = CONCAT(
        CASE cast(floor(rand()*20) as int)
                WHEN 0 THEN '陈'
        WHEN 1 THEN '张'
        WHEN 2 THEN '李'
        WHEN 3 THEN '刘'
        WHEN 4 THEN '赵'
                WHEN 5 THEN '钱'
        WHEN 6 THEN '孙'
        WHEN 7 THEN '李'
                WHEN 8 THEN '王'
        WHEN 9 THEN '章'
                WHEN 10 THEN '云'
        WHEN 11 THEN '苏'
        WHEN 12 THEN '潘'
        WHEN 13 THEN '柏'
        WHEN 14 THEN '雷'
                WHEN 15 THEN '吴'
        WHEN 16 THEN '郑'
        WHEN 17 THEN '吕'
                WHEN 18 THEN '水'
        WHEN 19 THEN '方'
        END
        ,CASE cast(floor(rand()*20) as int)
                WHEN 0 THEN '东'
        WHEN 1 THEN '佳'
        WHEN 2 THEN '慧'
        WHEN 3 THEN '丰'
        WHEN 4 THEN '顺'
                WHEN 5 THEN '奇'
        WHEN 6 THEN '魏'
        WHEN 7 THEN '力'
                WHEN 8 THEN '辉'
        WHEN 9 THEN '锋'
                WHEN 10 THEN '怡'
        WHEN 11 THEN '思'
        WHEN 12 THEN '一'
        WHEN 13 THEN '山'
        WHEN 14 THEN '宣'
                WHEN 15 THEN '世'
        WHEN 16 THEN '雅'
        WHEN 17 THEN '宜'
                WHEN 18 THEN '可'
        WHEN 19 THEN '柳'
        END
        ,CASE cast(floor(rand()*20) as int)
                WHEN 0 THEN '伟'
        WHEN 1 THEN '齐'
        WHEN 2 THEN '丽'
        WHEN 3 THEN '天'
        WHEN 4 THEN '红'
                WHEN 5 THEN '敦'
        WHEN 6 THEN '路'
        WHEN 7 THEN '中'
                WHEN 8 THEN '辉'
        WHEN 9 THEN '宇'
                WHEN 10 THEN '雨'
        WHEN 11 THEN '昆'
        WHEN 12 THEN '文'
        WHEN 13 THEN '梅'
        WHEN 14 THEN '平'
                WHEN 15 THEN '承'
        WHEN 16 THEN '翔'
        WHEN 17 THEN '采'
                WHEN 18 THEN '青'
        WHEN 19 THEN '燕'
        END);
	END;$$
DELIMITER ;

-- call generateName(@nam);
-- 
-- SELECT @nam;

DROP PROCEDURE IF EXISTS insertdata;
DELIMITER $$ -- 设置定界符
CREATE PROCEDURE insertdata()
BEGIN
DECLARE i int;
DECLARE count int;
DECLARE oldCount int;
DECLARE flage int;
DECLARE f1 int;
DECLARE name VARCHAR(255);
DECLARE oldName VARCHAR(255);
DECLARE manaCode VARCHAR(255);
DECLARE oldManaCode VARCHAR(255);
DECLARE boName VARCHAR(255);
DECLARE oldBoName VARCHAR(255);
DECLARE tel  VARCHAR(11);
DECLARE oldTel  VARCHAR(11);
DECLARE contrNo  VARCHAR(11);
DECLARE cCode  VARCHAR(11);
DECLARE oldCCode  VARCHAR(11);
DECLARE iouNo  VARCHAR(11);
DECLARE start_tm VARCHAR(50);
DECLARE tm2 VARCHAR(50);
DECLARE oldstart_tm VARCHAR(50);
DECLARE end_tm VARCHAR(50);
DECLARE calTm VARCHAR(50);
DECLARE oldCalTm VARCHAR(50);
DECLARE contrTm VARCHAR(50);
DECLARE oldContrTm VARCHAR(50);
DECLARE tm_a BIGINT;
DECLARE tm_b BIGINT;
DECLARE temp BIGINT;
DECLARE creAmt BIGINT;
DECLARE oldcreAmt BIGINT;
DECLARE loanAmt BIGINT;
DECLARE accumAmt BIGINT;
DECLARE rank VARCHAR(5);
DECLARE oldRank VARCHAR(5);
SET i=1;
SET start_tm = '2021-06-01'; -- 开始日期
SET end_tm = '2021-09-25'; -- 结束日期
SET flage = 0;
SET count = 0;
SET oldCount = 0;
SET f1 = 0;
WHILE i<=200 DO
		
		SET rank = CASE cast(floor(rand()*3) as int)
			WHEN 0 THEN 'A'
			WHEN 1 THEN 'B'
			WHEN 2 THEN 'C'
			WHEN 3 THEN 'Y'
    END;
		
		IF flage = 0 THEN
	    SET flage = cast(floor(rand()*100) as int);
			CALL test2(start_tm,@m3);
			SET start_tm = @m3;
    ELSE   
			SET flage = flage -1;
    END IF;
		
-- 		CALL test2(start_tm,@m3);
-- 		SET start_tm = @m3;
		
		SET tel = 15111111111+cast(floor(rand()*888888888) as VARCHAR(11));
		
		call generateName(@nam);
		SET name = @nam;
-- 		SET oldName = @nam;
		
				
		-- 授信金额
		SET creAmt = 10000;
		SET creAmt = creAmt + cast(floor(rand()*2000000) as int);
		
		-- 用信余额
		SET loanAmt = 10000 + cast(floor(rand()*(creAmt/20)) as int);
		
		
		-- 合同号 客户内码
		SET count = count + 1;
		IF count <= 9 THEN
			SET cCode = CONCAT('N10000',count);
			SET contrNo = CONCAT('A000',count);
			SET iouNo = CONCAT('J000',count);
		ELSEIF count <= 99 THEN
			SET cCode = CONCAT('N1000',count);
			SET contrNo = CONCAT('A00',count);
			SET iouNo = CONCAT('J00',count);
		ELSEIF count <= 999 THEN
			SET cCode = CONCAT('N100',count);
			SET contrNo = CONCAT('A0',count);
			SET iouNo = CONCAT('J0',count);
		ELSE
			SET cCode = CONCAT('N10',count);
			SET contrNo = CONCAT('A',count);
			SET iouNo = CONCAT('J',count);
		END IF;
		
		-- 月积数
		SET accumAmt = 10000 + cast(floor(rand()*(5000000)) as int);
		
		-- 客户经理id
		SET manaCode = CASE cast(floor(rand()*3) as int)
			WHEN 0 THEN 'A8001001'
			WHEN 1 THEN 'A8001002'
			WHEN 2 THEN 'A8001003'
    END;
		
		-- 支行名称
		SET boName = CASE cast(floor(rand()*3) as int)
			WHEN 0 THEN '西湖支行'
			WHEN 1 THEN '吴山支行'
			WHEN 2 THEN '钱江世纪城支行'
    END;
		
		
		-- 外呼时间 随机
		SET  tm_a = unix_timestamp('2021-05-23');
		SET  tm_b = unix_timestamp('2021-05-25')-tm_a;
		SET  calTm = date_format(from_unixtime(tm_a+floor(rand()*tm_b)), '%Y-%m-%d');
		
		-- 合同办理时间 假设 为下发时间 后一个月内的随机时间
		SET temp = unix_timestamp('2021-02-01') - unix_timestamp('2021-01-01');
		SET contrTm = date_format(from_unixtime(unix_timestamp(calTm)+floor(rand()*temp)), '%Y-%m-%d');

		
		SET temp = cast(floor(rand()*10) as int);
		IF ( (temp = 1 || temp = 3 || temp = 4 || temp = 5 || temp = 6 || temp = 7 || temp = 8) && count != 1 && f1 != 1) THEN
		-- 合同号一样的情况 姓名相同 客户内码相同 客户经理相同 银行代码相同 借据号不同 用信余额
		-- 人相同 合同号一样的情况
				IF oldCount <= 9 THEN
					SET contrNo = CONCAT('A000',oldCount);
					SET cCode = CONCAT('N10000',oldCount);
				ELSEIF oldCount <= 99 THEN
					SET contrNo = CONCAT('A00',oldCount);
					SET cCode = CONCAT('N1000',oldCount);
				ELSEIF oldCount <= 999 THEN
					SET contrNo = CONCAT('A0',oldCount);
					SET cCode = CONCAT('N100',oldCount);
				ELSE
					SET contrNo = CONCAT('A',oldCount);
					SET cCode = CONCAT('N10',oldCount);
				END IF;
				SET name = oldName; 
				SET manaCode = oldManaCode; 
				SET tel= oldTel; 
				SET start_tm = oldstart_tm; 
				SET calTm = oldCalTm;  
				SET rank = oldRank; 
				SET contrTm = oldContrTm; 
				SET creAmt = oldcreAmt; 
				SET boName = oldBoName;
				SET f1 = 0;
		-- 人相同 合同号不一样的情况		
		ELSEIF temp = 2 THEN
		-- 借据号为空的情况
				SET iouNo = '';
				SET loanAmt = 0;
				SET accumAmt = 0;
				SET oldCount = count;
				SET oldName = name;
				SET oldManaCode = manaCode;
				SET oldTel = tel;
				SET oldstart_tm = start_tm;
				SET oldCalTm = calTm;
				SET oldContrTm = contrTm;
				SET oldcreAmt = creAmt;
				SET oldRank= rank;
				SET oldBoName = boName;
				SET f1 = 1;
		ELSE	
				SET oldCount = count;
				SET oldName = name;
				SET oldManaCode = manaCode;
				SET oldTel = tel;
				SET oldstart_tm = start_tm;
				SET oldCalTm = calTm;
				SET oldContrTm = contrTm;
				SET oldcreAmt = creAmt;
				SET oldRank= rank;
				SET oldBoName = boName;
				SET f1 = 0;
		END IF;
		
		
    INSERT INTO bnk_stat
    (
    name,
    stat_tm,
		tel,
    of_mana,
    of_bo,
		c_code,
		contr_no,
		cal_tm,
		rls_tm,
		contr_tm,
		cre_amt,
		iou_no,
		loan_amt,
		accum_amt,
		intention
    )
    VALUES
    (
		name,
		start_tm,
		tel,
		manaCode,
		boName,
		cCode,
		contrNo,
		calTm,
		calTm,
		contrTm,
		creAmt,
		iouNo,
		loanAmt,
		accumAmt,
		rank
    );
    set i = i + 1;
END WHILE;
END;$$
 
DELIMITER; -- 还原定界符
 
call insertdata()
 
-- ----- try -----------------
 
-- DROP PROCEDURE IF EXISTS test2;
-- DELIMITER $$
-- CREATE PROCEDURE test2(out m3 VARCHAR(15))
-- BEGIN
--     declare m4 VARCHAR(15);
--     declare y int;
--     declare y2 int;
--     declare m int;
--     declare d int;
--     declare i int;
--     declare start_tm VARCHAR(30);
--     declare end_tm VARCHAR(30);
--     SET start_tm = '2016-10-11'; -- 开始日期
--     SET end_tm = '2021-06-30';  -- 结束日期
--     SET i = 1;
--     WHILE i < 20 DO
--         SET y = substring_index(start_tm,'-',1);
--         SET m = CAST(substring_index(substring_index(start_tm,'-',-2),'-',1) as INT);
--         SET d = substring_index(start_tm,'-',-1);
--         IF m = 12 THEN
--             set m3 = '01';
--             set y2 = y + 1;
--             set m3 = CONCAT(y2,'-',m3,'-','01');
--         ELSEIF m < 9  THEN
--             set m3 = m + 1;
--             set m3 = CONCAT('0',m3);
--             set m3 = CONCAT(substring_index(start_tm,'-',1),'-',m3,'-','01');
--         ELSE
--             set m3 = m + 1;
--             set m3 = CONCAT(substring_index(start_tm,'-',1),'-',m3,'-','01');
--         END IF;
--          
-- --      SELECT m3;
--          
--         SET y = substring_index(m3,'-',1);
--         SET m = CAST(substring_index(substring_index(m3,'-',-2),'-',1) as INT);
--         SET d = substring_index(m3,'-',-1);
--             IF m = 12 THEN
--             set m4 = '01';
--             set y2 = y + 1;
--             set m4 = CONCAT(y2,'-',m4,'-','01');
--         ELSEIF m < 9  THEN
--             set m4 = m + 1;
--             set m4 = CONCAT('0',m4);
--             set m4 = CONCAT(substring_index(m3,'-',1),'-',m4,'-','01');
--         ELSE
--             set m4 = m + 1;
--             set m4 = CONCAT(substring_index(m3,'-',1),'-',m4,'-','01');
--         END IF;
-- --      SELECT m4;
--     --  select DATEDIFF(m4,m3);
--         set m3 = CONCAT(substring_index(m3,'-',2),'-',DATEDIFF(m4,m3));
--         set i = i +1;
--         set start_tm = m3;
--         SELECT m3;
--     END WHILE;
-- END;$$
-- DELIMITER $$
--  
-- call test2(@m3);
-- SELECT @m3;
 
-- -------------------------------------------------------------------------------------------
-- DROP PROCEDURE IF EXISTS test3;
-- DELIMITER $$
-- CREATE PROCEDURE test3(out m3 int)
-- BEGIN
-- declare i int;
-- set i = 5;
-- IF i = 3 THEN
--    set m3 = 3;
-- ELSEIF i = 4 THEN
--    set m3 = 4;
-- ELSE
--      set m3 = 4;
-- END IF;
-- END;$$
-- DELIMITER $$
--  
-- call test3(@m3);
--  
-- SELECT @m3;
 
-- set  @a = unix_timestamp(@dt1);
-- SELECT @a;
-- set  @b = unix_timestamp(@dt2)-@a;
-- SELECT @b;
-- SELECT from_unixtime(@a+floor(rand()*@b));
-- SELECT date_format(from_unixtime(@a+floor(rand()*@b)), '%Y%m%d');