#存储过程和函数
/*
存储过程和函数,类似于java中的方法
特点:
1.提高代码的重用性
2.简化操作
3.减少编译次数,并且减少了和数据库服务器的连接次数,提高了效率
*/
#存储过程
/*
含义:一组预先编译好的sql语句的集合,理解成批处理语句
*/
#一、创建语法
/*
create procedure 存储过程名(参数列表)
begin
存储过程体(方法体,一组合法的ql语句)
end
注意:
1.参数列表包含三部分
参数模式 参数名 参数类型
举例:
IN stuname Varchar(20)
参数模式:
iN 该参数可以作为输入,需要传入值
out 该参数可以作为输出,即可作为返回值,
INout 该参数既可以作输入又可以作输出
2.如果存储过程体仅一句话, begin end 可以省略
存储过程体的每条sql语句的结尾要求必须加分号
存储过程的结尾可以使用 delimiter 重新设置
语法:
delimiter 结束标记
案例:
delimiter $
*/
#二、调用语法
/*
call 存储过程名(实参列表);
*/
#1.空参列表
#案例:插入到admin表中的3条记录
USE girls;
SELECT * FROM admin;
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,PASSWORD)
VALUE('jj','1111'),
('gg','2222'),
('xx','3333');
END $
CALL myp1() $;
#2.创建带in模式参数的存储过程
#案例1:创建存储过程实现,根据女神名,查询对应的男神信息
DELIMITER $
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty be ON bo.`id`=be.`boyfriend_id`
WHERE be.name=beautyName;
END $
#调用柳岩的男友
CALL myp2('柳岩');
CALL myp2('热巴');
#案例2:创建存储过程实现,用户是否登录成功
DELIMITER $
CREATE PROCEDURE myp3(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0;#声明变量result
SELECT COUNT(*) INTO result
FROM admin
WHERE admin.`username`=username
AND admin.`password`=PASSWORD;
SELECT IF(result>0,'成功','失败');#使用
END $
#调用
CALL myp3('张飞','8888');
CALL myp3('jj','1111');
#3.创建out模式参数的存储过程
#案例1:根据输入的女神名,返回对应的男神名
DELIMITER $
CREATE PROCEDURE myp4(IN beautyName VARCHAR(20),OUT boysName VARCHAR(20))
BEGIN
SELECT bo.`boyName` INTO boysName
FROM boys bo
RIGHT JOIN beauty be ON bo.`id`=be.`boyfriend_id`
WHERE be.name=beautyName;
END $
#用户变量的赋值为set = @,不是declare
SET @boName:='000'$
CALL myp4('热巴',@boName)$
SELECT @boName$
#4.创建带inout模式参数的存储过程
#案例1:传入a和b两个值,最后a和b都翻倍返回
DELIMITER $
CREATE PROCEDURE myp5(INOUT a INT,INOUT b INT)
BEGIN
SET a=a*2;
SET b=b*2;
END $
SET @m=1;
SET @n=2;
CALL myp5(@m,@n);
SELECT @m,@n;
DELIMITER ;
#三、删除存储过程
#语法:drop procedure 存储过程名
DROP PROCEDURE myp1;
DROP PROCEDURE myp2,myp3;#×不可以,
#四、查看存储过程的信息
DESC myp2;#×不可以
SHOW CREATE PROCEDURE myp2;
#=============================Test=======================
#一、创建存储过程实现传入用户名和密码,插入到admin表中
DELIMITER $
CREATE PROCEDURE emp1(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
INSERT INTO admin(admin.username,admin.password)
VALUES(username,PASSWORD);
END $
CALL emp1("内裤","11111")$
#二、创建存储过程实现传入女神编号,返回女神名称和女神电话
DELIMITER $
CREATE PROCEDURE emp2(IN beid INT,OUT beName VARCHAR(20),OUT bePhone VARCHAR(20))
BEGIN
SELECT be.name,be.phone INTO beName,bePhone
FROM beauty be
WHERE be.id=beid;
END $
CALL emp2(3,@bName,@bPhone)$
SELECT @bName,@bPhone;
#三、创建存储存储过程或函数实现传入两个女神生日,返回相差天数大小
DELIMITER $
CREATE PROCEDURE emp3(IN beBorndate1 DATETIME,IN beBorndate2 DATETIME,OUT result INT)
BEGIN
SELECT DATEDIFF(beBorndate1,beBorndate2) INTO result;
END $
SET @be1='1988-02-03';
SET @be2='1988-03-03';
CALL emp3(@be1,@be2,@res);
SELECT @res;
#四、创建存储过程或函数实现传入一个日期,格式化成xx年xx月xx日并返回
DELIMITER $
CREATE PROCEDURE emp4(IN mydate DATETIME,OUT strDate VARCHAR(50))
BEGIN
SELECT DATE_FORMAT(mydate,'%Y年%m月%d日') INTO strDate;
END $
SET @date='1988-02-03';
CALL emp4(@date,@strDa);
SELECT @strDa;
#五、创建存储过程或函数实现传入女神名称,返回:女神 and 男神 格式的字符串
DELIMITER $
CREATE PROCEDURE emp5(IN BeName VARCHAR(20),OUT strName VARCHAR(50))
BEGIN
SELECT CONCAT(Bename,' and ',IFNULL(bo.boyName,'null')) INTO strName
FROM beauty be
LEFT JOIN boys bo
ON be.`boyfriend_id`=bo.`id`
WHERE be.name=BeName;
END $
CALL emp5('柳岩',@str);
SELECT @str;
#六、创建存储过程或函数,根据传入的条目数和起始索引,查询beauty表的记录
DELIMITER $
CREATE PROCEDURE emp6(IN num INT,IN startnum INT)
BEGIN
SELECT *
FROM beauty
LIMIT startnum,num;
END $
CALL emp6(8,3)$
#=========================Test end========================
#函数
/*
特点:(与存储过程相同)
1.提高代码的重用性
2.简化操作
3.减少编译次数,并且减少了和数据库服务器的连接次数,提高了效率
区别:
存储过程:可以有0个返回或多个返回,适合做批量插入,批量更新
函数:有且仅有一个返回,适合做处理数据后返回一个结果
*/
#1、创建语法:
/*
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END
注意:
1.参数列表 包含两部分: 参数名和参数类型
2.函数体,肯定要有return语句,不建议将return放在非最后一句,虽然不报错
3.函数体只有一句话可以省略begin end
4.使用delimiter语句设置结束标记
*/
#2、调用语法
/*
SELECT 函数名(参数列表)
*/
#==================案例===============================
#1.无参输入有返回
#案例:返回公司员工个数
DELIMITER $
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE countnum INT DEFAULT 0;
SELECT COUNT(*) INTO countnum
FROM employees;
RETURN countnum;
END $
SELECT myf1()$
#2.有参有返回
#案例:根据员工名返回工资
DELIMITER $
CREATE FUNCTION myf2(emName VARCHAR(20)) RETURNS INT
BEGIN
DECLARE sal INT DEFAULT 0;
SELECT salary INTO sal
FROM employees
WHERE last_name=emName;
RETURN sal;
END $
SELECT myf2('Kochhar')$
#案例2:根据部门名,返回该部门的平均工资
CREATE FUNCTION myf3(depName VARCHAR(20)) RETURNS INT
BEGIN
DECLARE avgsal INT DEFAULT 0;
SELECT AVG(salary) INTO avgsal
FROM employees e
JOIN departments d ON e.`department_id`=d.`department_id`
WHERE d.`department_name`=depName;
RETURN avgsal;
END $
SELECT myf3('Hum')$
#三、查看函数
SHOW CREATE FUNCTION myf3;
#四、删除函数
DROP FUNCTION myf3;
#案例
#一、创建函数,实现传入两个float,返回二者之和
DELIMITER $
CREATE FUNCTION myf4(a FLOAT,b FLOAT) RETURNS FLOAT
BEGIN
DECLARE SUM FLOAT DEFAULT 0;
SET SUM:=a+b;
RETURN SUM;
END $
SELECT myf4(1.1,2.54554554)$
/*
存储过程和函数,类似于java中的方法
特点:
1.提高代码的重用性
2.简化操作
3.减少编译次数,并且减少了和数据库服务器的连接次数,提高了效率
*/
#存储过程
/*
含义:一组预先编译好的sql语句的集合,理解成批处理语句
*/
#一、创建语法
/*
create procedure 存储过程名(参数列表)
begin
存储过程体(方法体,一组合法的ql语句)
end
注意:
1.参数列表包含三部分
参数模式 参数名 参数类型
举例:
IN stuname Varchar(20)
参数模式:
iN 该参数可以作为输入,需要传入值
out 该参数可以作为输出,即可作为返回值,
INout 该参数既可以作输入又可以作输出
2.如果存储过程体仅一句话, begin end 可以省略
存储过程体的每条sql语句的结尾要求必须加分号
存储过程的结尾可以使用 delimiter 重新设置
语法:
delimiter 结束标记
案例:
delimiter $
*/
#二、调用语法
/*
call 存储过程名(实参列表);
*/
#1.空参列表
#案例:插入到admin表中的3条记录
USE girls;
SELECT * FROM admin;
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username,PASSWORD)
VALUE('jj','1111'),
('gg','2222'),
('xx','3333');
END $
CALL myp1() $;
#2.创建带in模式参数的存储过程
#案例1:创建存储过程实现,根据女神名,查询对应的男神信息
DELIMITER $
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
SELECT bo.*
FROM boys bo
RIGHT JOIN beauty be ON bo.`id`=be.`boyfriend_id`
WHERE be.name=beautyName;
END $
#调用柳岩的男友
CALL myp2('柳岩');
CALL myp2('热巴');
#案例2:创建存储过程实现,用户是否登录成功
DELIMITER $
CREATE PROCEDURE myp3(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0;#声明变量result
SELECT COUNT(*) INTO result
FROM admin
WHERE admin.`username`=username
AND admin.`password`=PASSWORD;
SELECT IF(result>0,'成功','失败');#使用
END $
#调用
CALL myp3('张飞','8888');
CALL myp3('jj','1111');
#3.创建out模式参数的存储过程
#案例1:根据输入的女神名,返回对应的男神名
DELIMITER $
CREATE PROCEDURE myp4(IN beautyName VARCHAR(20),OUT boysName VARCHAR(20))
BEGIN
SELECT bo.`boyName` INTO boysName
FROM boys bo
RIGHT JOIN beauty be ON bo.`id`=be.`boyfriend_id`
WHERE be.name=beautyName;
END $
#用户变量的赋值为set = @,不是declare
SET @boName:='000'$
CALL myp4('热巴',@boName)$
SELECT @boName$
#4.创建带inout模式参数的存储过程
#案例1:传入a和b两个值,最后a和b都翻倍返回
DELIMITER $
CREATE PROCEDURE myp5(INOUT a INT,INOUT b INT)
BEGIN
SET a=a*2;
SET b=b*2;
END $
SET @m=1;
SET @n=2;
CALL myp5(@m,@n);
SELECT @m,@n;
DELIMITER ;
#三、删除存储过程
#语法:drop procedure 存储过程名
DROP PROCEDURE myp1;
DROP PROCEDURE myp2,myp3;#×不可以,
#四、查看存储过程的信息
DESC myp2;#×不可以
SHOW CREATE PROCEDURE myp2;
#=============================Test=======================
#一、创建存储过程实现传入用户名和密码,插入到admin表中
DELIMITER $
CREATE PROCEDURE emp1(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
INSERT INTO admin(admin.username,admin.password)
VALUES(username,PASSWORD);
END $
CALL emp1("内裤","11111")$
#二、创建存储过程实现传入女神编号,返回女神名称和女神电话
DELIMITER $
CREATE PROCEDURE emp2(IN beid INT,OUT beName VARCHAR(20),OUT bePhone VARCHAR(20))
BEGIN
SELECT be.name,be.phone INTO beName,bePhone
FROM beauty be
WHERE be.id=beid;
END $
CALL emp2(3,@bName,@bPhone)$
SELECT @bName,@bPhone;
#三、创建存储存储过程或函数实现传入两个女神生日,返回相差天数大小
DELIMITER $
CREATE PROCEDURE emp3(IN beBorndate1 DATETIME,IN beBorndate2 DATETIME,OUT result INT)
BEGIN
SELECT DATEDIFF(beBorndate1,beBorndate2) INTO result;
END $
SET @be1='1988-02-03';
SET @be2='1988-03-03';
CALL emp3(@be1,@be2,@res);
SELECT @res;
#四、创建存储过程或函数实现传入一个日期,格式化成xx年xx月xx日并返回
DELIMITER $
CREATE PROCEDURE emp4(IN mydate DATETIME,OUT strDate VARCHAR(50))
BEGIN
SELECT DATE_FORMAT(mydate,'%Y年%m月%d日') INTO strDate;
END $
SET @date='1988-02-03';
CALL emp4(@date,@strDa);
SELECT @strDa;
#五、创建存储过程或函数实现传入女神名称,返回:女神 and 男神 格式的字符串
DELIMITER $
CREATE PROCEDURE emp5(IN BeName VARCHAR(20),OUT strName VARCHAR(50))
BEGIN
SELECT CONCAT(Bename,' and ',IFNULL(bo.boyName,'null')) INTO strName
FROM beauty be
LEFT JOIN boys bo
ON be.`boyfriend_id`=bo.`id`
WHERE be.name=BeName;
END $
CALL emp5('柳岩',@str);
SELECT @str;
#六、创建存储过程或函数,根据传入的条目数和起始索引,查询beauty表的记录
DELIMITER $
CREATE PROCEDURE emp6(IN num INT,IN startnum INT)
BEGIN
SELECT *
FROM beauty
LIMIT startnum,num;
END $
CALL emp6(8,3)$
#=========================Test end========================
#函数
/*
特点:(与存储过程相同)
1.提高代码的重用性
2.简化操作
3.减少编译次数,并且减少了和数据库服务器的连接次数,提高了效率
区别:
存储过程:可以有0个返回或多个返回,适合做批量插入,批量更新
函数:有且仅有一个返回,适合做处理数据后返回一个结果
*/
#1、创建语法:
/*
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END
注意:
1.参数列表 包含两部分: 参数名和参数类型
2.函数体,肯定要有return语句,不建议将return放在非最后一句,虽然不报错
3.函数体只有一句话可以省略begin end
4.使用delimiter语句设置结束标记
*/
#2、调用语法
/*
SELECT 函数名(参数列表)
*/
#==================案例===============================
#1.无参输入有返回
#案例:返回公司员工个数
DELIMITER $
CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE countnum INT DEFAULT 0;
SELECT COUNT(*) INTO countnum
FROM employees;
RETURN countnum;
END $
SELECT myf1()$
#2.有参有返回
#案例:根据员工名返回工资
DELIMITER $
CREATE FUNCTION myf2(emName VARCHAR(20)) RETURNS INT
BEGIN
DECLARE sal INT DEFAULT 0;
SELECT salary INTO sal
FROM employees
WHERE last_name=emName;
RETURN sal;
END $
SELECT myf2('Kochhar')$
#案例2:根据部门名,返回该部门的平均工资
CREATE FUNCTION myf3(depName VARCHAR(20)) RETURNS INT
BEGIN
DECLARE avgsal INT DEFAULT 0;
SELECT AVG(salary) INTO avgsal
FROM employees e
JOIN departments d ON e.`department_id`=d.`department_id`
WHERE d.`department_name`=depName;
RETURN avgsal;
END $
SELECT myf3('Hum')$
#三、查看函数
SHOW CREATE FUNCTION myf3;
#四、删除函数
DROP FUNCTION myf3;
#案例
#一、创建函数,实现传入两个float,返回二者之和
DELIMITER $
CREATE FUNCTION myf4(a FLOAT,b FLOAT) RETURNS FLOAT
BEGIN
DECLARE SUM FLOAT DEFAULT 0;
SET SUM:=a+b;
RETURN SUM;
END $
SELECT myf4(1.1,2.54554554)$