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;