第六章
事务管理：
保证在同一事务中的操作具有同步性
开启事务：starttransaction;
sql操作语句：updateaccount set money=money-100 where name=’a’;
update account set money=money+100 where name=’b’;【转账】
回滚操作：rollback;【假设有一条sql语句执行失败，则整个事务执行失败，可回滚】
提交事务：commit;
事务的隔离级别：
readuncommitted:读未提交，该级别下的事务可以读取到另一个事务中未提交的数据，脏读
read committed:读提交，该级别的事务只能读取其他事务已提交的数据，避免脏读，不能避免重复读【读取过程中修改数据】和幻读【读取过程中有事务添加数据记录】，大多数的数据库管理系统的默认隔离级别都是这个【Oracle】
repeatable read:可重复读，可以避免脏读、不可重复读的问题，确保同一事务的多个实例在并发读取数据时，会看到共同的数据行。理论上会出现幻读，但是mysql的存储引擎通过多版本并发控制机制解决了该问题，也就避免了幻读【mysql的默认事务隔离级别】
serializable:可串行化，是事务的最高隔离级别，它会强制对事务进行排序，也就是加锁，但是会造成大量的超时现象和锁竞争
set sessiontransaction isolation level 隔离级别;
存储过程：
创建存储过程：
createprocedure sp_name([proc_parameter])
[characteristics…]routine_body
createprocedure:创建存储过程
sp_name:存储过程的名称
proc_parameter:指定存储过程的参数列表
参数列表形式：[ in |out |inout ]param_name type
in:输入参数
out:输出参数
inout:输入输出参数
param_name:参数名称
type:参数类型
characteristics:用于指定存储过程的特性
1、 language sql:routine_body部分是由sql语句组成的
2、 [not] deterministic:指明存储过程执行的结果是否确定，deterministic表示结果是确定的。执行存储过程时，相同输入有相同输出；[not] deterministic相反，默认deterministic
3、 {contains sql | no sql | reads sql data | modifies sql data }:指明子程序使用sql语句的限制。containsql表明子程序包含sql语句，但是不包含读写数据的语句；no sql表明子程序不包含sql语句；reads sql data说明子程序包含读写数据的语句；modifies sql data 表明子程序包含写数据的语句。默认contains sql
4、 sql security{definer | invoker }:指明谁有权利来执行，definer表示只有定义者才能执行，invoker表示拥有权限的调用者可以执行，默认definer
5、 comment’string’:注释信息，用来描述存储过程
routine_body:sql代码的内容，可以用begin…end来表示sql代码的开始和结束
创建一个查看student表的存储过程：
delimiter //
create procedure Proc()
begin
selecter * from student;
end //
用delimiter //的作用是将//作为mysql的结束符，防止与MySQL的结束符分号;冲突
变量的使用：
定义变量：
declarevar_name[,varname]…date_type[default value];
var_name:局部变量的名称
defaultvalue:给变量提供一个默认值
declaremyvariable int default 100;【定义一个名为myvariable的变量，类型int，默认值100】
可为变量赋值改变其默认值：
setvar_name=expr[,var_name=expr]…;
declarevar1,var2,var3 int;
setvar1=10,var2=20;
setvar3=var1+var2;
可用select…into为一个或者多个变量赋值：
select clo_name[…] into var_name[…]table_expr;
declares_grade float;
declare s_gender char(5);
select grade,gender into s_grade,s_gender from srudent where name=’rose’;
【将student表中的rose同学的成绩和性别分别存储到s_grade和s_gender中】
定义条件和处理程序：
定义条件：
declarecondition_name condition for [condition_type];
//condition_type 的两种形式
[condition_type];
sqlstate[value] sqlstate_value| mysql_error_code
sqlstate_value:长度为5的字符串类型错误代码
mysql_error_code:数值类型的错误代码
conditidon_name:表示所定义的条件的名称
condition_type:表示条件的类型(都可以表示mysql的错误)
error1142(42000)中，sqlstate_value的值是42000，mysql_error_code的值是1142
定义“error1148(42000)”错误，名称为command_not_allow，
declarecommand_not _allow condition for sqlstate ‘42000’;
declarecommand_not_allow condition for 1148;
定义处理程序：
declarehandler_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:错误处理方式，参数取三个值
continue:表示遇到错误不处理，继续执行
exit:表示遇到错误马上退出
undo:表示遇到错误后撤回之前的操作【mysql中暂时不支持这样的操作】
sp_statement:程序语句段，表示在遇到定义的错误时，需要执行的存储过程
condition_value表示错误类型，可以有以下取值：
sqlstate[value]sqlstate_value包含5个字符的字符串错误值
1、 condition_name表示declare condition定义的错误条件名称
2、 sqlwarning匹配所有以01开头的sqlstate错误代码
3、 not found匹配所有以02开头的sqlstate错误代码
4、 sqlexception匹配所有没有被sqlwarning或not found捕获的sqlstate错误代码
5、 mysql_error_code匹配数值类型错误代码
定义处理程序的几种方式：
//方法一：捕获sqlstate_value
declare continue handler for sqlstate ‘42000’set @info=’no_such_table’;
//方法二：捕获mysql_error_code
declare continue handler for 1146 set@info=’no_such_table’;
//方法三：先定义条件，然后调用
declare no_such_table condition for1146;
declare continue handler forno_such_table set @infp=’error’;
//方法四：使用sqlwarning
declare exit handler for sqlwarning set@info=’error’
//方法五：使用 not found
declareexit handler for not found set @info=’no_such_table’;
//方法六：使用sqlexcaption
declareexit handler for sqlexecp set @infp=’error’;
定义条件和处理程序：
createtable chapter6.t(s1 int primary key);
delimiter//
createprocedure demo()
begin
declarecontinue handler for sqlstate ‘23000’ set @x=1;
set@x=1;
insertinto chapter6.t values(1);
set@x=2;
insertinto chapter6.t values(1);
set@x=3;
end;//
delimiter;
/*调用存储过程*/
calldemo();
select@x;
【补充一下，不知道为什么，我的必须要用//才能结束，不然始终不会执行】
@x是一个用户变量，使用set语句为其赋值，用户变量与连接有关，一个客户端定义的变量不能被其他客户端看到或使用。当客户端退出时，该客户连接的所有变量将自动释放。
光标的使用：
声明：
declarecursor_name cursor for select_statement;
cursor_name:光标的名称
select_statement:select语句返回的内容，返回一个用于创建光标的结果集
declarecursor_student cursor for select s_name,s_gender from student;
使用：
opencursor_name
fetchcursor_name into var_name[,var_name]…【into说明一切，赋值用的】
fetchcursor_student into s_name,s_gender;
关闭：
closecursor_name;
流程控制的使用：
if语句：
ifexpr_condition then statement_list
[elseif condition then statement_list]
[elsestatement_list]
endif
expr_condition：表示判断条件
statement_list：表示SQL语句列表，他可以包括一个或者多个语句
ifval is null
thenselect ‘val is null’;
elseselect ‘val is not null’;
endif;
case语句：
casecase_expr
whenwhen_value then statement_list
whenwhen_value then statement_list…
[elsestatement_list]
endcase;
case_expr：表示条件判断的表达式
when_value：表示表达式可能的值
caseval
when1 then select ‘val is 1’
when2 then select ‘val is 2’
elseselect ‘val is not 1 or 2’
endcase;
case语句的第二格式：
case
whenexpr_condition then statement_list
whenexpr_condition then statement_list
[elsestatement_list]
endcase;
loop语句：
[loop_label:]loop
statement_list
end loop[loop_lable]
loop_lable：表示loop语句的标注名称，可以省略
declare id intdefault 0;
add_loop:loop
set id=id+1;
if id>=10 then leave add_loop;
end if;
end loopadd_loop;
leave语句：用于退出任何被标注的流程控制构造
leavelabel
iterate语句：用于将执行顺序转到语句段的开头处
iteratelabel
createprocedure doiterate()
begin
declarep1 int default 0;
my_loop:loop
setp1=p1+1;
ifp1<10 then iterate my_loop;
elseifp1.20 then leave my_loop;
endif;
select ‘p1 is between 10 and 20’;
endloop my_loop;
end;
repeat语句：
[repeat_label:]repeat
statement_list
untilexpr_condition
endrepeat[repeat_label]
declareid int default 0;
repeat
setid=id+1;
untilid>=10;
endrepeat;
while语句：为真则执行
[while_label:]while expr_condition do
statement_list
endwhile [while_label]
declareI int default 0;
whilei<10 do
seti=i+1;
endwhile;
存储过程的使用：
调用存储过程：
callsp_name ([parameter[,…]])
定义存储过程：
delimiter//
createprocedure CountProcl(in s_gender varchar(50),out num int)
begin
selectcount(*) into num from student where gender=s_gender;
end//
delimiter;
调用存储过程：
callCountProcl(“女”,@num);
查看返回结果：
select@num;
查看存储过程：
1、 show status 语句查看存储过程的状态：
show {procedure| function } status [like ‘pattern’ ]【返回子程序的特征，如数据库、名字、类型、创建者及创建、修改日期】
show procedurestatus like’C%’ \G
2、 show create语句查看存储过程的状态：
show create{procedure | function } sp_name【返回以一个可用来重新创建已命名子程序的确切字符串】
show createprocedure chapter6.Countprocl \G
3、从information_schema.Rountines表中查看存储过程的信息：
在mysql中存储过程和函数的信息存储在information_schema数据库下的Routines表中。
select * from information_schema.Routines
whereroutine_name=’CountProcl’ and routinetype=’procedure’ \G
修改存储过程：
alter{procedure | function } sp_name [characteristic…]
sp_name：表示存储过程或函数的名称
characteristic:表示要修改存储过程的哪个部分
containssql：表示子程序包含sql语句，但不包含读或写数据的语句
nosql：表示子程序不包含sql语句
readssql data：表示子程序中包含读数据的语句
modifiessql data：表示子程序中包含写数据的语句
sqlsecurity { definer | invoker }：指明谁有权限来执行
definer：表示只有定义者自己才能够执行
invoker：表示调用者可以执行
comment’string’：表示注释信息
alterprocedure Countprocl modifies sql data sql security invoker;【修改存储过程Countprocl的定义】
删除存储过程：
drop{ procedure | function } [if exists] sp_name
sp_name:表示要移除的存储过程的名称
ifexists:表示如果程序不存在，它可以避免发生错误，产生一个警告，警告可用show warnings进行查询
dropprocedure CountProcl;
157
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
