MySQL学习笔记18：存储过程

最新推荐文章于 2024-04-23 17:55:48 发布

AsajuHuishi

最新推荐文章于 2024-04-23 17:55:48 发布

阅读量177

点赞数

分类专栏： MySQL 文章标签：数据库 mysql

本文链接：https://blog.csdn.net/qq_36937684/article/details/107594521

版权

MySQL 专栏收录该内容

25 篇文章 6 订阅

订阅专栏

简介

本系列（MySQL学习笔记）是我基于B站上SQL播放量第一的MySQL 基础+高级篇- 数据库 -sql -尚硅谷视频所做的笔记，方便大家学习和掌握MySQL。

说明

1.这个系列基本包含了视频中老师讲课的所有内容，包括知识点、案例、部分测试题。
2.所需的配套资料（来自B站评论区）

@黎曼的猜想：
配套资料下载–>公众号公众号 DragonWell 回复：mysql 注意：是公众号！！是公众号！！是公众号，点那个搜一搜搜索！！！
不是私人号，是公众号！！！ (可以直接在搜一搜搜索 dragonwell) 名称是：DragonWell公众号

3.SQLyog的安装（来自B站评论区）

@江左萌粽猪链接：https://pan.baidu.com/s/18PDjbqEeDSAjQM0VQye6og 提取码：qjuz

MySQL学习笔记1：数据查询语言（DQL语言）select基本用法
 MySQL学习笔记2：where 条件查询
 MySQL学习笔记3：order by 排序查询
 MySQL学习笔记4：常见函数（一）单行函数
 MySQL学习笔记5：常见函数（二）分组函数
 MySQL学习笔记6：group by 分组查询
 MySQL学习笔记7：join 连接查询（SQL 92标准）
MySQL学习笔记8：join 连接查询（SQL 99标准）
MySQL学习笔记9：子查询(内查询)
MySQL学习笔记10：union 联合查询
 MySQL学习笔记11：limit 分页查询
 MySQL学习笔记12：数据操纵语言（DML语言）insert update delete
MySQL学习笔记13：数据定义语言（DDL语言）create alter drop
MySQL学习笔记14：常见数据类型
 MySQL学习笔记15：常见约束
 MySQL学习笔记16：事务控制语言（TCL语言）
MySQL学习笔记17：视图
 MySQL学习笔记18：存储过程
 MySQL学习笔记19：函数
 MySQL学习笔记20：流程控制结构

存储过程

含义：预先编译好的SQL语句的集合，理解成批处理语句
提高代码的重用性
简化操作
减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

一.创建


create procedure 存储过程名(参数列表)
begin
	存储过程体(一组合法的SQL语句)
end
注意：
1.参数列表包含3部分
参数模式 参数名 参数类型
in stuame varchar(20)
参数模式：
in  该参数可以作为输入，也就是该参数需要调用方传入值
out 该参数可以作为输出，也就是该参数可以作为返回值
inout 该参数既可以作为输入，又可以作为输出，既可以传入又可以返回
2.加入存储过程体仅仅只有一句话，begin end 可以省略
3.存储过程体中的每条SQL语句的结尾必须加分号
存储过程的结尾可以使用delimiter重新设置
语法：
delimiter 结束标记

二.调用语法
call 存储过程名(实参列表);
*/
SELECT * FROM beauty;
SELECT * FROM boys;

#【调用语法部分以下需要复制到DOS命令行执行】

1.空参列表

插入到admin表5条记录
SELECT * FROM admin;
USE girls;
DELIMITER $
CREATE PROCEDURE p1()
BEGIN
	INSERT INTO admin(username,`password`)
	VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');


END $

复制到DOS命令行执行，刷新，girls数据库的存储过程中出现

# 调用
CALL p1()$ # 复制到DOS执行
SELECT * FROM admin $ # 查看

2.创建带in模式参数的存储过程

创建存储过程 根据女神名 查询对应的男神信息
CREATE PROCEDURE p2(IN beautyName VARCHAR(20))
BEGIN
	SELECT bo.*
	FROM boys AS bo
	RIGHT JOIN beauty AS b
	ON b.boyfriend_id=bo.id
	WHERE b.name = beautyName; #局部变量
END $

# 调用
CALL p2('柳岩')$

# 如果出现Incorrect string value: '\xC1\xF8\xD1\xD2' for column 'beautyName' at row 1
SET NAMES gbk $ 就可以了

