MySQL高级篇——索引、视图、存储过程和函数、触发器的相关概念及操作（下）

DROP PROCEDURE [IF EXISTS] sp_name;

 DECLARE var_name[,...] type [DEFAULT value] 
 SET var_name = expr [, var_name = expr] ...

if search_condition then statement_list
[elseif search_condition then statement_list] ...
[else statement_list]
end if;

create procedure procedure_name([in/out/inout] 参数名  参数类型)
...
IN :  该参数可以作为输入，也就是需要调用方传入值 , 默认
OUT:  该参数作为输出，也就是该参数可以作为返回值
INOUT: 既可以作为输入参数，也可以作为输出参数

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE;

while search_condition do
    statement_list
end while;

REPEAT
    statement_list
    UNTIL search_condition
END REPEAT;

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

DECLARE cursor_name CURSOR FOR select_statement;

OPEN cursor_name;

FETCH cursor_name INTO var_name [, var_name] ...

CLOSE cursor_name;

create table emp(
    id int(11) not null auto_increment ,
    name varchar(50) not null comment '姓名',
    age int(11) comment '年龄',
    salary int(11) comment '薪水',
    primary key(`id`)
)engine=innodb default charset=utf8;
insert into emp(id,name,age,salary) 
values(null,'金毛狮王',55,3800),(null,'白眉鹰王',60,4000),(null,'青翼蝠王',38,2800),(null,'紫衫龙王',42,1800);

CREATE FUNCTION function_name([param type ... ])
RETURNS type
BEGIN
...
END;

create trigger trigger_name
before/after insert/update/delete
on tbl_name
[ for each row ]  -- 行级触发器
begin
    trigger_stmt ;
end;

create table emp_logs(
    id int(11) not null auto_increment,
    operation varchar(20) not null comment '操作类型, insert/update/delete',
    operate_time datetime not null comment '操作时间',
    operate_id int(11) not null comment '操作表的ID',
    operate_params varchar(500) comment '操作参数',
    primary key(`id`)
)engine=innodb default charset=utf8;

create trigger emp_logs_insert_trigger
after insert 
on emp
for each row
begin
  insert into emp_logs(id,operation,operate_time,operate_id,operate_params)
  values(null,'insert',now(),new.id,concat('插入后(id:',new.id,', name:',new.name,',
age:',new.age,', salary:',new.salary,')'));
end$
insert into emp(id,name,age,salary) values(null, '光明左使',30,3500);
insert into emp(id,name,age,salary) values(null, '光明右使',33,3200);

create trigger emp_logs_delete_trigger
after delete
on emp
for each row
begin
  insert into emp_logs(id,operation,operate_time,operate_id,operate_params)
  values(null,'delete',now(),old.id,concat('删除前(id:',old.id,', name:',old.name,',
age:',old.age,', salary:',old.salary,')'));
end$
delete from emp where id = 5;

show triggers;

drop trigger [schema_name.]trigger_name;