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表进行参照完整性关系的维护。
- 当在score表中插入一条记录时,要检查其学号在student表中和课程号在course表中是否存在,若不存在则不允许插入。
- 对student表进行删除操作,则删除score表中对应学号的所有记录;对student表的学号进行更新操作,则更新score表中对应学号的所有记录;
- 对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';