MySQL基础-19 存储过程和函数

存储过程和函数

1 变量

分类：

• 系统变量
  • 全局变量【作用域：服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话（连接）有效，但不能跨重启】
  • 会话变量【作用域：仅仅针对于当前会话有效】
• 自定义变量
  • 用户变量
  • 局部变量

1.1 系统变量

说明，变量由系统提供，属于服务器层面
使用的语法：

1. 查看所有的系统变量

SHOW GLOBAL VARIABLES; 
SHOW session VARIABLES; 

2. 查看满足条件的部分系统变量

show global|session variables like '%char%';

3. 查看指定的某个系统变量的值
   select @@global|session.系统变量名;

4. 为某个系统变量赋值
   方法一：
   set global|session 系统变量名=值；
   方法二：
   set @@global|session.系统变量名=值；

注意：
如果是全局级别，则需要加global，如果是会话级别，则需要加session，如果不写，则默认为session。

1.2 自定义变量

使用步骤：
声明
赋值
使用

1.2.1 用户变量

作用域：针对于当前会话有效

1. 声明并初始化
   方法一：
   set @用户变量名=值；
   方法二：
   set @用户变量名:=值；
   方法三：
   select @用户变量名:=值；

2. 赋值
   方法一：
   set @用户变量名=值；
   set @用户变量名:=值；
   select @用户变量名:=值；
   方法二：
   select 字段 into @变量名
   from 表；

3. 使用
   查看用户变量的值
   select @用户变量名；

例如：

#声明并初始化
set @count=1;
#赋值
select count(*) into @count
froom employees;
#查看
select @count;

1.2.2 局部变量

作用域：仅仅在定义在它的begin end 中有效
应用在begin end 的第一句

1. 声明
   declare 变量名 类型;
   declare 变量名 类型 default 值;

2. 赋值
   方法一：
   set 局部变量名=值；
   set 局部变量名:=值；
   select @局部变量名:=值；
   方法二：
   select 字段 into 局部变量名
   from 表；

3.使用
select 局部变量名；

小结

	作用域	定义和使用的位置	语法
用户变量	当前会话	会话中的任何位置	必须加@符号，不用限定类型
局部变量	begin end 中	只能在begin end 中，且为第一句话	一般不用加@符号，需要限定类型

案例

1. 声明两个变量并赋初始值，求和，并打印
   用户变量

set @m=1;
set @n=2;
set @sum=@m+@n;
select @sum;

局部变量（必须放在begin end 中 使用）

declare m int default 1;
declare n int default 2;
declare sum int;
set sum=m+n;
select sum;

2 存储过程

含义：一组预先编译好的sql语句的集合，理解成批处理语句
好处;

1. 提高代码的重用性
2. 简化操作
3. 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率。

2.1 语法

2.1.1 创建

create procedure 存储过程名（参数列表）
begin
存储过程体（一组合法的sql 语句）
end

注意：

1. 参数列表包含三部分
   参数模式 参数名 参数类型
   举例：
   in stuname varchar(20)

参数模式：

• in ：该参数可以作为输入，也就是需要调用方传入值
• out ：该参数可以作为输出，也就是该参数可以做返回值
• inout ：该参数既可以作为输入又可以作为输出

2. 如果存储过程体仅仅只有一句话，begin end 可以省略
   存储过程体的每条sql 语句的结尾要求必须加分号
   存储过程的结尾可以使用delimiter 重新设置
   delimiter 结束标记 例如 delimiter $

2.1.2 使用

call 存储过程名（实参列表）;

2.1.3 案例

1. 空参列表
   插入到admin 表中五条记录

mysql> use girls;
Database changed
mysql> DELIMITER $
mysql> CREATE PROCEDURE myp1()
    -> BEGIN
    -> INSERT INTO admin(`username`,`password`)
    -> VALUES ('lily','0000'),('rose','0000'),('jack','0000'),('bob','0000'),('tom','0000');
    ->
    -> END $
Query OK, 0 rows affected (0.01 sec)

mysql> call myp1()$
Query OK, 5 rows affected (0.00 sec)

mysql> select * from admin$
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | john     | 8888     |
|  2 | lyt      | 6666     |
|  3 | lily     | 0000     |
|  4 | rose     | 0000     |
|  5 | jack     | 0000     |
|  6 | bob      | 0000     |
|  7 | tom      | 0000     |
+----+----------+----------+
7 rows in set (0.00 sec)

2. 创建带 in 模式参数的存储过程
   根据女神名，查询对应的男神信息

mysql> CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
    -> BEGIN
    -> SELECT bo.*
    -> FROM boys bo
    -> RIGHT JOIN `beauty` b ON bo.id=b.`boyfriend_id`
    -> WHERE b.name=beautyName;
    -> END $
Query OK, 0 rows affected (0.00 sec)

mysql> set names gbk$
Query OK, 0 rows affected (0.00 sec)

mysql> call myp2('Angelababy')$
+------+---------+--------+
| id   | boyName | userCP |
+------+---------+--------+
|    3 | 黄晓明        |     50 |
+------+---------+--------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

3. 创建存储过程实现，用户是否登录成功

