create_table.sql1.txt

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

--建立学生情况数据表

  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)     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;
/



CREATE OR REPLACE TRIGGER Score_CreditH
   BEFORE INSERT OR UPDATE OF SCORE ON SCORE
      FOR  EACH ROW
DECLARE
     v_Sno           SCORE.SNO%TYPE ;
     v_Cno           SCORE.CNO%TYPE ;
     v_Score         SCORE.SCORE%TYPE ;
     v_Credit        SCORE.CREDIT%TYPE ;
   BEGIN
     v_Sno   := :NEW.SNO ;
     v_Cno   := :NEW.CNO ;
     v_Score := :NEW.SCORE ; 
    
     SELECT CCREDIT INTO v_Credit FROM COURSE    --   取课程学分
      WHERE CNO  = v_Cno;
     DBMS_OUTPUT.PUT_LINE(v_Credit);

     IF INSERTING THEN                         --   插入一行数据
        INSERT INTO SCORE(SNO, CNO, SCORE, CREDIT)
           VALUES (v_Sno,  v_Cno,  v_Score,  v_Credit) ;
     ELSE 
         UPDATE SCORE
            SET CREDIT = v_Credit
            WHERE SNO  = v_Sno
              AND CNO  = v_Cno;
     END IF;
 
    -- DBMS_OUTPUT.PUT_LINE((v_Credit);
   END  Score_CreditH;
/


  INSERT INTO SCORE VALUES('96001', '002', 60, NULL);

  UPDATE SCORE 
    SET SCORE=61
   WHERE SNO='96001'
     AND CNO='001' ;

  SELECT * FROM SCORE ORDER BY SNO;




CREATE OR REPLACE TRIGGER Score_CreditH
   AFTER INSERT OR UPDATE OF SCORE ON SCORE
      FOR  EACH ROW
DECLARE
     v_Sno           SCORE.SNO%TYPE ;
     v_Cno           SCORE.CNO%TYPE ;
     v_Score         SCORE.SCORE%TYPE ;
     v_Credit        SCORE.CREDIT%TYPE ;
   BEGIN
     v_Sno   := :OLD.SNO ;
     v_Cno   := :OLD.CNO ;
     v_Score := :NEW.SCORE ; 

     SELECT CCREDIT  INTO v_Credit FROM COURSE    --   取课程学分
      WHERE CNO  = v_Cno;
 --    DBMS_OUTPUT.PUT_LINE(v_Sno);
         UPDATE SCORE
            SET CREDIT = v_Credit
            WHERE SNO  = v_Sno
              AND CNO  = v_Cno;
 --    DBMS_OUTPUT.PUT_LINE(v_Credit);
   END  Score_CreditH;
/