# 部门表 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 "" );
# 员工表 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 );
2. 设置参数:
创建函数的时候,可能会报错:
this function has none of deterministic……
我们得开启一个参数,首先执行如下语句可以查看该参数:
show variables like 'log_bin_trust_function_creators';
delimiter $$ create function rand_string(n int) returns varchar(255) begin declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyz'; 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 $$
delimiter $$ create function rand_num() returns int(5) begin declare i int default 0; set i = floor(100 + rand() * 10); return i; end $$
假如要删除rand_num函数,那么就是执行:
drop function rand_num;
4. 创建存储过程:
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, 4000, rand_num()); until i = max_num end repeat; commit; end $$
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 $$