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;