前言:
本篇文章是記錄學(xué)校學(xué)習(xí)SQL server中知識(shí),可用于復(fù)習(xí)資料.
一、存儲(chǔ)過(guò)程的創(chuàng)建
定義:
存儲(chǔ)過(guò)程是為了完成特定功能的SQL語(yǔ)句集合，存儲(chǔ)在數(shù)據(jù)庫(kù)中，用戶通過(guò)指定存儲(chǔ)過(guò)程的名稱并給出參數(shù)來(lái)執(zhí)行。
優(yōu)點(diǎn)：
1、方便修改。
??因?yàn)?mark>存儲(chǔ)過(guò)程是存儲(chǔ)在數(shù)據(jù)庫(kù)中的，如果需要涉及到修改SQL語(yǔ)句，那么數(shù)據(jù)庫(kù)專業(yè)人員只需要去修改數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程就可以，對(duì)程序毫無(wú)影響，如果用SQL語(yǔ)句的話，SQL語(yǔ)句是寫(xiě)在程序中的，如果涉及到修改SQL語(yǔ)句，那么就需要去修改源程序。
2、存儲(chǔ)過(guò)程比SQL語(yǔ)句執(zhí)行更快速:
??存儲(chǔ)過(guò)程是為了完成特定功能的SQL語(yǔ)句的集合，如果為了完成某一功能，使用了大量的SQL語(yǔ)句，那么執(zhí)行存儲(chǔ)過(guò)程只執(zhí)行一次就可以，而SQL語(yǔ)句呢，則是需要執(zhí)行多個(gè)。就類似于c語(yǔ)言中的自定義函數(shù),甚至比自定義函數(shù)還要靈活很多.
1、創(chuàng)建簡(jiǎn)單存儲(chǔ)過(guò)程
(1)創(chuàng)建一個(gè)名為stu_pr的存儲(chǔ)過(guò)程，該存儲(chǔ)過(guò)程能查詢出o51班學(xué)生的所有資料，包括學(xué)生的基本信息、學(xué)生的選課信息（含未選課同學(xué)的信息)。要求在創(chuàng)建存儲(chǔ)過(guò)程前請(qǐng)判斷該存儲(chǔ)過(guò)程是否已創(chuàng)建，若已創(chuàng)建則先刪除，并給出“已刪除! p信息，否則就給出“不存在，可創(chuàng)建! ”的信息。
先進(jìn)行判斷是否存在:
if exists (select * from sysobjects where name='stu_pr' and type='P')begin
drop procedure stu_pr print '已刪除! '
end
else
print '不存在，可創(chuàng)建! '
執(zhí)行結(jié)果:
創(chuàng)建語(yǔ)句:
create procedure stu_pr
as
select distinct * from student s
left join sc on s.sno=sc.sno
left join course c on c.Cno=sc.Cno where classno='051'
使用剛剛創(chuàng)建的存儲(chǔ)過(guò)程:
exec stu_pr
執(zhí)行結(jié)果:
2、創(chuàng)建帶參數(shù)的存儲(chǔ)過(guò)程
(1)創(chuàng)建一個(gè)名為stu_proc1的存儲(chǔ)過(guò)程，查詢某系、某姓名的學(xué)生的學(xué)號(hào)、姓名、年齡，選修課程名、成績(jī)。系名和姓名在調(diào)用該存儲(chǔ)過(guò)程時(shí)輸入，其默認(rèn)值分別為“%”與"林%”。執(zhí)行該存儲(chǔ)過(guò)程，用多種參數(shù)加以測(cè)試。
if exists (select * from sysobjects where name='stu_proc1' and type='P')
begin
drop procedure stu_proc1 print '已冊(cè)刪除!'
end
else
print '不存在，可創(chuàng)建! '
執(zhí)行結(jié)果:
創(chuàng)建語(yǔ)句:
create procedure stu_proc1
@sdept varchar(10)='%', @sname varchar(10)='林%'
as
select Sname , s.Sno, YEAR(getdate ( ) ) -YEAR(Birth) Age, Cname ,Grade from student s, Course c,sc
where s.Sno=sc.sno and c.Cno=sc.Cno
and s.Sname like @sname and s.sdept like @sdept
執(zhí)行結(jié)果:
執(zhí)行已經(jīng)創(chuàng)建好的存儲(chǔ)過(guò)程
執(zhí)行1:
exec stu_proc1
執(zhí)行2:
exec stu_proc1 @sdept='%', @sname ='林%'
執(zhí)行結(jié)果:
(2）創(chuàng)建一個(gè)名為Student_sc的存儲(chǔ)過(guò)程，可查詢出某段學(xué)號(hào)的同學(xué)的學(xué)號(hào)、姓名、總成績(jī)。(學(xué)號(hào)起始號(hào)與終止號(hào)在調(diào)用時(shí)輸入，可設(shè)默認(rèn)值)。執(zhí)行該存儲(chǔ)過(guò)程。
if exists (select name from sysobjects where name='student_sc' and type='P')
drop procedure student_sc
go
create procedure student_sc
@sno_begin varchar (10)='20110001 ',@sno_end varchar (10)='20110103'
as
select s.Sno,Sname , SUM (grade) total_grade from student s,sc
where s.sno=sc.sno and s.sno between @sno_begin and @sno_end
group by s.sno ,sname
執(zhí)行:
exec student_sc
3、創(chuàng)建帶輸出參數(shù)的存儲(chǔ)過(guò)程
(1）創(chuàng)建一個(gè)名為Course_sum的存儲(chǔ)過(guò)程，可查詢某門課程考試的總成績(jī)。總成績(jī)可以輸出，以便進(jìn)一步調(diào)用。
if exists (select name from sysobjects where name='Course_sum' and type='P ')
drop procedure course_sum
創(chuàng)建:
create procedure course_sum @cname varchar(10)='告'
as
select SUM(grade) total_grade , COUNT(sno)sno from course c, sc
where c.Cno=sc.Cno and Cname like cname
執(zhí)行:
exec course_sum '高數(shù)'
(2）創(chuàng)建一執(zhí)行該存儲(chǔ)過(guò)程的批處理，要求當(dāng)總成績(jī)小于100時(shí)，顯示信息為:“XXX課程的總.成績(jī)?yōu)?XX,其總分未達(dá)100分”。超過(guò)100時(shí)，顯示信息為。“XX課程的總成績(jī)?yōu)?XX”。
create procedure sum_grade2
@cname varchar (10) , @@sum smallint output
as
select @@sum=sum ( grade)from course c , sc
where c.Cno=sc.Cno and Cname like cname
創(chuàng)建:
declare @@sumgrade smallint
exec sum_grade2 '高數(shù)',@@sumgrade output if @@sumgrade<100
begin
print '高數(shù)的總成績(jī)?yōu)?'+CAST(@@sumgrade as varchar(20))+'，其總分未達(dá)到分。'
end
else
print '高數(shù)的總成績(jī)?yōu)?'+CAST(@@sumgrade As varchar (20))+'。'
創(chuàng)建:
create procedure update_sc
@cno varchar(10),@sno varchar (10) , @grade int with recompile,encryption--重編譯，加密
as
update sc
set grade=@grade
where sc.cno=@cno and sc.sno=@sno
exec update_sc '001','20110001','80'
二 、使用T一SQL語(yǔ)句管理和維護(hù)存儲(chǔ)過(guò)程
2.1 使用sp_helptext查看存儲(chǔ)過(guò)程student_sc的定義腳本
語(yǔ)句:
sp_helptext student_sc
2.2 使用select語(yǔ)句查看student_sc存儲(chǔ)過(guò)程的定義腳本（提示:通過(guò)查詢表sysobjects和表
語(yǔ)句:
syscomments)
select o.id, c.text
from sysobjects o inner join syscomments c
on o.id = c.id
where o.type = 'p' and o.name = 'student_sc'
2.3 修改存儲(chǔ)過(guò)程
將存儲(chǔ)過(guò)程stu_pr改為查詢學(xué)號(hào)為2011001的學(xué)生的詳細(xì)資料。
alter procedure stu_pr
@sno varchar (10)
as
select distinct * from student
where sno='20110001'
執(zhí)行:
exec stu_pr @sno=’2011001’
2.4 刪除存儲(chǔ)過(guò)程
drop procedure stu_pr
6、使用sQL Server Management Studi管理存儲(chǔ)過(guò)程
(1）在SQL Server Management Studio中重新創(chuàng)建剛刪除的存儲(chǔ)過(guò)程stu_pr
選擇數(shù)據(jù)庫(kù)student_info→可編程性→存儲(chǔ)過(guò)程，右擊“存儲(chǔ)過(guò)程”→新建存儲(chǔ)過(guò)程
(2）查看存儲(chǔ)過(guò)程`stu_pr`，并將該過(guò)程修改為查詢051班女生的所有資料。可編程性→存儲(chǔ)過(guò)程→>stu _pr，右擊stu _pr→>修改.
(3） 修改sQL語(yǔ)句，使之能查詢051班所有女生的資料的存儲(chǔ)過(guò)程
(3）刪除存儲(chǔ)過(guò)程stu_pr
選擇存儲(chǔ)過(guò)程stu _pr，右擊,選擇“刪除”文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-478762.html
文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-478762.html
到了這里，關(guān)于使用SQL語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
