MySQL学习笔记之存储过程与存储函数

存储过程

基本操作

创建

# 查看emplyoees表中所有数据
CREATE PROCEDURE select_all_employees ()
BEGIN
    SELECT
        *
    FROM
        employees;
END;

调用

CALL select_all_employees ();

再看一个例子，返回所有员工平均工资：

CREATE PROCEDURE avg_employees_salary ()
BEGIN
    SELECT
        AVG(salary)
    FROM
        employees;
END;

CALL avg_employees_salary ();

出参

带出参的存储过程，定义格式：out 变量名 变量类型，然后通过into把结果传给返回变量：

CREATE PROCEDURE min_salary (OUT min_salary DOUBLE)
BEGIN
    SELECT
        MIN(salary) INTO min_salary
    FROM
        employees;
END;

调用方式：

# 调用，通过@变量名定义变量，再select该变量即可
CALL min_salary (@min_salary);

SELECT
    @min_salary;

入参

带入参的存储过程，在存储过程语句中直接用该变量即可：

CREATE PROCEDURE show_single_salary (IN empname VARCHAR(20))
BEGIN
    SELECT
        salary
    FROM
        employees
    WHERE
        employees.last_name = empname;
END;

调用方式，直接传参：

CALL show_single_salary ('Abel');

也可以通过变量传参：

SET @empname := 'Abel';

CALL show_single_salary (@empname);

带入参和出参的存储过程

CREATE PROCEDURE show_single_salary_v2 (
    IN empname VARCHAR (20),
    OUT empsalary DOUBLE
)
BEGIN
    SELECT
        salary INTO empsalary
    FROM
        employees
    WHERE
        employees.last_name = empname;

END;

调用，并传参：

CALL show_single_salary_v2 ('Abel', @min_salary);

输出结果

SELECT
    @min_salary;

带inout参数的存储过程

CREATE PROCEDURE show_mgr_name (INOUT empname VARCHAR(20))
BEGIN
    SELECT
        employees.last_name INTO empname # 此时为out
    FROM
        employees,
        (
            SELECT
                manager_id
            FROM
                employees
            WHERE
                employees.last_name = empname # 此时为in
        ) manager
    WHERE
        employees.employee_id = manager.manager_id;
END;

通过变量调用inout存储过程，并查看结果：

SET @empname = 'Abel';

CALL show_mgr_name (@empname);

SELECT
    @empname;

存储函数

存储函数，即自定义函数。先设置信任创建自定义函数，然后才能创建自定义函数：

SET GLOBAL log_bin_trust_function_creators = 1;

创建

创建存储函数时，需要指定函数名、参数列表(可有可无)、返回值类型、约束条件(可有可无)

CREATE FUNCTION email_by_name () RETURNS VARCHAR (25)
BEGIN
    RETURN ( # 直接返回结果
        SELECT
            email
        FROM
            employees
        WHERE
            last_name = 'Abel'
    );

END;

调用存储函数，通过select语句：

SELECT
    email_by_name ();

带参数的存储函数

CREATE FUNCTION email_by_id (emp_id INT) RETURNS VARCHAR (25)
BEGIN
    RETURN (
        SELECT
            email
        FROM
            employees
        WHERE
            employees.employee_id = emp_id
    );
END;

调用：

SELECT
    email_by_id (101);

再看一个例子：查看某部门的员工数：

CREATE FUNCTION count_by_id (dept_id INT) RETURNS INT
BEGIN
    RETURN (
        SELECT
            count(*)
        FROM
            employees
        WHERE
            employees.department_id = dept_id
    );

END;

调用：

SELECT
    count_by_id (30);

存储函数和存储过程的对比

	存储过程	存储函数
关键字	PROCEDURE	FUNCTION
调用语法	CALL	SELECT
返回值	0个或多个	只能是一个
应用场景	更新数据	结果为一个的查询

• 存储函数可以放在查询语句中使用，存储过程不行；
• 存储过程中可以执行对表的操作(创建、删除等)和事务操作，但存储函数不行。

查看

show create语句

用于查看创建信息：

SHOW CREATE PROCEDURE show_mgr_name;

SHOW CREATE FUNCTION count_by_id;

show status语句

用于查看状态信息：

SHOW PROCEDURE STATUS;

显示所有：

SHOW PROCEDURE STATUS LIKE 'show_mgr_name';

显示单个，可以模糊查询:

SHOW FUNCTION STATUS LIKE 'count_by_id';

从information_schema.ROUTINES表中查看

一般用于查看详细信息：

SELECT
    *
FROM
    information_schema.ROUTINES
WHERE
    ROUTINE_NAME IN (
        'show_mgr_name',
        'count_by_id'
    );

修改

我们可以修改存储过程和存储函数，不过只能修改相关特性(charateristic)：

ALTER PROCEDURE show_single_salary SQL SECURITY INVOKER COMMENT '查询最高工资';

修改存储函数就是ALTER FUNCTION：

SHOW PROCEDURE STATUS LIKE 'show_single_salary';

删除

DROP FUNCTION # 或PROCEDURE
IF EXISTS count_by_id;

优缺点

存储过程或函数的优点：

• 一次编译多次使用；
• 减少开发工作量；
• 安全性强，可以设置使用权限；
• 减少网络传输量；
• 良好的封装性。

缺点：

• 可移植性差；
• 调试困难；
• 存储过程的版本管理困难；
• 不适合高并发。