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> delimiter ;;
mysql> create procedure salaryAll(out param double)
    -> begin
    -> select sum(sal+ifnull(comm,0)) 当月应发总薪资 from emp;
    -> end ;;
Query OK, 0 rows affected (0.05 sec)
mysql> delimiter ;

mysql> call salaryall(@sal);-- 这里参数是一个变量,
+----------------+
| 当月应发总薪资 |
+----------------+
|       31225.00 |
+----------------+
1 row in set (0.02 sec)

mysql> call salaryall(@empno);----调用无关列,但是参数类型相同
+----------------+
| 当月应发总薪资 |
+----------------+
|       31225.00 |
+----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> call salaryall(@ename);----调用无关列,但是参数类型不同
+----------------+
| 当月应发总薪资 |
+----------------+
|       31225.00 |
+----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 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;