#存储过程
存储过程和函数:类似于java中的方法
好处:
1.提高代码的重用性
2.简化操作
#存储过程 procedure
含义:一组预先编译好的SQL语句的集合,理解成批处理语句
1.提高代码的重用性
2.简化操作
3.减少了编译次数并且减少了和数据服务器的连接次数,提高了效率
#一、创建语法 begin end类型于{}
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体(一组合法的SQL语句)
END
注意:
1.参数列表包含三部分
参数模式 参数名 参数类型
举例:
IN stuname VARCHAR(20)
参数类型:
IN :该参数可以作为输入,也就是该参数需要调用方传入值
OUT :该参数可以作为输出,也就是该参数可以作为返回值
INOUT :该参数可以作为输入又可以作为输出,也就是参数既需要传入值,又可以返回值
2.如果存储过程体仅仅只有一句话,begin end可以省略
存储过程中的每条SQL语句的结尾必须加分号。
存储过程的结尾可以使用 DELIMITER 重新设置 delimter $结束标记 再遇到$符号就执行
语法:
DELIMITER 结束标记
案例: #
DELIMITER $
#二、调用语法
CALL 存储过程名(实参列表);
存储过程案例
#1、空参列表
#案例:插入到admin表中五条记录
SELECT * FROM admin;
#delimter $ 声明这个$符号遇到 end$时 当遇到这个符号再执行整段语句
因为储存过程中的sql语句结尾分号 如果不加标记编译器认为到分号就结束了
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,`password`)
VALUE('tom',777),('lily',577),('luce',767),('jack',797),('sim',377);
END $
复制到mysql自带客户端
#调用存储结构 call
CALL myp1()$ 成功的的插入五条数据
前面声明了遇到$符号才执行 我在select查询时 用分号无效用$符回车才执行select语句
#2、创建带in模式参数的存储过程
#案例1.创建存储过程实现 根据女神名,查询对应的男神名
这里的女神名需要当作参数
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty gr ON gr.`boyfriend_id`=bo.`id`
WHERE gr.name=beautyName;
END $
#调用
CALL myp2('小昭')$ 传入形参
#案例2.创建存储过程实现,用户是否登录成功
CREATE PROCEDURE myp3(IN username VARCHAR(20),IN `password` VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0;#声明局部变量并赋值
SELECT COUNT(*) INTO result #满足用户名密码与数据库中数据一致时 筛选条件满足count加1 赋值给result变量
FROM admin a
WHERE a.username=username
AND a.password=`password`;
SELECT IF(result>0,'成功','失败'); #查看局部变量 如果result>0则where筛选条件成功 count加1
END $
传入的值满足筛选条件传入name与password与 admin表中一致的话count(*)就将满足的数据加1,赋值给result。如果result大于0返回成功,反之返回失败
调用存储过程
CALL myp3('join','8888')$ admin 数据john 8888
#3、创建带out模式的存储过程
#案例1:根据女神名,返回对应的男神名
CREATE PROCEDURE myp4(IN girlName VARCHAR(20),OUT boyname VARCHAR(20))
BEGIN
SELECT bo.boyname INTO boyname #满足传入的女神名=连接查询的gir.name into赋值给 输出模式boyname
FROM boys bo
INNER JOIN beauty gir ON bo.id=gir.`boyfriend_id`
WHERE gir.name = girlName;
END $
#调用
#定义一个用户变量
SET @boyname=''$ #接收返回值的变量
#调用myp4
CALL myp4('热巴',@boyname)$
#查看用户变量@boyname
SELECT @boyname$
#案例2:根据女神名,返回对应的男身名和魅力值
多个out模式 bo.boyName,bo.userCP INTO boyName,userCP 满足筛选条件的 将男神名与CP值分别into到两个输出变量
CREATE PROCEDURE mpy5(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 gir ON bo.id=gir.boyfriend_id
WHERE gir.name=beautyName;
END $
/*set @boyname;
set @usercp;
也可不用定义*/
#调用mpy5
CALL mpy5('小昭',@boyname,@usercp)$
#查看调用结果
SELECT @boyname,@usercp$
#4.创建带inout模式参数的存储过程
#案例1:传入a和b两个值,最终a和b都翻倍并返回
CREATE PROCEDURE myp6(INOUT a INT,INOUT b INT)
BEGIN
SET a=a*2; #a和b都为局部变量 直接*2
SET b=b*2;
END $
#定义用户变量赋值
SET @a=8$
SET @b=9$
#调用 并传入参数
CALL myp6(@a,@b)$
#查看调用存储过程结果
SELECT @a,@b$
#三、删除存储过程
语法: DROP PROCEDURE 存储过程名
DROP PROCEDURE test2;
DROP PROCEDURE test1,test2;#错误的语法 报错
#四、查看存储过程的信息
DESC myp2;#错误的存储信息
SHOW CREATE PROCEDURE myp2;
#存储过程案例
#一、创建存储过程或函数实现传入用户名和密码,插入到admin表中
CREATE PROCEDURE test1(IN `name` VARCHAR(10),IN `passwords` VARCHAR(16))
BEGIN
INSERT INTO admin(username,`password`)
VALUE(`name`,passwords);
END $
#调用test1
CALL test1('jack','6666')$
存储过程成功创建 与 调用成功插入
SELECT * FROM admin$
#二、创建存储过程或函数实现传入女神编号,返回女神名称和女神电话
CREATE PROCEDURE test2(IN beautyId INT,OUT beautyName VARCHAR(20),OUT phone VARCHAR(11))
BEGIN
SELECT gir.name,gir.phone INTO beautyName,phone
FROM beauty gir
WHERE gir.id=beautyId;
END $
#调用test2 传入女神3 ID
CALL test2(3,@beautyName,@phone)$
SELECT @beautyName,@phone$
#三、创建存储过程或函数实现传入两个女神生日,返回大小
CREATE PROCEDURE test3(INT date1 DATETIME,INT date2 DATETIME,OUT result VARCHAR(2))
BEGIN
SELECT DATEDIFF(date1,date2) INTO result;
SELECT result,
CASE
WHEN result>0 THEN '大'
WHEN result<0 THEN '小'
ELSE '相等'
END;
END $
CALL test3(NOW(),'1996-1-6',@result)$