未来_我来
因为渴望改变,所以必须努力

事务与存储过程

事务管理

  事务的概念

    谓的事务就是针对数据库的一组操作,它可以由一条或多条SQL语句组成,同一个事务的操作具备同步的特点,即事务中的语句要么都执行,要么都不执行。

  事务的使用

    开启事务START TRANSACTION;

    执行SQL语句

    提交事务COMMIT;

    取消事务(回滚)

  事务的提交

    事务中的操作语句都需要使用COMMIT语句手动提交,只有事务提交后其中的操作才会生效。

  事务的回滚

    如果不想提交当前事务,可使用ROLLBACK语句取消当前事务。

    ROLLBACK语句只能针对未提交的事务执行回滚操作,已提交的事务是不能回滚的。

  事务的隔离级别

    REPEATABLE READ(可重复读)

    READ UNCOMMITTED(读未提交)

    READ COMMITTED(读提交)

    SERIALIZABLE(可串行化)

事务的定义特性

  原子性

      原子性是指一个事务必须被视为一个不可分割的最小工作单元,只有事务中所有的数据库操作都执行成功,才算整个事务执行成功

  一致性

      一致性是指事务将数据库从一种状态转变为下一种一致的状态。

  隔离性

      隔离性还可以称为并发控制、可串行化、锁等,当多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。  

  持久性

      事务一旦提交,其所做的修改就会永久保存到数据库中,即使数据库发生故障也不应该对其有任何影响。

      事务的持久性不能做到100%的持久,只能从事务本身的角度来保证永久性,而一些外部原因导致数据库发生故障,如硬盘损坏,那么所有提交的数据可能都会丢失。

存储过程的创建

  什么是存储过程

    存储过程就是一条或多条SQL语句的集合,当对数据库进行一系列复杂操作时,存储过程可以将这些复杂操作封装成一个代码块,以便重复使用,大大减少数据库开发人员的工作量。

  创建存储过程

    CREATE PROCEDURE sp_name([proc_parameter])

    [characteristics…]routine_body

 

    CREATE PROCEDURE:为用来创建存储过程的关键字。

    sp_name:为存储过程的名称。

    proc_parameter:为指定存储过程的参数列表。

    characteristics:用于指定存储过程的特性。

  变量的使用

    定义

      在MySQL中,变量可以在子程序中声明,用于保存数据处理过程中的值,这些变量的作用范围在BEGIN…END程序中。

      DECLARE var_name[,varname]…date_type[DEFAULT value];

      var_name:为局部变量的名称。

      DEFAULT value:子句给变量提供一个默认值,该值可以被声明为一个常数或一个表达式。如果没有DEFAULT子句,变量的初始值为NULL。

    使用SET语句为变量赋值

      SET var_name =

      expr[,var_name = expr]…;

 

    使用SELECT…INTO为一个或多个变量赋值

      SELECT col_name[…]

      INTO var_name[…] table_expr;

 

  定义条件和处理程序

    定义条件是指事先定义程序执行过程中遇到的问题

      DECLARE condition_name CONDITION FOR [condition_type];

      // condition_type的两种形式:

      [condition_type]:

      SQLSTATE[VALUE] sqlstate_value|mysql_error_code

    处理程序定义了在程序执行过程中遇到问题时应当采取的处理方式,并且保证存储过程在遇到警告或错误时能继续执行处理过程使用DECLARE语句定义

      DECLARE handler_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:为错误处理方式,参数取值

      有3个:CONTINUE、EXIT和UNDO。

       CONTINUE:表示遇到错误不处理,继续执行;

       EXIT:遇到错误马上退出。

       UNDO:表示遇到错误后撤回之前的操作,MySQL中暂时不支持这样的操作。sp_statement:参数为程序语句段,表示在遇到定义的错误时,需要执行的存储过程。

      condition_value:表示错误类型。

  光标的使用

    在编写存储过程时,查询语句可能会返回多条记录,如果数据量非常大,则需要使用光标来逐条读取查询结果集中的记录。光标是一种用于轻松处理多行数据的机制。

    声明

      语法:DECLARE cursor_name CURSOR FOR select_statement

      示例:DECLARE cursor_student CURSOR FOR select s_name,s_gender FROM student;

    使用

      语法:OPEN cursor_name FETCH cursor_name INTO var_name[,var_name]…

      示例:FETCH cursor_student INTO s_name, s_gender;

    关闭

      CLOSE cursor_name

流程控制的使用

  存储过程中的流程控制语句用于将多个SQL语句划分或组合成符合业务逻辑的代码块,MySQL中的流程控制语句有7个

  1、 IF语句

    语法格式如下:

    IF expr_condition THEN statement_list

    [ELSEIF expr_condition THEN statement_list]

    [ELSE statement_list]

    END IF

  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_label:]LOOP

    statement_list

    END LOOP [loop_label]

    4、LEAVE语句

    语法格式如下:

    LEAVE lable

  5、ITERATE语句

    语法格式如下:

    ITERATE lable

    6、REPEAT语句

    语法格式如下:

    [repeat_lable:] REPEAT

    statement_list

    UNTIL expr_condition

    END REPEAT[repeat_lable]

  7、WHILE语句

    语法格式如下:

    [[while_lable:] WHILE expr_condition DO

    Statement_list

    END WHILE [while_lable]

存储过程的使用

  调用存储过程

    CALL sp_name([parameter[,…]])

    CALL:为调用存储过程的关键字。

    sp_name:为存储过程的名称。

    Parameter:为存储过程的参数。

  查看存储过程

    1、使用SHOW STATUS语句

      CALL sp_name([parameter[,…]])

 

    2、使用SHOW CREATE语句

      SHOW CREATE{PROCEDURE|FUNCTION} sp_name

    3、information_schema.Routines表中查看

      SELECT * FROM  information_schema.Routines

      WHERE ROUTINE_NAME='CountProc1'

      AND ROUTINE_TYPE='PROCEDURE'\G

  修改存储过程

    ALTER {PROCEDURE|FUNCTION} sp_name[characteristic…]

    sp_name:表示存储过程或函数的名称。characteristic:表示要修改存储过程的哪个部分, characteristic 的取值分为8部分。

  删除存储过程

    DROP{ PROCEDURE|FUNCTION }[IF EXISTS] sp_name

    综合案例--存储过程应用

posted on 2017-10-04 14:32  未来_我来  阅读(2548)  评论(0编辑  收藏  举报