Mysql存储过程、触发器和事务

最新推荐文章于 2023-07-29 23:06:34 发布

menghuangxiao

最新推荐文章于 2023-07-29 23:06:34 发布

阅读量417

点赞数

文章标签： mysql

原文链接：http://blog.sina.com.cn/s/blog_4fada4550102wp5m.html

版权

一、存储过程

定义存储过程

语法：

create procedure 过程名（参数1，参数2....）

begin

sql语句;

end

创建存储过程之前我们必须修改mysql语句默认结束符; 要不能我们不能创建成功，使用delimiter可以修改执行符号。

mysql> delimiter % #这样结束符就为%

mysql> create procedure selCg()

-> begin

-> select * from category;

-> end %

调用存储过程

语法：call 过程名（参数1，参数2）;

mysql> call selCg();

存储过程参数类型

In参数

特点：读取外部变量值，且有效范围仅限存储过程内部

例1：

mysql> delimiter //

mysql> create procedure pin(in p_in int)

-> begin

-> select p_in;

-> set p_in=2;

-> select p_in;

-> end //

mysql> delimiter ; #使用完马上恢复默认的

mysql> call pin(1); #调用pin存储过程

例2：定义存储过程 getOneBook，当输入某书籍 id 后，可以调出对应书籍记录

mysql> create procedure getOneBook(in b int)

-> begin

-> select * from books where bId=b;

-> end //

Query OK, 0 rows affected (0.01 sec)

mysql> call getOneBook(3);

Out参数

特点：不读取外部变量值，在存储过程执行完毕后保留新值

mysql> create procedure pout(out p_out int)

-> begin

-> select p_out;

-> set p_out=2;

-> select p_out;

-> end;

mysql> set @p_out=1;

mysql> call pout(@p_out);

通过out参数把查出的结果传递出来。

mysql> create procedure demo(out pa varchar(200))

-> begin

-> select bName into pa from books where bId=3;

-> end

调用，执行：

mysql> call demo(@a);

查看变量@a 中的值：

mysql> select @a;

+-------------------+

| @a |

+-------------------+

| 网络程序与设计－asp |

+-------------------+

Inout参数

特点：读取外部变量，在存储过程执行完后保留新值<<font face="宋体">类似银行存款>

mysql> delimiter //

mysql> create procedure pinout(inout p_inout int)

-> begin

-> select p_inout;

-> set p_inout=2;

-> select p_inout;

-> end //

mysql> delimiter ;

mysql> set @p_inout=1;

mysql> call pinout(@p_inout);

不加参数的情况

如果在创建存储过程时没有指定参数类型，则需要在调用的时候指定参数值

mysql> create table t2(id int(11)); #创建表

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次

存储过程变量的使用

MySQL中使用declare进行变量定义

变量定义：declare 变量名,变量名2,... 数据类型;

datatype为MySQL的数据类型，如:int, float, date, varchar(length)

变量赋值：SET 变量名 = 值 #变量赋值可以在不同的存储过程中继承

mysql> create procedure decl()

-> begin

-> declare name varchar(200);

-> set name=(select bName from books where bId=12);

-> select name;

-> end //

存储过程语句的注释

做过开发的都知道，写注释是个利人利己的事情。便于理解维护

MySQL注释有两种风格

单行注释: --

多行注释:

例子：

mysql> create procedure decl() --procedure name is decl

-> begin

-> declare name varchar(200);

-> set name=(select bName from books where bId=12);

-> select name;

-> end //

存储过程流程控制语句

变量作用域：

内部的变量在其作用域范围内享有更高的优先权，当执行到end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储过程外再也不能找到这个申明的变量，但是你可以通过out参数或者将其值指派给会话变量来保存其值。

mysql> DELIMITER //

mysql> CREATE PROCEDURE proc3()

-> begin

-> declare x1 varchar(5) default 'outer';

-> begin

-> declare x1 varchar(5) default 'inner';

-> select x1;

-> end;

-> select x1;

-> end; //

条件语句

1：if 条件 then ... Else ... 语句

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

2：case语句：

create procedure pr3(in parameter int)

BEGIN

declare var int;

set var=parmeter+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

循环语句：

1：while 条件 do ··· end while

create procedure pr4()

BEGIN

declare var int;

set var=0;

while var<6 dO

insert into t values(var);

set var=var+1;

end while;

END

#whil 1一直循环至到break才退出循环：

while 1 do ... if **** then break; end while;

2：repeat ··· until 条件 end repeat

#执行操作后检查结果，而while则是执行前进行检查。

create procedure pr5()

BEGIN

declare v int;

set v=0;

REPEAT

insert into t values(v);

set v=v+1;

until v>=5

end REPEAT;

END

3：loop ··· end loop

loop循环不需要初始条件， leave语句的意义是离开循环。

create procedure pr6()

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

4：LABLES 标号

标号可以用在begin repeat while 或者loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。

5：ITERATE迭代

通过引用复合语句的标号,来从新开始复合语句

create procedure pr9()

BEGIN

declare v int;

set v=0;

LOOP_LABLE:loop

if v=3 then

set v=v+1;

iterate Loop_LABLE;

end if;

insert into t values(v);

set v=v+1;

if v>=5 then

leave LOOP_LABLE;

end if;

end loop;

END

查看存储过程

查看存储过程内容：

mysql> show create procedure pr9 \G

查看存储过程：

mysql> show procedure status \G

