【正文】 o char(10) as select 職工編號(hào) ,職工姓名 ,職業(yè)名稱 ,日期 ,情況 from State3 where 職工編號(hào) = S_Wno /*員 工對(duì)清潔工值班記錄的插入 (視圖 )*/ create procedure js_zlc1 S_Wno char(10), S_Wname char(20), S_Jname char(10), S_Wdate char(20), S_state char(20) as insert into State1 values(S_Wno,S_Wname,S_Jname,S_Wdate,S_state) /*員工對(duì)清潔工值班記錄的刪除 (視圖 )*/ create procedure js_zls1 S_Wno char(10) 04 級(jí)計(jì)算機(jī)專業(yè) 1 班 數(shù)據(jù)庫(kù)原理與應(yīng)用課程設(shè)計(jì)論文 第 29 頁(yè) as delete from State1 where 職工編號(hào) = S_Wno /*主任除清潔工外的其他職工值班記錄的插入 (視圖 )*/ create procedure js_zlc2 S_Wno char(10), S_Wname char(20), S_Jname char(10), S_Wdate char(20), S_state char(20) as insert into State2 values(S_Wno,S_Wname,S_Jname,S_Wdate,S_state) /*主任除清潔工外的其他職工值班記錄的刪除 (視圖 )*/ create procedure js_zls2 S_Wno char(10) as 04 級(jí)計(jì)算機(jī)專業(yè) 1 班 數(shù)據(jù)庫(kù)原理與應(yīng)用課程設(shè)計(jì)論文 第 30 頁(yè) delete from State2 where 職工編號(hào) = S_Wno /*主任對(duì)職工信息表的插入 */ create procedure js_zlc3 W_Wno char(10), W_Wname char(20), W_Wsex char(4), W_Wage int, W_Jname char(10), W_Addr char(40), W_Tel char(20) as insert into Worker values(W_Wno,W_Wname,W_Wsex,W_Wage,W_Jname,W_Addr,W_Tel) /*用游標(biāo)完成的主任對(duì)職工信息表的修改 */ CREATE PROCEDURE js_dmtxg1 04 級(jí)計(jì)算機(jī)專業(yè) 1 班 數(shù)據(jù)庫(kù)原理與應(yīng)用課程設(shè)計(jì)論文 第 31 頁(yè) W_Wno char(10), W_Wname char(20), W_Wsex char(4), W_Wage int, W_Jname char(10), W_Addr char(40), W_Tel char(20) AS DECLARE addWorker CURSOR STATIC FOR select * from Worker where Wno=W_Wno and Wname=W_Wname and Wsex=W_Wsex and Wage=W_Wage and Jname=W_Jname and Addr=W_Addr and Tel=W_Tel OPEN addWorker IF CURSOR_ROWS0 UPDATE Worker set Wno=W_Wno and Wname=W_Wname and Wsex=W_Wsex and Wage=W_Wage and Jname=W_Jname and Addr=W_Addr and Tel=W_Tel where Wno=W_Wno 04 級(jí)計(jì)算機(jī)專業(yè) 1 班 數(shù)據(jù)庫(kù)原理與應(yīng)用課程設(shè)計(jì)論文 第 32 頁(yè) ELSE insert into Worker(Wno,Wname,Wsex,Wage,Jname,Addr,Tel) values(W_Wno,W_Wname,W_Wsex,W_Wage,W_Jname,W_Addr,W_Tel) CLOSE addWorker DEALLOCATE addWorker /*主任對(duì)職工信息表的刪除 */ create procedure js_zls3 W_Wno char(10) as delete from Worker where Jname = W_Wno /*員工對(duì)多媒體設(shè)備損壞紀(jì)錄的插入 */ create procedure js_dmtcr1 M_Rname char(10), M_Wno char(10), M_Wname char(20), 04 級(jí)計(jì)算機(jī)專業(yè) 1 班 數(shù)據(jù)庫(kù)原理與應(yīng)用課程設(shè)計(jì)論文 第 33 頁(yè) M_Describe char(20), M_Dlevel char(10), M_Repair char(4), M_Mdate char(20), M_Pmark char(50) as insert into Media values(M_Rname,null,null,null,null,null,null,null) /*多媒體維修工對(duì)多媒體損壞紀(jì)錄的修改 */ CREATE PROCEDURE js_dmtxg1 M_Rname char(10), M_Wno char(10), M_Wname char(20), M_Describe char(20), M_Dlevel char(10), M_Repair char(4), M_Mdate char(20), M_Pmark char(50) as 04 級(jí)計(jì)算機(jī)專業(yè) 1 班 數(shù)據(jù)庫(kù)原理與應(yīng)用課程設(shè)計(jì)論文 第 34 頁(yè) delete from Media where Rname=M_Rname and Wno = null insert into Media values(M_Rname,M_Wno,M_Wname,M_Describe,M_Dlevel,M_Repair,M_Mdate,M_Pmark) /*員工對(duì)多媒體設(shè)備損壞紀(jì)錄的插入 */ create procedure js_dmtcr2 M_Rname char(10), M_Wno char(10), M_Wname char(20), M_Describe char(20), M_Repair char(4), M_Mdate char(20), M_Pmark char(50) as insert into Routine values(M_Rname,null,null,null,null,null,null) 04 級(jí)計(jì)算機(jī)專業(yè) 1 班 數(shù)據(jù)庫(kù)原理與應(yīng)用課程設(shè)計(jì)論文 第 35 頁(yè) /*維修工對(duì)常規(guī)損壞紀(jì)錄的修改 */ CREATE PROCEDURE js_dmtxg2 M_Rname char(10), M_Wno char(10), M_Wname char(20), M_Describe char(20), M_Repair char(4), M_Mdate char(20), M_Pmark char(50) as delete from Routine where Rname=M_Rname and Wno = null insert into Routine values(M_Rname,M_Wno,M_Wname,M_Describe,M_Repair,M_Mdate,M_Pmark) 04 級(jí)計(jì)算機(jī)專業(yè) 1 班 數(shù)據(jù)庫(kù)原理與應(yīng)用課程設(shè)計(jì)論文 第 36 頁(yè) 建立觸發(fā)器 /*建立職工信息表觸發(fā)器 */ CREATE TRIGGER js_zg ON FOR DELETE AS delete Duty from deleted where = delete State from deleted where = 3 數(shù)據(jù)庫(kù)調(diào)試與試運(yùn)行 由于內(nèi)容較多，這里只調(diào)試和運(yùn)行一部分。 04 級(jí)計(jì)算機(jī)專業(yè) 1 班 數(shù)據(jù)庫(kù)原理與應(yīng)用課程設(shè)計(jì)論文 第 17 頁(yè) （ 1）數(shù)據(jù)存取方面 確定數(shù)據(jù)庫(kù)的物理結(jié)構(gòu)，在關(guān)系數(shù)據(jù)庫(kù)中主要指存取方法和存儲(chǔ)結(jié)構(gòu)； 對(duì)物理結(jié)構(gòu)進(jìn)行評(píng)價(jià)，評(píng)價(jià)的 重點(diǎn)是時(shí)間和空間效率。 教室（校區(qū)名，教室名稱，所在位置，教室類型，容量）； 課程（課程號(hào)，教室名稱，課程名稱，教師編號(hào)，周次，星期，節(jié)次，課程學(xué)時(shí)，系名，班級(jí)，人數(shù)）； 職工（職工編號(hào)，姓名，職業(yè)名稱，性別，年齡，住址，聯(lián)系電話）； 04 級(jí)計(jì)算機(jī)專業(yè) 1 班 數(shù)據(jù)庫(kù)原理與應(yīng)用課程設(shè)計(jì)論文 第 14 頁(yè) 多媒體設(shè)備