存儲過程
剛開始我接觸到數據庫的時候,感覺存儲過程是很難的,但是當你看完我給你列舉的例子,你就能夠輕松的掌握存儲過程的創建和使用了。
存儲過程是在大型數據庫系統中存儲過程在數據庫中經過第一次編譯後就不需要再次編譯,用戶通過指定存儲過程的名字並給出參數來執行
例子:
查詢計算機系學生成績,列出學生姓名、課程名、成績
create procedure student_grade1
as
select sname,cname,grade
from student s join sc on s.sno=sc.cno
join course c on c,cno=sc.cno
where sdept = ‘計算機系’;
查詢某個制定系學生的考試情況,列出姓名,所在系、課程名和成績
create procedure student_grade2
@dept char(20) --參數
as
select sname,sdept,cname,grade
from student s,sc,course c
where s.sno=sc.cno and c.cno=sc.cno
and sdept = @dept
查詢某個學生,某門課程的成績,列出學生名、課程名、成績
create procedure student_grade3
@stu_name char(10),@course_name char(20)
as
select sname,cname,grade
from student s join sc on s.sno=sc.cno
join course c on c.cno=sc.cno
where
sname = @stu_name and
cname= @course_name
調用過程用exec
exec student_grade3
可以輸入指定學生和某門課程
查詢某個學生某門課程的考試成績,默認課程為數據庫
create procedure student_grade4
@stu_name char(10),
@course_name char(20) = ‘數據庫’
as
select sname,cname,grade
from student s join sc on s.sno=sc.cno
join course c on c.cno=sc.cno
where
sname = @stu_name and
cname= @course_name
查詢指定列,指定性別的學生中年齡大於制定年齡的學生
create procedure student_grade5
@sex char(2) = ‘男’,
@age int = 20,
@dept char(20) = ‘計算機’
as
select * from student
where sex = @sex and age<@age
and sdept = @sdept
計算兩個數的和
create procedure sum
@var1 int ,var2 int,var3 int output
as
var3 = var1+var2
統計制定課程的平均成績,並將統計結果用輸出參數返回
create procedure avggrade
@cname char(20),
@avg_grade int output
as
select @avg_grade = avggrade
from sc join course c on c.cno=sc.cno
where cname = @cname
將指定課程的學分加2分
create procedure udgrade
@cname char(20)
as
update course set credit = credit+2
where cname = @cname