MySQL学习（5）—— 存储过程与存储函数

一. 存储过程

1.创建存储过程

CREATE PROCEDURE 存储过程名 [ ( IN | OUT | INOUT   参数名   参数类型) ]

BEGIN

              存储过程体;

END

IN：有参数无返回值，OUT：无参数有返回值，IN | OUT：有参数有返回值，INOUT：有参数有返回值。

#创建存储过程，用来查看Student表中的平均年龄 
DELIMITER //                   #默认是以 ; 结束，由于BEGIN——END 中的SELECT语句需要;结束，因此没有办法执行到END，所以使用DELIMITER将结束符改为//
CREATE PROCEDURE Avg_Age()
BEGIN 
	SELECT AVG(Sage)
    FROM Student;
END //
DELIMITER ;

CALL Avg_Age();      #调用存储过程

#创建存储过程，用来查看Student表中的最小年龄，并将最小年龄通过参数ma输出
DELIMITER //                   
CREATE PROCEDURE Min_Age(OUT ma INT)
BEGIN 
	SELECT MIN(Sage) INTO ma
    FROM Student;
END //
DELIMITER ;

CALL Min_Age(@ma);     #调用

SELECT @ma;      #查看变量的值

#创建存储过程，用来查看Student表中某个学生的年龄，并用参数S_name来输入学生姓名
DELIMITER //                   
CREATE PROCEDURE Show_Age(IN S_name VARCHAR(45))
BEGIN 
	SELECT Sage
    FROM Student
    WHERE Sname = S_name;
END //
DELIMITER ;

SET @S_name = '张三';
CALL Show_Age(@S_name);     #调用

#创建存储过程，用来查看Student表中某个学生的年龄，并用参数S_name来输入学生姓名，用S_age输出年龄
DELIMITER //                   
CREATE PROCEDURE Show_Age(IN S_name VARCHAR(45), OUT S_age INT)
BEGIN 
	SELECT Sage INTO S_age
    FROM Student 
    WHERE Sname = S_name;
END //
DELIMITER ;

SET @S_name = '张三';
CALL Show_Age(@S_name,@S_age);     #调用
SELECT @S_age;

#创建存储过程，用来查看Student表中某个学生的专业，并用参数Sn来输入学生姓名，用Sn输出专业名
DELIMITER //                   
CREATE PROCEDURE Show_Sdept(INOUT Sn VARCHAR(45))
BEGIN 
	SELECT Sdept INTO Sn
    FROM Student 
    WHERE Sname = Sn;
END //
DELIMITER ;

SET @Sn = '张三';
CALL Show_Sdept(@Sn);     #调用
SELECT @Sn;

2.查看存储过程

查看存储过程的创建信息：SHOW CREATE PROCEDURE 存储过程名;

查看存储过程的状态信息：SHOW PROCEDURE STATUS LIKE '存储过程名';

3.删除存储过程

DROP PROCEDURE 存储过程名;

 

二. 存储函数

1.创建存储函数

CREATE FUNCTION 函数名 (参数名   参数类型，....)

RETURNS 返回值类型

BEGIN

              函数体;

END

函数体中必须要有RETURN语句

#创建存储过程，该函数查询“张三”的年龄并返回 
DELIMITER //
CREATE FUNCTION Show_Age()
RETURNS INT DETERMINISTIC   #DETERMINISTIC（确定的）
BEGIN
	RETURN(
		SELECT Sage
		FROM Student
		WHERE Sname = '张三');
END //

DELIMITER ;

SELECT Show_Age();   #调用

#创建存储函数，该函数查询某个学生的年龄并返回 
DELIMITER //                   
CREATE FUNCTION Show_Age(Sn VARCHAR(45))
RETURNS INT DETERMINISTIC   
BEGIN
	RETURN(
		SELECT Sage
		FROM Student
		WHERE Sname = Sn);
END //
DELIMITER ;

SELECT Show_Age('张三');

#创建存储函数，该函数查询某个学生所在的专业人数并返回 
DELIMITER //                   
CREATE FUNCTION Show_num(Sn VARCHAR(45))
RETURNS INT DETERMINISTIC   
BEGIN
	RETURN(
		SELECT COUNT(*)
		FROM Student
		WHERE Sdept = (SELECT Sdept
					   FROM Student
                       WHERE Sname = Sn));
END //
DELIMITER ;

SET @Sn = '张三';
SELECT Show_num(@Sn);

2.查看存储函数

查看存储函数的创建信息：SHOW CREATE FUCTION 函数名;

查看存储函数的状态信息：SHOW FUCTION STATUS LIKE '函数名';

3.删除存储函数

DROP FUCTION 函数名;