Mysql批量数据脚本（十三）

函数：有返回值

public String getUserInfo(){

return user.toString();

}

存储过程：没有返回值

public void getUserInfo2(){

}

1建表：

#新建数据库
create database bigData;
use bigData;


#新建dept
CREATE TABLE dept(
id INT UNSIGNED PRIMARY KEY auto_increment,
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT null DEFAULT "",
loc  VARCHAR(13)   NOT null DEFAULT ""
)ENGINE=INNODB DEFAULT CHARSET=GBK;


#新建emp
create table emp(
id INT UNSIGNED PRIMARY key auto_increment,
empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/
ename VARCHAR(20) not NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT null DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED not null DEFAULT 0,/*上级编号*/
hiredate Date NOT NULL,/*入职时间*/
sal DECIMAL(7,2) NOT NULL,/*薪水*/
comm DECIMAL(7,2) not null,/*红利*/
deptno MEDIUMINT UNSIGNED not null DEFAULT 0/*部门编号*/

)ENGINE=INNODB DEFAULT CHARSET=GBK;

2.设置参数log_bin_trust_function_creators

3.创建函数，保证每条数据不同

   3.1随机产生字符串

use bigData;
delimiter $$
CREATE FUNCTION rand_string(n INT) RETURN VARCHARS(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;
END $$

   3.2随机产生部门编号

delimiter $$
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
  DECLARE i INT DEFAULT 0;
  SET i=FLOOR(100+RAND()*10);
RETURN i;
END $$

4创建存储过程

  4.1往emp表中插入数据的存储过程

delimiter $$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;

#SET autocommit=0;把自动提交设置为0
SET autocommit=0;
REPEAT
SET i=i+1;
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES((START+i),rand_string(6),'SALESMAN','0001',CURDATE(),2000,400,rand_num());
until i=max_num
end REPEAT;
commit;
END $$

  4.2往dept表中插入数据的存储过程

delimiter $$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit=0;
REPEAT
SET i=i+1;
INSERT INTO dept(deptno,dname,loc) VALUES((START+i),rand_string(10),rand_string(8));
until i=max_num
end REPEAT;
commit;
END $$

5调用存储过程

5.1dept表插入10条记录

delimiter;
CALL insert_dept(100,10);
select * from dept

5.2emp表插入30完条记录

CALL insert_emp(100001,300000);
select * from emp;