6.2 存储过程

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



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



*/

#一、创建语法

sql

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN

	存储过程体（一组合法的SQL语句）
END

#注意：
/*
1、参数列表包含三部分
参数模式  参数名  参数类型
举例：
in stuname varchar(20)

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

2、如果存储过程体仅仅只有一句话，begin end可以省略
存储过程体中的每条sql语句的结尾要求必须加分号。
存储过程的结尾可以使用 delimiter 重新设置
语法：
delimiter 结束标记
案例：
delimiter $
*/


#二、调用语法

sql

CALL 存储过程名(实参列表);

#--------------------------------案例演示-----------------------------------
#1.空参列表
【例 1】
创建无参存储过程proc1，查询所有学生的基本信息，显示字段：学号、姓名、性别、年龄、班级、籍贯。
-- 修改语句结束符

sql

DELIMITER //
-- 创建存储过程
CREATE PROCEDURE proc1()
BEGIN
    -- 查询所有学生基础信息
    SELECT 
        Sno AS 学号,
        Sname AS 姓名,
        Ssex AS 性别,
        sage AS 年龄,
        class AS 班级,
        sbirthplace AS 籍贯
    FROM student;
END //
-- 恢复默认结束符
DELIMITER ;

-- 调用存储过程

sql

CALL proc1();

-- 重写时先删除存储过程
-- DROP PROCEDURE IF EXISTS proc1;

#2.创建带in模式参数的存储过程
【例 3】
创建带输入参数的存储过程proc3，输入参数为学生学号in_sno，查询该学生的所有考试成绩，显示字段：课程名、考试分数。

sql

DELIMITER //
CREATE PROCEDURE proc3(IN in_sno VARCHAR(10))
BEGIN
    -- 成绩表关联课程表，按学号筛选
    SELECT 
        c.Cname AS 课程名,
        sc.score AS 考试分数
    FROM sc
    LEFT JOIN course c ON sc.Cno = c.Cno
    WHERE sc.Sno = in_sno;
END //
DELIMITER ;

-- 调用示例：查询学号001学生的成绩

sql

CALL proc3('001');

【题目 4】
创建带输入参数的存储过程proc4，输入参数为课程号in_cno，查询并显示该课程的考试平均分，结果保留 2 位小数。

sql

DELIMITER //
CREATE PROCEDURE proc4(IN in_cno VARCHAR(10))
BEGIN
    -- 按课程号计算平均分
    SELECT 
        c.Cname AS 课程名,
        ROUND(AVG(sc.score),2) AS 课程平均分
    FROM sc
    LEFT JOIN course c ON sc.Cno = c.Cno
    WHERE sc.Cno = in_cno
    GROUP BY sc.Cno;
END //
DELIMITER ;

-- 调用示例：查询01号课程的平均分

sql

CALL proc4('01');

#3.创建out 模式参数的存储过程
【题目 6】
创建带输入和输出参数的存储过程proc6，输入参数为学生学号in_sno，输出参数为该学生的考试总科目数out_count。

sql

DELIMITER //
CREATE PROCEDURE proc6(
    IN in_sno VARCHAR(10),
    OUT out_count INT
)
BEGIN
    -- 统计学生参考科目数，赋值给输出参数
    SELECT COUNT(*) INTO out_count
    FROM sc
    WHERE Sno = in_sno;
END //
DELIMITER ;

-- 调用示例：查询学号001的参考科目数

sql

CALL proc6('001', @count);

-- 查看输出结果

sql

SELECT @count AS 总科目数;

#4.创建带inout模式参数的存储过程
#案例1：传入a和b两个值，最终a和b都翻倍并返回

sql

CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT)
BEGIN
	SET a=a*2;
	SET b=b*2;
END $

#调用

sql

SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$

#三、删除存储过程
#语法：drop procedure 存储过程名

sql

DROP PROCEDURE p1;
DROP PROCEDURE p2,p3;#×

#四、查看存储过程的信息
DESC myp2;×

sql

SHOW CREATE PROCEDURE  myp2;

←

上一节 6.1 变量

下一节 6.3 【案例讲解】存储过程

→