function、procedure
#新建库
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;
#开启过慢查询日志
show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;
#创建函数rand_string--用于随机产生员工姓名或者部门名
Delimiter $$
Create 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 $$
#删除函数
#drop function rand_string;
#创建函数rand_num--用于随机产生员工对应的部门编号
delimiter $$
create function rand_num()
returns int(5)
begin
declare i int default 0;
set i = floor(100+rand()*10);
return i;
end $$
#创建向emp表插入数据的存储过程insert_emp
delimiter $$
create procedure insert_emp(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 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 $$
#创建向dept表插入数据的存储过程insert_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 $$
#调用存储过程insert_dept,100表示部门编号从100开始，10表示插入10条数据
call insert_dept(100,1000000);
存储过程示例：
DELIMITER &&
CREATE PROCEDURE pro_user()
BEGIN
DECLARE a,b VARCHAR(20) ;
INSERT INTO t_user VALUES(NULL,a,b);
END
&&
DELIMITER ;
DELIMITER &&
CREATE PROCEDURE pro_user2()
BEGIN
DECLARE a,b VARCHAR(20) ;
SET a='java1234',b='123456';
INSERT INTO t_user VALUES(NULL,a,b);
END
&&
DELIMITER ;
DELIMITER &&
CREATE PROCEDURE pro_user3()
BEGIN
DECLARE a,b VARCHAR(20) ;
SELECT userName2,password2 INTO a,b FROM t_user2 WHERE id2=1;
INSERT INTO t_user VALUES(NULL,a,b);
END
&&
DELIMITER ;
DELIMITER &&
CREATE PROCEDURE pro_user4()
BEGIN
DECLARE a,b VARCHAR(20) ;
DECLARE cur_t_user2 CURSOR FOR SELECT userName2,password2 FROM t_user2;
OPEN cur_t_user2;
FETCH cur_t_user2 INTO a,b;
INSERT INTO t_user VALUES(NULL,a,b);
CLOSE cur_t_user2;
END
&&
DELIMITER ;
DELIMITER &&
CREATE PROCEDURE pro_user5(IN bookId INT)
BEGIN
SELECT COUNT(*) INTO @num FROM t_user WHERE id=bookId;
IF @num>0 THEN UPDATE t_user SET userName='java12345' WHERE id=bookId;
ELSE
INSERT INTO t_user VALUES(NULL,'2312312','2321312');
END IF ;
END
&&
DELIMITER ;
DELIMITER &&
CREATE PROCEDURE pro_user6(IN bookId INT)
BEGIN
SELECT COUNT(*) INTO @num FROM t_user WHERE id=bookId;
CASE @num
WHEN 1 THEN UPDATE t_user SET userName='java12345' WHERE id=bookId;
WHEN 2 THEN INSERT INTO t_user VALUES(NULL,'2312312','2321312');
ELSE INSERT INTO t_user VALUES(NULL,'231231221321312','2321312321312');
END CASE ;
END
&&
DELIMITER ;
DELIMITER &&
CREATE PROCEDURE pro_user7(IN totalNum INT)
BEGIN
aaa:LOOP
SET totalNum=totalNum-1;
IF totalNum=0 THEN LEAVE aaa ;
ELSE INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
END IF ;
END LOOP aaa ;
END
&&
DELIMITER ;
DELIMITER &&
CREATE PROCEDURE pro_user8(IN totalNum INT)
BEGIN
aaa:LOOP
SET totalNum=totalNum-1;
IF totalNum=0 THEN LEAVE aaa ;
ELSEIF totalNum=3 THEN ITERATE aaa ;
END IF ;
INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
END LOOP aaa ;
END
&&
DELIMITER ;
DELIMITER &&
CREATE PROCEDURE pro_user9(IN totalNum INT)
BEGIN
REPEAT
SET totalNum=totalNum-1;
INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
UNTIL totalNum=1
END REPEAT;
END
&&
DELIMITER ;
DELIMITER &&
CREATE PROCEDURE pro_user10(IN totalNum INT)
BEGIN
WHILE totalNum>0 DO
INSERT INTO t_user VALUES(totalNum,'2312312','2321312');
SET totalNum=totalNum-1;
END WHILE ;
END
&&
DELIMITER ;
CALL pro_user();
CALL pro_user2();
CALL pro_user3();
CALL pro_user4();
CALL pro_user5(5);
CALL pro_user6(6);
CALL pro_user7(11);
CALL pro_user8(11);
CALL pro_user9(11);
CALL pro_user10(10);
DELETE FROM t_user;
