存储过程
说明
都类似于JAVA中的方法,将一组完成特定功能的逻辑语句包装起来,对外暴露名字
好处
1.提高重用性
2.SQL语句简单
3.减少了和数据库服务器连接的次数,提高了效率
存储过程-创建语法
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体(一组合法的SQL语句)
END
注意
#1.参数模式包含三部分
参数模式 参数名 参数类型
例如:
IN stuname VARCHAR(20)
--------------------------------
#2.参数模式
IN:该参数可以作为输入,也就是该参数需要调用方传入值;
OUT:该参数可以作为输出,也就是该参数可以作为返回值;
INOUT:该参数既可以作为输入又可以作为输出,也就是该参数急需要传入值,又可以返回值;
---------------------------------
#3.如果存储过程提仅仅只有一句话,BEGIN END可以省略;
存储过程体的每条SQL语句的结尾要求必须加分号;
存储过程的结尾可以使用 DELIMITER 重新设置;
语法:
DELIMITER 结束标记
例如:
DELIMITER $;
调用语法
语法
CALL 存储过程名(实参列表);
空参列表
=1= 案例:插入到ADMIN表中五条记录
-------------------------------------
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,'password');
VALUES('john1',0000),
('lily',0000),
('rose',0000),
('jack',0000),
('tom',0000);
END $
调用:
CALL myp1()$
创建带IN模式参数的存储过程
=1=案例:创建存储过程,实现根据女神名,查询对应的男神信息.
-----------------------------------------------------
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('小昭')$
------------------------------------------------------
=2=案例:创建存储过程实现,用户是否登陆成功
------------------------------------------------------
CREATE PROCEDURE myp3(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 myp3('张飞','8888')$
#3.创建带OUT模式的存储过程
=1=案例:根据女神名,返回对应的男神名
--------------------------------------------
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),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 = beautyName;
END $
调用:
SET @bName$
CALL myp5('小昭',@bname)$
SELECT @bName$
--------------------------------------------
=2=案例:根据女神名,返回对应的男神名和男神魅力值
--------------------------------------------
CREATE PROCEDURE myp6(IN beautyName VARCAHR(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 $
调用:
SET @bName$
SET @userCP$
CALL myp6('小昭',@bName,@userCP)$
SELECT @bName,@userCP $
创建带INOUT模式参数的存储过程
=1=案例:传入a和b两个值,最终a和b都翻倍并返回
-------------------------------------------------
CREATE PROCEDURE myp8(INOUT a INT,INOUT b INT)
BEGIN
SET a = a*2;
SET b = b*2;
END $
SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$
删除存储过程
语法:
DROP PROCEDURE 存储过程名;
//一次只能删除一个存储过程
查看存储过程的信息
语法:
SHOW CREATE PROCEDURE myp2;
存储过程相关习题
创建存储过程或函数实现:传入一个日期,格式化为xx年xx月xx日并返回
CREATE PROCEDIRE test_pro4(IN mydate DATEIME,OUT strDate VARCHAR(50))
BEGIN
SELECT DATE_FORMAT(mydate,'%y年%m月%d日') INTO strDate;
END $
CALL test_pro4(NOW(),@str)$
SELECT @str $
创建存储过程或函数实现:传入女神姓名,返回:女神 and 男神 格式的字符串
CREATE PROCEDURE test_pro5(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_pro5('小昭',@str)$
SELECT @str $
创建存储过程或函数,根据传入的条目数和起始索引,查询beauty表的记录
CREATE PROCEDURE test_pro6(IN startIndex INT,IN size INT)
BEGIN
SELECT *
FROM beauty
LIMIT startIndex,size;
END $
CALL test_pro6(3,5)$