procedure and function
==================================================
一、概述：
存储过程 和 函数，是事先经过编译并存储在数据库中的 一段SQL语句的集合。
存储过程 和 函数的区别：
• 函数必须有返回值，而 存储过程没有。
• 存储过程的参数可以是 IN、OUT、INOUT 类型， 函数的参数只能是 IN
• 系统提供了大量的 既定 函数 供我们使用。 《参见附录，Mysql 函数大全》
优点：
• 存储过程只在创建时进行编译；
而SQL语句每执行一次就编译一次，
所以使用存储过程可以提高数据库执行速度。
• 简化复杂操作，结合事务一起封装。
• 复用性好
• 安全性高，可指定存储过程的使用权。
说明：
并发量少的情况下，很少使用存储过程。
并发量高的情况下，为了提高效率，用存储过程比较多。
二、创建与调用 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
创建存储过程语法 ：
create procedure sp_name(参数列表)
[特性...]过程体
存储过程的参数形式：[IN | OUT | INOUT]参数名 类型
IN 输入参数
OUT 输出参数
INOUT 输入输出参数
delimiter $$ ##SQL ; 号 ==⇒ $$
create procedure 过程名 (形式参数列表)
begin
SQL语句1;
SQL语句2;
SQL语句3;
end $$
delimiter ;
调用：
call 存储过程名(实参列表)
存储过程 三种参数类型：IN, OUT, INOUT
===================NONE========================
mysql> \d $$
mysql> create procedure p1()
-> begin
-> select count(user) from mysql.user; ##聚合函数 count (字段名)，统计当前用户的数量
-> end$$
Query OK, 0 rows affected (0.51 sec)
mysql> \d ;
mysql> call p1(); ### 调用存储过程。
mysql> create table db_class.t1(id int,cc varchar(100));
mysql> \d $$
mysql> create procedure autoinsert1()
BEGIN
declare i int default 1;
while(i<=20000) do insert into db_class.t1 values(i,md5(i));
set i=i+1;
end while;
END
$$
mysql> \d;
#是声明的意思，就是声明变量的，例如：
#declare v_number number(10);
#这个一般是用在函数和存储过程中的.
====================IN 可以指定输入参数 交互式 ==========================
mysql> \d $$
mysql> create procedure AUTO2(IN a int)
BEGIN
declare i int default 1; ## i 为局部变量定义。
while(i<=a) do insert into school.t1 values(i,md5(i));
set i=i+1;
end while;
END$$
Query OK, 0 rows affected (0.00 sec)
mysql> call autoinsert1(10);
Query OK, 1 row affected (1.10 sec)
# Mysql 设置用户会话变量，退出登录后消失，对应的是全局变量（一般都在配置文件定义 ） =============
mysql> set @num=20; ## 定义一个用户会话变量
mysql> select @num; ## 调用一个用户会话变量
+------+
| @num |
+------+
| 20 |
+------+
1 row in set (0.00 sec)
mysql> call autoinsert1(@num);
================== OUT 将某些结果，输出到指定的参数中 =======================
mysql> delimiter $$
mysql> CREATE PROCEDURE p2 (OUT param1 INT)
-> BEGIN
-> SELECT COUNT(user) INTO param1 FROM mysql.user;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter;
mysql> select @a; ## 证明当前没有对 a 变量进行任何定义，a 变量是个空值
+------+
| @a |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
mysql> CALL p2(@a); ## 在这里 @a 将接受 OUT 出来的 param1 的值，也就是 sql语句中的 count（）的值。
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @a;
+------+
| @a |
+------+
| 4 |
+------+
================= IN 和 OUT =====================
作用：统计指定部门的员工数
mysql> delimiter $$
mysql> create procedure count_num(IN p1 varchar(50), OUT p2 int)
-> BEGIN
-> select count(*) into p2 from company.employee5 where post=p1;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> \d ;
mysql> call count_num('hr',@a);
mysql>select @a;
练习1： 输入一个课程的代码，存储过程自动帮你输出，选择了该课程的 学生名单。
MariaDB [myclass]> \d$$
MariaDB [myclass]> CREATE PROCEDURE S1(IN p1 INT) BEGIN SELECT students.`姓名` FROM students WHERE 所选课程=p1;END$$
 |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
