實(shí)驗(yàn)六使用TSQL編寫存儲(chǔ)過程訪問數(shù)據(jù)庫(kù).doc

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

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

3、 實(shí)驗(yàn)內(nèi)容與完成情況： create database zhangsy-drop database zhangsygouse zhangsy-創(chuàng)建三個(gè)表，分別是學(xué)生表，課程表和學(xué)生選課表；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-分別向這三個(gè)表中插入數(shù)據(jù)；insert into student values(2006010101,張

5、揚(yáng),男,19,default)insert into student values(2006010102,李勇,男,21,default)insert into student values(2006010103,王小,男,20,數(shù)學(xué)系)insert into student values(2006010104,趙娣,女,18,外語(yǔ)系)insert into student values(2006010105,劉柳,女,19,外語(yǔ)系)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,計(jì)算機(jī)文化基礎(chǔ),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 沒有學(xué)生選此門課 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 計(jì)算機(jī)文化基礎(chǔ),avg=avggrade outputprint avggrade-查詢指定系的學(xué)生的學(xué)號(hào)，姓名，所在系，課程名

10、和成績(jī)create procedure sp_dispsc_sdept sdept varchar(20)=計(jì)算機(jī)系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ù)學(xué)系10-3-查詢指

11、定系，指定成績(jī)等級(jí)的學(xué)生的學(xué)號(hào)，姓名，所在系，課程名和成績(jī)create procedure sp_dispsc_dept_level sdept varchar(20)=計(jì)算機(jī)系,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 錯(cuò)誤：輸入的成績(jī)等級(jí)不符合要求！else execute(sqltext+ where sdept=+sdept + and grade +gradestr)goexecute sp_dispsc_dept_level gradel