MySQL中的存储过程和函数

这几天学了存储过程和函数，总结的不是太完整，先记下来。

#存储过程
/*


含义：一组经过预先编译的sql语句的集合
好处：
1、提高了sql语句的重用性，减少了开发程序员的压力
2、提高了效率
3、减少了传输次数


分类：
1、无返回无参
2、仅仅带in类型，无返回有参
3、仅仅带out类型，有返回无参
4、既带in又带out，有返回有参
5、带inout，有返回有参
注意：in、out、inout都可以在一个存储过程中带多个
*/

案例

#1.创建存储过程或函数实现传入用户名和密码，插入到admin表中
DELIMITER $

CREATE PROCEDURE test_pro1(IN username VARCHAR(20),IN psw VARCHAR(20))
BEGIN
	INSERT INTO admin(username,PASSWORD) VALUES('123','123');
END $


#2.创建存储过程或函数实现传入女神编号，返回女神名称和女神电话
CREATE PROCEDURE test_pro2(IN beautyId INT,OUT bName VARCHAR(20),OUT bPhone VARCHAR(20))
BEGIN 
	SELECT NAME,phone INTO bName,bPhone
	FROM beauty b
	WHERE id = beautyId;
END $

#3.创建存储存储过程或函数实现歘人两个女神生日，返回大小
CREATE PROCEDURE test_pro7(IN bDate1 DATETIME,IN bDate2 DATETIME,OUT result INT)
BEGIN
	CASE 
	WHEN DATEDIFF(bDate1,bDate2`beauty`)>0 THEN SELECT 1 INTO result;
	WHEN DATEDIFF(bDate1,bDate2)=0 THEN SELECT 0 INTO result;
	ELSE SELECT -1 INTO result;
	END CASE;
END$

#调用
CALL ('1995-5-5','1996-6-6',@r);
SELECT @r$

#四、创建存储过程或函数实现传入一个日期，格式化成xx年xx月xx日并返回
CREATE PROCEDURE test_pro8(IN myDate DATETIME,OUT formatdate VARCHAR(20))
BEGIN
	SELECT DATE_FORMAT(myDate,'%Y年%m月%d日') INTO formatdate;
END$

#五、创建存储过程或函数实现传入女神名称，返回：女神 and 男神  格式的字符串
#如 传入 ：小昭
#返回： 小昭 AND 张无忌

CREATE PROCEDURE test_pro9(IN bName VARCHAR(20),OUT result VARCHAR(20))
BEGIN
	SELECT CONCAT(bName,' and ',IFNULL(boyName,'无')) INTO result
	FROM boys b
	JOIN beauty be ON b.id = be.boyfriend_id
	WHERE be.name = bName;
END$

CALL test_pro9('赵敏',@r)$
SELECT @r$

#六、创建存储过程或函数，根据传入的条目数和起始索引，查询beauty表的记录

CREATE PROCEDURE test_pro12(IN size INT,IN startIndex INT)
BEGIN
	SELECT * FROM beauty
	LIMIT startIndex,size;
END$

CALL test_pro12(5,1);

函数
#函数
#一、创建函数


学过的函数：LENGTH、SUBSTR、CONCAT等
语法：


CREATE FUNCTION 函数名(参数名 参数类型,...) RETURNS 返回类型
BEGIN
函数体


END


#二、调用函数
SELECT 函数名（实参列表）

案例

#案例1：歘人一个女生名称，返回对应男神名，如果没有该男神，则返回null


CREATE FUNCTION my_fun1(bName VARCHAR(20)) RETURNS VARCHAR(20)
BEGIN
	DECLARE boyName VARCHAR(20);#声明一个变量
	SELECT boys.`boyName` INTO boyName
	FROM boys
	RIGHT JOIN beauty b
	ON boys.`id`=b.boyfriend_id
	WHERE b.name=bName;
	
	RETURN boyName;
END $



#案例2：传入用户名和密码，返回admin表中该用户的记录数

CREATE FUNCTION my_fun2(username VARCHAR(20),PASSWORD VARCHAR(20)) RETURNS INT
BEGIN
	DECLARE i INT DEFAULT 0;
	SELECT COUNT(*) INTO i
	FROM admin 
	WHERE admin.username=username AND admin.PASSWORD=PASSWORD;
	RETURN i;
END $

三、函数和存储过程的区别

函数：关键字 FUNCTION ，调用语法 SELECT 函数() ，返回值 只能是一个 ， 应用场景  一般用于查询结果为一个值并返回时，当有返回值而且仅仅一个


存储过程:关键字 PROCEDURE  ,调用语法  CALL 存储过程(), 返回值  可以有0个或多个 , 应用场景  一般用于更新