【数据库作业14】第五章： 数据库完整性 习题 + 存储过程

6

CREATE TABLE DEPT
(  
   Dno NUMBER(2),
   Dname VARCHAR(10),
   Mname VARCHAR(10),
   TL Char(12)
   CONSTRAINT PK_SC RIMARY KEY(Deptno)
);
CREATE TABLE EMP
(
   Eno NUMBER(4),
   Ename VARCHAR(10),
   Age NUMBER(2),
   CONSTRAINT C1 CHECK ( Aage<=60),
   Job VARCHAR(9),
   Sal NUMBER(7,2),
   Deptno NUMBER(2),
   CONSTRAINT FK_DEPTNO
   FOREIGN KEY(Deptno)
   REFFERENCES DEPT(Deptno)
);

已经建立了Student，SC , course（加入离散数学）三张表

先创建储存分段和人数的表

CREATE TABLE NUM
(
  division CHAR(20),   /*成绩划分*/
  number      INT       /*人数*/
);

储存过程

IF (exists (select * from sys.objects where name = 'count_dispersemath'))
DROP PROCEDURE count_dispersemath0
GO
CREATE OR REPLACE PROCEDURE fenshu() 
--定义存储过程
AS
DECLARE  @a90 INT,  --定义大于90分的人数
    @a8090 INT,  --定义大于80分的人数
    @a7080 INT,  --定义大于70分的人数
    @a6070 INT, --定义大于60分的人数
    @a60 INT;  --定义小于60分的人数
BEGIN
SELECT Cno
INTO countcno 
FROM course
WHERE Cname = '离散数学';
SELECT @a60=COUNT(*)
FROM SC
WHERE Cno = '8' AND Grade < 60;
SELECT  @a6070=COUNT(*)
FROM SC
WHERE Cno = '8' AND Grade >= 60 AND Grade < 70;
SELECT @a7080=COUNT(*)
FROM SC
WHERE Cno = '8' AND Grade >= 70 AND Grade < 80;
SELECT @a8090=COUNT(*)
FROM SC
WHERE Cno = '8' AND Grade >= 80 AND Grade < 90;
SELECT @a90=COUNT(*)
FROM SC
WHERE Cno = '8' AND Grade >= 90;
create table countScore( scorestage char(10), number smallint);
insert into countscore values('x<60', 60);
insert into countscore values('60<=x<70', 6070);
insert into countscore values('70<=x<80', 7080);
insert into countscore values('80<=x<90', 8090);
insert into countscore values('x>=90', 90);
END

CREATE OR REPLACE PROCEDURE  P() 
AS
BEGIN 
declare curname char(40) default ‘*‘; 
declare curcno char(4) default ‘**‘; 
declare curavg char(4); 
declare  mycursor cursor for 
select Cno, Cname 
from course;
open mycursor;
loop 
fetch mycursor into curcno, curname;  
    select avg(grade) into curavg from sc 
    where Cno = curcno; 
    insert into scoreavg values(curcno, curname, curavg); 
    end loop; 
close mycursor; 
END 

CREATE OR REPLACE PROCEDURE CP ()
AS
BEGIN
declare chgrade char(1) default 'A';
declare grade smallint(6) default 0;
declare ID int default 0;
declare  mycursor2 cursor for
    select grade from sc;
alter table sc add column(newgrade char(1));
open mycursor2;
    loop 
        fetch mycursor2 into grade;
        if grade < 60 then
            chgrade = 'E';
        elseif grade < 70 then
            chgrade = 'D';
        elseif grade < 80 then
            chgrade = 'C';
        elseif grade < 90 then
            chgrade = 'B';
        elseif
            chgrade = 'A';
        update id set id = id + 1 where id < 9
        update sc(id) set newgrade = chgrade;
    end loop;  
    close mycursor;
    END