首页 > 课堂 > 基础知识 > 正文

事务与存储过程

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;

(编辑:武林网)

发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表