mysql> CREATE PROCEDURE myp3(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
    -> BEGIN
    -> DECLARE result INT DEFAULT 0;#声明并初始化
    ->
    -> SELECT COUNT(*) INTO result #赋值
    -> FROM admin
    -> WHERE admin.`username`=username AND admin.`password`=PASSWORD;
    ->
    -> SELECT IF(result>0,'成功','失败'); #使用
    -> END $
Query OK, 0 rows affected (0.00 sec)

mysql> CALL myp3('张飞','9999')$
+----------------------------+
| IF(result>0,'成功','失败')       |
+----------------------------+
| 失败                          |
+----------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CALL myp3('bob','0000')$
+----------------------------+
| IF(result>0,'成功','失败')       |
+----------------------------+
| 成功                          |
+----------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

4. 带out模式参数的存储过程
   根据女神名，返回对应的男神名

mysql> CREATE PROCEDURE myp4(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
    -> BEGIN
    -> SELECT bo.`boyName` INTO boyName
    -> FROM boys bo
    -> RIGHT JOIN `beauty` b ON bo.id=b.`boyfriend_id`
    -> WHERE b.name=beautyName;
    -> END $
Query OK, 0 rows affected (0.00 sec)

mysql> CALL myp4('Angelababy',@bName)$
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @bName$
+--------+
| @bName |
+--------+
| 黄晓明       |
+--------+
1 row in set (0.00 sec)

5. 根据女神名，返回对应的男神名和男神魅力值

mysql> CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)
    -> BEGIN
    -> SELECT bo.`boyName`,bo.`userCP` INTO boyName,userCP
    -> FROM boys bo
    -> RIGHT JOIN `beauty` b ON bo.id=b.`boyfriend_id`
    -> WHERE b.name=beautyName;
    -> END $
Query OK, 0 rows affected (0.00 sec)

mysql> CALL myp5('Angelababy',@bName,@userCP)$
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @bName,@userCP$
+--------+---------+
| @bName | @userCP |
+--------+---------+
| 黄晓明       |      50 |
+--------+---------+
1 row in set (0.00 sec)

6. 带 inout 模式参数的存储过程
传入a ，b ,最终a和b都翻倍并返回

mysql> CREATE PROCEDURE myp6(INOUT a INT,INOUT b INT)
    -> BEGIN
    -> SET a=a*2;
    -> SET b=b*2;
    ->
    -> END $
Query OK, 0 rows affected (0.00 sec)

mysql> SET @m=10$
Query OK, 0 rows affected (0.00 sec)

mysql> SET @n=20$
Query OK, 0 rows affected (0.00 sec)

mysql> CALL myp6(@m,@n)$
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @m,@n$
+------+------+
| @m   | @n   |
+------+------+
|   20 |   40 |
+------+------+
1 row in set (0.00 sec)

2.1.4 删除存储过程

drop procedure 存储过程名

2.1.5 查看存储过程信息

show create procedure 存储过程名

3 函数

与存储过程的区别：
存储过程：可以由0个返回，也可以有多个返回，适合做批量插入、批量更新。
函数：有且仅有1个返回，适合做处理数据后返回一个结果。

3.1 创建语法

create function 函数名（参数列表）returns 返回类型
begin
函数体
end

注意：

1. 函数列表包含两部分
   参数名 参数类型
2. 函数体：肯定会有return 语句，如果没有会报错
   如果return 语句没有放在函数体的最后也不会报错，但不建议
   return 值;
3. 函数体中仅有一句话，则可以省略begin end ;
4. 使用 delimiter 语句设置结束标记。

3.2 调用语法

select 函数名（参数列表）

3.3 案例演示

1. 无参有返回
   返回公司的员工个数

mysql> use myemployees$
Database changed
mysql> CREATE FUNCTION myf1() RETURNS INT
    -> BEGIN
    -> DECLARE c INT DEFAULT 0;
    -> SELECT COUNT(*) INTO c
    -> FROM employees;
    -> RETURN c;
    -> END $
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SELECT myf1()$
+--------+
| myf1() |
+--------+
|    107 |
+--------+
1 row in set (0.00 sec)

2. 有参有返回
   根据员工名，返回他的工资

mysql> CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
    -> BEGIN
    -> SET @sal=0;
    -> SELECT salary INTO @sal
    -> FROM employees
    -> WHERE last_name=empName;
    -> RETURN @sal;
    -> END $
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SELECT myf2('kochhar')$
+-----------------+
| myf2('kochhar') |
+-----------------+
|           17000 |
+-----------------+
1 row in set (0.00 sec)

3. 根据部门名，返回部门的平均工资

mysql> CREATE FUNCTION myf4(depName VARCHAR(20)) RETURNS DOUBLE
    -> BEGIN
    -> DECLARE sal DOUBLE;
    -> SELECT AVG(salary) INTO sal
    -> FROM employees e
    -> JOIN `departments` d ON e.`department_id`=d.department_id
    -> WHERE d.`department_name`=depName;
    -> RETURN sal;
    -> END $
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT myf4('IT') $
+------------+
| myf4('IT') |
+------------+
|       5760 |
+------------+
1 row in set (0.00 sec)

3.4 查看

show create function 函数名;

3.5 删除

drop function 函数名;