mysql存储过程原子性_MySQL<事务与存储过程>

事务与存储过程

事务管理

事务的概念

谓的事务就是针对数据库的一组操作，它可以由一条或多条SQL语句组成，同一个事务的操作具备同步的特点，即事务中的语句要么都执行，要么都不执行。

事务的使用

开启事务START TRANSACTION;

执行SQL语句

提交事务COMMIT;

取消事务(回滚)

事务的提交

事务中的操作语句都需要使用COMMIT语句手动提交，只有事务提交后其中的操作才会生效。

事务的回滚

如果不想提交当前事务，可使用ROLLBACK语句取消当前事务。

ROLLBACK语句只能针对未提交的事务执行回滚操作，已提交的事务是不能回滚的。

事务的隔离级别

REPEATABLE READ(可重复读)

READ UNCOMMITTED(读未提交)

READ COMMITTED(读提交)

SERIALIZABLE(可串行化)

事务的定义特性

原子性

原子性是指一个事务必须被视为一个不可分割的最小工作单元，只有事务中所有的数据库操作都执行成功，才算整个事务执行成功

一致性

一致性是指事务将数据库从一种状态转变为下一种一致的状态。

隔离性

隔离性还可以称为并发控制、可串行化、锁等，当多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

持久性

事务一旦提交，其所做的修改就会永久保存到数据库中，即使数据库发生故障也不应该对其有任何影响。

事务的持久性不能做到100%的持久，只能从事务本身的角度来保证永久性，而一些外部原因导致数据库发生故障，如硬盘损坏，那么所有提交的数据可能都会丢失。

存储过程的创建

什么是存储过程

存储过程就是一条或多条SQL语句的集合，当对数据库进行一系列复杂操作时，存储过程可以将这些复杂操作封装成一个代码块，以便重复使用，大大减少数据库开发人员的工作量。

创建存储过程

CREATE PROCEDURE sp_name([proc_parameter])

[characteristics…]routine_body

CREATE PROCEDURE：为用来创建存储过程的关键字。

sp_name：为存储过程的名称。

proc_parameter：为指定存储过程的参数列表。

characteristics：用于指定存储过程的特性。

变量的使用

定义

在MySQL中，变量可以在子程序中声明，用于保存数据处理过程中的值，这些变量的作用范围在BEGIN…END程序中。

DECLARE var_name[,varname]…date_type[DEFAULT value];

var_name：为局部变量的名称。

DEFAULT value：子句给变量提供一个默认值，该值可以被声明为一个常数或一个表达式。如果没有DEFAULT子句，变量的初始值为NULL。

使用SET语句为变量赋值

SET var_name =

expr[,var_name = expr]…;

使用SELECT…INTO为一个或多个变量赋值

SELECT col_name[…]

INTO var_name[…] table_expr;

定义条件和处理程序

定义条件是指事先定义程序执行过程中遇到的问题

DECLARE condition_name CONDITION FOR [condition_type];

// condition_type的两种形式：

[condition_type]:

SQLSTATE[VALUE] sqlstate_value|mysql_error_code

处理程序定义了在程序执行过程中遇到问题时应当采取的处理方式，并且保证存储过程在遇到警告或错误时能继续执行处理过程使用DECLARE语句定义

DECLARE handler_type HANDLER FOR condition_value[,…] sp_statement

handler_type:

CONTINUE|EXIT|UNDO

condition_value:

|condition_name

|SQLWARNING

|NOT FOUND

|SQLEXCEPTION

|mysql_error_code

handler_type：为错误处理方式，参数取值

有3个：CONTINUE、EXIT和UNDO。

CONTINUE：表示遇到错误不处理，继续执行；

EXIT：遇到错误马上退出。

UNDO：表示遇到错误后撤回之前的操作，MySQL中暂时不支持这样的操作。sp_statement：参数为程序语句段，表示在遇到定义的错误时，需要执行的存储过程。

condition_value：表示错误类型。

光标的使用

在编写存储过程时，查询语句可能会返回多条记录，如果数据量非常大，则需要使用光标来逐条读取查询结果集中的记录。光标是一种用于轻松处理多行数据的机制。

声明

语法：DECLARE cursor_name CURSOR FOR select_statement

示例：DECLARE cursor_student CURSOR FOR select s_name,s_gender FROM student;

使用

语法：OPEN cursor_name FETCH cursor_name INTO var_name[,var_name]…

示例：FETCH cursor_student INTO s_name, s_gender;

关闭

CLOSE cursor_name

流程控制的使用

存储过程中的流程控制语句用于将多个SQL语句划分或组合成符合业务逻辑的代码块，MySQL中的流程控制语句有7个

1、 IF语句

语法格式如下：

IF expr_condition THEN statement_list

[ELSEIF expr_condition THEN statement_list]

[ELSE statement_list]

END IF

2、CASE语句

语法格式如下：

CASE case_expr

WHEN when_value THEN statement_list

[WHEN when_value THEN statement_list]…

[ELSE statement_list]

END CASE

3、 LOOP语句

语法格式如下：

[loop_label:]LOOP

statement_list

END LOOP [loop_label]

4、LEAVE语句

语法格式如下：

LEAVE lable

5、ITERATE语句

语法格式如下：

ITERATE lable

6、REPEAT语句

语法格式如下：

[repeat_lable:] REPEAT

statement_list

UNTIL expr_condition

END REPEAT[repeat_lable]

7、WHILE语句

语法格式如下：

[[while_lable:] WHILE expr_condition DO

Statement_list

END WHILE [while_lable]

存储过程的使用

调用存储过程

CALL sp_name([parameter[,…]])

CALL：为调用存储过程的关键字。

sp_name：为存储过程的名称。

Parameter：为存储过程的参数。

查看存储过程

1、使用SHOW STATUS语句

CALL sp_name([parameter[,…]])

2、使用SHOW CREATE语句

SHOW CREATE{PROCEDURE|FUNCTION} sp_name

3、information_schema.Routines表中查看

SELECT * FROM  information_schema.Routines

WHERE ROUTINE_NAME='CountProc1'

AND ROUTINE_TYPE='PROCEDURE'\G

修改存储过程

ALTER {PROCEDURE|FUNCTION} sp_name[characteristic…]

sp_name：表示存储过程或函数的名称。characteristic：表示要修改存储过程的哪个部分， characteristic 的取值分为8部分。

删除存储过程

DROP{ PROCEDURE|FUNCTION }[IF EXISTS] sp_name

综合案例--存储过程应用