事务与存储过程

2024-09-12 20:30:15

字体：大中小

来源：转载

供稿：网友

事务与存储过程：

　　1.事务管理

　　（1）概念：事务指逻辑上的一组操作，组成这组操作的各个单元，要不全部成功，要不全部不成功。

　　（2）MySQL默认就自带事务，但是MySQL自带的事务是一条语句独占一个事务

　　（3）也可以自己控制事务：**

　　star transcation; --开启事务，在这条语句之后的sql将处在同一个事务中
　　...........
　　...........#语句
　　commit; #提交事务，让这个事务中的sql对数据库的影响立即发生
　　rollback; #回滚事务，测回

　　create table account(
　　id int primary key auto_increment,
　　name varchar(40),
　　money double

　　);

　　insert into account values(null,'赖泽铵',2000),(null,'侯文泽',1000);

　　**（4）
　　原子性：事务时一组不可分割的单位，要么同时成功要么同时不成功。

　　一致性：事务前后的数据完整性应该保持一致

　　隔离性：多个用户并发访问数据库时，一个用户的事务不能被其他用户事务干扰。

　　持久性：一旦提交，数据的改变将是永久性

　　隔离性：本质就是多个线程操作同一个资源造成的多线程并发安全问题，加锁可以保证隔离性，但是造成数据库性能下降

　　如果两个事务并发的修改：必须隔离
　　如果两个事务并发查询：不用隔离
　　如果一个事务修改一个查询：脏读#中途撤销，不可重复读#中途修改，虚读#新增内容**

　　四大隔离：
　　read uncommitted #不隔离
　　read committed #可以防止脏读
　　Repeatable read #不能防止虚读，只能读到开始时间事务的数据，想查看之后时间的数据只能终止事务才能看到
　　Serializable #数据库运行在串行化未实现，性能低，直接锁住，对方不能修改，待事务结束。

　　默认Repeatable read

　　设置语句：

　　SET SESSION TRANSCATION ISOLATION LEVEL 隔离等级;

　　查询语句：

　　select @@tx_isolation; #上面语句执行成功后

　　2.存储过程的创建

　　重复使用某一功能的情况，减少工作量

　　（1) 语法：

　　CREATE PROCEDURE sp_name([proc_parameter])
　　[characteristics...] routine_body

　　~proc_parameter#参数列表
　　形式：[IN | OUT | INOUT]param_name#参数名称 type#参数类型

　　~characteristics#存储特性

　　LANGUAGE SQL:说明routine_body部分由SQL语句组成

　　[NOT]DETERMINISTIC:指明存储过程执行的结果是否确定。默认NOT

　　{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}:分别是包含SQL语句但不包含读写数据语句，不包含SQL语句，读写数据语句，写数据语句。
　　默认CONTAINS SQL

　　SQL SECURITY {DEFINER | INVOKER}:指明谁有权限来执行，DEFINER 表示只有定义者才能执行，INVOKER 表示拥有权限的调用者可以执行。
　　默认 DEFINER

　　COMMENT'string‘：注释信息，可以用来描述存储过程

　　~routine_body:SQL语句，
　　DELIMITER // #将结束符定义为//
　　BEGIN
　　.....
　　......
　　END

　　delimiter //

　　create procedure nbaf()
　　begin
　　select from team left join star on team.id = star.team_id
　　union
　　select from team right join star on team.id = star.team_id;
　　end//

　　delimiter ;

　　call nbaf();

　　(2)在存储过程中定义一个变量
　　变量的声明一定要在存储过程的BEGIN和END之间，作用范围是当前的存储范围
　　DECLARE var_name [,varname]...data_type [DEFAULT value];

　　修改变量值1：
　　SET var_name = expr[,var_name = expr]#表达式赋予给.....;

　　修改变量值2：
　　SELECT col_name [...]#数据 into#复制 var_name [....]#变量 table_expr#查询条件;

　　delimiter //

　　create procedure pf(in p_id int)
　　begin
　　select from team left join star on team.id = star.team_id where team.id = p_id
　　union
　　select from team right join star on team.id = star.team_id where team.id = p_id;
　　end//

　　delimiter ;

　　call pf();

　　3.定义条件和处理程序

　　（1）定义条件：是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时应当采取的处理方式，并且保证存储过程中遇到警告或错误时能继续执行。
　　DECLARE condition_name CONDITION FOR [condition_type];

　　condition_type 的两种形式：
　　[condition_type]：
　　SQLSTATE[VALUE] sqlstate_value | mysql_error_code

