[大数据]数据库使用函数和存储批量添加数据

#sql 生成n位数的随机字符串函数
delimiter $$
create function randString( n int) returns varchar(255)
begin
declare stringArray varchar(60) default 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
declare resString varchar(255) default '';
declare i int default 0;
while i<n do 
set resString = concat(resString,substring(stringArray,floor(rand()*52+1),1));
set i=i+1;
end while;
return resString;
end $$


#sql 生成100~109的随机部门编号
delimiter $$
create function randNum() returns int(5)
begin
declare resNum int(5) default '100';
set resNum=floor(100+rand()*10);
return resNum;
end $$



#sql 创建存储过程，往tbl_dept表中加数据
delimiter $$
create procedure insert_dept(IN startNum int(10), IN amount int (10))
begin
declare i int(10) default 0;
set autocommit = 0;
while i<=amount do
insert into tbl_dept(deptno,dname,loc) values ((startNum+i),randString(6),cast((300+i) as char));
set i = i+1;
end while;
commit;
end $$


#sql 创建存储过程，往tbl_emp中加数据
DELIMITER $$
CREATE PROCEDURE insert_emp(IN startNum INT(10) ,IN amount INT(10))
BEGIN
DECLARE i INT(10) DEFAULT 0;
DECLARE rand_job VARCHAR(50) DEFAULT '';
DECLARE rand_num INT(10) DEFAULT 0;
SET autocommit=0;
WHILE i<amount DO
SET rand_num=FLOOR (RAND()*5);
set rand_job= CASE WHEN rand_num=0 THEN 'saleMan'
       		WHEN rand_num=1 THEN 'manager'
        		WHEN rand_num=2 THEN 'salesManger'
        		WHEN rand_num=3 THEN 'technician'
       		WHEN rand_num=4 THEN 'king'
END;
INSERT INTO tbl_emp (empno,ename,job,mgr,hiredata,sal,comm,deptno) VALUES (i,randString(3),rand_job,FLOOR(RAND()*amount),CURDATE(),FLOOR(RAND()*5000+3000),FLOOR(RAND()*1000),randNum());
SET i=i+1;
END WHILE;
COMMIT;
END$$