mysql事务处理------存储过程和函数,变量和条件(异常)处理9.6更新

CodeMartain

已于 2022-05-25 23:22:56 修改

阅读量386

点赞数

分类专栏： Database_All 文章标签： mysql 数据库 sql

于 2021-09-06 10:56:23 首次发布

本文链接：https://blog.csdn.net/weixin_54061333/article/details/120118931

版权

Database_All 专栏收录该内容

62 篇文章 1 订阅

订阅专栏

mysql事务处理

存储过程和函数
Mysql中的隔离模式

存储过程和函数

存储程序可以分为存储过程和函数。在MySQL中，创建存储过程和函数使用的语句分别是CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用（引用函数名），也能返回标量值。存储过程也可以调用其他存储过程。

一句话解释存储过程/函数----------------就是提前将我们常用的一些查询存储起来,用的时候直接通过别名调用,非常的方便;

为什么要有存储过程/函数

当我们经常要查询某张表的信息的时候,不用每次都输入重复的代码,而是将该代码通过一点过方式存储起来,下次用的时候直接通过过程名调用即可;
使用存储过程将简化操作，减少冗余的操作步骤，同时，还可以减少操作过程中的失误，提高效率;

创建存储过程与调用存储过程

-- 格式----
delimiter ;;
create procedure  过程名([参数1,参数2,.......])
[存储过程特性]
begin -- 开始事务
过程内容
end ;; -- 结束事务
dilimiter ; -- 改回原来的结束符
-- 调用过程
call 过程名([参数1,参数2,....])
-- [] 内为可选内容


/*参数中的内容---- [in|out|inout] 参数名 参数类型
其中，IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；param_name表示参数名称；type表示参数的类型，该类型可以是MySQL数据库中的任意类型*/

/* 存储过程/函数的特性---
●　LANGUAGE SQL：说明routine_body部分是由SQL语句组成的，
当前系统支持的语言为SQL。SQL是LANGUAGE特性的唯一值。
●　[NOT] DETERMINISTIC：指明存储过程执行的结果是否正确。
DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。
NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。
●　{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIESSQL DATA }：指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含SQL语句，但是不包含读写数据的语句；
NO SQL表明子程序不包含SQL语句；
READS SQL DATA说明子程序包含读数据的语句；
MODIFIES SQL DATA表明子程序包含写数据的语句。
默认情况下，系统会指定为CONTAINS SQL。
●　SQL SECURITY { DEFINER | INVOKER }：指明谁有权限来执行。
DEFINER表示只有定义者才能执行。
INVOKER表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINER。
●　COMMENT 'string'：注释信息，可以用来描述存储过程或函数。
*/

不带参数的存储过程

例如以下操作---------------->>>>

-- 创建存储过程-----
mysql> delimiter /  -- 更改结束符
mysql> create procedure search() -- 此存储过程没有参数，但是后面的()仍然需要
    -> begin
    -> select * from emp ;
    -> end /
Query OK, 0 rows affected (0.04 sec)
mysql> delimiter ;  -- 改回原来的结束符
-- 调用存储过程


mysql>  call search();
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7367 | NULL   | NULL      | NULL | NULL       |    NULL |    NULL |     40 |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
15 rows in set (0.00 sec)

Query OK, 0 rows affected (0.10 sec)

练习------
– 查询当月应发的总薪资 ,因为每个月都要发,所以可以设置为过程

mysql>  create procedure salaryAll()
    -> begin
    -> select sum(sal+ifnull(comm,0)) 当月应发总薪资 from emp;
     ->  end ;;
Query OK, 0 rows affected (0.01 sec)

mysql>

mysql> call salaryall();
+----------------+
| 当月应发总薪资 |
+----------------+
|       31225.00 |
+----------------+
1 row in set (0.02 sec)

带有输入参数的存储过程

– 比如想要查询名字中带有’A’ 的员工信息

mysql> create procedure searchB(in name varchar(20))-- 设置参数 name
    ->    begin
    ->      if name is null ||name ='' then
    ->     select * from emp ;
    ->      else
    ->     select * from emp where ename like concat('%',name,'%');-- 字符拼接
    ->      end if;
    ->     end;;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> call searchB('A');;-- 传入参数
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB      | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+----------+------+------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER  | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER  | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7876 | ADAMS  | CLERK    | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK    | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
+-------+--------+----------+------+------------+---------+---------+--------+
7 rows in set (0.00 sec)

