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.设计一个存储过程proc_avg，输出grade表中每个学生的平均成绩。编写程序并调用该存储过程。

 

DELIMITER@@

CREATE PROCEDURE proc_avg()

BEGIN

SELECT  `student_info`.`sno`,sname,AVG(`grade`.`grade`) FROM grade JOIN student_info

ON `grade`.`sno`=`student_info`.`sno`

GROUP BY `student_info`.`sno`,`student_info`.`sname`;

END@@

 

CALL proc_avg;

 

 2.设计一个存储过程proc_list，查询grade表中指定课程(参数@cid char(4))的成绩排名前3的学生成绩信息。编写程序并调用该存储过程。

DELIMITER@@

CREATE PROCEDURE proc_list(IN cid CHAR(4))

BEGIN

SELECT sno,grade FROM grade WHERE cid=grade.cno ORDER BY grade DESC

LIMIT 3;

END@@

CALL proc_list('0001')

 

 

 

 

3.设计一个存储过程proc_infoByCname，查询某门课程某个分数段的学生信息，显示学号、姓名、分数，要求课程名称和分数段均为输入参数，请编写程序并调用该存储过程。

DELIMITER@@

CREATE PROCEDURE proc_course(IN p_cname CHAR(10),IN p_max_fs INT,IN p_min_fs INT)

BEGIN

SELECT `student_info`.`sno`,`student_info`.`sname`,`grade`.`grade`

FROM `grade` JOIN `student_info` JOIN `curriculum`

ON `grade`.`sno`=`student_info`.`sno` AND `curriculum`.`cno`=`grade`.`cno`

WHERE `curriculum`.`cname`=p_cname AND p_max_fs>=`grade`.`grade` AND `grade`.`grade`>=p_min_fs;

END@@

CALL proc_course('计算机应用基础',90,10);

 

 

 

 

 

4.设计一个存储过程proc_course，查询某门课程的平均成绩并将其输出给用户。课程名称为输入参数，平均成绩为输出参数，编写程序并调用该存储过程。

 

DELIMITER@@

CREATE PROCEDURE proc_course(IN p_cname CHAR(10),OUT p_avg_cj DECIMAL(5,2))

BEGIN

SELECT AVG(`grade`.`grade`) FROM `curriculum` JOIN `grade`

ON `curriculum`.`cno`=`grade`.`cno`

WHERE `curriculum`.`cname`=p_cname;

END@@

SET @o_avg=0.0;

CALL proc_course("数据库原理与应用",@o_avg)

 

 

 

5.设计一个存储过程count_grade_proc并编写一个事务完成如下功能：向grade表中插入一条某个学生某门课程的数据（’0004’,’0004’,0），显示该门课程的人数，如果人数超过3人，回滚该事务，并显示“该门课程人数已满”。

 

DELIMITER@@

CREATE PROCEDURE count_grade_proc(IN p_sno CHAR(4),IN p_cno CHAR(4),IN p_grade INT)

BEGIN

DECLARE course_count INT DEFAULT 0;

START TRANSACTION;

INSERT INTO grade(sno,cno,grade) VALUES(p_sno,p_cno,p_grade);

SELECT COUNT(grade) INTO course_count FROM `grade` WHERE `cno`=p_cno;

IF course_count > 3 THEN

   ROLLBACK;

   SELECT "该门课程人数已满";

ELSE

   COMMIT;

   SELECT course_count AS  "课程人数";

   END IF;

END@@

 

CALL count_grade_proc('0004','0004',0)

 

 

 

 

6.设计一个存储过程proc_credit完成这样的功能：输入学号@sid、课程名称@cname参数值，通过查询curriculum、grade表，从输出参数@score、@credit获取该学生该课程的成绩和学分，如果分数大于等于60，则返回对应课程的学分，否则返回学分值0，请编写程序并调用该存储过程。

DELIMITER@@

CREATE PROCEDURE proc_credit(IN p_sid CHAR(10),IN p_name CHAR(10),OUT p_score INT,OUT p_credit INT)

BEGIN

DECLARE course_credit INT;

SELECT `grade`.`grade`,`curriculum`.`credit` INTO p_score,course_credit FROM `curriculum` JOIN `grade`

ON `curriculum`.`cno`=`grade`.`cno`

WHERE `grade`.`sno`=p_sid AND `curriculum`.`cname`=p_name;

IF p_score>=60 THEN

SET p_credit=course_credit;

ELSE

SET p_credit=0;

END IF;

END@@

 

SET @score=60;

SET @credit=NULL;

CALL proc_credit('0001','C语言程序设计',@score,@credit);

 

SELECT @score AS "成绩",

@credit AS "学分";

 

 

 

二、实验思考

1.什么是事务，事务的ACID属性是什么？

答：

事务就是去做一个动作，要么成功，要么失败。

ACID属性是原子性，一致性，隔离性，持久性