数据库 存储过程

数据库

任务一：

利用存储过程实现下面的应用：从账户1转指定数额的款项到账户2中，假设账户关系表为account(accountnum，total)， accountnum为账号， total为余额。

任务二：

在实验二创建的学生数据库基础上，使用存储过程实现业务规则的封装，完成以下功能：

1. 统计某门课的平均成绩；
2. 按分数段[100,90]、(90,80]、(80,70]、(70,60]和(60,0]与等级制A、B、C、D、E的对应关系；将学生选课成绩从百分制改为等级制（即A、B、C、D、E）。

任务一

1.创建一个数据库bank,创建一个账户关系表Account,插入两条数据

use bank; 
CREATE TABLE Account(
Accountnum CHAR(13),
Total FLOAT
);
INSERT INTO Account VALUES(101,50);
INSERT INTO Account VALUES(108,100); 

2.定义存储过程

delimiter $
CREATE PROCEDURE transfer(IN inAccount INT,IN outAccount INT,IN amount FLOAT)

proc_label:BEGIN

DECLARE totalDepositOut FLOAT;
DECLARE totalDepositIn FLOAT;
DECLARE inAccountnum INT;


SELECT Total INTO totalDepositOut 
FROM Account 
WHERE accountnum=outAccount;

IF totalDepositOut is null 
then
ROLLBACK;
leave proc_label;
END IF;

IF totalDepositOut < amount 
then 
ROLLBACK;
LEAVE proc_label;
END IF;

SELECT Accountnum INTO inAccountnum 
FROM Account
WHERE accountnum=inAccount;
IF inAccountnum IS NULL then
ROLLBACK;
LEAVE proc_label;
END IF;

UPDATE Account SET total=total-amount WHERE accountnum=outAccount;

UPDATE Account SET total=total+amount WHERE accountnum=inAccount;

END $

delimiter ;

DROP PROCEDURE transfer;
CALL transfer(101,108,10);

任务二

1.统计某门课的平均成绩

1. 创建学生数据库

create database stu_course default character set utf8 collate utf8_general_ci;

use stu_course;

2. 建立学生student表，课程Course表，选课SC表，插入数据

CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) UNIQUE,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);
CREATE TABLE Course
(Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40) not null,
Cpno CHAR(4),
Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES Course(Cno)
);
CREATE TABLE SC
(Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (Sno,Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno),
FOREIGN KEY (Cno) REFERENCES Course(Cno)
);
INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('201215121','李勇','男',20,'CS');
INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('201215122','刘晨','女',19,'CS');
INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('201215123','王敏','女',18,'MA');
INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('201215125','张立','男',19,'IS');

INSERT INTO Course(Cno,Cname,Ccredit) VALUES('2','数学','2');
INSERT INTO Course(Cno,Cname,Ccredit) VALUES('6','数据处理','2');
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('4','操作系统','6','3');
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('7','PASCAL语言','6','4');
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('5','数据结构','7','4');
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('1','数据库','5','4');
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('3','信息系统','1','4');

INSERT INTO SC(Sno,Cno,grade) VALUES('201215121','1','92');
INSERT INTO SC(Sno,Cno,grade) VALUES('201215121','2','85');
INSERT INTO SC(Sno,Cno,grade) VALUES('201215121','3','88');
INSERT INTO SC(Sno,Cno,grade) VALUES('201215122','2','90');
INSERT INTO SC(Sno,Cno,grade) VALUES('201215122','3','80');

3. 求某门课程的平均成绩

delimiter $
CREATE PROCEDURE transfer(   /*定义存储过程*/
IN c_name CHAR(40),       /*定义参数课程名，输入c_name*/
OUT avg_grade SMALLINT)    /*定义参数平均成绩，输出avg_grade*/

proc_label:BEGIN

DECLARE cccname CHAR(40);     /*定义变量课程名*/

SELECT Cname INTO cccname FROM Course WHERE Cname=c_name;    /*将课程名Cname赋值给cccname*/  

IF cccname IS NULL
THEN
SELECT '此课程不存在';
ROLLBACK;
LEAVE proc_label;

END IF;
SELECT AVG(grade) INTO avg_grade FROM SC WHERE Cno IN (SELECT Cno FROM Course WHERE Cname=c_name);
LEAVE proc_label;
END $
delimiter ;

CALL transfer('语文',@avg_grade);  /*调用函数*/
SELECT @avg_grade;

DROP PROCEDURE  IF EXISTS transfer;

2.将学生选课成绩从百分制改为等级制（即A、B、C、D、E）

ALTER TABLE SC add newgrade char(6);    /*向SC表中增加newgrade列*/
SELECT * from SC;    


delimiter $
CREATE PROCEDURE mall()
proc_label:BEGIN


DECLARE v_sno CHAR(9);
DECLARE v_cno CHAR(9);       /*创建用于接受游标值的变量*/
DECLARE v_grade SMALLINT;
DECLARE flag int DEFAULT 0; /*游标结束的标志*/

DECLARE cur_grade CURSOR FOR SELECT Sno,Cno,Grade FROM SC; 
/*定义游标*/
DECLARE CONTINUE HANDLER FOR NOT found set flag=1;
/*指定游标循环结束时的返回值*/

OPEN cur_grade;  /*打开游标*/
FETCH cur_grade INTO v_sno,v_cno,v_grade;
/*用于判断flag*/
WHILE flag !=1 DO
/*while循环*/
IF v_grade < 60 THEN
UPDATE SC SET newgrade='E' WHERE Sno=v_sno AND Cno=v_cno;

ELSEIF v_grade < 70 AND v_grade>=60 THEN
UPDATE SC SET newgrade='D' WHERE Sno=v_sno AND Cno=v_cno;

ELSEIF v_grade < 80 AND v_grade>=70 THEN
UPDATE SC SET newgrade='c' WHERE Sno=v_sno AND Cno=v_cno;

ELSEIF v_grade < 90 AND v_grade>=80 THEN
UPDATE SC SET newgrade='B' WHERE Sno=v_sno AND Cno=v_cno;

ELSEIF v_grade <= 100 AND v_grade >=90 THEN
UPDATE SC SET newgrade='A' WHERE Sno=v_sno AND Cno=v_cno;
END IF;

FETCH cur_grade INTO v_sno,v_cno,v_grade;

END WHILE;
close cur_grade;

END $
delimiter ;

CALL mall();  /*执行存储过程*/

DROP procedure mall;

DROP PROCEDURE if exists mall;   /*删除存储过程*/