存储过程和函数

• 类似于java中的方法
• 好处：
  • 提高代码的重用性
  • 简化操作

1. 存储过程

• 含义：一组预先编译好的sql语句的集合，理解成批处理语句
• 好处：
  • 提高代码的重用性
  • 简化操作
  • 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
• 语法：
  • 创建语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体(一组合法的SQL语句)
END

• • 注意
    • 参数列表包含三部分：参数模式，参数名，参数类型
      • 举例：in stuname varchar(20)
      • 参数模式：
        • in：该参数可以作为输入，也就是该参数需要调用方传入值
        • out：该参数可以作为输出，也就是该参数可以作为返回值
        • inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又需要返回值
    • 如果存储过程体仅仅只有一句话，begin end可以省略
    • 存储过程体中的每条SQL语句的结尾要求必须加分号
    • 存储过程的结尾可以使用 DELIMITER 重新设置
      • 语法：DELIMITER 结束标记
      • 案例：

DELIMITER $

• • 调用语法
    • call 存储过程名(实参列表)；
• 空参列表
  • 案例1：插入到admin表中五条记录

SELECT * FROM admin;
DELIMITER $

CREATE PROCEDURE myp1()
BEGIN
INSERT INTO admin(username, `password`)
VALUES
('john1', '0000'),
('asd', '0000'),
('joqqhn1', '0000'),
('qa', '0000'),
('ww', '0000');
END $

CALL myp1()$

• 创建带in模式参数的存储过程
  • 案例1：创建存储过程实现：根据女神名，查询对应的男神信息

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('柳岩')$
CALL myp2('王语嫣')$

• • 案例2：创建存储过程实现，用户是否登录成功

CREATE PROCEDURE myp4(IN username VARCHAR(20), IN passward 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, '成功！', '失败！'); # 使用
END $

CALL myp4('张飞', '8888')$

• 创建带out模式的存储过程
  • 案例1：根据女神名，返回对应的男神名

CREATE PROCEDURE myp5(IN beautyname VARCHAR(20), OUT boyname VARCHAR(20))
BEGIN
SELECT bo.boyname INTO boyname
FROM boys bo
INNER JOIN beauty b ON bo.id = b.boyfriend_id
WHERE b.name=beautyname;
END $

CALL myp5('王语嫣', @bname)$
SELECT @bname$

• • 案例2：根据女神名，返回对应的男神名和男神魅力值

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 b ON bo.id = b.boyfriend_id
WHERE b.name=beautyname;
END $

CALL myp6('王语嫣', @bname, @usercp)$
SELECT @bname, @usercp$

• 创建带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 @m=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$

 

• 练习题
  • 习题1：创建存储过程实现传入用户名和密码，插入到admin表中

CREATE PROCEDURE test_pro1(IN username VARCHAR(20), IN loginpwd VARCHAR(20))
BEGIN
INSERT INTO admin(admin.`username`, PASSWORD)
VALUES(username, loginpwd);
END $

CALL test_pro1('litian', '1234')$
SELECT * FROM admin$

• • 习题2：创建存储过程实现传入女神编号，返回女神名称和女神电话

CREATE PROCEDURE test_pro2(IN id INT, OUT NAME VARCHAR(20), OUT phone VARCHAR(20))
BEGIN
SELECT b.name, b.phone INTO NAME, phone
FROM beauty b
WHERE b.id=id;
END $

SET @n=''$
SET @m=''$
CALL test_pro2(1, @n, @m)$
SELECT @m,@n$

• • 习题3：创建存储过程来实现传入两个女神生日，返回大小

CREATE PROCEDURE test_pro3(IN birth1 DATETIME, IN birth2 DATETIME, OUT result INT)
BEGIN
SELECT DATEDIFF(birth1, birth2) INTO result;
END $

CALL test_pro3('1990-2-3', NOW(), @result)$
SELECT @result$

• 存储过程的删除
  • 语法：drop procedure 存储过程名

DROP PROCEDURE myp1;

• • 不能同时删除多个存储过程
• 查看存储过程的信息
  • 语法：show create procedure 存储过程名

SHOW CREATE PROCEDURE myp2;

• • 不能修改存储过程中的语句，需要修改的话，就删了重写。
• 练习题
  • 练习题1：创建存储过程实现传入一个日期，格式化成xx年xx月xx日并返回

CREATE PROCEDURE test_pro4(IN mydate DATETIME, OUT strdate VARCHAR(20))
BEGIN
SELECT DATE_FORMAT(mydate, '%y年%m月%d天') INTO strdate;
END $

CALL test_pro4(NOW(), @str)$
SELECT @str $

• • 练习题2：创建存储过程实现传入女神名称，返回：女神 and 男神 格式的字符串

CREATE PROCEDURE test_pro5(IN beautyname VARCHAR(20), OUT str VARCHAR(50))
BEGIN
SELECT CONCAT(beautyname, ' and ', IFNULL(boyname, 'null')) INTO str
FROM boys bo
RIGHT JOIN beauty b
ON b.boyfriend_id=bo.id
WHERE b.name=beautyname;
END $

CALL test_pro5('王语嫣', @result)$
SELECT @result$

• • 练习题3：创建存储过程，根据传入的起始索引和条目数，查询beauty表的记录

CREATE PROCEDURE test_pro6(IN startindex INT, IN size INT)
BEGIN
SELECT * FROM beauty LIMIT startindex, size;
END $

CALL test_pro6(3, 3)$

2. 函数

• 好处：
  • 提高代码的重用性
  • 简化操作
  • 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
• 与存储过程的区别：
  • 存储过程：可以有0个返回，也可以有多个返回（适合做批量插入、更新）
  • 函数：有且仅有1个返回（适合处理数据后返回一个结果）
• 创建语法：

create function 函数名（参数列表） returns 返回类型

begin

函数体

end

• • 注意事项：
    • 参数列表：包含两部分：参数名 参数类型，注意：没有in，out，inout这种模式了
    • 函数体：肯定会有return语句，如果没有会报错
    • 如果return语句没有放在函数体的最后也不报错，但不建议
    • 函数体中仅有一句话，则可以省略begin end
    • 使用delimiter语句设置结束标记
• 调用语法：select 函数名(参数列表)
  • 无参有返回
    • 案例1：返回公司的员工个数

CREATE FUNCTION myf1() RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0;# 定义变量
SELECT COUNT(*) INTO c# 赋值
FROM employees;
RETURN c;
END $

SELECT myf1()$

• • 有参有返回

案例1：根据员工名，返回他的工资

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')$

案例2：根据部门名，返回该部门的平均工资

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')$

• 查看函数：

SHOW CREATE FUNCTION myf3;

• 删除函数：

DROP FUNCTION myf3;

• 案例1：创建函数，实现传入两个float，返回二者之和

CREATE FUNCTION test_fun1(num1 FLOAT, num2 FLOAT) RETURNS FLOAT
BEGIN
DECLARE SUM FLOAT DEFAULT 0;
SET SUM=num1+num2;
RETURN SUM;
END $

SELECT test_fun1(1,2)$

原文链接：https://blog.csdn.net/qq_21579045/article/details/98111827