實驗六使用TSQL編寫存儲過程訪問數(shù)據(jù)庫.doc

1、一 實驗目的1. 理解存儲過程的概念、使用方式；2. 熟悉使用t-sql編寫存儲過程來進行數(shù)據(jù)庫應用程序的設計。二 實驗工具sql server 2005利用sql server 2005 ssms及其sql查詢編輯器，使用t-sql編寫存儲過程。三 實驗內容和要求 建立學生-課程數(shù)據(jù)庫，其中包含學生表student(sno,sname,ssex,sage,sdept)、課程表：course(cno,cname,cpno,ccredit)和學生選課表：sc(sno,cno,grade)；編寫相應的存儲過程，完成下面的功能： （1）編寫一個存儲過程，可以查詢指定系的學生的選課信息，列出學號、姓名

2、、所在系、課程名和成績等內容。調用該存儲過程，測試執(zhí)行結果。（2）編寫一個存儲過程，返回指定課程的平均分。調用該存儲過程，測試執(zhí)行結果。（3）編寫一個存儲過程，可以查詢指定系指定成績等級的學生的選課信息，列出學號、姓名、所在系、課程名和成績等內容。調用該存儲過程，測試執(zhí)行結果。（成績等級為優(yōu)、良、中、及格、不及格，其中成績在90分到100分之間為優(yōu)，在80分到89分之間為良，在70分到79分之間為中，在60分到69分之間為及格，在0分到59分之間為不及格。）要求：提交創(chuàng)建存儲過程的sql腳本，并標識必要的注釋。保證程序能夠正確編譯和運行，并有相應的測試代碼。四 實驗報告4.1 實驗環(huán)境：4.2

3、 實驗內容與完成情況： create database zhangsy-drop database zhangsygouse zhangsy-創(chuàng)建三個表，分別是學生表，課程表和學生選課表；create table student ( sno char ( 10 ) primary key, sname char ( 10 ) not null, ssex char (2) check (ssex = 男 or ssex = 女), sage tinyint check (sage = 15 and sage 0), period int check (period 0), primary ke

4、y(cno) ) gocreate table sc ( sno char(10) not null, cno char(4) not null, grade tinyint, check (grade = 0 and grade = 100), primary key ( sno, cno ), foreign key ( sno ) references student ( sno ), foreign key ( cno ) references course ( cno ) )go-分別向這三個表中插入數(shù)據(jù)；insert into student values(2006010101,張

5、揚,男,19,default)insert into student values(2006010102,李勇,男,21,default)insert into student values(2006010103,王小,男,20,數(shù)學系)insert into student values(2006010104,趙娣,女,18,外語系)insert into student values(2006010105,劉柳,女,19,外語系)goupdate student set sname=王小 where sno=2006010103select * from studentgoinsert i

6、nto course values(j001,tc,2,32)insert into course values(j002,vb,2,32)insert into course values(j003,vc,2,32)insert into course values(j004,計算機文化基礎,2,32)goselect * from coursegoinsert into sc values(2006010101,j001,85)insert into sc values(2006010101,j002,80)insert into sc values(2006010101,j003,78)

7、insert into sc values(2006010101,j004,90)insert into sc values(2006010102,j001,75)insert into sc values(2006010102,j002,60)insert into sc values(2006010102,j003,77)insert into sc values(2006010102,j004,92)insert into sc values(2006010103,j001,85)insert into sc values(2006010103,j003,90)insert into s

8、c values(2006010104,j004,89)insert into sc values(2006010105,j004,76)goselect * from scgo-查詢課程的平均分create procedure sp_avggrade cname varchar(20) = tc, avg int outputas select avg=avg(grade) from sc,course where o=o and ame =cname if avg=0 print 沒有學生選此門課 else return avggodeclare avggrade intexecute s

9、p_avggrade avg=avggrade outputprint avggradedeclare avggrade intexecute sp_avggrade vb,avg=avggrade outputprint avggradedeclare avggrade intexecute sp_avggrade vc,avg=avggrade outputprint avggradedeclare avggrade intexecute sp_avggrade 計算機文化基礎,avg=avggrade outputprint avggrade-查詢指定系的學生的學號，姓名，所在系，課程名

10、和成績create procedure sp_dispsc_sdept sdept varchar(20)=計算機系asselect s.sno, s.sname, s.sdept, c.cname, sc.grade from student s left join sc on s.sno=sc.sno left join course c on sc.cno=c.cno where sdept=sdeptgoexecute sp_dispsc_sdept execute sp_dispsc_sdept 信息系execute sp_dispsc_sdept sdept=數(shù)學系10-3-查詢指

11、定系，指定成績等級的學生的學號，姓名，所在系，課程名和成績create procedure sp_dispsc_dept_level sdept varchar(20)=計算機系,gradelevel varchar(6)asdeclare sqltext varchar(200),gradestr varchar(30)set sqltext=select s.sno, s.sname, s.sdept, c.cname, sc.grade from student s left join sc on s.sno=sc.sno left join course c on sc.cno=c.c

12、noset gradestr= case when gradelevel=優(yōu) then between 90 and 100when gradelevel=良 then between 80 and 89when gradelevel=中 then between 70 and 79when gradelevel=及格 then between 60 and 69when gradelevel=不及格 then between 0 and 59when gradelevel is null then is nullelse levelerror endif gradestr=levelerror print 錯誤：輸入的成績等級不符合要求！else execute(sqltext+ where sdept=+sdept + and grade +gradestr)goexecute sp_dispsc_dept_level gradel