存储过程和函数:类似于java中的方法
好处:
1、提高代码的复用性
2、简化操作
一、 存储过程
含义:一组预先编译好的SQL语句的集合,理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率
(1)创建语句
CREATE PROCEDURE 存储过程的名字(参数列表)
BEGIN
存储过程体(一组合法的 SQL 语句 )
END;
注意:
1、参数列表包含三部分
参数模式 参数名 参数类型
举例
IN stuname VARCHAR(20)
参数模式:
IN
:该参数可作为输入,也就是该参数需要调用方传入值
OUT
:该参数可以作为输出,也就是该参数可以作为返回值
INOUT
:该参数既可以作为输入又可以作为输出,也就是该参数既需要传入值,又可以返回值
2、如果存储过程体仅仅只有一句话, BEGIN END
可以省略
存储过程体中的每条 SQL 语句的结尾要求必须加上分号
存储过程的结尾可以使用 DELIMITER
重新设置
--语法:
DELIMITER 结束标记
--案例:
DELIMITER $
(2)调用语法
CALL 存储过程名(实参列表);
1、空参列表
插入
DELIMITER $
CREATE PROCEDURE myadmin()
BEGIN
INSERT INTO admin(username,`password`) VALUES('张世杰','ming456'),('唐莲','admin'),('雷鸣','ray520'),('李如意','ling526'),('Ray','fang203');
END $
--调用
CALL myadmim
2.创建带in模式参数的存储过程
案例一
创建存储过程实现,根据女神名,查询对应的男神名
USE girls;
DELIMITER $
CREATE PROCEDURE myboys(IN beautyName VARCHAR(20))
BEGIN
SELECT bo.`boyName`
FROM boys bo
RIGHT JOIN beauty m
ON bo.`id`=m.`boyfriend_id`
WHERE m.name=beautyName;
END $
--调用
CALL myboys('周芷若')$
3、创建带out
模式的存储过程
案例1:根据女神名,返回对应的男神名
DELIMITER $
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
SELECT bo.boyName INTO boyName
FROM boys bo
LEFT JOIN beauty m
ON bo.id=m.boyfriend_id
WHERE m.name=beautyName;
END $
--调用
CALL myp5('小昭',@bname)$
--查询
SELECT @bname$
案例2:根据女神名,返回对应的男神名和男神魅力值
DELIMITER $
CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)
BEGIN
SELECT bo.boyName,bo.userCP INTO boyName,userCP
FROM boys bo
INNER JOIN beauty m
ON bo.id=m.boyfriend_id
WHERE m.name = beautyName;
END $
--调用
CALL myp6('周芷若',@bname,@userCp)$
--查询
SELECT @bname,@userCp$
4、创建带inout
模式参数的存储过程
案例1:传入a和b的值,最终a和b都翻倍
DELIMITER $
CREATE PROCEDURE myp7(INOUT a INT,INOUT b INT )
BEGIN
SET a=a*2;
SET b=b*2;
END $
--分别为@x,@y设置值
SET @x = 10;
SET @y = 20;
--调用
CALL myp7(@x,@y)$
-- 查询
SELECT @x,@y$
(3)删除存储过程
drop procedure 存储过程名称;
(4)查看存储过程的信息
SHOW CREATE PROCEDURE 存储过程名称;
二、函数
含义:一组预先编译号的SQL语句的集合,理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少编译次数并且减少了和数据库服务器的连接次数,提高了效率
区别:
存储过程:可以有0个返回,也可以有多个返回,适合做批量插入、批量更新
函数:有且仅有1个返回,适合做处理数据后返回一个结果
(1)创建语法
CREATE FUNCTION 函数名(参数列数) RETURNS 返回类型
BEGIN
函数体
END
注意:
参数列表 包含两部分:参数名 参数类型
函数体:肯定会有return
语句,如果没有会报错
如果return
语句没有放在函数体的最后也不报错,但不建议
return 值;
3.函数体中仅只有一句话,则可以省略begin end
4.使用delimiter
语句 设置结束标记
如:
delimiter $
(2)调用语法
SELECT 函数名(参数列表)
1.无参有返回
案例:返回公司的员工个数
DELIMITER $
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;#定义变量
SELECT COUNT(*) INTO c #赋值
FROM employees;
RETURN c;
END $
-- 调用
SELECT myf1()$
2.有参有返回
案例:根据员工名,返回它的工资
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @sal = 0;#定义用户变量
SELECT salary INTO @sal #赋值
FROM employees
WHERE last_name=empName;
RETURN @sal;
END $
--调用
SELECT myf2('Kochhar') $
案例①:根据部门名,返回该部门的平均工资
CREATE FUNCTION myf3(depName VARCHAR(20)) RETURNS DOUBLE
BEGIN
SET @avgSalary= 0;#定义用户变量
SELECT AVG(salary) INTO @avgSalary #赋值
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
WHERE d.department_name=depName;
RETURN @avgSalary;
END $
-- 调用
SELECT myf3('Mar')
案例②:创建函数,实现传入两个float,返回二者之和
CREATE FUNCTION Demo(num1 FLOAT,num2 FLOAT)RETURNS FLOAT
BEGIN
DECLARE SUM FLOAT DEFAULT 0;
SET SUM =num1+num2;
RETURN SUM;
END $
--调用
SELECT Demo(5,7)$
三、查看函数
SHOW CREATE FUNCTION 函数名;
四、删除函数
DROP FUNCTION 函数名;