Query OK, 0 rows affected (0.09 sec)

带有输入输出参数的存储过程----

例如要查询某个职位员工的信息----

mysql> 
mysql>  delimiter ;;
CREATE PROCEDURE searchinfo ( IN jobs VARCHAR ( 20 ), OUT countnum INT )BEGINIFjobs IS NULL OR jobs = '' THENSELECT* FROMemp;ELSESELECT* FROMemp WHEREjob = jobs;END IF;select FOUND_ROWS() into countnum;END
;;
mysql>  delimiter ;
mysql> call searchinfo('clerk',@countnum) ;
+-------+--------+-------+------+------------+---------+------+--------+
| EMPNO | ENAME  | JOB   | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+--------+-------+------+------------+---------+------+--------+
|  7369 | SMITH  | CLERK | 7902 | 1980-12-17 |  800.00 | NULL |     20 |
|  7876 | ADAMS  | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL |     20 |
|  7900 | JAMES  | CLERK | 7698 | 1981-12-03 |  950.00 | NULL |     30 |
|  7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL |     10 |
+-------+--------+-------+------+------------+---------+------+--------+
4 rows in set (0.02 sec)

Query OK, 1 row affected (0.04 sec)

mysql> select @countnum ;
+-----------+
| @countnum |
+-----------+
|         4 |
+-----------+
1 row in set (0.00 sec)

创建存储函数与调用存储函数

-- 格式--
delimiter ;;
create function 函数名 ([参数1,参数2,......]) 
returns type 
[函数特性]
return 函数内容
;;

delimiter ;
 --调用
 select 存储函数名(参数....)

内容解析基本跟存储过程一样;

mysql> delimiter ;;
mysql> create function nameall()
    -> returns varchar(20)
       -> return ( select ename from emp where empno=7893)
    -> ;;
    -- 错误提示  没有函数特性来约束,
    --- 解决方式----
   -- 1,添加函数特性约束;
    -- 2 ,设置| log_bin_trust_function_creators 为ON状态
    
    
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

mysql> show variables like '%trust%';
    -> ;;
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> set global log_bin_trust_function_creators = 'on';
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;;
mysql> create  function test()
    -> returns varchar(20) -- 函数返回值类型
    -> return (select ename from emp where empno=7893)-- 执行的操作
    -> ;;
Query OK, 0 rows affected (0.03 sec)
-- 调用存储函数--
mysql> delimiter ;
mysql> select test();
+--------+
| test() |
+--------+
| NULL   |----创建存储函数时empno 值写错了
+--------+
1 row in set (0.02 sec)
-- 将表中king的empno数据改为7893,在执行
mysql> select test();
+--------+
| test() |
+--------+
| KING   |
+--------+
1 row in set (0.00 sec)
-- 这里强调一下为了安全建议不要开启log_bin_trust_function_creators ,而是为存储函数添加存储特性来解决上述ERROR 1418 (HY000):问题

添加函数存储特性----


mysql> set global log_bin_trust_function_creators = 'off';
Query OK, 0 rows affected (0.00 sec)
-- 创建存储函数
mysql> delimiter ;;
mysql> create function find()
    ->    returns varchar(20)
    ->   READS SQL DATA
    ->      return (select empno from emp where ename='KING' )
    ->      ;;
-- 调用
mysql> select find();
+--------+
| find() |
+--------+
| 7893   |
+--------+
1 row in set (0.00 sec)

如果在存储函数中的return语句跟returns返回值类型不一样,那么返回值会被强制准换为恰当的类型
如果函数返回值为varchar(20) 但是return返回的是一个整数,那么该整数会被转换成varchar类型;

举个例子----


mysql> delimiter ;;
mysql> create function find2()
    ->    returns varchar(2)
    ->   READS SQL DATA
    ->      return (select empno from emp where ename='KING' )
    ->      ;;
Query OK, 0 rows affected (0.03 sec)

mysql> delimiter ;
mysql> select find2();
ERROR 1406 (22001): Data too long for column 'find2()' at row 2

mysql> delimiter ;;
mysql> create function find5()
    ->    returns  int(2)
    ->   READS SQL DATA
    ->      return (select empno from emp where ename='KING' )
    ->      ;;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> delimiter ;
mysql> select find5();
+---------+
| find5() |
+---------+
|    7893 |
+---------+
1 row in set (0.00 sec)

