create_table.sql

可实现学生成绩管理系统的各种功能,无需登陆,直接查询学生的各种信息

--建立学生情况数据表

  CREATE TABLE STUDENT
   (SNO        CHAR(5),
    SNAME      VARCHAR(10)   NOT NULL,
    SDEPT      CHAR(2)       NOT NULL,
    SCLASS     CHAR(2)       NOT NULL,
    SSEX       CHAR(2)       CHECK (SSEX IN ('男','女','')), 
    SAGE       NUMBER(2)     CONSTRAINT C_SAGE CHECK (SAGE BETWEEN 16 AND 25),
    CONSTRAINT PK_S PRIMARY KEY(SNO));


--建立课程名称数据表

  CREATE TABLE COURSE 
   (CNO        CHAR(3),       
    CNAME      VARCHAR(16)   NOT NULL,
    CTIME      NUMBER(3),
    CCREDIT    NUMBER(4, 1),
    CONSTRAINT PK_C PRIMARY KEY(CNO));


--建立教师授课数据表

  CREATE TABLE TEACH 
   (TNAME      CHAR(8),
    CNO        CHAR(3),
    TDATE      DATE,   
    TDEPT      CHAR(2),
    CONSTRAINT PK_T PRIMARY KEY(TNAME,CNO,TDEPT),
    CONSTRAINT FK_T_C FOREIGN  KEY (CNO) REFERENCES COURSE(CNO)); 


--建立成绩数据表

  CREATE TABLE SCORE
   (SNO        CHAR(5),
    CNO	       CHAR(3),
    SCORE      NUMBER(5, 2),
    CREDIT     NUMBER(4, 1), 
    CONSTRAINT PK_SC PRIMARY  KEY (SNO,CNO) ,
    CONSTRAINT FK_SC_S FOREIGN  KEY (SNO)
              REFERENCES STUDENT(SNO) ON DELETE CASCADE, 
    CONSTRAINT FK_SC_C FOREIGN  KEY (CNO) REFERENCES COURSE(CNO)); 


--建立成绩视图

  CREATE VIEW STU_GR
    AS
      SELECT STUDENT.SNO, SNAME, SDEPT, SCLASS, COURSE.CNO, CNAME, SCORE
        FROM STUDENT, COURSE, SCORE
       WHERE STUDENT.SNO = SCORE.SNO
         AND COURSE.CNO  = SCORE.CNO 
         ORDER BY SDEPT, SCLASS, STUDENT.SNO; 



CREATE OR REPLACE PROCEDURE Update_Score_CreditH(
       P_Sno     IN      SCORE.SNO%TYPE,
       P_Cno     IN      SCORE.CNO%TYPE,
       p_Credit  OUT     SCORE.CREDIT%TYPE )
   AS
       v_Score        SCORE.SCORE%TYPE;
   BEGIN
      SELECT SCORE INTO v_Score FROM SCORE
       WHERE SNO  = P_Sno
         AND CNO  = P_Cno;
      IF v_Score >= 60.0 THEN                   --   取得学分，修改SCORE表中的CREDITT
         UPDATE SCORE
            SET CREDIT = ( SELECT CCREDIT  FROM COURSE
                            WHERE CNO  = P_Cno)
            WHERE SNO  = P_Sno
              AND CNO  = P_Cno;
      ELSE
         UPDATE SCORE
            SET CREDIT = NULL
            WHERE SNO  = P_Sno
              AND CNO  = P_Cno;
      END IF;
   COMMIT;
   SELECT CREDIT INTO p_Credit FROM SCORE
       WHERE SNO  = P_Sno
         AND CNO  = P_Cno;   
   DBMS_OUTPUT.PUT_LINE(TO_CHAR(p_Credit));
   END  Update_Score_CreditH;
/