《西北工業(yè)大學(xué)數(shù)據(jù)庫實(shí)驗(yàn)報(bào)告5》由會(huì)員分享，可在線閱讀，更多相關(guān)《西北工業(yè)大學(xué)數(shù)據(jù)庫實(shí)驗(yàn)報(bào)告5（11頁珍藏版）》請?jiān)谘b配圖網(wǎng)上搜索。

《數(shù)據(jù)庫原理》實(shí)驗(yàn)報(bào)告 題目：實(shí)驗(yàn)一 數(shù)據(jù)庫和表的創(chuàng)建與管理 學(xué)號(hào) 姓名 班級(jí) 日期 一. 實(shí)驗(yàn)內(nèi)容、步驟以及結(jié)果 1. 使用系統(tǒng)存儲(chǔ)過程(sp_rename)將視圖“V_SPJ”更名為“V_SPJ_三建”。（5分） exec sp_rename v_spj,v_spj_三建 2. 針對SPJ數(shù)據(jù)庫，創(chuàng)建并執(zhí)行如下的存儲(chǔ)過程：（共計(jì)35分） (1) 創(chuàng)建一個(gè)帶參數(shù)的存儲(chǔ)過程—jsearch。該存儲(chǔ)過程的作用是：當(dāng)任意輸入一個(gè)工程代號(hào)時(shí)，將返回供應(yīng)該工程零件的供應(yīng)商的名稱(SNAME)和零件的名稱(PNAME)以及工程的名稱(JNAME)。執(zhí)行jsearch存儲(chǔ)過程，查詢“J1”對應(yīng)的信息。（10分） 創(chuàng)建： create procedure jsearch(@search_jno nchar(20) ) as begin select j.jname,s.sname,p.pname from s,p,j,spj where spj.jno=@search_jno and spj.jno=j.jno and spj.sno=s.sno and spj.pno=p.pno end 執(zhí)行： EXEC jsearch @search_jno=J1 (2) 使用S表，為其創(chuàng)建一個(gè)加密的存儲(chǔ)過程—jmsearch。該存儲(chǔ)過程的作用是：當(dāng)執(zhí)行該存儲(chǔ)過程時(shí)，將返回北京供應(yīng)商的所有信息。（10分） 創(chuàng)建： create procedure jmsearch with encryption as begin select * from S where city=北京 end 使用系統(tǒng)存儲(chǔ)過程sp_helptext查看jsearch, jmsearch的文本信息。（5分） exec sp_helptext jsearch exec sp_helptext jmsearch (3) 執(zhí)行jmsearch存儲(chǔ)過程，查看北京供應(yīng)商的情況。（5分） 執(zhí)行： exec jmsearch (4) 刪除jmsearch存儲(chǔ)過程。（5分） drop procedure jmsearch 3. 針對Student數(shù)據(jù)庫，創(chuàng)建和執(zhí)行如下的觸發(fā)器：（共計(jì)40分） (1) 刪除SC表上的外鍵約束，針對SC表創(chuàng)建一個(gè)名為insert_s的INSERT觸發(fā)器。該觸發(fā)器的功能：當(dāng)用戶向SC表中插入記錄時(shí)，如果插入的cno值不是C表中Cno的已有值，則提示用戶“不能插入記錄這樣的紀(jì)錄”，否則提示“記錄插入成功”。觸發(fā)器創(chuàng)建成功之后，向SC表插入記錄，驗(yàn)證觸發(fā)器是否正常工作。(5分) create trigger insert_s on SC instead of insert as if (exists(select *from inserted where cno not in (select cno from C))) begin print 不能插入這樣的記錄??！ rollback transaction end else print 記錄插入成功??！ 驗(yàn)證： insert into sc values (95009,8,98) 更改數(shù)據(jù)：insert into sc values (95009,5,98) (2) 為S表創(chuàng)建一個(gè)名為dele_s1的DELETE觸發(fā)器，該觸發(fā)器的作用是禁止刪除S表中的記錄。觸發(fā)器創(chuàng)建成功之后，刪除S表中的記錄，驗(yàn)證觸發(fā)器是否正常工作。(5分) 創(chuàng)建： create trigger dele_s1 on s instead of delete as rollback transaction print 禁止刪除 驗(yàn)證： delete from s where sno=95001 (3) 為S表創(chuàng)建一個(gè)名為dele_s2的DELETE觸發(fā)器，該觸發(fā)器的作用是刪除S表中的記錄時(shí)刪除SC表中該學(xué)生的選課紀(jì)錄。觸發(fā)器創(chuàng)建成功之后，刪除S表中的記錄刪除S表中的記錄，驗(yàn)證觸發(fā)器是否正常工作(SC表中的數(shù)據(jù)被正常刪除)。(5分) 創(chuàng)建： create trigger dele_s2 on s after delete as delete from sc where sno in (select sno from deleted) 驗(yàn)證： delete from s where sno=95001 S表中： Sc表中： (4) 為S表創(chuàng)建一個(gè)名為update_s的UPDATE觸發(fā)器，該觸發(fā)器的作用是禁止更新S表中“sdept”字段的內(nèi)容。觸發(fā)器創(chuàng)建成功之后，更新S表中“sdept”字段的內(nèi)容，驗(yàn)證觸發(fā)器是否正常工作。(5分) 創(chuàng)建: create trigger update_s on s instead of update as if update(sdept) begin raiserror(sdept 不能被修改,10,1) end 驗(yàn)證： (5) 禁用update_s觸發(fā)器。禁用之后，更新S表中的“sdept”字段的內(nèi)容，驗(yàn)證觸發(fā)器是否還繼續(xù)正常工作。(5分) 禁用觸發(fā)器： disable trigger update_s on s 驗(yàn)證： update s set sdept=CS1 where sno =95002 (6) 刪除update_s觸發(fā)器。(5分) drop trigger update_s (7) 創(chuàng)建一個(gè)新的課程成績統(tǒng)計(jì)表 CAvgGrade(Cno, Snum, examSNum, avgGrade)，分別表示課號(hào)，選該課程的學(xué)生人數(shù)，參加考試人數(shù)，該門課程的平均成績。利用觸發(fā)器實(shí)現(xiàn)如下的功能：當(dāng)SC表中有記錄插入、刪除或者某個(gè)人的成績更新時(shí)，自動(dòng)更新表CAvgGrade。注意SC表中的grade為NULL時(shí)表明該學(xué)生還未參加考試，計(jì)算平均成績時(shí)不需要計(jì)算該成績，但是grade為0即考試成績?yōu)?時(shí)，需要計(jì)算該成績。(10分) 創(chuàng)建表： 創(chuàng)建觸發(fā)器： use student go create trigger update_sc_cavggrade on sc for insert , delete , update as begin declare @cno char(10) declare @ssum int declare @examssum int declare @avggrade int select @cno from inserted select @cno from deleted select @ssum =count(*) from sc where @cno=cno select @examssum= count(*) from sc where @cno=cno and cgrade>=0 select @avggrade= AVG(cgrade) from sc where @cno=cno and cgrade>=0 update CAvgGrade set Ssum=@ssum,examSsum=@examssum,avgGrade=@avggrade where cno=@cno end 驗(yàn)證觸發(fā)器： 初始的CAvgGrade表 插入數(shù)據(jù)時(shí)： insert into sc values(95004,1,65) Sc表： CAvgGrade表： 刪除數(shù)據(jù)時(shí)： delete from sc where sno=95001 sc表的變化： CAvgGrade表變化： 更新數(shù)據(jù)時(shí)： update sc set cgrade=99 where sno=95004 SC表的變化： CAvgGrade表變化： 4. 創(chuàng)建一個(gè)works數(shù)據(jù)庫，其中包含員工表empoyee(eID, eName, salary)，假設(shè)該表中有1000條員工數(shù)據(jù)，完成下列要求（總計(jì)20分，每題10分）。 (1) 為了協(xié)助本題自動(dòng)生成1000條員工數(shù)據(jù)，創(chuàng)建一個(gè)自動(dòng)生成員工ID的用戶自定義函數(shù)generateEID。其中員工ID要求是一個(gè)8位的數(shù)字，前四位表示插入員工數(shù)據(jù)的當(dāng)前年份，后四位按照從0001到9999的順序增長。例如2015年插入的第一條數(shù)據(jù)是20050001，所有1000條員工ID分別是20150001-20151000。調(diào)用該函數(shù)實(shí)現(xiàn)自動(dòng)插入1000條數(shù)據(jù)。（注意插入數(shù)據(jù)的時(shí)候員工姓名可以為任意值，工資是2000-5000之間的數(shù)字） 創(chuàng)建了一個(gè)存儲(chǔ)過程來完成： use work go create procedure generateEID as begin declare @i int set @i=0 while (@i<1000) begin insert into dbo.employee values(20160001+@i,name+CAST(@i as nchar(20)),2000+CAST(FLOOR(rand()*3001)as int)) set @i=@i+1 return end end (2) 該公司計(jì)劃為員工按照一定的規(guī)則漲工資，請使用游標(biāo)創(chuàng)建一個(gè)存儲(chǔ)過程，執(zhí)行該存儲(chǔ)過程完成本次工資調(diào)整： 工資增長規(guī)則如下： l 工資在3000元以下，每月漲300元； l 工資在3000-4000元之間，每月漲200元； l 工資大于或者等于4000元，每月漲50元； use work go declare mycursor CURSOR for select salary from employee open mycursor declare @salary int fetch next from mycursor into @salary while @@FETCH_STATUS=0 begin if(@salary<3000) begin update employee set salary=@salary+300 where current of mycursor end else if(@salary<4000) begin update employee set salary=@salary+200 where current of mycursor end else begin update employee set salary=@salary+50 where current of mycursor end fetch next from mycursor into @salary end close mycursor deallocate mycursor 未改變時(shí)： 改變后： 二. 實(shí)驗(yàn)中出現(xiàn)的問題以及解決方案(對于未解決問題請將問題列出來) 除了標(biāo)題內(nèi)容以外，該部分內(nèi)容中還可以寫對于實(shí)驗(yàn)的一些感受，建議，意見等。 觸發(fā)器是對表進(jìn)行插入、更新、刪除的時(shí)候會(huì)自動(dòng)執(zhí)行的特殊存儲(chǔ)過程，本章學(xué)習(xí)的觸發(fā)器類型是after觸發(fā)器，即只有當(dāng)執(zhí)行update,delete,insert的時(shí)候這個(gè)觸發(fā)器才會(huì)被使用到，觸發(fā)器的使用為數(shù)據(jù)庫操作真的提供了很多便利 批閱者： 批閱日期： 實(shí)驗(yàn)成績： 批注：