存储过程和函数:类似于java中的方法
好处:
- 提高代码的重用性
- 简化操作
一、存储过程
含义:一组预先编译好的SQL语句的集合,可以理解成批处理语句
- 提高代码的重用性
- 简化操作
- 减少了编译次数并且减少了和数据库服务器连接的次数,提高了效率
(一)创建语法
CREATE PROCEDURE 储存过程名(参数列表)
BEGIN
存储过程体(一组合法的SQL语句)
END
注意:
1. 参数列表包含三部分
参数模式 参数名 参数类型
举例:
IN stuName VARCHAR(20)
参数模式:
IN:该参数可以作为输入,也就是改参数需要调用方输入值
OUT:该参数可以作为输出值,也就是该参数可以作为返回值
INOUT:该参数既可以作为输入也可以作为输出,也就是改参数即需要传入值,又可以返回值
2. 如果存储过程体仅仅只有一句话,begin end 可以省略
3. 存储过程体中的每条SQL语句的结尾都要求必须加分号
4. mysql 5.7之前的版本需要设置存储过程的结尾符号可以使用 DELIMITER 重新设置
语法:DELIMITER 结束标记
例子:DELIMITER $
(二)调用语法
CALL 存储过程名(实参列表);
(三)案例
1.创建和调用空参列表的存储过程
例子1
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,`password`)
VALUES('JOhn1','0000'),('JOhn2','0000'),('JOhn3','0000'),('JOhn4','0000');
END $
call myp1() $
注意 如果是mysql5.7版本之后的,则不需要使用DELIMITER
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,`password`)
VALUES('JOhn1','0000'),('JOhn2','0000'),('JOhn3','0000'),('JOhn4','0000');
END;
call myp1();
2. 创建带in模式参数的存储过程
例子2:创建存储过程实现 根据女生名,查询对应的男神信息
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('娜扎');
例子3:创建存储过程,用户是否登录成功(带多个IN参数)
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,'成功','失败')result; #使用
END;
call myp3('赵云','8888');
3.创建带out模式的存储过程
例子4:根据女神名,返回对应的男神名
CREATE PROCEDURE myp4(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
SELECT boys.boyName INTO boyName
FROM boys
INNER JOIN beauty ON boys.id = beauty.boyfriend_id
WHERE beauty.name = beautyName;
END;
调用
CALL myp4('娜扎',@boyName);# @boyName是用户变量
SELECT @boyName;
例子5:根据女神名,返回对应的男神名和男神魅力值(多个OUT参数)
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)
BEGIN
SELECT boys.boyName INTO boyName,boys.userCP INTO userCP
FROM boys
INNER JOIN beauty ON boys.id = beauty.boyfriend_id
WHERE beauty.name = beautyName;
END;
CALL myp5('娜扎',@boyName,@userCP);
SELECT @boyName,@userCP;
4.创建带inout模式参数的存储过程
例子6:传入a和b两个值,最终a和b都翻倍并返回
CREATE PROCEDURE myp6(INOUT a INT,INOUT b INT)
BEGIN
SET a = a*2;
SET b = b*2;
END;
SET @m = 20;
SET @n = 10;
CALL myp6(@m,@n);
SELECT @m,@n;
(四)删除存储过程
语法:
DROP PROCEDURE 存储过程名;
(五)查看存储过程的信息
语法:
SHOW CREATE PROCEDURE 存储过程名;
二、函数
含义:一组预先编译好的SQL语句的集合,可以理解成批处理语句
- 提高代码的重用性
- 简化操作
- 减少了编译次数并且减少了和数据库服务器连接的次数,提高了效率
(一)创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END
注意:
1. 参数列表包含两部分
参数名 参数类型
2. 肯定会有return语句,如果没有会报错
3. 函数体中仅有一句话,则可以省略begin end
4. 可以使用DELIMITER语句设置结束标志
(二)调用语法
SELECT 函数名(参数列表)
(三)案例
注意:在创建函数前,先设置下面的全局变量,不然会报1146错误
SET GLOBAL log_bin_trust_function_creators = ON;
1. 无参有返回
例子1:返回公司的员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;#定义局部变量
SELECT COUNT(*) INTO c #赋值
FROM employees;
RETURN c;
END;
SELECT myf1();
2.有参有返回
例子2:根据员工名返回工资
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @sal = 0;#定义用户变量
SELECT salary INTO @sal # 赋值
FROM employees
WHERE employees.last_name = empName;
RETURN @sal;
END;
SELECT myf2('Kochhar');
例子3 根据部门名,来返回该部门的平均工资
CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
DECLARE sal DOUBLE;
SELECT AVG(salary) INTO sal
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = deptName;
RETURN sal;
END;
SELECT myf3('IT');
(四)删除函数
DROP FUNCTION 函数名
(五)查看函数的信息
SHOW CREATE FUNCTION 函数名
三、存储过程和函数的区别
返回值 | 适用类型 | |
---|---|---|
存储过程 | 0个或多个 | 批量插入、批量更新 |
函数 | 有且仅有1个 | 处理数据后返回一个结果 |