笔记整理2.11-存储过程-触发器

create procedure 过程名（参数1，参数2....）
begin
sql语句;
end

 

mysql>use test;
mysql> delimiter % 这样结束符就为%
mysql> create procedure selCg2()
-> begin
-> select * from firewall;
-> end %
DROP PROCEDURE selCg2() %
mysql> call selCg2()%

 

mysql>use test;
mysql> delimiter //
mysql> create procedure xx(in abc int)
-> begin
-> select abc;
-> set abc=2;
-> select abc;
-> end;
-> //
mysql> delimiter ; 使用完马上恢复默认的
mysql> set @y=1; 局部变量
call xx(@y);
call xx(6);

 

mysql>delimiter //
mysql> create procedure iP(in b int) # b是参数名
-> begin
-> select host,port from firewall where port=b;
-> end //
Query OK, 0 rows affected (0.01 sec)
mysql> call iP(21)//

 

create procedure pout(out p_out int)#传出变量

create procedure pinout(inout p_inout int) 传入传出

 循环

mysql> create table t2(id int(11)); 创建表
mysql> delimiter //
mysql> create procedure t2(n1 int)
-> begin
-> set @x=0;
-> repeat set @x=@x+1; 作了循环
-> insert into t2 values(@x);
-> until @x>n1
-> end repeat;
-> end;
-> //
mysql> delimiter ;
mysql> call t2(5); 循环5次
select * from t2;

 

存储过程中定义变量

mysql> delimiter //
mysql> create procedure decl()
-> begin
-> declare name varchar(200);
-> set name=(select host from firewall where port=21);
-> select name;
-> end//
delimiter ;
call decl();

“--“：单行注释
“/*…..*/”:一般用于多行注释

两层嵌套

mysql > CREATE PROCEDURE proc3() 
-> begin 
-> declare x1 varchar(5) default 'outer'; 
-> begin 
-> declare x1 varchar(5) default 'inner'; 
-> select x1; 
-> end; 
-> select x1; 
-> end; 
-> // 
mysql > DELIMITER ;

 循环判断案例

（1）if-then -else语句
mysql> delimiter //
mysql> create procedure proc2(in parameter int)
    -> begin
    -> declare var int;
    -> set var=parameter+1;
    -> if var=0 then
    -> insert into t values(17);
    -> end if;
    -> if parameter=0 then
    -> update t set s1=s1+1;
    -> else
    -> update t set s1=s1+2;
    -> end if;
    -> end;
    -> //
mysql> delimiter ；


（2）case语句
mysql> delimiter //
mysql> create procedure proc3 (in parameter int)
    -> begin
    -> declare var int;
    -> set var=parameter+1;
    -> case var
    -> when 0 then
    -> insert into t values(17);
    -> when 1 then
    -> insert into t values(18);
    -> else
    -> insert into t values(19);
    -> end case;
    -> end;
    -> //
mysql> delimiter ;


（3）循环语句
1）while ···· end while
mysql> delimiter //                                                                                       

                                          
mysql> create procedure proc4 ()
    -> begin
    -> declare var int;
    -> set var=0;
    -> while var<6 do
    -> insert into t values(var);
    -> set var=var+1;
    -> end while;
    -> end;
    -> //
mysql> delimiter ；


2）repeat···· end repeat
执行操作后检查结果，而while则是执行前进行检查。
mysql> create procedure proc5 ()
    -> begin
    -> declare v int;
    -> set v=0;
    -> repeat
    -> insert into t values(v);
    -> set v=v+1;
    -> until v>=5
    -> end repeat;
    -> end;
    -> //
mysql> delimiter ;

loop循环不需要初始条件，这点和while循环相似，同时和repeat循环一样不需要结束条件, leave语句的意义是离开循

环。
mysql> delimiter //
mysql> create procedure proc6 ()
    -> begin
    -> declare v int;
    -> set v=0;
    -> loop_lable:loop
    -> insert into t values(v);
    -> set v=v+1;
    -> if v >=5 then
    -> leave loop_lable;
    -> end if;
    -> end loop;
    -> end;
    -> //
mysql> delimiter ;

 

 

show create procedure iP \G

show procedure status \G 查看所有存储过程

drop procedure iP;#删除存储过程
DROP PROCEDURE IF EXISTS存储过程名

触发器

create trigger 触发器名称 触发的时机 触发的动作 on 表名 for each row 触发器状态。
参数说明：
触发器名称：自己定义
触发的时机： before /after 在执行动作之前还是之后
触发的动作：指的激发触发程序的语句类型<insert ,update,delete>
each row：操作第一行我都监控着
触发器创建语法四要素：
1.监视地点(table)
2.监视事件(insert/update/delete)
3.触发时间(after/before)
4.触发事件(insert/update/delete)

mysql> delimiter //
mysql> create trigger delCategory after delete on category for each row
-> delete from books where bTypeId=3;
-> //

mysql> show create trigger delCategory\G

mysql> show triggers\G   这个查看所有的

drop trigger delCategory;//

事务
mysql> set autocommit=0;
mysql> delimiter //
mysql> start transaction;
-> update books set bName="ccc" where bId=1;
-> update books set bName="ddd" where bId=2;
-> commit;//

 

事务测试回滚需要innodb引擎
mysql> alter table books engine=innodb;
mysql> alter table category engine=innodb;

mysql> delimiter //
mysql> start transaction; update books set bName="AH" where bId=1; update books set bName="BL" where 

bId=2;//  不提交
mysql> delimiter ;
mysql> select bName from books where bId=1 or bId=2;

回滚：
mysql> rollback;
mysql> select bName from books where bId=1 or bId=2;