Mysql06触发器，存储过程

存储过程
函数
触发器

会话变量
 一次会话过程中，使用的变量
 可以自定义任意变量保存任意数据

set @v1=‘abc’;
select @v1;

 可以访问数据库的系统变量
set @@tx_isolation=‘repeatable-read’
set tx_isolation=‘repeatable-read’

存储过程
 存储在数据库服务器上的一段程序代码

创建存储过程
delimiter // – 自定义结束符
delimiter ; – 重新设置用分号表示结束

create procedure p1()
begin
select * from employees;
end;
//

调用存储过程
call p1();
//
查看存储过程
show create procedure p1\G
show procedure status\G
show procedure status where db=‘test’\G
删除
drop procedure p1;
//
存储过程的参数
 in 输入参数
只用来接收传入的值

 out 输出参数
只用来为外部变量赋值

 inout 输入输出参数

 定义：create procedure p1(in a int, out b int)
set b=…;
 调用： call p1(5, @v1);
select @v1;

流程控制
 If then - else - end if
if 条件 then
代码
end if;

if 条件 then
代码1
else
代码2
end if;

 case
when 条件 then ….;
when 条件 then …;
else …;
end case;

 循环
while 条件 do
…
end while;

loop
…
end loop;

repeat
…
until 条件 end repeat;

leave 跳出循环
iterate 直接进入下次迭代

循环命名
lp: loop
leave lp;
iterate lp;
end loop;

局部变量
 declare v1 int default 0;
 局部变量只在它定义的代码块内有效
函数
 函数有返回值
 需要定义返回类型，并最终返回一个计算结果

创建函数
create function f(参数) returns int
begin
…
return 计算结果;
end;

查看函数

     show create function f\G
     show function status\G
     show function status where db='test'\G

删除函数
drop function f;

触发器
 在添加、修改、删除数据时，
触发一段代码执行
 六个触发时间(一个表最多6个触发器)：
before insert
before update
before delete

after insert
after update
after delete
 两个隐含对象: new、old
 new : 新数据行
 old : 旧数据行

 insert
 new: 新插入的数据行
 old: 没有
 update
 new: 修改之后的数据
 old: 修改之前的数据
 delete
 new: 没有
 old: 被删除的数据

创建触发器
create trigger 名称 before insert on tb1 for each row
begin
…
end;

查看触发器
use information_schema
select * from triggers\G

删除触发器
drop trigger 名称;

数据库备份和恢复
 备份
Windows命令行执行：

mysqldump -uroot -p
–default-character-set=utf8
库名>d:\a\abc.sql

 恢复
Windows 命令行执行：(恢复的数据库要事先存在)

mysql -uroot -p
–default-character-set=utf8
库名< d:\a\abc.sql
练习

1.   测试存储过程参数
   

delimiter //
drop procedure if exists p1;
//
create procedure
p1(in a double, out b double)
begin
set @r=a*a;
select @r;
set b=@r;
end;
//

set @v1=0;
call p1(5, @v1);
//
select @v1;
//

2.   if判断测试
   

drop procedure if exists p2;
//
create procedure p2(in a int)
begin
if a=1 then
select ‘a的值是1’ c1;
end if;
– 此处是分割线
if a=2 then
select ‘a的值是2’ c2;
else
select ‘a的值不是2’ c3;
end if;
end;
//
call p2(1);
//
call p2(2);
//
call p2(3);
//

3.   case测试
   

drop procedure if exists p3;
//
create procedure p3(in a int)
begin
case
when a=1 then select 100;
when a=2 then select 200;
when a=3 then select 300;
else select 10000;
end case;
end;
//
call p3(1);
call p3(2);
call p3(3);
call p3(4);
//

4.   循环向表中插入1到5
   

