Day106.存储过程 -MySQL

存储过程

存储过程和函数:类似于java中方法

含义: 一组预先编译好的SQL语句的集合，可理解成批处理语句（一组语句）
适合做批量插入、更新，增删改

好处:
1、提高代码的重用性
2、简化操作
3、减少编译次数，并减少和数据库服务器的连接次数，提高了效率

.

一、创建语法

CREATE PROCEDURE 存储过程名(参数列表)
			BEGIN
				存储过程体（一组合法有效的SQL语句）
			END

注意:
	1、参数列表包含三部分
		参数模式 参数名 参数类型
		   举例:
			IN stuname VARCHAR(20)
			
		 参数模式:
			IN: 该参数可以作为输入，该参数需要调用方传入值
			OUT: 该参数可以作为输出，该参数可以作为返回值
			INOUT: 该参数既可做输出也可以做输出，该参数既需要传入值，又可以返回值
			
	2、如果存储过程体仅仅只有一句话，BEGIN END可以省略
		
	3、存储过程体中每条SQL语句的结尾要求必须加分号；
		
	4、存储过程的结尾可以使用DELIMITER 重新设置
		语法:
			DELIMITER 结束标记
		举例:
			DELIMITER $

二、调用语法

CALL 存储过程名(实参列表);  #---实参列表中的个数和顺序要求与存储过程中的参数列表一致

1、空参列表

空参列表	

	#案例: 插入到admin表中5条记录
    
		#创建存储过程
			SELECT * FROM `admin`;
			DELIMITER $ #设置结束标记
			CREATE PROCEDURE myp1()
			
			BEGIN
				INSERT INTO admin(`username`,`password`) 
				VALUES("mike","0000"),("nick","1111"),("Snack","2222"),("Ellis","3333"),("Amy","4444");			
			END $ 
			
		#调用
			CALL myp1()$

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

创建带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 $
						
	#调用
		SET NAMES gbk$#主要设置正确的字符集
		CALL myp2("热巴")$
						
#案例2: 创建存储过程实现，用户是否登录成功
				
	#创建存储过程
		CREATE PROCEDURE myp3(IN userName VARCHAR(20),IN PASSWORD VARCHAR(20))
		
		BEGIN
		DECLARE result INT DEFAULT 0; #声明并初始化变量
							
		SELECT COUNT(*) INTO result #符合条件的个数赋值给result
		FROM `admin`
		WHERE `admin`.`username`=userName
		AND `admin`.`password`=PASSWORD; 
							
		SELECT IF(result>0,"登录成功","登入失败"); #使用

		END $
	#调用
		CALL myp3("nick","1111")$
					

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

#案例1: 根据女生名，返回对应的男生名
					
	#创建存储过程
		CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
						
		BEGIN
		SELECT bo.`boyName` INTO boyName #将查询出对应女生名的男生名结果 赋值给boyName
		FROM `boys` bo
		JOIN `beauty` b ON b.`boyfriend_id`=bo.`id`
		WHERE b.`name`=beautyName;
						
		END $
						
	#调用
		SET @bName$ #定义用户变量（使用在begin end之外）
		CALL myp5("唐艺昕",@bName)$
		SELECT @bName$
						
#案例2: 根据女生名，返回对应的男生名和男生魅力值
					
	#创建存储过程
		CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)
						
		BEGIN
		SELECT bo.`boyName`,bo.`userCP` INTO boyName,userCP #将查询出对应女生名的男生名和魅力值的结果 赋值给boyName和userCP
		FROM `boys` bo
		JOIN `beauty` b ON b.`boyfriend_id`=bo.`id`
		WHERE b.`name`=beautyName;
							
		END $
						
	#调用
		SET @bName$ #定义用户变量（使用在begin end之外）
		SET @boyCP$ 
		CALL myp6("唐艺昕",@bName,@boyCP)$
		SELECT @bName,@boyCP$	

4、创建带inout模式参数的存储过程

#案例1: 传入a和b两个值，最终a和b都翻倍并返回
				
	#创建存储过程
		 CREATE PROCEDURE myp7(INOUT m INT,INOUT n INT)
		BEGIN
			SET m = m*2
			SET n = n*2
		 END $
					 
	#调用	
		SET @a=10$
		SET @b=20$
		CALL myp7(@a,@b)$
		SELECT @a,@b

.

三、删除语法

语法:
	DROP PROCEDURE 存储过程名（一次只能删一个）
			
	DROP PROCEDURE myp1;
	DROP PROCEDURE myp1,myp2;#报错

.

四、查看结构语法

DESC `myp2`; #报错
SHOW CREATE PROCEDURE myp2;