/* MySQL-进阶18
存储过程 和 函数
存储过程和函数:类似于java中的方法
好处:
1.提高代码的重用性
2.简化操作
*/
#存储过程
/*
含义: 一组已经预见编译好的SQL语句的集合, 理解成批处理语句;
好处: 减少操作次数,减少了编译次数,减少了和服务器的连接次数,提高了效率
*/
/*
#一: 创建语句
create procedure 存储过程名(参数列表)
begin
存储过程体(一组合法的SQL语法)
end
注意:
1.参数列表包含三个部分 : 参数模式 参数名 参数类型
举例:
in stuname varchar(20)
参数模式:
in : 该参数可以作为输入, 也就是该参数需要调用方 传入值
out : 该参数可以作为输出,也就是该参数可以作为返回值
inout : 该参数既可以作为输入也可以作为输出,也就是该参数既需要传入值,又需要返回值
2.如果存储过程体只有一句话,begin 和 end 可以省略;
存储过程体中的每条sql 语句的结尾要求必须加分号;
存储过程的结尾可以使用 delimiter 重新设置;
语法:
delimiter 结束标记
案例:
delimiter $
#二:调用语法
call 存储过程名(实参列表);
#二 : 删除存储过程
#语法:drop procedure 存储过程名 ,不支持删除多个
DROP PROCEDURE `myp1`;
#三 : 查看 存储过程的信息
#错误写法:DESC myp2;
SHOW CREATE PROCEDURE myp2; #......................
#四: 修改 ,无法修改内部语句
*/
#1.空参列表
#案例:插入到admin 表中五条记录
SELECT * FROM admin;
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(`username`,`password`)
VALUES('a1',''),('a2',''),('a3',''),('a4',''),('a5','');
END $
#调用
CALL myp1() $;
#2:创建带有in 模式参数的存储过程
#案例 : 创建存储过程实现 根据女生名,查询对应的男生信息
DELIMITER $
CREATE PROCEDURE myp2(IN beautyName VARCHAR(50))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty b
ON bo.id = b.boyfriend_id
WHERE b.name = beautyName;
END $
#调用 ,必须紧紧挨着'END $'
CALL myp2('柳岩') $;
#案例2: 创建存储过程实现,用户是否登录成功
DELIMITER $
CREATE PROCEDURE myp3(IN `uname` VARCHAR(10),IN psw VARCHAR(10))
BEGIN
DECLARE result VARCHAR(20) DEFAULT ''; #声明局部变量并进行初始化
SELECT COUNT(*) INTO result #赋值
FROM admin
WHERE username = uname AND admin.`password`=psw;
SELECT result;
END $
CALL myp3('lyt','') $; #返回1
#call myp3('lyt','') $; #返回0
#3-2:(使用IF 语句)创建存储过程实现,用户是否登录成功
DELIMITER $
CREATE PROCEDURE myp4(IN `uname` VARCHAR(10),IN psw VARCHAR(10))
BEGIN
DECLARE result INT DEFAULT 0; #声明局部变量并进行初始化
SELECT COUNT(*) INTO result #利用into 赋值
FROM admin
WHERE username = uname AND admin.`password`=psw;
SELECT IF(result>0,'成功','失败'); #使用
END $
CALL myp4('lyt','') $; #返回 '失败'
# CALL myp4('lyt','') $; #返回 '成功'
#3:创建带有out 模式参数的存储过程
#案例1:根据女神名,返回对应的男神名
DELIMITER $
CREATE PROCEDURE myp5(IN in_name VARCHAR(50),OUT boyName VARCHAR(20))
BEGIN
SELECT bo.boyName INTO boyName #赋值,重名时按照就近原则
FROM boys bo
INNER JOIN beauty b ON bo.`id`=b.`boyfriend_id`
WHERE b.name = in_name;
END $
#set @bname $ ,定义一个用户变量
CALL myp5('柳岩',@bname) $
SELECT @bname $; #鹿晗
#案例2:根据女神名,返回对应的男神名 和男神魅力值
DELIMITER $
CREATE PROCEDURE myp6(IN in_name VARCHAR(50),OUT boyName VARCHAR(20),OUT userCP INT)
BEGIN
SELECT bo.boyName ,bo.userCP INTO boyName,userCP
FROM boys bo
INNER JOIN beauty b ON bo.`id`=b.`boyfriend_id`
WHERE b.name = in_name;
END $
#set @bname $ ,定义一个用户变量
CALL myp6('柳岩',@bname,@usercp) $
SELECT @bname,@usercp $; #鹿晗 ,800
##4.创建带inout 模式参数的存储过程
#案例1: 传入a 和 b两个值,最终a和b都翻倍并返回; 注意需要传入的需要是用户变量
DELIMITER $
CREATE PROCEDURE myp7(INOUT a INT,INOUT b INT )
BEGIN
SET a=a+a;
SET b=b+b;
END $
CALL myp7(@a,@b) $
SELECT @a,@b ; #24 26
SET @a=12;
SET @b=13;
#二 : 删除存储过程
#语法:drop procedure 存储过程名 ,不支持删除多个
DROP PROCEDURE `myp1`;
#三 : 查看 存储过程的信息
#错误写法:DESC myp2;
SHOW CREATE PROCEDURE myp2; #......................
#四: 修改 ,无法修改内部语句
#------------------------------------------
COMMIT;
#五: 练习1-:
##练习1
#test1 : 创建一个存储过程实现传入一个日期,格式化为xx年xx月xx日 并返回
DELIMITER $
CREATE PROCEDURE test_pro4(IN mydate DATETIME,OUT strDate VARCHAR(50))
BEGIN
SELECT DATE_FORMAT(mydate,'%y年%m月%d日') INTO strDate;
END $
CALL test_pro4(@mydate,@strDate) $;
SET @mydate=NOW();
SET @strDate='';
SELECT @mydate,@strDate; #2018-11-01 17:01:41,18年11月01日
##练习2
#test2 : 创建一个存储过程或者函数实现传入女神名称,返回 女神 and 男神 名字加在一块的字符串
#如传入:小昭 ;返回:小昭 and 张无忌
COMMIT;
DROP PROCEDURE test_pro6;
DELIMITER $
CREATE PROCEDURE test_pro7(IN s1 VARCHAR(50),OUT s2 VARCHAR(50))
BEGIN
SELECT CONCAT(s1,' and ',IFNULL(boys.`boyName`),'null')
FROM boys
INNER JOIN beauty b
ON b.`boyfriend_id`=boys.`id`
WHERE b.name=s1;
END $
#set @result1='' 可以省略!!
CALL test_pro7('热巴',@result1) $ #返回 : 热巴 and 鹿晗
SELECT @result1;
#六: 创建存储过程或者函数 ,根据传入的目录条数和起始索引(反了!),查询beauty表的记录
DELIMITER $
CREATE PROCEDURE test_pro8(IN size INT,IN startIndex INT)
BEGIN
SELECT * FROM beauty
LIMIT startIndex,size;
END $
CALL test_pro8(3,5) $