存储过程和函数:类似于java中的方法好处
1、提高代码的重用性
2、简化操作

存储过程

含义:一组预先编译好的SQL语句的集合,理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率

#1、创建语法

  1. CREATE PROCEDURE 存储过程名(参数列表) 
  2. BEGIN
  3.         存储过程体
  4. END

注意：
1、参数列表包含三部分IN stuname VARCHAR(20)

  • 参数模式:

IN—-该参数作为输入
OUT—-该参数作为输出，即作为返回值
INOUT—既可作为输入也可以作为输出
2、如果存储过程只有一句话，BEGIN ND 可以省略
存储过程体的每条SQL语句的结尾必须加分号
存储过程的结尾可以使用 DELIMITER 重新设置
语法：
DELIMITER 结束标记——- DELIMITER ￥

#2、调用语法
CALL 存储过程名(实参列表)；

案例：#1、空参列表

#插入到admin表中五条记录
SELECT * FROM admin;

DELIMITER $
CREATE  PROCEDURE  myp1()
BEGIN
    INSERT INTO admin(username,`password`)
    VALUES('jack1','0000'),
    ('jack2','0000'),
    ('jack3','0000'),
    ('jack4','0000'),
    ('jack5','0000');
END $

#调用
CALL myp1() $

2、创建带IN模式参数的存储过程

#创建存储过程实现根据女神名,查询对应的男神信息

DELIMITER $
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('关晓彤')$

3、创建带out模式的存储过程

#根据女神名，返回男神名

CREATE PROCEDURE myp4(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.borndate
    WHERE b.`name` = beautyName;

END $

(SET @bName$)
CALL myp4('小昭',@bName)$

SELECT @bName$

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.borndate
    WHERE b.`name` = beautyName;
END $

CALL myp6('热巴',@bName,@userCP)$

SELECT @bName,@userCP;

4、创建带inout模式参数的存储过程

CREATE PROCEDURE myp7(INOUT a INT,INOUT b INT)
BEGIN
            SET a=a*2;
            SET b=b*2;
END $
#调用
set @m=10$
set @n=20$
CALL myp(@m,@n)$
 select @m,@n$

函数

含义:一组预先编译好的SQL语句的集合,理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率
区别:
存储过程:可以有0个返回,也可以有多个返回,适合做批量插入、批量更新
函数:有且仅有1个返回,适合做处理数据后返回一个结果

#一、创建语法

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

END

注意：
1、参数列表：包含两部分—-参数名、参数类型
2、函数体：必须有return语句，否则报错，建议放在函数体最后—-return 值；
3、函数体中仅有一句话，可以省略begin end
4、使用delimiter语句设置结束符号

#二、调用语法
SELECT 函数名(参数列表)；

案例：#1、

CREATE FUNCTION myf1() RETURNS INT 
BEGIN
        DECLARE c INT DEFAULT 0;
        SELECT COUNT(*) INTO c
        FROM employees
        RETURNS 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')$

3、

#根据部门名，返回该部门的平均工资
CREATE FUNCTION myp3(depatName 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=depName；
        RETURN sal;
END $

SELECT myf3('IT')$

查看函数

  • show create funcation myf3;

删除函数

  • drop function myf3;