存储过程和函数

简介

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

好处

• 提高代码的重用性
• 简化操作

---

存储过程

含义

一组预先编译好的SQL语句的集合，理解成批处理语句

好处

• 提高代码的重用性
• 简化操作
• 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

---

创建存储过程

语法

create procedure 存储过程名(参数列表)
begin 
	存储过程体  -- 指一组合法的SQL语句
end

注意
1、参数列表包含三部分：参数模式 参数名 参数类型

例如：in stuname varchar(20)

参数模式：

in：该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

2、如果存储过程体仅仅只有一句话，begin end可以省略

3、存储过程体中的每条sql语句的结尾要求必须加分号。存储过程的结尾可以使用 delimiter 重新设置
语法：

delimiter 结束标记 --重新设定之后，mysql只有碰到 结束标记 才会运行这段语句

---

调用存储过程

语法

CALL 存储过程名(实参列表);

#具体用法：
-- 调用in模式的参数：
call myp（‘值’）;

-- 调用out模式的参数：
set @name; 
CALL myp(@name);SELECT @name;

-- 调用inout模式的参数：
set @name=值; 
CALL myp(@name); SELECT @name;

---

创建和调用存储过程具体案例

空参列表

#案例：插入到admin表中五条记录
SELECT * FROM admin;

-- 创建
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username, `password`)
	VALUES('ace', '1111'),
	('bob', '222'),
	('cc', '333'),
	('dell', '4242'),
	('eva', '53535');
END $

-- 调用
CALL myp1();

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

#案例1：创建存储过程实现 根据女神名，查询对应的男神信息
SELECT * FROM boys;

DELIMITER $
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
	
	SELECT boys.* FROM boys
	RIGHT JOIN beauty
	ON boys.`id` = beauty.`boyfriend_id`
	WHERE beauty.`name` = beautyName;
END $

-- 调用
CALL myp2('苍老师');


#案例2 ：创建存储过程实现，用户是否登录成功(判断输入的账号密码是否正确)

-- 创建存储过程
DELIMITER $
CREATE PROCEDURE myp4(IN username VARCHAR(10), IN `password` VARCHAR(10))
BEGIN 
	#使用变量来表示结果
	-- 1.声明
	DECLARE result VARCHAR(20);
	
	-- 2.赋值
	SELECT COUNT(*) INTO result
	FROM admin 
	WHERE admin.`username`=username 
	AND admin.`password`=`password`;
	
	-- 3.使用
	SELECT result;

END $

-- 调用
CALL myp3('john', '887788');

创建out 模式参数的存储过程

#案例1：根据输入的女神名，返回对应的男神名
DELIMITER $
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20))
BEGIN
	SELECT bo.`boyName` INTO boyName 
	FROM boys bo
	RIGHT JOIN beauty be
	ON bo.`id` = be.`boyfriend_id`
	WHERE be.`name` = '柳岩';

END $

#调用
SET @Name := '';
CALL myp5('小昭', @Name);

SELECT @Name;


#案例2：根据输入的女神名，返回对应的男神名和魅力值
DELIMITER $
CREATE PROCEDURE myp6(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20), OUT userCP INT)
BEGIN
	SELECT boys.`boyName`, boys.`userCP` INTO boyName, userCP
	FROM boys
	RIGHT JOIN beauty
	ON beauty.`boyfriend_id` = boys.`id`
	WHERE beauty.`name` = beautyName;

END $

SET @boyName := '';
SET @userCP := '';
CALL myp6('柳岩',@boyName, @userCP);
SELECT @boyName, @userCP;

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

#案例1：传入a和b两个值，最终a和b都翻倍并返回
DELIMITER $
CREATE PROCEDURE myp7(INOUT a INT, INOUT b INT)
BEGIN
	#select 2*a, 2*b into a, b;
	
	SET a:= 2*a;
	SET b:= 2*b;
END $

SET @a := 2, @b := 4;
CALL myp7(@a, @b);

SELECT @a, @b;

---

删除存储过程

语法

DROP PROCEDURE 存储过程名;

注意： 一次只能删除一个存储过程

示例

DROP PROCEDURE myp7;
-- 错误示范
DROP PROCEDURE myp1, myp2;

---

查看存储过程

SHOW CREATE PROCEDURE myp1;
-- 错误示范
DESC myp1;

---------------------------------------------------------------------------

函数

含义

一组预先编译好的SQL语句的集合，理解成批处理语句

好处

• 提高代码的重用性
• 简化操作
• 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

区别

• 存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新；
• 函数：有且仅有1 个返回，适合做处理数据后返回一个结果

---

创建函数

语法

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体 -- 一组sql语句
END

注意

1、参数列表包含两部分：参数名 参数类型

2、函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错，但不建议

return 值;

3、函数体中仅有一句话，则可以省略begin end

4、使用 delimiter语句设置结束标记

---

调用函数

语法

SELECT 函数名(参数列表);

案例

#1.无参有返回
-- 返回公司的员工个数
/**
如果没有下面这条语句，会报错：
'This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de...'
原因：如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数
*/
SET GLOBAL log_bin_trust_function_creators=TRUE;
DELIMITER $
CREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE num INT;
	SELECT COUNT(*) INTO num
	FROM employees;
	RETURN num;
END $

SELECT myf1();

#2.有参有返回
#案例1：根据员工名，返回他的工资
DELIMITER $
CREATE FUNCTION myf2(eName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE salary DOUBLE;
	SELECT employees.`salary` INTO salary
	FROM employees
	WHERE employees.`last_name` = eName;
	RETURN salary;
END $

SELECT * FROM employees;
SELECT myf2('张飞');

#案例2：根据部门名，返回该部门的平均工资
DELIMITER $
CREATE FUNCTION myf3(deName VARCHAR(20)) RETURNS DOUBLE 
BEGIN 
	DECLARE avg_salary DOUBLE;
	SELECT AVG(salary) INTO avg_salary
	FROM employees
	INNER JOIN departments
	ON employees.`department_id`=departments.`department_id`
	WHERE departments.`department_name` = deName;
	RETURN avg_salary;
	
END $

SELECT myf3('IT');

---

删除函数

DROP FUNCTION myf3;

---

查看存储过程

DROP FUNCTION myf3; -- 查看函数myf3