資料庫原理：實驗五：存盤程序與觸發器（22題全部代碼）

資料庫原理：實驗五：存盤程序與觸發器（22題全部代碼）

-------工程大資料庫原理實驗課
-------來源：計算機系0211181藍多多
2020年12月18日最新版
注意你的s、sc、c表中包含的資料是否正確哦，代碼藍多多一個一個除錯的都是沒問題的哦！
閱讀（抄作業）之前請先點個贊，
–1、創建一個存盤程序Pro_Com，該存盤程序的作用是：當執行該存盤程序時，回傳"計算機"系學生的所有資訊，

create procedure Pro_Com
as 
select * from s where sdept='計算機'
go

–2、執行Pro_Com存盤程序，查看"計算機"系學生的情況

exec Pro_Com

–3、洗掉Proc_Com存盤程序

drop procedure Pro_Com

–4、創建一個存盤程序Proc_grade_90，在sc表中查出成績大于90分的學生在s表中的基本資訊，并執行該存盤程序

create procedure Proc_grade_90
as 
select s.sno,sname,age,sex,sdept from s,sc where grade>90 and sc.sno=s.sno
go
exec Proc_grade_90

–5、創建帶引數的存盤程序Proc_Sname,該存盤程序回傳student資料庫中某個姓名學生選修的課程名及分數情況

create procedure Proc_Sname @sname varchar(8)
as
begin
select c.cname,grade from c,sc,s where c.cno=sc.cno and sname=@sname and s.sno=sc.sno
end
go

–6、執行Proc_Sname存盤程序，查詢“宋江”的學號、選修課程名稱和課程成績

exec Proc_Sname '宋江'

–7、使用系統存盤程序sp_helptext 查看存盤程序Proc_Sname的文本資訊

sp_helptext Proc_Sname

–8、創建一個帶引數的存盤程序Proc_Couse，該存盤程序的作用：當任意輸入一門課程的課程名時，回傳選修該課程的學生學號、學生姓名和成績，

create procedure Proc_Couse @cname varchar(8)
as
begin
select s.sno,sname,grade from c,sc,s where c.cno=sc.cno and cname=@cname and s.sno=sc.sno
end
go

–9、執行Proc_Couse 存盤程序，查詢選修“作業系統”的學生學號、學生姓名和成績

exec Proc_Couse '作業系統'

–10、創建一個帶引數的存盤程序Proc_sno，該存盤程序回傳“某個學號”學生所選課程的平均分，若執行成功，回傳整數0；若出錯，回傳錯誤號，

create PROCEDURE Proc_sno
@snoid varchar(4),@avg_grade real output
AS 
DECLARE @errorvalue int 
SET @errorvalue=0
set @avg_grade=(select avg(grade)from sc where sno=@snoid)
if(@@error<>0) --@@error是系統變數，存放當前錯誤號
   set @errorvalue=@@error
Return @errorvalue
GO

–11、呼叫Proc_sno，回傳100004學生所選課程的平均分

declare @stu_no varchar(4),@returnvalue int,@avg_g real
select @avg_g=0
select @stu_no='s2'
execute @returnvalue=Proc_sno @stu_no,@avg_g output
print'本程式執行結果：'
print'學生所選課程的平均分：'
print @avg_g
print '程式的回傳值='+cast(@returnvalue as char(14))

–12、在student中的sc表上創建一個insert觸發器Tri_insert_sc，若插入記錄的score大于已有分數的平均分，則不能插入，并提示"不能插入，分數大于已有分數的平均分"

create Trigger Tri_insert_sc on sc 
after insert as 
if (select inserted.grade from inserted)>(select avg(grade)from sc)
   begin
    print'不能插入，分數大于已有分數的平均分'
	rollback transaction
   end

–13、在SC表中插入一條記錄，學號為“S4”，課號為C4，成績為“88”

insert sc values('c4','s4',88)

–14、修改觸發器Tri_insert_sc，使得當有新的選課記錄時，若分數小于0分大于100分，則拒絕該條資料記錄，并提示“成績值必須在0-100之間，不能插入”

alter trigger Tri_insert_sc on sc after insert
as 
if (select grade from inserted)>100 or  (select grade from inserted)<0
begin 
print'成績值必須在0-100之間，不能插入'
rollback
end

–15、在student中的SC表上創建一個觸發器Tri_update_SC，對SC表中的score屬性的變動時，要求變動不能超過5%

create Trigger Tri_update_sc on sc 
after update as if update(grade)
begin
if (select (inserted.grade-deleted.grade)from inserted,deleted 
where inserted.sno=deleted.sno and inserted.cno=deleted.cno)>=(select grade*0.05 from deleted)
   begin
    print'變動不能超過5%'
	rollback transaction
   end
end

–16、將學號為“s2”、課號為“c4”的成績更改為98分

select grade from sc where sno='s2' and cno='c4';
update sc set grade=98 where sno='s2' and cno='c4';

–17、在student中的sc表上創建一個觸發器Tri_delete_sc，sc表中，若score>=60分則不允許洗掉記錄

create trigger Tri_delete_sc on sc
instead of delete
as
if (select grade from deleted)>=60
begin
  print'score>=60分,不允許洗掉記錄'
  rollback transaction
end
go

–18、洗掉學號為S2、課號為C4的選課記錄

delete from sc
where cno='c4'and sno='s2'

–19、在S表中創建一個名為Tri_Update_Sname 的UPDATE觸發器，該觸發器的作用是禁止更新S表中的SNAME欄位中的內容

create trigger Tri_Update_Sname on s
after update
as
if update(sname)
begin
print'禁止更新S表中的SNAME欄位中的內容'
rollback transaction
end

–20、將學號為“s1”的學生姓名更改為“黎明”

update s set sname='黎明' where sno='s1' 

–21、禁用Tri_Update_Sname觸發器，再次執行第（20)題的資料

alter table s disable trigger Tri_Update_Sname

–22、洗掉Tri_Update_Sname觸發器

drop trigger Tri_Update_Sname