第六章
事务管理:
保证在同一事务中的操作具有同步性
开启事务: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;