create procedure dept_or_emp() begin begin declare e_n int default 0; declare d_n int default 0; select count(*) into e_n from emp; select count(*) into d_n from dept; select e_n,d_n; end; begin declare max_s double default 0; declare min_s double default 0; select max(salary) into max_s from emp; select min(salary) into min_s from emp; select max_s,min_s; end; end;
create procedure dept_or_emp1() begin declare e_n int default 0; declare d_n int default 0; declare max_s double default 0; declare min_s double default 0; begin select count(*) into e_n from emp; select count(*) into d_n from dept; end; begin
select max(salary) into max_s from emp; select min(salary) into min_s from emp; end; select e_n,d_n,max_s,min_s; end;
create procedure emp_if_id(eid int) begin declare ename varchar(20) default ''; if(eid%2=0) then select name into ename from emp where id=eid; select ename; else select eid; end if; end;
create procedure emp_if_salary(eid int) begin declare esalary double default 0; if(eid=1) then update emp set salary=salary+1000 where id=eid; elseif(eid=2) then update emp set salary=salary+1000 where id=eid; elseif(eid=3) then update emp set salary=salary+1000 where id=eid; else update emp set salary=salary where id=eid; end if; select salary into esalary from emp where id=eid; select esalary; end;
create procedure emp_case_salary(eid int) begin declare esalary double default 0; case (eid) when 1 then update emp set salary=salary+1000 where id=eid; when 2 then update emp set salary=salary+1000 where id=eid; when 3 then update emp set salary=salary+1000 where id=eid; else update emp set salary=salary where id=eid; end case; select salary into esalary from emp where id=eid; select esalary; end;
create procedure emp_view() begin declare eid int default 1; declare ename varchar(20) default ''; while(eid<=5) do select name into ename from emp where id=eid; select ename; set eid=eid+1; end while; end;
create procedure emp_view1() begin declare eid int default 1; declare ename varchar(20) default ''; repeat select name into ename from emp where id=eid; select ename; set eid=eid+1; until eid>5 end repeat; end;
create procedure emp_all_view() begin declare eid int default 1; declare ename varchar(20) default ''; declare c_emp cursor for select id,name from emp; open c_emp; fetch c_emp into eid,ename; select eid,ename; close c_emp; end;
create procedure emp_all_view1() begin declare eid int default 1; declare ename varchar(20) default ''; declare c_emp cursor for select id,name from emp; open c_emp; loop fetch c_emp into eid,ename; select eid,ename; end loop; close c_emp; end;
格式： create function 函数名(参数) returns 返回类型 begin ..... return 返回值; end;
案例：
通过输入的id获取员工的姓名
1 2 3 4 5 6
create function getName(eid int) returns varchar(20) begin declare ename varchar(20) default ''; select name into ename from emp where id=eid; return ename; end;
注意：
这是我们开启了bin-log, 我们就必须指定我们的函数指定一个参数deterministic 不确定的 no sql 没有SQL语句，当然也不会修改数据 reads sql data 只是读取数据，当然也不会修改数据 modifies sql data 要修改数据 contains sql 包含了SQL语句
1 2 3 4 5 6 7 8 9 10 11 12 13 14
create function getName(eid int) returns varchar(20) reads sql data begin declare ename varchar(20) default ''; select name into ename from emp where id=eid; return ename; end;
格式： create event[IF NOT EXISTS] event_name -- 创建事件 on schedule 时间和频率 -- on schedule 什么时候来执行 [on completion [NOT] preserve] -- 调度计划执行完成后是否还保留 [enable | disable] -- 是否开启事件，默认开启 [comment '事件描述'] -- 事件的注释 do event_body;-- 需要执行的SQL
注意：
单次计划任务示例 在2019年2月1日4点执行一次 on schedule at ‘2019-02-01 04:00:00’
重复计划执行 on schedule every 1 second 每秒执行一次 on schedule every 1 minute 每分钟执行一次 on schedule every 1 day 没天执行一次
指定时间范围的重复计划任务 每天在20:00:00执行一次 on schedule every 1 day starts ‘2019-02-01 20:00:00’
案例：
每5秒向emp_log,插入当前日期时间记录
1
mysql> desc emp_log;
1 2 3 4 5 6 7 8 9
create event e_insert on schedule every 5 second on completion preserve enable comment '每5秒插入一次' do begin insert into emp_log values(null,1,'insert1',now()); end; #do call 存储过程 #do select 函数名
