MySql存储过程与函数详解

《MySql存储过程与函数详解》是一篇涉及MySql数据库存储操作的文章，本文将详细讲解MySql存储过程与函数的概念、语法及使用方法，并提供两个示例来帮助读者更好地理解。

MySql存储过程

概念

MySql存储过程是一种预先编写好的用于执行特定任务的程序单元，存储在数据库中，其类似于程序代码的概念，可以通过调用存储过程来完成数据库操作。

语法

创建存储过程：

CREATE PROCEDURE 存储过程名([输入参数,[输出参数,...]])
BEGIN
    存储过程的操作语句;
END;

调用存储过程：

CALL 存储过程名([参数值,...]);

示例1

创建一个存储过程，完成对student表中指定学号的学生信息的查询，并将结果返回：

CREATE PROCEDURE select_student_by_id(IN stu_id INT, OUT stu_name VARCHAR(20), OUT stu_age INT)
BEGIN
        SELECT name,age INTO stu_name,stu_age FROM student WHERE id=stu_id;
END;

调用存储过程，对学号为001的学生进行查询：

CALL select_student_by_id(001,@name,@age);
SELECT @name,@age;

示例2

创建一个存储过程，完成对student表中指定班级的学生总数的查询，并将结果返回：

CREATE PROCEDURE count_student_by_class(IN class_name VARCHAR(20), OUT stu_num INT)
BEGIN
        SELECT COUNT(*) INTO stu_num FROM student WHERE class=class_name;
END;

调用存储过程，查询班级为一班的学生总数：

CALL count_student_by_class('一班',@num);
SELECT @num;

MySql函数

概念

MySql函数是一种可重用的代码块，用于完成一定的查询和计算操作，并返回一个值或表。

语法

创建函数：

CREATE FUNCTION 函数名(参数列表) RETURNS 返回值类型
BEGIN
    函数的SQL语句;
    RETURN 返回值;
END;

调用函数：

SELECT 函数名(参数列表);

示例1

创建一个函数，计算指定学号的学生平均分并返回：

CREATE FUNCTION get_avg_score(stu_id INT) RETURNS DECIMAL(5,2)
BEGIN
        DECLARE avg_score DECIMAL(5,2);
        SELECT AVG(score) INTO avg_score FROM score WHERE id=stu_id;
        RETURN avg_score;
END;

调用函数，查询学号为001的学生的平均分：

SELECT get_avg_score(001);

示例2

创建一个函数，查询所有学生成绩最高的课程及分数：

CREATE FUNCTION max_score_course() RETURNS TABLE(course_name VARCHAR(20),score INT)
BEGIN
        DECLARE max_score INT DEFAULT 0;
        SELECT MAX(score) INTO max_score FROM score;
        SELECT course,score INTO course_name,score FROM score WHERE score=max_score;
        RETURN;
END;

调用函数，查询所有学生成绩最高的课程及分数：