欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

荐 MySQL精讲(五) |存储过程和函数

程序员文章站 2022-07-02 10:25:41
存储过程和函数都是事先经过编译并存储在数据库中的一组sql语句的集合。使用存储过程的优点有:①提高代码重用性 ②减少编译与连接次数 ③提高效率。本文介绍存储过程和函数,并附带相关练习题。...

MySQL精讲系列文章(更新完毕)
《MySQL入门必看知识》
《MySQL精讲(一) | DQL数据查询语句全解析》
《MySQL精讲(二) | DML数据操作语句全解析》
《MySQL精讲(三) | DDL数据定义语句全解析 |数据类型和约束》
《MySQL精讲(四) | TCL事务控制语句全解析|视图》
《MySQL精讲(五) | 存储过程和函数》
《MySQL精讲(六) | 流程控制》
                                                                                               – by 凡尘阿凉


一、变量

系统变量

系统变量分为全局变量会话变量,是由系统提供的。

  • 全局变量作用域:服务器每次启动将为所有的全局变量初始化,不能跨重启。

  • 会话变量作用域:仅针对当前会话有效。

【全局变量】 
作用域:服务器每次启动将为所有的全局变量初始化,不能跨重启
# 1.查看所有的全局变量
SHOW GLOBAL VARIABLES;
# 2.查看部分全局变量
SHOW GLOBAL VARIABLES LIKE '%char%';
# 3.查看某个全局变量的值
SELECT @@global.autocommit;# 查看是否自动提交
SELECT @@global.tx_isolation;# 查看隔离级别
#4.为某个指定的全局变量赋值
SET @@global.autocommit=0;

【会话变量】
作用域:仅针对当前会话有效.
# 1.查看所有的会话变量
SHOW SESSION VARIABLES;
SHOW VARIABLES;#默认省略session
# 2.查看部分会话变量
SHOW SESSION VARIABLES LIKE '%char%';
# 3.查看某个会话变量的值
SELECT @@tx_isolation;
SELECT @@session.tx_isolation;
# 4.为某个指定的会话变量赋值
SET @@session.autocommit=0;

自定义变量

自定义变量分为用户变量局部变量,是用户自定义的。

对比 作用域 定义和使用位置 语法
用户变量 当前会话 会话中的任何地方 必须加@符号,不用限定类型
局部变量 BEGIN…END中 只能在BEGIN…END中,且为第一句话 一般不加@符号,需要限定类型
【用户变量】
作用域:针对当前会话有效,和会话变量的作用域相同

# 声明并初始化
# 下面三种方式都可以
SET @count=1; 
# set @count:=1;
# select @count:=1;

# 赋值
SELECT COUNT(*) INTO @count FROM employees;
# 查看用户变量
SELECT @count;

【局部变量】
作用域:仅在定义的begin end中有效,应用在begin end中的第一句话

# 声明
DECLARE 变量名 类型;
# declare 变量名 类型 default 值;
# 赋值
SET 局部变量名=;
# 或set 局部变量名:=值;
# select @局部变量名:=值

# 使用
SELECT 局部变量名;


【案例】
# 案例:声明两个变量并赋初值,求和,打印
# 用户变量
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;

二、存储过程

定义:事先经过编译并存储在数据库中的一组sql语句的集合。

存储过程的优点:①提高代码重用性 ②减少编译与连接次数 ③提高效率。

创建语法:

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体(一组合法的sql语句);
END 结束符

调用语法:
CALL 存储过程名(实参列表)

删除语法:
DROP PROCEDURE 存储过程名;

查看语法:
SHOW CREATE PROCEDURE 存储过程名;

存储过程的参数别聊提供了3种参数:

  • IN:需要输入,需要调用方传入值。
  • OUT:可以输出,可以作为返回值。
  • INOUT:可以输入和输出,既需要入参又需要返回值。

注意:

  • 如果存储过程只有一句话,可以省略BEGIN END。
  • 存储过程体中的每条sql语句的结尾要求必须加分号。
  • 存储过程的结尾可以使用delimiter重写结束标志,DELIMITER 结束标记。
【空参存储过程】
# 案例:向admin表中插入5条记录
# 重置结尾符为$
DELIMITER $
# 创建存储过程
CREATE PROCEDURE myp1()
BEGIN
   INSERT INTO admin(username,PASSWORD)
   VALUES('join1','000'),('join2','000'),('join3','000'),('join4','000'),('join5','000');
END $

# 调用存储过程
CALL myp1()$



【带in模式的存储过程】
# 案例:创建存储过程,实现根据女神名查询对应的男朋友信息
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$

CALL myp2('柳岩')$

# 案例:创建存储过程,查看用户是否登录成功
CREATE PROCEDURE myp3(IN username VARCHAR(10),IN PASSWORD VARCHAR(10))
BEGIN
	DECLARE result INT DEFAULT 0;# 声明并初始化
	
	SELECT COUNT(*) INTO result# 赋值
	FROM admin
	WHERE admin.username=username
	AND PASSWORD=PASSWORD;
	
	SELECT IF(result,'成功','失败');#打印变量
END$

# 调用
CALL myp3('john','8888')$



