sqlmysql
11. 尚硅谷MySQL存储过程和函数.pdf

变量

分类
系统变量
全局变量：作用域：针对于所有会话（连接）有效，但不能跨重启(重启mysql恢复默认)
会话变量：作用域：针对于当前会话（连接）有效
自定义变量
用户变量：作用域：针对于当前会话（连接）有效，作用域同于会话变量
局部变量：作用域：仅仅在定义它的begin end块中有效

系统变量

变量由系统定义，不是用户定义，属于服务器层面
注意
全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认会话级别
使用步骤

  1. 查看所有系统变量

**show global|[session] variables;**

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

**show global|[session] variables like '%char%';**

  1. 查看指定的系统变量的值

**select @@global|[session].系统变量名;**

  1. 为某个系统变量赋值

方式一
**set global|[session] 系统变量名=值;**
方式二
**set @@global|[session].系统变量名=值;**

  1. #1》全局变量
  2. #作用域：针对于所有会话（连接）有效，但不能跨重启(重启mysql恢复默认)
  4. #①查看所有全局变量
  5. SHOW GLOBAL VARIABLES;
  6. #②查看满足条件的部分系统变量
  7. SHOW GLOBAL VARIABLES LIKE '%char%';
  8. #③查看指定的系统变量的值
  9. SELECT @@global.autocommit;
  10. #④为某个系统变量赋值
  11. SET @@global.autocommit=0;
  12. SET GLOBAL autocommit=0;
  14. #2》会话变量
  15. #作用域：针对于当前会话（连接）有效
  17. #①查看所有会话变量
  18. SHOW SESSION VARIABLES;
  19. #②查看满足条件的部分会话变量
  20. SHOW SESSION VARIABLES LIKE '%char%';
  21. #③查看指定的会话变量的值
  22. SELECT @@autocommit;
  23. SELECT @@session.tx_isolation;
  24. #④为某个会话变量赋值
  25. SET @@session.tx_isolation='read-uncommitted';
  26. SET SESSION tx_isolation='read-committed';

自定义变量

