Oracle - Exp(4)_存储过程与触发器

1．索引与数据库完整性

  例1-1： (建立索引)  为Score表按课程号升序、分数降序建立索引，索引名为SC_GRADE。

  例1-2： (删除索引)  删除索引SC_GRADE。

例1-3： (修改数据库表)  添加成绩表Score的参照完整性约束关系,如存在则先删除再增加。

  例1-4： (修改数据库表)  删除成绩表Score的参照完整性约束关系。

2． 存储过程与触发器：

例2-1： (存储过程) 创建一个显示学生总人数的存储过程STU_COUNT。

例2-2： (存储过程) 创建显示学生信息的存储过程STUDENT_LIST。

例2-3： (存储过程) 创建一个显示学生平均成绩的存储过程。

例2-4： (存储过程) 创建显示所有学生平均成绩的存储过程。

例2-5：(存储过程) 创建一个对学生姓名进行模糊查找的带参存储过程。

例2-6： (触发器) 创建包含插入、删除、修改多种触发事件的触发器Trig_xs_kc，对student、course和score表进行参照完整性关系的维护。

1. 当在score表中插入一条记录时，要检查其学号在student表中和课程号在course表中是否存在，若不存在则不允许插入。
2. 对student表进行删除操作，则删除score表中对应学号的所有记录；对student表的学号进行更新操作，则更新score表中对应学号的所有记录；
3. 对course表进行删除操作，则删除score表中对应课程号的所有记录；对course表的课程号进行更新操作，则更新score表中对应课程号的所有记录；

---

---

--例1-1： (建立索引)  为Score表按课程号升序、分数降序建立索引，索引名为SC_GRADE。
CREATE INDEX SC_GRADE ON SCORE(CNO ASC,SCORE DESC);

--例1-2： (删除索引)  删除索引SC_GRADE。
DROP INDEX SC_GRADE;

--例1-3： (修改数据库表)  添加成绩表Score的参照完整性约束关系,如存在则先删除再增加。
ALTER TABLE SCORE DROP CONSTRAINT FKS_CNO;
ALTER TABLE SCORE DROP CONSTRAINT FKS_SNO;
ALTER TABLE SCORE ADD CONSTRAINT FKS_SNO FOREIGN KEY(SNO) REFERENCES STUDENT(SNO);
ALTER TABLE SCORE ADD CONSTRAINT FKS_CNO FOREIGN KEY(CNO) REFERENCES COURSE(CNO);

--例1-4： (修改数据库表)  删除成绩表Score的参照完整性约束关系。
ALTER TABLE SCORE DROP CONSTRAINT FKS_CNO;
ALTER TABLE SCORE DROP CONSTRAINT FKS_SNO;



--例2-1： (存储过程) 创建一个显示学生总人数的存储过程STU_COUNT。
SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE STU_COUNT
AS
TOTAL NUMBER(4);
BEGIN
    SELECT COUNT(SNO) INTO TOTAL FROM STUDENT;
    DBMS_OUTPUT.PUT_LINE('学生总人数:'||total);
END;
/
EXECUTE STU_COUNT();




--例2-2： (存储过程) 创建显示学生信息的存储过程STUDENT_LIST。
SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE STUDENT_LIST
AS
CURSOR SELECT_INFO IS
SELECT * FROM STUDENT;
BEGIN
    FOR I IN SELECT_INFO LOOP
        DBMS_OUTPUT.PUT_LINE(I.SNO||' '||I.SNAME||' '||I.SDEPT||' '||I.SCLASS||' '||I.SSEX||' '||I.SAGE);
    END LOOP;
END;
/
EXECUTE STUDENT_LIST();


--例2-3： (存储过程) 创建一个显示学生平均成绩的存储过程。
SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE STU_AVG_SCORE
(S_NO IN STUDENT.SNO%TYPE)
AS
AVG_SCORE NUMBER(4,2);
BEGIN
    SELECT AVG(SCORE) INTO AVG_SCORE FROM SCORE GROUP BY SNO HAVING SNO=S_NO;
    DBMS_OUTPUT.PUT_LINE('学号:'||S_NO||' 平均成绩:'||AVG_SCORE);
