存储过程、存储函数、触发器、游标

create procedure pro_test11()
begin 
    declare e_id int(11);
    declare e_name varchar(50);
    declare e_age int(11);
    declare e_salary int(11);
    declare emp_result cursor for select * from emp;
    open emp_result;
    fetch emp_result into e_id,e_name,e_age,e_salary;
    select concat('id=',e_id,',name=',e_name,',age=',e_age,',薪资为：',e_salary);
    fetch emp_result into e_id,e_name,e_age,e_salary;
    select concat('id=',e_id,',name=',e_name,',age=',e_age,',薪资为：',e_salary);
    close emp_result;
end$

create procedure pro_test12()
begin 
    declare e_id int(11);
    declare e_name varchar(50);
    declare e_age int(11);
    declare e_salary int(11);
    declare has_data int default 1;
    
    declare emp_result cursor for select * from emp;
--     退出申明必须写在游标之后
    DECLARE EXIT HANDLER FOR NOT FOUND set has_data=0;
    open emp_result;
    repeat
        fetch emp_result into e_id,e_name,e_age,e_salary;
        select concat('id=',e_id,',name=',e_name,',age=',e_age,',薪资为：',e_salary);
        until has_data = 0
    end repeat;
    close emp_result;
end$
drop procedure pro_test12()$

-- 存储函数
create function fun1(countryId int)
RETURNS int
begin
    declare cnum int;
    select count(*) into cnum from city where country_id = countryId;
    return cnum;
end$

select fun1(1)$
drop function fun1()$

-- 通过触发器记录 emp 表的数据变更日志，包含增加，修改，删除;
select * from emp;

create table emp_logs(
    id int(11) not null auto_increment,
    operation varchar(20) not null comment '操作类型,insert/update/delete',
    operate_time datetime not nul 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_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);

select * from emp;

create trigger emp_update_trigger
after update
on emp
for each row
begin
    insert into emp_logs(id,operation,operate_time,operate_id,operate_params) values(null,'update',now(),new.id,concat('更改前(id:',old.id,',name:',old.name,',age:',old.age,',salary:',old.salary,'),更改后(id:',new.id,',name:',new.name,',age:',new.age,',salary:',new.salary,')'));
end;

show triggers/G;
drop triggers emp_insert_trigger;