MySQL 存储过程

MySQL数据

DROP DATABASE IF EXISTS studentsdb;
CREATE DATABASE IF NOT EXISTS studentsdb
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_general_ci;
USE studentsdb;
CREATE TABLE student_info (
sno char(4) NOT NULL PRIMARY KEY,
sname char(8) NOT NULL,
sex char(2),
sbirth date,
sadd varchar(50)
);


CREATE TABLE curriculum (
cno char(4) NOT NULL PRIMARY KEY,
cname varchar(50),
credit int
);


CREATE TABLE grade (
sno char(4) NOT NULL,
cno char(4) NOT NULL,
grade int,
PRIMARY KEY(sno, cno),
CONSTRAINT fk_sno FOREIGN KEY(sno) REFERENCES student_info(sno),
CONSTRAINT fk_cno FOREIGN KEY(cno) REFERENCES curriculum(cno)
);


INSERT INTO student_info
(sno,sname,sex,sbirth,sadd)
VALUES
('0001','张青平','男','2000-10-01','衡阳市东风路77号'),
('0002','刘东阳','男','1998-12-09','东阳市八一北路33号'),
('0003','马晓夏','女','1995-05-12','长岭市五一路763号'),
('0004','钱忠理','男','1994-09-23','滨海市洞庭大道279号'),
('0005','孙海洋','男','1995-04-03','长岛市解放路27号'),
('0006','郭小斌','男','1997-11-10','南山市红旗路113号'),
('0007','肖月玲','女','1996-12-07','东方市南京路11号'),
('0008','张玲珑','女','1997-12-24','滨江市新建路97号');

INSERT INTO curriculum
VALUES
('0001 ','计算机应用基础',2 ),
('0002 ','C语言程序设计',2 ),
('0003 ','数据库原理与应用',2 ),
('0004 ','英语',4 ),
('0005 ','高等数学',4 );

INSERT INTO grade
VALUES
('0001','0001',80),
('0001','0002',91),
('0001','0003',88),
('0001','0004',85),
('0001','0005',77),
('0002','0001',73),
('0002','0002',68),
('0002','0003',80),
('0002','0004',79),
('0002','0005',73),
('0003','0001',84),
('0003','0002',92),
('0003','0003',81),
('0003','0004',82),
('0003','0005',75),
('0004','0003',null),
('0005','0003',null);

 

 

 

 

一、上机内容 1. 输入以下代码，创建存储过程stu_info，执行时通过输入姓名，可以查询该姓名的学生的各科成绩。 DELIMITER @@ CREATE PROCEDURE stu_info(IN name CHAR(8))  BEGIN   SELECT s.学号,姓名,课程编号,分数 FROM student_info s,grade g    WHERE s.学号=g.学号 and 姓名=name;  END @@ 使用CALL命令执行存储过程stu_info，其参数值为'张青平'。 DELIMITER ; CALL stu_info('张青平'); DELIMITER @@ CREATE PROCEDURE stu_info(IN NAME CHAR(8))  BEGIN   SELECT s.`sno`,`sname`,`cno`,`grade` FROM student_info s,grade g    WHERE s.`sno`=g.`sno` AND `sname`=NAME;  END @@ DELIMITER ; CALL stu_info('张青平');   2. 使用studentsdb数据库中的student_info表、curriculum表、grade表。 （1）创建一个存储过程stu_grade，查询学号为0001的学生的姓名、课程名称、分数。 DELIMITER @@ CREATE PROCEDURE stu_grade(IN g_sno CHAR(8))  BEGIN   SELECT `sname`,`cno`,`grade` FROM student_info s,grade g    WHERE s.`sno`=g.`sno` AND g.`sno`=g_sno;  END @@ （2）调用存储过程stu_grade。 DELIMITER ; CALL stu_grade('0001');   3. 使用studentsdb数据库中的student_info表、curriculum表、grade表。 （1）创建存储过程stu_name，当任意输入一个学生的姓名时，查看其课程的最高分、最低分、平均分。 DELIMITER @@ CREATE PROCEDURE stu_name(IN s_name CHAR(8))  BEGIN   SELECT MIN(grade),MAX(grade),AVG(grade) FROM student_info s,grade g    WHERE s.`sno`=g.`sno`  AND `sname`=s_name;  END @@ （2）调用存储过程stu_name。 DELIMITER ; CALL stu_name('张青平'); （3）删除存储过程stu_name。 DROP PROCEDURE stu_name   4. 使用studentsdb数据库中的grade表。 （1）创建一个存储过程stu_g_r，当输入一个学生的学号时，通过返回输出参数获取该学生选修课程的门数。 DELIMITER @@ CREATE PROCEDURE stu_g_r(     IN s_sno CHAR(8),     OUT course_count INT ) BEGIN     SELECT COUNT(g.`cno`) INTO course_count     FROM grade g     WHERE g.`sno` = s_sno; END @@ DELIMITER ; （2）执行存储过程stu_g_r，输入学号0002。 SET @v_count = 0; CALL stu_g_r('0002', @v_count); SELECT @v_count AS '选修课程门数'; （3）显示0002号学生的选课门数。       5. 使用studentsdb数据库中的curriculum表、grade表。 （1）创建一个存储函数num_func，统计指定课程名称的选课人数。 DELIMITER @@ CREATE FUNCTION num_func(     c_name_in VARCHAR(50)  ) RETURNS INT DETERMINISTIC COMMENT '统计指定课程名称的选课人数' BEGIN     DECLARE num INT;     SELECT COUNT(g.sno) INTO num     FROM curriculum c     JOIN grade g ON c.cno = g.cno     WHERE c.cname = c_name_in;     RETURN num; END @@ DELIMITER ; （2）执行存储函数num_func，查看“C语言程序设计”选课人数。 SELECT num_func('C语言程序设计') AS 'C语言程序设计选课人数';   二、实验思考 1. 存储函数和存储过程如何将运算结果返回给外界？ 存储函数：通过返回值,关键字：return 存储过程：通过参数将运算结果返回，三种类型（in,out,inout） 存储函数有OUT参数、INOUT参数吗？     存储函数不能使用out,inout参数，因为MySQL存储函数被调用，必须返回一个单一的值