MySQL之存储过程和函数的示例分析

67次阅读

没有评论

共计 6105 个字符，预计需要花费 16 分钟才能阅读完成。

自动写代码机器人，免费开通

这篇文章将为大家详细讲解有关 MySQL 之存储过程和函数的示例分析，丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

一、变量

系统变量

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

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

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

【全局变量】 
作用域: 服务器每次启动将为所有的全局变量初始化, 不能跨重启 # 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$

学习了存储过程，尝试完成下列习题吧

习题答案如下↓

【习题答案】#  习题 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 birth2 DATETIME,IN birth3 DATETIME,OUT result INT)BEGIN
 SELECT DATEDIFF(birth2,birth3) 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 INTBEGIN
 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 DOUBLEBEGIN
 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 DOUBLEBEGIN
 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 的函数，尝试完成下列习题

答案：
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’)$