查看存储过程的运行状态：

mysql> show processlist;

删除存储过程

方法一：

mysql> drop procedure p_inout; #p_inout是存储过程名称

方法二：可以加if exists判断存储过程是否存在再删除

mysql> drop procedure if exists存储过程名

二：触发器

什么是触发器：

触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力

触发器的作用：

1. 安全性

可以基于数据库的值使用户具有操作数据库的某种权利。

可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据

可以基于数据库中的数据限制用户的操作，例如不允许股票的价格的升幅一次超过10%

2. 审计

可以跟踪用户对数据库的操作

审计用户操作数据库的语句

把用户对数据库的操作写入审计表

3. 实现复杂的数据完整性规则

实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象。

例如，触发器可回退任何企图吃进超过自己保证金的期货。

4．实现复杂的非标准的数据库相关完整性规则。

触发器可以对数据库中相关的表进行连环更新。

例如，在auths表author_code列上的删除触发器可导致相应删除在其它表中的与之匹配的行。

触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务

5．实时同步地复制表中的数据

6．自动计算数据值

如果数据的值达到了一定的要求，则进行特定的处理。

例如，如果公司的帐号上的资金低于5万元则立即给财务人员发送警告数据

创建触发器：

语法：

create trigger 触发器名称触发的时机触发的动作 on 表名 for each row 触发器状态。

参数说明：

触发器名称: 自己定义

触发的时机： before / after 在执行动作之前还是之后

触发的动作: 指的激发触发程序的语句类型<</span>insert,update,delete>

触发器创建语法四要素：

1.监视点(table)

2.监视事件(insert/update/delete)

3.触发时间(after/before)

4.触发事件(insert/update/delete)

例：当cate表中，删除一个bTypeid=3的图书分类时，books表中也要删除对应分类的图书信息

mysql> use book;

创建触发

mysql> delimiter //

mysql> create trigger delcate after delete on cate for each row

-> delete from books where bTypeId=3; //

测试：删除cate表中的bTypeId=3的记录，同时会把books表中bTypeId类别为3的记录也删除。

mysql> delete from cate where bTypeId=3;

查看触发器：

1.查看创建过程

mysql> show create trigger delCate \G

2.查看所有触发器详细信息

mysql> show triggers \G

删除触发器:

语法：drop trigger 触发器名称;

mysql> drop trigger delCate;

三：事务

什么是事务：出错可以回滚减少出错率

数据库事务：（database transaction）: 事务是由一组SQL语句组成的逻辑处理单元，要不全成功要不全失败。

事务处理：可以确保非事务性单元的多个操作都能成功完成，否则不会更新数据资源。

数据库默认事务是自动提交的，也就是发一条 sql 它就执行一条。如果想多条 sql 放在一个事务中执行，则需要使用事务进行处理。当我们开启一个事务，并且没有提交，mysql 会自动回滚事务。或者我们使用rollback 命令手动回滚事务。

优点：通过将一组操作组成一个，执行时，要么全部成功，要么全部失败的单元。

使程序更可靠，简化错误恢复。

例：

• A汇款给B1000元

• A账户-1000

• B账户+1000

• 以上操作对应数据库为两个update。这两个操作属于一个事物。否则，可能会出现A账户钱少了，B账户钱没增加的情况。

事务四大特性：

事务是必须满足4个条件（ACID）

1、原子性（Autmic）：事务在执行性，要做到“要么不做，要么全做！”。

2、一致性（Consistency）：事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。在事务开始之前和结束之后，数据库的完整性约束没有被破坏。

3、隔离性（Isolation）：一个事务的执行不能被其他事务干扰。并发执行的各个事务之间不能互相干扰，这些通过锁来实现。

4、持久性（Durability）：指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障（比如说宕机等）不应该对其有任何影响。

MySQL事务处理的方法：

MYSQL默认是自动提交的，需要使用SET autocommit=0更改为手动提交：

SET AUTOCOMMIT = {0 | 1} #设置事务是否自动提交，默认是自动提交的。

0：禁止自动提交

1：开启自动提交

※ MYSQL中只有INNODB和BDB类型的数据表才能支持事务处理！其他的类型是不支持！

实验： 测试事务回滚功能

1、查看数据库引擎，数据库存储引擎是否为innodb,因为MyISAM无法成功启动事务，虽然提交了，却无法回滚。

mysql> show create table books \G

2、修改数据库存储引擎为innodb

mysql> alter table books engine=innodb;

mysql> alter table category engine=innodb;

3、开启事务，并测试回滚

mysql> set autocommit=0; #关闭自动提交

mysql> delimiter //

mysql> start transaction;

-> update books set bName="HA" where bId=1;

-> update books set bName="LB" where bId=2;

-> commit;//

mysql> delimiter ;

注意：使用commit提交后，就无法回滚。

mysql> delimiter //

mysql> start transaction; update books set bName="AH" where bId=1; update books set bName="BL" where bId=2;// #不提交

mysql> delimiter ;

4、回滚：

mysql> rollback; #完成恢复。

menghuangxiao

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mysql存储过程、触发器和事务

一、存储过程定义存储过程语法：create procedure过程名（参数1，参数2....）beginsql语句;end创建存储过程之前我们必须修改mysql语句默认结束符;要不能我们不能创建成功，使用delimiter可以修改执行符号。mysql>delimiter %#这样结束符就为%mysql>create procedure ...
复制链接

扫一扫