可以发现int被转换换成了字符串类型,由于长度要求为2个,所以会提示ERROR 1406 (22001):错误;但是如果为int类型的即使指定显示宽度也会将内容显示出来;

小结----

指定参数为IN、OUT或INOUT只对PROCEDURE是合法的。（FUNCTION中总是默认为IN参数）。RETURNS子句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。

变量的使用

变量可以在存储过程中声明并使用，这些变量的作用范围是在BEGIN…END程序中;

定义变量----

--语法格式--
declare 变量名 变量数据类型 [default 默认值];
-- 如果没有default 则默认为该数据类型的默认值--- int 为 0 字符串为 null,类似于java中的默认值;
-- 如果为同一类型的变量,则可以写在一起最后定义数据类型
declare a ,b c int;
-- 变量值除了可以被声明为一个常数外,还可以是一个表达式;

设置变量值

-- 格式----
set  变量名 =值
set a= 10,b=30; 
set c=a+b;

还可以将select 语句查询到的值赋值到变量上;

declare id int;
declare name varchar(8);
select empno ,ename  into id ,name  from emp where empno=7893;

定义条件以及处理异常

为什么要定义条件???

定义条件的目的其实就像java中的try catch 取捕捉过程或者函数过程中可能遇到的异常;
在java中如果遇到异常而不去处理,那么程序会因为异常而中断,在mysql中也是;
举个例子------------------->>>>>

先定义一张表---->>

mysql> create table mytest (
    -> id int  primary key ,
    -> name varchar (8) not null
    -> );
Query OK, 0 rows affected (0.03 sec)

创建过程批量插入数据----->>>

-- 处理异常的过程--
mysql>  create procedure mytestin()
    ->      begin
    ->      declare mycondition condition for 1062;
    ->     declare continue handler for mycondition set @info='重复值';
    ->      insert into mytest values(1,'张三');
    ->      insert into mytest values(1,'李四');
    ->      insert into mytest values(2,'李四'),(3,'王五');
    ->     insert into mytest values(2,'李四'),(1,'赵六');
    -> end
    -> ;;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> call mytestin();;
Query OK, 1 row affected (0.00 sec)
-- 不处理异常的过程
mysql>  create procedure mytestin1()
    ->      begin
    ->      insert into mytest values(10,'张三');
    ->      insert into mytest values(10,'李四');
    ->      insert into mytest values(20,'李四'),(30,'王五');
    ->     insert into mytest values(20,'李四'),(10,'赵六');
    -> end
    -> ;;
Query OK, 0 rows affected (0.01 sec)
    -> ;;

mysql> call mytestin1();;
ERROR 1062 (23000): Duplicate entry '10' for key 'mytest.PRIMARY'
ERROR 1062 (23000): Duplicate entry '10' for key 'mytest.PRIMARY'
mysql> delimiter ;

-- 查看数据------
mysql> select * from mytest ;
+----+------+
| id | name |
+----+------+
|  1 | 张三 |
|  2 | 李四 |
|  3 | 王五 |
| 10 | 张三 |
+----+------+
4 rows in set (0.00 sec)

可以发现由于主键的非空,唯一约束,所以没有做异常处理的会提示报错,这也导致后后面的本该正常插入的 insert into mytest values(20,‘李四’),(30,‘王五’);也没法插入;
而做了异常处理的----(continue表示遇到该异常直接跳过,继续执行下条插入语句)会执行异常要求的内容;

定义条件的方式

----使用declare 语句----

declare 条件名 condition for 条件类型;
条件类型有两种表示方式---
-- 1, sqlstate 值  (该值并非随便的一个值而是跟系统底层的规定要符合)
declare 条件名 condition for sqlstate '42000';
-- 2,mysql_error_code 
declare 条件名 condition for 1148 ;
-- 1148 就是mysql_error_code所代表的值;

我们在写mysql语句时偶尔也会看到错误提示-----例如

ERROR 1062 (23000): Duplicate entry '10' for key 'mytest.PRIMARY'

sqlstate 后的值为一个长度为5 的字符串’ 42000’
my_error_code 的值为1062

定义处理层序的方式

--语法格式--
declare 处理类型 handler for  已经定义的条件名  set @info='定义的含义';

