SQL第9篇 存储过程与函数

存储过程

含义：一组预先编译好的sql语句，可以理解成批处理语句，有点类似于函数
优点：
提高代码重用性，
简化操作，
减少编译次数和数据库服务器的连接次数，提高效率

一、创建语法

 

 create procedure 存储过程名(参数列表)
 begin

      存储过程体(一组合法的sql语句)
 end

注意：
1.参数列表包含三部分
参数模式 参数名 参数类型
举例：
in fun_name varchar(20)

参数模式
in：输入，需要调用方传入值
out：输出，可以作为返回值
inout：既可以作为输入又可以作为输出，既要传入值又可以返回值

2.加入存储过程体只有一句话，begin end 可以省略
存储过程体中的结尾必须加分号，存储过程体的结尾用delimiter重新设置
语法：
delimiter 结束标志
举例：delimiter $

二、调用方法：

CALL 存储过程名(实参列表)
in : call myp(值)  call myp(@name)  call myp(@name)$

1.空参列表

案例：向gils库中的admin表插入5条数据

 

SELECT * FROM admin; #id is AUTO_INCREMENT


DELIMITER $
CREATE PROCEDURE myp1()
BEGIN 

    INSERT INTO admin (username,`password`)
    VALUES('a','111'),('b','112'),('c','113'),
    ('d','114'),('e','115'),('f','116');
END $

CALL myp1()$

注意结束符号的使用

 

myp1-执行.png

 

插入后的结果

 

myp1.png

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('杨紫')$

cmd中设置字符集，显示中文。

 

myp2-in参数.png

案例：创建存储过程，用户是否登录（admin里存在即登录成功，查询个数>=1）

 

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,'已登录','未登录');#变量的使用
END$

CALL myp3('张飞','0000')$

if函数的使用

 

myp3.png

3.带out参数的存储过程

案例：根据女神名，返回对应的男神名

 

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

SET @bName$
CALL myp5('李沁',@bName)$

#或者 直接使用用户变量@bName
CALL myp5('杨紫',@bName,@boName)$
SELECT @bName,@boName$

多个out 的形式

 

多个out.png

4.带inout参数法存储过程

案例：传入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=10$
SET @n=20$
CALL myp6(@m,@n)$  #调用的时候传入的是变量，所以先去声明变量
SELECT @m,@n$

inout参数.png

三、删除存储过程

一次只能删除一个
语法： drop procedure 存储过程名

 

DROP PROCEDURE myp4;

四、查看存储过程信息

 

SHOW CREATE PROCEDURE myp5;

不能修改存储过程中间的sql语句，想修改的话，直接删掉重新建。

五、案例练习

1.创建存储过程或者函数，传入日期，返回xx年xx月xx日

 

CREATE PROCEDURE test_myp1(IN mydate DATETIME, OUT date_str VARCHAR(50))
BEGIN
    SELECT DATE_FORMAT(mydate,'%y年%m月%d日') INTO date_str;
END$

CALL test_myp1(NOW(),@str_date)$
SELECT @str_date$

test1.png

2.根据女神名，返回'女神名 and 男神名'，如果女神没有对应的男神，返回 '女神名 and null'
与之前的多输出进行对比，看看区别与联系，concat的使用，ifnull的使用

 

DROP PROCEDURE test_myp2$
CREATE PROCEDURE test_myp2(IN beautyName VARCHAR(20),OUT str VARCHAR(50))
BEGIN
    SELECT CONCAT(beautyName ,' and ',IFNULL(bo.boyName,'null')) INTO str
    FROM beauty b LEFT JOIN boys bo
    ON b.boyfriend_id =bo.id
    WHERE b.name=beautyName;
END$


CALL test_myp2('苍老师',@result)$
SELECT @result$

concat连接.png

3.根据传入的条目数和起始索引，查询beauty表的记录

 

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

CALL test_myp3(3,5)$

limit的使用，起始索引，显示条数；

 

limit.png

函数

含义与优点与存储过程相同
区别：
存储过程：可以有0个返回值也可以有多个返回值，适合批量插入，更新
函数：有且只有1个返回值，适合处理数据后返回一个结果

一、创建语法

 

      CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
      BEGIN

          函数体
      END

注意：
1.参数列表包含两部分：参数名 参数类型

2.函数体：肯定要有return语句，如果ruturn语句没有放在函数体最后也不报错，但是不建议
3.函数仅有一条语句，begin end 可以省略
4.使用delimiter 设置结束标记

二、调用函数

 

  SELECT 函数名(参数列表)

三、案例演示

1.无参数

返回员工个数

 

USE `myemployees`$
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(employee_name VARCHAR(20)) RETURNS DOUBLE
BEGIN
    SET @sal=0;
    SELECT salary  INTO @sal
    FROM `employees`
    WHERE `last_name`=employee_name ;
    
    RETURN @sal;
END$

SELECT myf2('Kochhar')$

3.根据部门名返回该部门的平均工资

 

DROP FUNCTION myf3$
CREATE FUNCTION myf3(department_name VARCHAR(20)) RETURNS DOUBLE
BEGIN
    DECLARE result DOUBLE ;
    
    SELECT AVG(salary) INTO result
    FROM employees e JOIN `departments` d
    ON e.`department_id`=d.`department_id`
    WHERE d.`department_name`=department_name;
    
    RETURN result;
END$

SELECT myf3('Exe')$

三、查看与删除

 

SHOW CREATE FUNCTION myf3;
DROP FUNCTION myf3;