创建存储过程 用户是否登陆成功
CREATE PROCEDURE p4(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 $


# 调用
CALL p4('john','8888')$ #成功

3.创建带out模式的存储过程

 根据女神名返回对应的男神名
CREATE PROCEDURE p5(IN beautyname VARCHAR(20),OUT boyname VARCHAR(20))
BEGIN
	SELECT bo.boyname INTO boyname 
	FROM boys AS bo
	INNER JOIN beauty AS b
	ON b.boyfriend_id = bo.id
	WHERE  b.name = beautyname;
END $

# 调用
CALL p5('小昭',@bname) $  #用户变量
SELECT @bname$

 根据女神名返回对应的男神名和男神魅力值
CREATE PROCEDURE p6(IN beautyname VARCHAR(20),OUT boyname VARCHAR(20),OUT usercp INT)
BEGIN
	SELECT bo.boyname,bo.userCP INTO boyname, usercp
	FROM boys AS bo
	INNER JOIN beauty AS b
	ON b.boyfriend_id = bo.id
	WHERE  b.name = beautyname;	
END $
# 调用 
CALL p6('娜扎',@bname,@usercp) $
SELECT @bname,@usercp $

4.创建带inout模式的存储过程

# 传入a,b两个值，最终a和b都翻倍并返回
CREATE PROCEDURE p7(INOUT a INT,INOUT b INT)
BEGIN
	SET a = a * 2;
	SET b = b * 2;
END $
# 调用
SET @a=14,@b=22 $
CALL p7(@a,@b)$
CALL p7(@a,@b)$
SELECT @a,@b$  #56,88

练习
创建存储过程或函数试下传入用户名和密码，插入admin表中
DELIMITER $
USE girls $
CREATE PROCEDURE s1(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
	INSERT INTO admin(username,`password`)
	VALUES(username,PASSWORD);
END $ 
SET @p='123456' $
CALL s1('longtao',@p) $

 创建存储过程或函数实现传入女神编号，返回女神名和女神电话
CREATE PROCEDURE s2(IN beautyid INT,OUT beautyname VARCHAR(20),OUT beautytel VARCHAR(20))
BEGIN
	SELECT NAME,phone INTO beautyname,beautytel
	FROM beauty
	WHERE beauty.id = beautyid;
END $
SET @id='5' $
CALL s2(@id,@name,@tel) $

 创建存储过程或函数实现 输入两个女神生日 返回大小
CREATE PROCEDURE s3(IN date1 DATE,IN date2 DATE,OUT result INT)
BEGIN 
	SELECT DATEDIFF(date1,date2) INTO result;
	SET result=IF(result<0,-1,1);
END $
SET @x1='1997-3-24',@x2='1997-1-20' $
CALL s3(@x1,@x2,@result) $
SELECT @result $

二.存储过程的删除

# 语法： 
DROP PROCEDURE 存储过程名
DROP PROCEDURE s1;    # 类似视图，但不能一次删除多个

三.查看存储过程的结构

USE girls;
SHOW CREATE PROCEDURE s3; # 类似视图

#很少有修改存储过程，存储过程里面的不能改

测试题

创建存储过程或函数实现输入一个日期，格式化成xx年xx月xx日并返回
DELIMITER $
CREATE PROCEDURE s4(IN dt DATE,OUT str VARCHAR(20))
BEGIN
	 
	SET str=DATE_FORMAT(dt,'%y年%m月%d日');
END $
CALL s4('2011-5-11',@str) $
SELECT @str $

创建存储过程或函数实现传入女神名称 返回女神 and 男神 格式的字符串
CREATE PROCEDURE s5(IN bname VARCHAR(50),OUT combine VARCHAR(80))
BEGIN
	SELECT CONCAT(b.name,' and ',bo.boyname) INTO combine
	FROM beauty AS b
	INNER JOIN boys AS bo
	ON bo.id = b.boyfriend_id
	WHERE b.name =bname;
END $
CALL s5('娜扎',@output) $
SELECT @output $

创建存储过程或函数 根据传入的条目数和起始索引，查询beauty表的记录
CREATE PROCEDURE s6(IN startloc INT,IN num INT)
BEGIN 
	SELECT * FROM beauty 
	LIMIT startloc,num;
END $
CALL s6(2,4) $

SELECT * FROM beauty 
LIMIT 2,4;