处理类型包括-:
continue---- 跳过错误(不处理错误)继续执行;
exit---- 遇到错误---->退出,也是默认的一种方式;
undo— 遇到错误撤回之前额操作----可是在mysql中不支持该操作;

create procedure test1()
 begin
 declare insertin condition for 1062;
 declare undo handler for insertin set @info='重复值';
 insert into test001 values(10);
insert into test001 values(10);
 set@info='重复值1';
 end;;
> 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'undo handler for insertin set @info='重复值';
   insert into test001 values(10' at line 4
> 时间: 0.001s

定义处理程序的几种方式-----

在这里插入图片描述总结起来就两种模式---->>>

直接定义------
declare continue handler for condition_name set @info=‘异常解析’;

先定义后调用----
declare condition_name condition for mysql_error_code ;
或者— declare condition_name condition for sqlstate ‘42000’; 调用
declare continue handler for condition_name set @info=‘异常解析’;

两种方式选一种即可;

小练习----
为了简便起见建一张简单的表----


mysql> create table test(
    -> id int primary key ,
    -> name varchar(6),
    -> age tinyint not null
    -> );
Query OK, 0 rows affected (0.05 sec)

建立过程插入数据----

mysql>  create procedure mypro()
    ->      BEGIN
    ->    declare mycondition1 condition for 1048 ;
    ->    declare mycondition2 condition for 1062 ;
    ->    declare continue handler for mycondition1 set  @info='不能为空';
    ->     declare exit handler for mycondition2 set @info ='重复值出现,退出';
    ->    insert into test values(1,'张三',22);
    ->    insert into test values(2,'李四',26);
    ->     insert into test values(3,null,26);
    ->      insert into test values(4,'王五',28);
    ->     insert into test values(2,'王尔',46);
    ->     insert into test values(8,'王二尔',56);
    ->     end
    ->     ;;

查询插入的数据----

mysql> select * from test;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | 张三 |  22 |
|  2 | 李四 |  26 |
|  3 | NULL |  26 |
|  4 | 王五 |  28 |
+----+------+-----+
4 rows in set (0.00 sec)

可以看到在insert into test values(2,‘王尔’,46);时由于重复值出现,处理方式为退出,所以 insert into test values(8,‘王二尔’,56);并没有插入表格;

Mysql中的隔离模式

在说隔离模式之前，要了解什么是事务，

所谓事务就是一次有限的数据操作;包括insert update delete;

数据库中会出现什么事务呢?

脏读

在这里插入图片描述

不可重复读
幻读

总结：

脏读,不可重复读是修改或者删除，幻读是插入。

无论是脏读，还是不可重复读，还是幻读，它们都是数据库的读一致性的问题，都是在一个事务里面前后两次读取出现了不一致的情况。

针对以上情况,有对应的解决方案-----事物的隔离级别

事物的隔离级别

Read Uncommitted（未提交读）

一个事务可以读取到其他事务未提交的数据，会出现脏读，所以叫做 RU，它没有解决任何的问题。

Read Committed（已提交读）

一个事务只能读取到其他事务已提交的数据，不能读取到其他事务未提交的数据，它解决了脏读的问题，但是会出现不可重复读的问题。

Repeatable Read（可重复读）

它解决了不可重复读的问题，也就是在同一个事务里面多次读取同样的数据结果是一样的，但是在这个级别下，没有定义解决幻读的问题。

Serializable（串行化）

在这个隔离级别里面，所有的事务都是串行执行的，也就是对数据的操作需要排队，已经不存在事务的并发操作了，所以它解决了所有的问题。

这个就类似于redis的单线程,一次只能处理一个事务,其他的要排队;
在这里插入图片描述

如何让解决数据一致性的问题---->>
两中解决方案—
1,读加锁

即我读取数据的时候不允许别人修改;

但是实际情况中一般是读的次数多,写的次数少,如果读的时候加锁,那么会严重影响数据操作的效率;

- 2,读备份
就是读取的时候一直是原数据的副本,这就能够保持前后读取的数据一致;

这有些像CopyOnWriteList

CodeMartain

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
mysql事务处理------存储过程和函数,变量和条件(异常)处理9.6更新

-- 创建存储过程-----mysql> delimiter /mysql> create procedure search() -> begin -> select * from emp ; -> end ; -> /Query OK, 0 rows affected (0.04 sec)-- 调用存储过程mysql> delimiter ;mysql> call search();+-------+---
复制链接

扫一扫