数据库与Mysql - 12

存储过程

说明

都类似于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)$