《事务及存储过程》由会员分享，可在线阅读，更多相关《事务及存储过程（29页珍藏版）》请在金锄头文库上搜索。
1、事务及存储过程,MySQL进阶,事务,事务在数据库的使用过程中，是一个非常重要的概念。事务（TRANSACTION）也称工作单元，是一个或多个SQL语句组成的序列，这些SQL操作作为一个完整的工作单元，要么全部执行，要么全部不执行。通过事务的使用，能够使一系列相关操作关联起来，防止出现数据不一致现象,例如：你需要进行一个转帐的活动，即把A账户内资金转帐到B账户上，转帐金额为1000元，那么这个活动包含两个动作： 1A账户减少1000元 2B账户增加1000元 如果没有事务，我们也许就会遇到一个不太愉快的现象，由于系统的非正常因素。第二个操作失败了，而第一个操作成功了，那么你A账户上的资金已经减
2、少了1000元了。而B账户上却没有相应的增加1000元。对银行来讲这或许是个好消息。可对用户而言这绝对是不公平的。为了避免这样的情况发生，我们可以将以上这两个物理上分离但逻辑上是一个整体的操作定义到一个事务中。那么两个操作将同时成功或是同时失败。从而保证数据的一致性。,原子性 事务是由一个或是多个组合在一起的动作组成，就像一个独立的工作单元。原子性保证要么所有的操作都要成功，要不全部都失败。如果所有的动作都成功了，我们就是说这个事务成功了，不然就是失败的，要回滚,事务的四个特性,一致性一旦一个事务完成了（不管是成功的还是失败的），整个系统处于操作规则的统一状态，也就是说数据不会损坏,隔离性事务
3、的隔离性是指数据库中一个事务的执行不能被其他事务干扰。事务应该允许多个用户去同时操作一个数据，但是一个用户的操作不应该影响另一个用户的操作。所以，事务应该隔离起来，目的为了防止同时的写和读操作,持久性事务的持久性也称为事务的永久性，它是指一个事务一旦被提交，则起对数据库中数据的改变就是永久的。通常，我们把事务的结果存放在数据库中，或是别的持久性设备上,事务相关SQL语句1 START TRANSACTION：开始一项新的事务 2 COMMIT：提交当前事务，使变更成为永久变更3 ROLLBACK：回滚当前事务，取消其变更4 SET AUTOCOMMIT：可以禁用或启用默认的autocommit
4、模式,案例START TRANSACTION;SELECT A:=SUM(salary) FROM table1 WHERE type=1;UPDATE table2 SET summary=A WHERE type=1;COMMIT;,存储过程是命名程序的一种，是由流控制和SQL语句书写的代码集合，是一系列数据库操作的集合，这个过程经编译和优化后存储在数据库服务器中，应用程序使用时只要调用即可。,存储过程和函数,1存储过程的能力大大增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。 2可保证数据的安全性和完整性。通过存储过程可以使
5、没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。,使用存储过程的优点,3在运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。这种已经编译好的过程可极大地改善SQL语句的性能。由于执行SQL语句的大部分工作已经完成，所以存储过程能以极快的速度执行。 4可以降低网络的通信量。,5使体现企业规则的运算程序放入数据库服务器中，以便集中控制。当企业规则发生变化时在服务器中改变存储过程即可，无须修改任何应用程序。企业规则的特点是要经常变化，如果把体现企业规则的运算程序放入应用程序中，则当企业规则发生变化
6、时，就需要修改应用程序工作量非常之大（修改、发行和安装应用程序）。如果把体现企业规则的运算放入存储过程中，则当企业规则发生变化时，只要修改存储过程就可以了，应用程序无须任何变化。,匿名语句块,程序可以使用BEGINEND复合语句来包含多个语句 DECLARE局部变量declare var_name type default value这个语句被用来声明局部变量。局部变量的作用范围在它被声明的BEGINEND块内 SELECT INTO语句select col_name into var_name table_expr该语句把选定的列直接存储到变量，因此，只有单一的行可以被取回BEGINEND复
7、合语句,IF THEN ELSE END IF IF boolean_expression1 THEN ELSIF boolean_expression2 THEN ELSE END IF; 条件为NULL与FALSE相同,流程控制语句,简单循环 LOOPIF boolean_expr THENEXIT;END IF; END LOOP;,WHILE循环 WHILE boolean_expression LOOP END LOOP;,FOR循环 FOR loop_counter IN REVERSE low_boundhigh_bound LOOP END LOOP;,存储过程和函数1. 存储
8、过程和函数是用CREATE PROCEDURE和CREATE FUNCTION语句创建的子程序。2. 过程只能用输出变量传回值，函数能返回标量值 语法1.创建过程create procedure sp_name(proc_parameter)routine_body2.创建函数create function sp_name(func_parameter)returns typeroutine_body,存储过程和函数的创建,Proc_parameterIN|OUT|INOUT param_name type Func_parameterparam_name type,PROCEDURE的参数模
9、式,IN 在调用过程的时候，实际参数的值被传递给该过程；在过程内部，形参是只可读的。 OUT 在调用过程时，任何的实参将被忽略；在过程内部，形参是只可写的。 IN OUT 是IN与OUT的组合。在调用过程的时候，实参的值可以被传递给该过程；在过程内部，形参也可以被读出也可以被写入；过程结束时，控制会返回给控制环境，而形式参数的内容将赋给调用时的实际参数。 参数的缺省模式是IN,Create procedure simpleproc(out param1 int)beginselect count(*) into param1 from t;end;,在函数的主体内部，return语句用来将控制
10、通过一个数值返回给调用环境 RETURN ; 在一个函数主体中，可以使用多个返回语句，只有一个会被执行 没有返回语句的函数将是一个错误（见ClassInfo.sql）,函数的返回语句,Create function hello(s char(20)returns char(50)return concat(Hello,s,!);/,Alter procedure 和 alter functionalter procedure|function sp_name characteristiccharacteristic:contains sql|no sql|reads sql data|modi
11、fies sql data| sql securitydefiner|invoker|comment string,修改过程和函数,drop procedure 和 drop functiondrop procedure|function if exists sp_nameif exists 字句是一个MySQL的扩展。如果程序或函数不春出，它防止发生错误,删除过程和函数,声明光标declare cursor_name cursor for select_statement可以在子程序中定义多个光标，但是一个块中的每一个光标必须有唯一的名字 光标OPEN语句和CLOSE语句open curso
12、r_name close cursor_name 光标fecth语句fetch cursor_name into var_name,var_name用指定的打开光标读取下一行,光标,游标的属性 %FOUND %NOTFOUND %ISOPEN %ROWCOUNT（当前游标的指针位移量）,create procedure curdemo() Begindeclare done int default 0;declare a char(16);declare b,c int;declare cur1 cursor for select id,data from test.t1;open cur1;repeat fetch cur1 into a,b;if not done thenend if;until done end repeat;close cur1; end,光标案例,