use test;
drop table if exists tb1;
//
create table tb1(
id int primary key auto_increment,
c int
);
//
drop procedure if exists p4;
//
create procedure p4()
begin
declare i int default 0; – 定义局部变量
– while测试
while i<5 do
set i=i+1;
insert into tb1© values(i);
end while;
– loop测试
set i=0;
lp: loop
set i=i+1;
insert into tb1© values(i);
if i=5 then
leave lp;
end if;
end loop;
– repeat循环
set i=0;
repeat
set i=i+1;
insert into tb1© values(i);
until i=5 end repeat;
end;
//
call p4();
//
Select * from tb1;
//

5.   创建函数 pow(a, b) 求a的b次方
   

drop function if exists pow;
//
create function pow(a double, b int)
returns double
begin
declare r double default a;
declare i int default 0;
if b=0 then
return 1;
end if;

       while i<b-1 do
                set r=r*a;
                set i=i+1;
       end while;
       return r;

end;
//
select pow(2,3);
select pow(2,0);
select pow(3.14,3);
//

6.   生成随机字符串，长度[n1, n2)
   

floor(5+[0,1)*5) -> 5+[0, 5)
rand()

s = ‘abcdefg’ substring(s,r,1) 提取r位置开始的1个字符
|
r

delimiter //
drop function if exists rand_str;
//
create function rand_str(n1 int, n2 int)
returns varchar(255)
begin
declare len int; – 随机长度
declare r int; – 字符串中的随机位置
declare s0 varchar(600) default ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ的一是在不了有和人这中大为上个国我以要他时来用们生到作地于出就分对成会可主发年动同工也能下过子说产种面而方后多定行学法所民得经十三之进着等部度家电力里如水严龙飞’; – 一堆字符
declare s varchar(255) default ‘’; – 产生的结果
declare i int default 0; – 控制循环次数
– 产生一个 [n1,n2)的随机长度
set len = floor(n1+(n2-n1)*rand());
– 循环 len 次
while i<len do
– 产生随机位置1+[0,char_length(s0))
set r=floor(1+ char_length(s0)*rand());
– r位置字符拼到s
set s=concat(s, substring(s0,r,1));
set i=i+1;
end while;
return s; – 返回随机字符串
end;
//
select rand_str(2,5); //

7.   存储过程，循环插入大量数据
   

1.   创建内存表
   

engine=innodb
myisam
memory 内存表
2) 循环产生数据，插入内存表
3) 把内存表数据，批量插入磁盘表

– 内存表
drop table if exists tm;
//
create table tm(
s varchar(255)
)engine=memory;
//
– 磁盘表
drop table if exists t;
//
create table t(
id int primary key auto_increment,
c varchar(255)
)engine=innodb;
//

                        -- 创建存储过程用来产生大量数据插入t表
                        drop procedure if exists gen_data;
                        //
                        create procedure gen_data(in n int)
                        begin
                                 declare i int default 0;
                                 while i<n do
                                           insert into tm values(rand_str(2,5));
                                           set i=i+1;
                                 end while;
                                 insert into t(c) select s from tm;
                                 delete from tm;
                        end;
                        //
                        call gen_data(10); //

8.   测试查询过滤效率
   

select * from t limit 10; //
select * from t where c=‘的一’; //
select * from t where c like ‘的一%’; //
select * from t where c like ‘%的一%’; //

explain select * from t where c=‘的一’; //
explain select * from t where c like ‘的一%’; //
explain select * from t where c like ‘%的一%’; //

explain select * from t where id=3355332; //

9.   创建 c 字段索引，再查询测试查询效率
   

create index c_index on t©; //

10. 测试触发器，修改用户数据时，自动修改updated字段的时间
    use jtdb;

create trigger user_before_update_trigger
before update on tb_user for each row
begin
set new.updated=now();
end;
//
– 修改id=14用户的密码
update tb_user set password=md5(‘123’)
where id=14;
//
– 查询id=14用户，查看修改时间是否改变
Select * from tb_user where id=14;
//

11. 不允许删除商品（tb_item）
    delete from tb_item where …
    直接出错

create trigger item_before_delete_trigger
before delete
on tb_item for each row
begin
– 暴力抛出错误
delete from 不允许删除商品;
end;
//
Delete from tb_item where id=536563;
//
Select * from tb_item where id=536563;
//