练习2： 写一个存储过程，实现， 我们输入一个工资的值， 帮我们返回 薪资水平，
大于您所输入的 值的 同学名字，以及 薪资。
MariaDB [myclass]> \d$$
MariaDB [myclass]> CREATE PROCEDURE s-saraly(IN s INT)
BEGIN
SELECT students.`姓名`,students.`就业薪资` FROM students WHERE students.`就业薪资`>s;
END
MariaDB [myclass]>$$
MariaDB [myclass]>\d ;
练习3 ：统计指定部门工资超过 5000 的总人数
mysql> create procedure count_num(IN p1 varchar(50), IN p2 float(10,2), OUT p3 int)
-> BEGIN
-> select count(*) into p3 from employee where post=p1 and salary=>p2;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> \d ;
mysql> call count_num('hr',5000,@a);
mysql>select @a;
练习： 输入一个课程名称，统计选择了该课程的同学，工资超过12000的人的姓名和薪资。
================= INOUT 作了解======================
mysql> create procedure proce_param_inout(inout p1 int)
-> begin
-> if (p1 is not null) then
-> set p1=p1+1;
-> else
-> select 100 into p1;
-> end if;
-> end$$
Query OK, 0 rows affected (0.00 sec)
mysql> select @h; ## h 用户变量当前没有值
+------+
| @h |
+------+
| NULL |
+------+
1 row in set (0.00 sec)
mysql> call proce_param_inout(@h); ### 根据判断，@h =100
Query OK, 1 row affected (0.00 sec)
mysql> select @h;
+------+
| @h |
+------+
| 100 |
+------+begin
1 row in set (0.00 sec)
mysql> call proce_param_inout(@h); ##此时 @h 已经=100
Query OK, 0 rows affected (0.00 sec)
mysql> select @h; ### 经过判断， @h=@h+1 = 101
+------+
| @h |
+------+
| 101 |
+------+
1 row in set (0.00 sec)
存储过程与函数的维护：#########################################
MariaDB [myclass]> show create procedure `S-Saraly` \G
MariaDB [myclass]> SHOW PROCEDURE STATUS\G ## 查询所有的存储过程。
MariaDB [myclass]> DROP PROCEDURE `S-Saraly`;
存储过程与函数的区别：==============================
1、存储过程，支持 in out inout 的定义， 函数只能支持 in
2、存储过程不需要定义返回值，函数需要。
3、存储支持 DDL ，函数中，数据和表格的 删除，修改 无法支持。
存储相对可以支持更加复杂的业务的 应用。
4、存储过程里面可以调用函数， 函数中不能调用存储过程。
5、函数需要 用 return 语句控制返回值， 存储过程不需要。
6、调用方式，函数 select 存储过程 call
FUNCTION 函数 ==========================================
创建函数的语法：
delimiter $$
create function 函数名(参数列表) returns 返回值类型
begin
有效的SQL语句
end$$
delimiter ;
调用：
select 函数名(参数)
delimiter $$
create function fun1(str char(20)) returns char(50)
return concat("hello",str,"!");
$$
delimiter ;
select fun1( 'function');
=================================================
mysql> CREATE FUNCTION hello(s CHAR(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)
mysql> select hello('tianyun');
+------------------+
| hello('tianyun') |
+------------------+
| Hello, tianyun! |
+------------------+
root@(company)> select hello('tianyun') return1;
+-----------------+
| return1 |
+-----------------+
| Hello, tianyun! |
+-----------------+
1 row in set (0.00 sec)
## 函数输入一个员工的编号，函数输出他的姓名。
mysql> create function name_from_employee(x int) returns varchar(50)
-> BEGIN
-> return (select emp_name from employee where emp_id=x);
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> select name_from_employee(3);
mysql> select * from employee where emp_name=name_from_employee(1);
+--------+----------+------+------------+------------+-----------------+---------+--------+--------+
| emp_id | emp_name | sex | hire_date | post | job_description | salary | office | dep_id |
+--------+----------+------+------------+------------+-----------------+---------+--------+--------+
| 1 | jack | male | 2013-02-02 | instructor | teach | 5000.00 | 501 | 100 |
+--------+----------+------+------------+------------+-----------------+---------+--------+--------+
1 row in set (0.00 sec)