【带out模式的存储过程】
# 案例:根据女神名,返回对应的男神名
CREATE PROCEDURE myp4(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.boyName INTO boyName #赋值
	FROM boys bo
	INNER JOIN beauty b
	ON b.boyfriend_id=bo.id
	WHERE b.name=beautyName;
END $

CALL myp4('热巴',@bName)$ # 不定义,直接使用用户变量填充

# 调用
SELECT @bName$


# 案例:根据女神名,返回对应的男神名和男神魅力值
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
	INNER JOIN beauty b
	ON b.boyfriend_id=bo.id
	WHERE b.name=beautyName;
END $

CALL myp5('热巴',@bName,@usercp)$ # 不定义,直接使用用户变量填充

# 调用
SELECT @bName,@userCP$

【带inout模式的存储过程】
# 案例:传入a和b两个值,最终a和b都翻倍并返回
CREATE PROCEDURE myp6(INOUT a INT,INOUT b INT)
BEGIN
	SET a=a*2;
	SET b=b*2;
END $

SET @m=10$
SET @n=20$

CALL myp6(@m,@n)$

SELECT @m,@n$

学习了存储过程,尝试完成下列习题吧
荐
                                                        MySQL精讲(五) |存储过程和函数
习题答案如下↓

【习题答案】
# 习题1:创建存储过程实现传入用户名和密码,插入到admin表中
DELIMITER $

CREATE PROCEDURE test_1(IN username VARCHAR(10), IN loginPwd VARCHAR(10))
BEGIN
	INSERT INTO admin(admin.username,PASSWORD)
	VALUES(username,loginPwd);
END $

CALL test_1('admin','111')$

# 习题2:创建存储过程或函数实现传入女神编号,返回女神名称和电话
CREATE PROCEDURE test_2(IN id INT,OUT NAME VARCHAR(20),OUT phone VARCHAR(20))
BEGIN 
	SELECT b.name,b.phone INTO NAME,phone
	FROM beauty b
	WHERE b.id=id;
END $

CALL test_2(1,@n,@p)$

SELECT @n,@p;

# 习题3:创建存储过程或函数实现传入两个女神的生日,返回大小
CREATE PROCEDURE test_3(IN birth1 DATETIME,IN birth2 DATETIME,OUT result INT)
BEGIN
	SELECT DATEDIFF(birth1,birth2) INTO result;
END $

CALL test_3('1998-1-1',NOW(),@result)$

SELECT @result$

# 习题4:创建存储过程或函数实现传入一个日期,格式化成 xx 年 xx 月 xx 日并返回
CREATE PROCEDURE test_4(IN mydate DATETIME,OUT strdate VARCHAR(50))
BEGIN
	SELECT DATE_FORMAT(mydate,'%y年%m月%d日') INTO strdate;
END $

CALL test_4(NOW(),@str)$

SELECT @str $

#习题5:创建存储过程或函数实现传入女神名称,返回:女神 and 男神 格式的字符串
CREATE PROCEDURE test_5(IN beautyName VARCHAR(20),OUT str VARCHAR(50))
BEGIN
	SELECT CONCAT(beautyName,'and',IFNULL(boyName,'null')) INTO str
	FROM boys bo
	RIGHT JOIN beauty b
	ON b.boyfriend_id=bo.id
	WHERE b.name=beautyName;
END $

CALL test_5('热巴',@str)$

SELECT @str $

#习题6:创建存储过程或函数,根据传入的条目数和起始索引,查询 beauty 表的记录
CREATE PROCEDURE test_6(IN startIndex INT,IN size INT)
BEGIN
	SELECT * FROM beauty LIMIT startIndex,size;
END $

CALL test_6(3,5)$

三、函数

存储过程和函数的区别?

  • 存储过程可以有0个或多个返回;函数有且只有一个返回。
  • 存储过程适合做批量插入、批量更新;函数适合做处理数据后返回一个结果。

创建语法:
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体(一定有return语句);
END 结束符

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

查看函数:
SHOW CREATE FUNCTION my_f3;

删除函数:
DROP FUNCTION my_f3;

【无参有返回】
# 返回公司的员工个数
CREATE FUNCTION my_f1() RETURNS INT
BEGIN
	DECLARE n INT DEFAULT 0;# 定义变量
	SELECT COUNT(*) INTO n # 赋值
	FROM employees;
	RETURN n;
END $

SELECT my_f1()$

【有参有返回】
# 根据员工名,返回工资
CREATE FUNCTION my_f2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @sal=0;# 定义用户变量
	SELECT salary INTO @sal # 赋值
	FROM employees
	WHERE last_name=empName;
	RETURN @sal;
END $

SELECT my_f2('Kochhar')$

# 3.根据部门名,返回该部门平均工资
CREATE FUNCTION my_f3(deptName 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=deptName;
	RETURN sal;
END $

SELECT my_f3('IT')$

学习了mysql的函数,尝试完成下列习题
荐
                                                        MySQL精讲(五) |存储过程和函数
答案:
1、
CREATE FUNCTION test_1(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
DECLARE SUM FLOAT DEFAULT 0;
SET SUM=num1+num2;
RETURN SUM;
END $
SELECT test_1(1,2)$
2、
CREATE FUNCTION test_2(jobName VARCHAR(20)) RETURNS INT
BEGIN
DECLARE number INT DEFAULT 0; # 定义变量
SELECT COUNT(employee_id) INTO number # 赋值
FROM employees e
JOIN jobs j ON e.job_id=j.job_id
WHERE j.job_title=jobName;
RETURN number;
END $
SELECT test_2(‘President’)$
3、
CREATE FUNCTION test_3(empName VARCHAR(20)) RETURNS VARCHAR(20)
BEGIN
DECLARE managerName VARCHAR(20) DEFAULT ‘’; # 定义变量
SELECT e1.last_name AS managerName INTO managerName # 赋值
FROM employees e1
WHERE e1.employee_id=(SELECT e2.manager_id FROM employees e2 WHERE e2.last_name=empName);
RETURN managerName;
END $
SELECT test_3(‘Kochhar’)$

本文地址:https://blog.csdn.net/weixin_43691058/article/details/106604374