变量由用户自定义，而不是系统提供的
使用步骤

  1. 声明
  2. 赋值
  3. 使用（查看、比较、运算等） ```sql

    1》用户变量

    作用域：针对于当前会话（连接）有效，作用域同于会话变量

赋值操作符：=或:=

①声明并初始化

SET @变量名=值; SET @变量名:=值; SELECT @变量名:=值;

②赋值（更新变量的值）

方式一：

  1. SET @变量名=值;
  2. SET @变量名:=值;
  3. SELECT @变量名:=值;

方式二：

  1. SELECT 字段 INTO @变量名 FROM 表; #将查出来的一个值赋值给@变量名

③使用（查看变量的值）

SELECT @变量名;

2》局部变量

作用域：仅仅在定义它的begin end块中有效

应用在 begin end中的第一句话

①声明

DECLARE 变量名 类型 【DEFAULT 值】;

②赋值（更新变量的值）

方式一：

  1. SET 局部变量名=值;
  2. SET 局部变量名:=值;
  3. SELECT @局部变量名:=值;

方式二：

  1. SELECT 字段 INTO 局部变量名 FROM 表;

③使用（查看变量的值）

SELECT 局部变量名;

案例：声明两个变量，求和并打印

用户变量

SET @m=1; SET @n=1; SET @sum=@m+@n; SELECT @sum;

局部变量

DECLARE m INT DEFAULT 1; DECLARE n INT DEFAULT 1; DECLARE SUM INT; SET SUM=m+n; SELECT SUM;

  1. 用户变量和局部变量的对比
  3. |  | **作用域** | **定义位置** | **语法** |
  4. | --- | --- | --- | --- |
  5. | **用户变量** | **当前会话** | **会话的任何地方** | **加@符号，不用指定类型** |
  6. | **局部变量** | **定义它的BEGIN END中** | **BEGIN END的第一句话** | **一般不用加@,需要指定类型** |
  9. ---
  11. <a name="ww4gr"></a>
  12. ## 存储过程和函数
  13. 存储过程和函数类似于java中的方法<br />好处
  15. 1. 提高代码的重用性
  16. 1. 简化操作
  17. <a name="4mI2b"></a>
  18. ### 存储过程
  19. 含义<br />一组预先编译并存储在数据库的SQL语句的集合，理解成批处理语句<br />好处
  21. 1. 提高代码的重用性
  22. 1. 简化操作
  23. 1. 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
  24. <a name="8rSZ3"></a>
  25. #### 创建存储过程
  26. 语法<br />`**CREATE PROCEDURE 存储过程名 (参数列表)**`<br />`**BEGIN **`<br />`**存储过程体（一组合法的SQL语句）**`<br />`**END**`<br />注意
  28. 1. 参数列表包含三部分
  30. **参数模式  参数名  参数类型**<br />举例<br />in stuname varchar(20)<br />参数模式
  32. - **in**：该参数可以作为输入，也就是该参数需要调用方传入值
  33. - **out**：该参数可以作为输出，也就是该参数可以作为返回值
  34. - **inout**：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值
  35. 2. 如果存储过程体仅仅只有一句话，begin end可以省略
  36. 2. 存储过程体中的每条sql语句的结尾要求必须加分号
  38. 存储过程的结尾可以使用**delimiter**重新设置<br />语法<br />`**delimiter 结束标记**`<br />示例<br />**delimiter $**<br />**CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名  参数类型, ...)**<br />**BEGIN**<br />**sql语句1;**<br />**sql语句2;**<br />**END $**
  39. <a name="fIGVZ"></a>
  40. #### 调用存储过程
  41. `**CALL 存储过程名(实参列表)结束标记**`
  42. ```sql
  43. #1.空参列表
  44. #案例：插入到admin表中五条记录
  45. DELIMITER $
  46. CREATE PROCEDURE myp1()
  47. BEGIN
  48.     INSERT INTO admin(username, `password`)
  49.     VALUES ('john1', '0000'),
  50.            ('lily', '0000'),
  51.            ('rose', '0000'),
  52.            ('jack', '0000'),
  53.            ('tom', '0000');
  54. END $
  56. #调用
  57. CALL myp1()$
  60. #2.创建带in模式参数的存储过程
  62. #案例1：创建存储过程实现 根据女神名，查询对应的男神信息
  63. CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
  64. BEGIN
  65.     SELECT bo.*
  66.     FROM boys bo
  67.              RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
  68.     WHERE b.name = beautyName;
  69. END $
  70. #调用
  71. CALL myp2('柳岩')$
  73. #案例2 ：创建存储过程实现，用户是否登录成功
  74. CREATE PROCEDURE myp4(IN username VARCHAR(20), IN PASSWORD VARCHAR(20))
  75. BEGIN
  76.     DECLARE result INT DEFAULT 0;    #声明并初始化
  78.     SELECT COUNT(*)
  79.     INTO result    #赋值
  80.     FROM admin
  81.     WHERE admin.username = username
  82.       AND admin.password = PASSWORD;
  84.     SELECT IF(result > 0, '成功', '失败');    #使用
  85. END $
  86. #调用
  87. CALL myp3('张飞', '8888')$
  90. #3.创建out 模式参数的存储过程
  92. #案例1：根据输入的女神名，返回对应的男神名
  93. CREATE PROCEDURE myp6(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20))
  94. BEGIN
  95.     SELECT bo.boyname
  96.     INTO boyname
  97.     FROM boys bo
  98.              RIGHT JOIN
  99.          beauty b ON b.boyfriend_id = bo.id
  100.     WHERE b.name = beautyName;
  101. END $
  103. SET @bname$
  104. CALL myp6('小昭',@bname)$
  105. SELECT @bname$
  107. #案例2：根据输入的女神名，返回对应的男神名和魅力值
  108. CREATE PROCEDURE myp7(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20), OUT usercp INT)
  109. BEGIN
  110.     SELECT boys.boyname, boys.usercp
  111.     INTO boyname,usercp
  112.     FROM boys
  113.              RIGHT JOIN
  114.          beauty b ON b.boyfriend_id = boys.id
  115.     WHERE b.name = beautyName;
  116. END $
  117. #调用
  118. CALL myp7('小昭', @name, @cp)$
  119. SELECT @name, @cp$
  121. #4.创建带inout模式参数的存储过程
  122. #案例1：传入a和b两个值，最终a和b都翻倍并返回
  123. CREATE PROCEDURE myp8(INOUT a INT, INOUT b INT)
  124. BEGIN
  125.     SET a = a * 2;
  126.     SET b = b * 2;
  127. END $
  128. #调用
  129. SET @m = 10$
  130. SET @n = 20$
  131. CALL myp8(@m, @n)$
  132. SELECT @m, @n$

