存储过程和函数
存储过程和函数:类似java中的方法
存储过程:自足预先编译好的SQL语句的集合,理解成批处理语句
好处:
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据可服务器的连接次数,提高了效率
一、创建函数
语法:
create procedure 存储过程名(参数列表)
begin
存储过程体、方法体(一组合法有效的SQL语句)
end
注意
1、begin - end 语句的作用类似于java中的{}
2、参数泪飙包含三部分
参数模式 参数名 参数类型
ex. in name varchar(20)
3、参数模式
in:该参数可以作为输入值,也就是该参数需要调用方法传入值
out:该参数可以作为输出值,也就是该参数可以作为返回值
inout : 该参数既可以作为输入有可以作为输出,也就该参数既需要传入值,有需要返回值
4、如果存储过程体只有一句话:begin - end 可以省略
5、存储过程体中的每条SQL语句结尾必须加分号
6、存储过程体的结尾可以用 delimiter 关键字重新设置
语法:
delimiter 结束标记;
如果读到结束标记,函数结束
二、调用语法
语法
call 存储过程名(实参列表) 结束标记;
SELECT * FROM ADMIN;
案例:插入到admin表中5条记录
DELIMITER $;
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO `admin`(username,`password`)
VALUES
('john1','0001'),
('john2','0002'),
('john3','0003'),
('john4','0004'),
('john5','0005');
END $
调用
CALL myp1()$;
2、创建一个 in 参数模式的存储过程
根据女神名,查询对应的男生信息
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 $
二、函数的调用
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 padmin.assword = PASSWORD;
SELECT IF (result>0,'成功','失败');#使用
END$;
调用
CALL myp3(‘张飞’,‘8888’)$;
3、创建带out模式的存储过程
案例1: 根据女神名,返回对应的男神名
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN
SELECT b.boyName INTO boyName
FROM beauty g
INNER JOIN boys b
ON b.id = g.boyfriend_id
WHERE g.name = beautyName;
END $;
调用
SET @ bName$;
CALL myp5('小昭',@bName)$;
SELECT @bName;
案例2 根据女神名,返回对应的男神名和男神魅力值
CREATE PROCEDURE mpy6(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)
BEGIN
SELECT b.boyName,b.userCP INTO boyName,userCP
FROM beauty g
INNER JOIN boys b
ON b.id = g.boyfriend_id
WHERE g.name = beautyName;
END $;
调用
SET @userCP;
CALL myp6('小昭',@bName,@userCP);
4、创建带inout 模式参数的存储过程
案例1:传入a和b两个值,最终a和b都翻倍并返回
CREATE PROCEDURE myp8(INOUT a INT,INOUT b INT)
BEGIN
SET a = a * 2;
SET b = b * 2;
END $;
调用
SET @a = 10;
SET @b = 20;
CALL myp8(@a,@b)$;
SELECT @a,@b;
创建存储过程或函数实现比较两个女神的生日,返回大小
CREATE PROCEDURE compara(IN beautyName1,IN beautyName2 OUT result VARCHAR(20))
BEGIN
DECLARE b1 DATE
DECLARE b2 DATE
SELECT g1.borndate,g2.borndate INTO b1,b2
FROM beauty g1,beauty g2
WHERE g1.name = beautyName1
AND g2.name = beautyName;
result = IF(b1>b2,1号女神大,2号女神大);
END $;
二、存储过程的删除
语法:
drop procedure 存储过程名
查看存储过程的信息
SHOW CREATE PROCEDURE mpy2;
练习1:创建存储过程或函数实现传入一个日期,格式化成xx年xx月xx日,并返回
CREATE PROCEDURE test_pro1(IN birth DATETIME,OUT strDAte VARCHAR(20))
BEGIN
SELECT DATE_FORMAT(mydate,'%y年%m月%日d') INTO strDate;
END $;
练习2:创建存储过程或函数实现传入一个女神名,返回:女神 and 男神 格式的字符串
如 传入:小昭
返回 小昭 and 张无忌
CREATE PROCEDURE test_pro2(IN beautyName VARCHAR(20),OUT str VARCHAR(50))
BEGIN
SELECT CONCAT(beautyName,'and',IFNULL(boyName,'没有男朋友')) INTO str
FROM beautys g
LEFT JOIN boys b
ON b.id = g.boyfriend_id
WHERE g.name = beautyName;
END $;
CALL test_pro2('柳岩',@str)$;
SELECT @str $;
创建存储过程或函数,根据传入的条目和其实索引,查询beauty表的记录
CREATE PROCEDURE test_pro3(IN startIndex INT,IN size INT)
BEGIN
SELECT * FROM beau9ty LIMIT startIndex,size
END $;
CALL test_pro3(3,5);
函数
存储过程和函数:类似java中的方法
函数:自足预先编译好的SQL语句的集合,理解成批处理语句
好处:
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据可服务器的连接次数,提高了效率
函数和存储过程的区别
存储过程:可以有0个返回值,也可以有多个返回值,
适合左批量的插入,批量的更新
函数:有且仅有1个返回值
适合处理一个数据后返回一个结果
一、函数的创建语法
create function 函数名(参数列表)return 返回类型
begin
函数体
end $;
注意:
1、参数列表包含两部分:参数名 参数类型
2、函数体:肯定会有return语句,如果没有会报错
如果return语句没有放在函数体的最后也不会报错,但不建议
3、函数体中仅有一句话,则可以省略begin end
4、使用 delimiter 语句设置结束标记
二、调用语法
select 函数名(参数列表)
三、查看函数
show create function 函数名
四、删除函数
drop function 函数名;
案例【无参数有返回】返回公司公司员工的个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0; #定义局部变量
SELECT COUNT(*) INTO c #赋值
FROM employees;
RETURN c;
END $;
SELECT myf1()$;
【有参有返回】根据员工名,返回他的工资
CREATE FUNCTION myf2(employeeName VARCHAR(20)) RETURN DOUBLE
BEGIN
SET @salary = 0; # 定义用户变量
SELECT e.salary INTO salary # 赋值
FROM employees e
WHERE last_name = employeeName;
RETURN @salary;
END $;