mysql存储过程 cast_mysql 存储过程参考 - 木鱼若愚 - 51Testing软件测试网 51Testing软件测试网-软件测试人的精神家园...

DELETE FROM jyj_zqctest_v1;

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `zqc_test2`$$

CREATE DEFINER=`root`@`%` PROCEDURE `zqc_test2`()

BEGIN

DECLARE i INT DEFAULT 1 ;

WHILE

i <= 5 DO SET @firstname = SUBSTRING(

'赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮卞齐康伍余元卜顾孟平黄和穆萧尹姚邵湛汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞熊纪舒屈项祝董梁杜阮蓝闵席季麻强贾路娄危江童颜郭梅盛林刁钟徐邱骆高夏蔡田樊胡凌霍虞万支柯昝管卢莫柯房裘缪干解应宗丁宣贲邓郁单杭洪包诸左石崔吉钮龚程嵇邢滑裴陆荣翁荀羊于惠甄曲家封芮羿储靳汲邴糜松井段富巫乌焦巴弓牧隗山谷车侯宓蓬全郗班仰秋仲伊宫甯仇栾暴甘钭历戎祖武符刘景詹束龙叶幸司韶郜黎蓟溥印宿白怀蒲邰从鄂索咸籍赖卓蔺屠蒙池乔阳郁胥能苍双闻莘党翟谭贡劳逄姬申扶堵冉宰郦雍却璩桑桂濮牛寿通边扈燕冀浦尚农温别庄晏柴瞿阎充慕连茹习宦艾鱼容向古易慎戈廖庾终暨居衡步都耿满弘匡国文寇广禄阙东欧殳沃利蔚越夔隆师巩厍聂晁勾敖融冷訾辛阚那简饶空曾毋沙乜养鞠须丰巢关蒯相查后荆红游竺权逮盍益桓公万俟司马上官欧阳夏侯诸葛闻人东方赫连皇甫尉迟公羊澹台公冶宗政濮阳淳于单于太叔申屠公孙仲孙轩辕令狐徐离宇文长孙慕容司徒司空',

FLOOR(1+25 * RAND()),

1

) ;

SET @XGZL=LOAD_FILE('D:/RTCK/临时/09/testfile/a.jpg');

SET @name1 = SUBSTRING(

'一二三四五六七八九十元萱碧容乐薇采晴晓',

ROUND(1+47 * RAND()),

1

) ;

SET @CSRQ=CONCAT(FLOOR(1928 + (RAND() * 90)),LPAD(FLOOR(1 + (RAND() * 12)),2,0),LPAD(FLOOR(3 + (RAND() * 8)),2,0));

SET @namefull = CONCAT(@firstname, @name1) ;

SET @sex='02,1,4,9';

SET @type='个人,企业';

SET @type1=SUBSTRING_INDEX(SUBSTRING_INDEX(@type,',',(RAND()*10)%2+1),',',-1);

SET @sfz=CONCAT(452128,FLOOR(1990 + (RAND() * 25)),LPAD(FLOOR(1 + (RAND() * 12)),2,0),LPAD(FLOOR(3 + (RAND() * 8)),2,0),

RIGHT(CONCAT('0000',CAST(FLOOR((RAND() * 9999)) AS CHAR)),4));

SET @xiaoshu=CONVERT(RAND()*10000000,DECIMAL(10,3));

SET @zhengshu=RIGHT(CONCAT('0000',CAST(FLOOR((RAND() * 9999)) AS CHAR)),4);

SET @time=CONCAT(FLOOR(2000 + (RAND() * 18)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(3 +

(RAND() * 8)),2,0) ,' ',FLOOR(10+RAND()*10),':',FLOOR(10+RAND()*49),':',

FLOOR(10+RAND()*49));

SET @cishu=CAST(FLOOR((RAND() * 9)) AS CHAR);

SET @zhongwen= SUBSTRING(

'

你好',

FLOOR(1+25 * RAND()),

8

) ;

SET @tel=CONCAT('13','0',CAST(FLOOR((RAND() * 99999999)) AS CHAR));

SET @danwei='公安局,教育局';

SET @danwei1=SUBSTRING_INDEX(SUBSTRING_INDEX(@danwei,',',(RAND()*10)%2+1),',',-1);

SET  @ty_name=@namefull;

IF @type1 ='个人'

THEN

INSERT INTO `jyj_zqctest_v1` (`code`,`name`,`sex`,`type`,`ty_name`

)

VALUES

(

i,@sfz,SUBSTRING_INDEX(SUBSTRING_INDEX(@sex,',',(RAND()*10)%2+1),',',-1),@type1,@ty_name

)

;

ELSE

INSERT INTO `jyj_zqctest_v1` (`code`,`name`,`sex`,`type`,`ty_name`

)

VALUES

(

i,@sfz,SUBSTRING_INDEX(SUBSTRING_INDEX(@sex,',',(RAND()*10)%2+1),',',-1),@type1,@danwei1

)

;

END IF;

SET i = i + 1 ;

END WHILE ;

END$$

DELIMITER ;

CALL zqc_test2();

SELECT * FROM `jyj_zqctest_v1`;