删除存储过程

**drop procedure 存储过程名**

  1. DROP PROCEDURE p1;
  2. DROP PROCEDURE p2,p3;#❌一次只能删一个

查看存储过程的信息

  1. DESC myp2;#❌
  2. SHOW CREATE PROCEDURE myp2;

函数

创建函数

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

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

区别

调用语法 返回值
存储过程 CALL 存储过程() 可以有0个或多个 适合做批量插入、批量更新
函数 SELECT 函数() 只能是一个 适合做处理数据后返回一个结果

创建
**CREATE FUNCTION 函数名(参数名 参数类型, ...) RETURNS 返回类型**
**BEGIN**
**函数体**
**END**
注意

  1. 参数列表 包含两部分

参数名 参数类型

  1. 函数体肯定会有return语句，如果没有会报错

如果return语句没有放在函数体的最后也不报错，但不建议
return 值;

  1. 函数体中仅有一句话，则可以省略begin end
  2. 使用 delimiter 语句设置结束标记

调用语法
**SELECT 函数名(实参列表)**

  1. use myemployees;
  2. delimiter $
  3. #1.无参有返回
  4. #案例：返回公司的员工个数
  5. CREATE FUNCTION myf1() RETURNS INT
  6. BEGIN
  7.     DECLARE c INT DEFAULT 0;    #定义局部变量
  8.     SELECT COUNT(*)
  9.     INTO c#赋值
  10.     FROM employees;
  11.     RETURN c;
  12. END $
  13. SELECT myf1() $
  16. #2.有参有返回
  18. #案例1：根据员工名，返回它的工资
  19. CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
  20. BEGIN
  21.     SET @sal = 0;    #定义用户变量
  22.     SELECT salary
  23.     INTO @sal            #赋值
  24.     FROM employees
  25.     WHERE last_name = empName;
  27.     RETURN @sal;
  28. END $
  29. SELECT myf2('k_ing') $
  31. #案例2：根据部门名，返回该部门的平均工资
  32. CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
  33. BEGIN
  34.     DECLARE sal DOUBLE;
  35.     SELECT AVG(salary)
  36.     INTO sal
  37.     FROM employees e
  38.              JOIN departments d ON e.department_id = d.department_id
  39.     WHERE d.department_name = deptName;
  40.     RETURN sal;
  41. END $
  42. SELECT myf3('IT')$

查看函数
**SHOW CREATE FUNCTION 函数名;**

  1. SHOW CREATE FUNCTION myf3;

删除函数
**DROP FUNCTION 函数名;**

  1. DROP FUNCTION myf3;
  2. #案例
  3. #创建函数，实现传入两个float，返回二者之和
  4. CREATE FUNCTION test_fun1(num1 FLOAT, num2 FLOAT) RETURNS FLOAT
  5. BEGIN
  6.     DECLARE SUM FLOAT DEFAULT 0;
  7.     SET SUM = num1 + num2;
  8.     RETURN SUM;
  9. END $
  10. SELECT test_fun1(1, 2)$