實驗三具體要求

1、數(shù)據(jù)庫系統(tǒng)實驗二具體要求課程代碼： 課程名稱： 數(shù)據(jù)庫系統(tǒng)實驗名稱： 數(shù)據(jù)庫編程1.實驗目的1) 熟悉存儲過程的創(chuàng)建; 2) 熟悉存儲過程的調用； 熟悉帶輸入?yún)?shù)和輸出參數(shù)的存儲過程。 2.實驗工具MS SQL server3.實驗要求(1) 查詢學生表中的所有學生，并實現(xiàn)調用。CREATE PROCEDURE dbo.stu1ASBEGINSELECT * from studentEND(2) 根據(jù)學生的學號，查詢該生的詳細信息，并實現(xiàn)調用。CREATE PROCEDURE STU2stuno char(9)AS begin select * from student where Sno=s

2、tuno end調用：exec STU2 stuno=200215121(3) 根據(jù)學生的學號，查詢學生的姓名（姓名為輸出參數(shù)），并實現(xiàn)調用。create procedure stu6stuno char(9), stuname char(20) outputasbeginselect stuname=sname from student where Sno=stunoend調用：declare stuname1 char(20)exec stu6 200215122, stuname1 outputprint stuname1(4) 刪除學號為sno的學生，并實現(xiàn)調用。create proc

3、edure stu7stuno char(9)As delete from student where sno=stuno調用：exec stu7 200215121(5) 修改學號為sno學生的姓名，性別，年齡，系別，并實現(xiàn)調用。create procedure stu8stuno char(9),stuname char(20),stusex char(2),stuage smallint,studept char(20)asbeginupdate studentset Sname=stuname, Ssex=stusex, Sage=stuage, Sdept=studeptwhere

4、sno=stunoend調用：exec stu8 stuno=200215121, stuname=hello, stusex=女, stuage=22, studept=MS(6) 向學生表中插入學生，并實現(xiàn)調用。create procedure stu9stuno char(9),stuname char(20),stusex char(2), stuage smallint,studept char(20)asbegininsert into student values(stuno, stuname, stusex, stuage, studept)end調用：exec stu9 st

5、uno=200215125, stuname=張軍, stusex=男, stuage=20,studept=MA(7) 查詢姓名為sname選修的數(shù)據(jù)庫課程的成績，并實現(xiàn)調用。create procedure stu10sname char(20)as beginselect gradefrom student,course,scwhere student.sno=sc.sno and o=o and cname=數(shù)據(jù)庫 and sname=snameendexec stu10 sname=hello方法二：實現(xiàn)輸出ALTER procedure dbo.stu12sname char(20

6、),gr smallint outputas beginselect gr=gradefrom student,course,scwhere student.sno=sc.sno and o=o and cname=數(shù)據(jù)庫 and sname=snameenddeclare gr smallintexec stu12 hello, gr outputprint gr查詢選修了課程名為cname并且成績高于該門課程平均分的學生學號和成績，并實現(xiàn)調用。SQL 語句：select sno,grade from scwhere grade=(select avg(grade) from sc wher

7、e cno in(select cno from course where cname=數(shù)據(jù)庫)create procedure stu13cname char(20)asbeginselect sno,grade from scwhere grade=(select avg(grade) from sc where cno in(select cno from course wherecname=cname)end調用：exec stu13 數(shù)據(jù)庫create procedure stu14cname char(20),noo char(8) output,gr smallint outputasbeginselect noo=sno,gr=grade from scwhere grade=(select avg(grade) from sc where cno in(select cno fr