数据库第五章 数据库完整性（+存储过程） 课后题

--6
CREATE TABLE Employee(
	Eno CHAR(9) PRIMARY KEY,
	Name CHAR(5),
	Age SMALLINT CHECK(Age <= 60),
	Etitle CHAR(20),
	Salary INT,
	Dno CHAR(9),
	FOREIGN KEY (Dno) REFERENCES Department(Dno)
);

CREATE TABLE Department(
	Dno CHAR(9) PRIMARY KEY,
	Dname CHAR(20),
	Manager CHAR(9),
	Tel CHAR(20)
);

写第五章例题的时候，存储过程的地方感觉不太懂，于是差了一些资料，在这里做一下笔记吧：

局部变量要用 @ 开头声明，使用前要用DECLARE语句对其声明
    DECLARE @变量名 数据类型;
局部变量要用SET、SELECT语句来赋值
全局变量要用 @@ 开头声明
 
执行T-SQL语句超过一个的时候就要使用 BEGIN-END 语句
 
IF-ELSE 条件判断语句
 
CASE 表达式
WHEN 表达式 THEN 表达式
ELSE 表达式
END
 
WHILE 表达式
BIGIN
END

存储过程通过EXEC语句来调用
raiserror(‘说明文本’,错误严重级别(0 ~ 18),错误标识(1 ~ 127))
修改存储过程的语法格式：
ALTER PROCEDURE <存储过程名称>
删除修改过程名称
DROP PROCEDURE <存储过程名称>

--2.1
CREATE PROCEDURE Proc_GetMathStatus @Cname CHAR(9)
AS
BEGIN TRANSACTION trans
	DECLARE
	@less60 INT,
	@more60less70 INT,
	@more70less80 INT,
	@more80less90 INT,
	@more90 INT,
	@Cno CHAR(4),
	@cnt INT
	SELECT @Cno = Cno 
	FROM Course 
	WHERE Cname=@Cname
	SELECT @cnt = count(*) 
	FROM SC 
	WHERE Cno = @Cno;
	IF @cnt = 0
	BEGIN
		raiserror('这个课没有学生或者没有这个课',5,5);
	END

	SELECT @less60 = COUNT(*) 
					 FROM SC
					 WHERE SC.Cno = @Cno AND SC.Grade < 60;
	select @more60less70 = COUNT(*) 
					 FROM SC
					 WHERE SC.Cno = @Cno AND SC.Grade >= 60 AND SC.Grade<70;
	select @more70less80 = COUNT(*) 
					 FROM SC
					 WHERE sc.Cno = @Cno AND SC.Grade >= 70 AND SC.Grade<80;
	select @more80less90 = COUNT(*) 
					 FROM SC
					 WHERE SC.Cno = @Cno AND SC.Grade >= 80 AND SC.Grade<90;
	select @more90 = COUNT(*) 
					 FROM sc
					 WHERE SC.Cno = @Cno AND SC.Grade >= 90;
	print'[0,60):'
	print @less60
	print'[60,70):'
	print @more60less70
	print'[70,80):'
	print @more70less80
	print'[80,90):'
	print @more80less90
	print'[90,100]:'
	print @more90
COMMIT TRANSACTION trans
--2.2
CREATE PROCEDURE Proc_GetCourseAvgGrade @Cname CHAR(9)
AS
BEGIN  TRANSACTION trans
	DECLARE 
	@Cno CHAR(4),
	@StuNum SMALLINT,
	@Avg SMALLINT
	SELECT @Cno = Cno 
	FROM Course 
	WHERE Cname = @Cname;
	IF @Cno is null 
	BEGIN
		raiserror('这门课不存在',5,5);
	END;
	SELECT @StuNum = count(*) 
	FROM SC 
	WHERE Cno=@Cno;
	SELECT @Avg = AVG(Grade) 
	FROM SC 
	WHERE Cno= @Cno;
	print @Cname+'共有'+convert(varchar,@StuNum)+'个学生，平均分：'+convert(varchar,@Avg)
	COMMIT;
--2.3
ALTER TABLE SC
ADD LEVEL CHAR(1)
#先执行上面这两句
CREATE PROCEDURE Proc_GetLevel
AS
	UPDATE SC 
	SET level = 'A' 
	WHERE Grade >= 80;
	UPDATE SC 
	SET level = 'B' 
	WHERE Grade < 40 and Grade >= 20;
	UPDATE SC 
	SET level = 'C' 
	WHERE Grade < 60 and Grade >= 40;
	UPDATE SC 
	SET level = 'D' 
	WHERE Grade < 80 and Grade >= 60;
	UpDATE SC 
	SET level = 'E' 
	WHERE Grade < 20;

存储过程这一段，当时上课打了个小盹，结果上课没太听懂，一些语句都不清楚什么意思，只能课后回来补习一下了，这个第一个问还是参照同学的思路做出来的，晚上看来得找同学请教一下了