CREATE DEFINER = `root`@`localhost` FUNCTION rand_string (n int)
 RETURNS varchar(255)
BEGIN
declare  chars_str varchar(100) default ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ‘;
declare  return_str varchar(255) default "";
declare i int default 0;
while i<n do
  set return_str=concat(return_str,substring(chars_str,floor(1+rand()*52),1));
    set i=i+1;
end while;
return return_str;
END;

CREATE DEFINER = `root`@`localhost` FUNCTION `rand_loc`()
 RETURNS varchar(10)
BEGIN
declare return_str varchar(10) default ‘Shanghai‘;
declare n int default 0;
set n=floor(rand()*10);
case n
    when 0 then set return_str=‘Shanghai‘;
    WHEN 1 THEN SET return_str = ‘Beijing‘;
  WHEN 2 THEN SET return_str = ‘Shenzhen‘;
  WHEN 3 THEN SET return_str = ‘Guangzhou‘;
  WHEN 4 THEN SET return_str = ‘Tianjin‘;
  WHEN 5 THEN SET return_str = ‘Chongqing‘;
  WHEN 6 THEN SET return_str = ‘Chengdu‘;
  WHEN 7 THEN SET return_str = ‘Wuhan‘;
  WHEN 8 THEN SET return_str = ‘Changcha‘;
  WHEN 9 THEN SET return_str = ‘Shenyang‘;
  ELSE SET return_str = ‘Dalian‘;
 end CASE;
 
  return return_str;
end;

CREATE DEFINER = `root`@`localhost` PROCEDURE insert_dept(IN start int(10),IN max int(10))
BEGIN
declare i int default 0;
set autocommit=0;
REPEAT 
  set i=i+1;
  insert into dept_innodb values((start+i),rand_string(6),rand_loc());
until i=max
end repeat;
commit;
END;