　　sqlstate_value：是长度为5的字符串类型错误代码，
　　mysql_error_code：为数值类型的错误代码
　　例如：ERROR1142(42000) ,sqlstate_value:42000,mysql_error_code:1142

　　（2）定义处理程序
　　DECLARE handler_type HANDER FOR condition_value[,...] sp_statement

　　handler_type：CONTINUE | EXIT | UNDO#遇到错误撤回之前的操作，但是MySQL不支持

　　condition_value:

　　SQLSTATE[VALUE] sqlstate_value：包含5个字符的字符串错误值
　　condition_name ：错误条件名称
　　SQLWARNING ：匹配所有以01开头的SQLSTATE错误代码
　　NOT FOUND ：匹配所有以02开头的SQLSTATE错误代码
　　SQLEXCEPTION ：匹配所有除01，02开头外的SQLSTATE错误代码
　　mysql_error_code ：匹配数值类型的错误代码

　　定义处理程序的几种方式

　　declare continue handler for SQLSTATE '42S02' set @info= 'NO_SUCH_TABLE' ; #info输出

　　declare continue handler for 1146 set @info= 'NO_SUCH_TABLE' ; #1146，捕获mysql_error_code

　　declare no_such_table condition for 1146;
　　declare continue handler for NO_SUCH_TABLE set @info= 'ERROR' ; #先定义条件，然后调用

　　declare exit handler for SQLWARNING set @info= 'ERROR' ;

　　declare exit handler for NOT FOUND set @info= 'NO_SUCH_TABLE' ;

　　declare exit handler for SQLEXCEPTION set @info= 'ERROR' ;

　　4.光标的使用：数据量非常大时使用光标逐条查询

　　（1）光标的声明：在声明变量、条件后，声明处理程序之后

　　DECLARE cursor_name CURSOR FOR select_statement

　　(2)光标的使用
　　打开光标：
　　OPEN cursor_name;
　　FETCH cursor_name INTO var_name [,var_name]....
　　关闭光标：
　　CLOSE curse_name

　　5.控制流程的使用：在编写存储过程中

　　（1）IF语句：
　　IF expr_condition THEN statement_list
　　[ELSEIF expr_contidion THEN statement_list]
　　[ELSE statement_list]
　　END IF
　　//expr_condition 判断语句 statement_list SQL语句

　　（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_table:] LOOP
　　statement_list
　　END LOOP [loop_tabel];

　　                    //loop_table表示标注名称，可以省略

　　----------------------------------------------------------------------------------------------------------------------------------
　　delimiter //

　　create procedure east()
　　begin
　　declare ep1 int default 0;
　　declare ep2 int default 7;
　　east_p:LOOP
　　set ep1 = ep1 + 1;
　　if ep1< 4 then select * from team left join star on team.id = star.team_id where team.id = ep1
　　     union
　　     select * from team right join star on team.id = star.team_id where team.id = ep1;

　　else leave loop;
　　end if;
　　end LOOP esat_p;

　　end//

　　delimiter ;

　　call east();
　　//不会用
　　------------------------------------------------------------

　　（4）LEAVE

　　LEAVE label #退出循环

　　（5）ITERATE
　　ITERATE label #再次循环，回到开头

　　（6）REPEAT

　　[repeat_lable:] REPEAT
　　     statement_list
　　UNTIL expr_condition                  #直到判断语句为真退出
　　END REPEAT[repeat_lable]
　　（7）WHILE

　　[while_lable:] WHEIL expr_condition DO
　　statement_list
　　END WHILE [while_lable]

　　6.调用存储过程

　　（1）执行存储过程

　　CALL sp_name([parameter[.....]])

　　7.查看存储过程

　　（1）SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

　　show procedure status;

　　show procedure status like 'nbaf';

　　show procedure status like '%f'/G #查看以f结尾的存储过程

　　8.修改存储过程

　　ALTER {PROCEDURE | FUNCTION } sp_name [characteristic....]
　　#characteristic表示要修改的存储过程的哪个部分，取值如下
　　~CONTAINS SQL
　　~NO SQL
　　~READS SQL DATA #读数据
　　~MODIFIES SQL DATA #写数据
　　~SQL SECURITY { DEFINER | INVOKER}
　　~COMMENT'string' #注释

　　目前MySQL还不提供对已经存在的存储过程代码进行修改，要修改先删除

　　9.删除存储过程

　　DROP {PROCEDURE | FUNCTION } [IF EXISTS] sp_name;

（编辑：武林网）

上一篇：基于xtrabackup来实现InnoDB存储引擎的备份恢复

下一篇：mybatis if test非空判断数字0为什么是false