END;
/

EXECUTE STU_AVG_SCORE('96001');

--例2-4： (存储过程) 创建显示所有学生平均成绩的存储过程。
SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE ALL_STU_AVG_SCORE
AS
CURSOR ALL_AVG IS
SELECT SNO,AVG(SCORE) AS 平均成绩 FROM SCORE GROUP BY SNO;
BEGIN
    FOR I IN ALL_AVG LOOP
        DBMS_OUTPUT.PUT_LINE('学号:'||I.SNO||' 平均成绩:'||I.平均成绩);
    END LOOP;
END;
/

EXECUTE ALL_STU_AVG_SCORE();

--例2-5：(存储过程) 创建一个对学生姓名进行模糊查找的带参存储过程。
SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE FIND_NAME
(S_NAME IN STUDENT.SNAME%TYPE)
AS
CURSOR XXX IS
SELECT SNAME FROM STUDENT WHERE SNAME LIKE '%'||S_NAME||'%';
BEGIN
FOR I IN XXX LOOP
    DBMS_OUTPUT.PUT_LINE(I.SNAME);
END LOOP;
END;
/

EXECUTE FIND_NAME('马');



--例2-6： (触发器) 创建包含插入、删除、修改多种触发事件的触发器Trig_xs_kc，对student、course和score表进行参照完整性关系的维护。
--(1)	当在score表中插入一条记录时，要检查其学号在student表中和课程号在course表中是否存在，若不存在则不允许插入。
--(2)	对student表进行删除操作，则删除score表中对应学号的所有记录；对student表的学号进行更新操作，则更新score表中对应学号的所有记录；
--(3)	对course表进行删除操作，则删除score表中对应课程号的所有记录；对course表的课程号进行更新操作，则更新score表中对应课程号的所有记录；
CREATE OR REPLACE TRIGGER TRIG_XS_KC AFTER INSERT ON SCORE
FOR EACH ROW
DECLARE
    X STUDENT.SNO%TYPE;
    Y COURSE.CNO%TYPE;
BEGIN
    SELECT SNO INTO X FROM STUDENT WHERE SNO = :NEW.SNO;
    SELECT CNO INTO y FROM COURSE WHERE CNO = :NEW.CNO;
    IF (X IS NULL AND Y IS NULL) THEN
        ROLLBACK;
    END IF;
END;
/
INSERT INTO SCORE VALUES('123','456',88);


CREATE OR REPLACE TRIGGER TRIG_XS_KC_1 AFTER UPDATE OR DELETE ON STUDENT
FOR EACH ROW
BEGIN
    IF DELETING THEN
        DELETE FROM SCORE WHERE SNO = :OLD.SNO;
    END IF;
    IF UPDATING THEN
        UPDATE SCORE SET SNO = :NEW.SNO WHERE SNO = :OLD.SNO;
    END IF;
END;
/
UPDATE STUDENT SET SNO='96006' WHERE SNO='96005';


CREATE OR REPLACE TRIGGER TRIG_XS_KC_2 AFTER UPDATE OR DELETE ON COURSE
FOR EACH ROW
BEGIN
    IF UPDATING THEN
        UPDATE SCORE SET CNO = :NEW.CNO WHERE CNO = :OLD.CNO;
        UPDATE TEACH SET CNO = :NEW.CNO WHERE CNO = :OLD.CNO;  --因为teach表和course表有约束关系，所以必须更新两个表
    END IF;
    IF DELETING THEN
        DELETE FROM SCORE WHERE CNO = :OLD.CNO;
        UPDATE TEACH SET CNO = :NEW.CNO WHERE CNO = :OLD.CNO;
    END IF;
END;
/
DELETE FROM COURSE WHERE CNO = '006';
UPDATE COURSE SET CNO = '010' WHERE CNO = '007';