SQLSERVER存儲過程基本語法

一、定義變量
--簡單賦值?
declare?@a?int
set?@a=5?
print?@a?
??
--使用select語句賦值?
declare?@user1?nvarchar(50)?
select?@user1='張三'
print?@user1?
declare?@user2?nvarchar(50)?
select?@user2?=?Name?from?ST_User?where?ID=1?
print?@user2?
??
--使用update語句賦值?
declare?@user3?nvarchar(50)?
update?ST_User?set?@user3?=?Name?where?ID=1?
print?@user3
?

二、表、臨時表、表變量

--創(chuàng)建臨時表1?
create?table?#DU_User1?
(?
?????[ID]?[int]??NOT?NULL,?
?????[Oid]?[int]?NOT?NULL,?
?????[Login]?[nvarchar](50)?NOT?NULL,?
?????[Rtx]?[nvarchar](4)?NOT?NULL,?
?????[Name]?[nvarchar](5)?NOT?NULL,?
?????[Password]?[nvarchar](max)?NULL,?
?????[State]?[nvarchar](8)?NOT?NULL
);?
--向臨時表1插入一條記錄?
insert?into?#DU_User1?(ID,Oid,[Login],Rtx,Name,[Password],State)?values?(100,2,'LS','0000','臨時','321','特殊');?
??
--從ST_User查詢數(shù)據(jù)，填充至新生成的臨時表?
select?*?into?#DU_User2?from?ST_User?where?ID<8?
??
--查詢并聯(lián)合兩臨時表?
select?*?from?#DU_User2?where?ID<3?union?select?*?from?#DU_User1?
??
--刪除兩臨時表?
drop?table?#DU_User1?
drop?table?#DU_User2
?
--創(chuàng)建臨時表?
CREATE?TABLE?#t?
(?
????[ID]?[int]?NOT?NULL,?
????[Oid]?[int]?NOT?NULL,?
????[Login]?[nvarchar](50)?NOT?NULL,?
????[Rtx]?[nvarchar](4)?NOT?NULL,?
????[Name]?[nvarchar](5)?NOT?NULL,?
????[Password]?[nvarchar](max)?NULL,?
????[State]?[nvarchar](8)?NOT?NULL,?
)?
??
--將查詢結(jié)果集（多條數(shù)據(jù)）插入臨時表?
insert?into?#t?select?*?from?ST_User?
--不能這樣插入?
--select?*?into?#t?from?dbo.ST_User?
??
--添加一列，為int型自增長子段?
alter?table?#t?add?[myid]?int?NOT?NULL?IDENTITY(1,1)?
--添加一列，默認填充全球唯一標識?
alter?table?#t?add?[myid1]?uniqueidentifier?NOT?NULL?default(newid())?
??
select?*?from?#t?
drop?table?#t
--給查詢結(jié)果集增加自增長列?
??
--無主鍵時：?
select?IDENTITY(int,1,1)as?ID,?Name,[Login],[Password]?into?#t?from?ST_User?
select?*?from?#t?
??
--有主鍵時：?
select?(select?SUM(1)?from?ST_User?where?ID<=?a.ID)?as?myID,*?from?ST_User?a?order?by?myID
--定義表變量?
declare?@t?table
(?
????id?int?not?null,?
????msg?nvarchar(50)?null
)?
insert?into?@t?values(1,'1')?
insert?into?@t?values(2,'2')?
select?*?from?@t
?三、循環(huán)

--while循環(huán)計算1到100的和?
declare?@a?int
declare?@sum?int
set?@a=1?
set?@sum=0?
while?@a<=100?
begin
????set?@sum+=@a?
????set?@a+=1?
end
print?@sum
四、條件語句

--if,else條件分支?
if(1+1=2)?
begin
????print?'對'
end
else
begin
????print?'錯'
end
??
--when?then條件分支?
declare?@today?int
declare?@week?nvarchar(3)?
set?@today=3?
set?@week=case
????when?@today=1?then?'星期一'
????when?@today=2?then?'星期二'
????when?@today=3?then?'星期三'
????when?@today=4?then?'星期四'
????when?@today=5?then?'星期五'
????when?@today=6?then?'星期六'
????when?@today=7?then?'星期日'
????else?'值錯誤'
end
print?@week
?

五、游標

declare?@ID?int
declare?@Oid?int
declare?@Login?varchar(50)?
??
--定義一個游標?
declare?user_cur?cursor?for?select?ID,Oid,[Login]?from?ST_User?
--打開游標?
open?user_cur?
while?@@fetch_status=0?
begin
--讀取游標?
????fetch?next?from?user_cur?into?@ID,@Oid,@Login?
????print?@ID?
????--print?@Login?
end
close?user_cur?
--摧毀游標?
deallocate?user_cur
六、觸發(fā)器

　　觸發(fā)器中的臨時表：

　　Inserted?
　　存放進行insert和update?操作后的數(shù)據(jù)?
　　Deleted?
　　存放進行delete?和update操作前的數(shù)據(jù)

--創(chuàng)建觸發(fā)器?
Create?trigger?User_OnUpdate??
????On?ST_User??
????for?Update?
As?
????declare?@msg?nvarchar(50)?
????--@msg記錄修改情況?
????select?@msg?=?N'姓名從“'?+?Deleted.Name?+?N'”修改為“'?+?Inserted.Name?+?'”'?from?Inserted,Deleted?
????--插入日志表?
????insert?into?[LOG](MSG)values(@msg)?
??????
--刪除觸發(fā)器?
drop?trigger?User_OnUpdate
七、存儲過程

--創(chuàng)建帶output參數(shù)的存儲過程?
CREATE?PROCEDURE?PR_Sum?
????@a?int,?
????@b?int,?
????@sum?int?output
AS
BEGIN
????set?@sum=@a+@b?
END
??
--創(chuàng)建Return返回值存儲過程?
CREATE?PROCEDURE?PR_Sum2?
????@a?int,?
????@b?int
AS
BEGIN
????Return?@a+@b?
END
??????
--執(zhí)行存儲過程獲取output型返回值?
declare?@mysum?int
execute?PR_Sum?1,2,@mysum?output
print?@mysum?
??
--執(zhí)行存儲過程獲取Return型返回值?
declare?@mysum2?int
execute?@mysum2=?PR_Sum2?1,2?
print?@mysum2
?
??

八、自定義函數(shù)

　　函數(shù)的分類：

　　　　1）標量值函數(shù)

　　　　2）表值函數(shù)

　　　　　　　　a:內(nèi)聯(lián)表值函數(shù)

　　　　　　　　b:多語句表值函數(shù)

　　　　3）系統(tǒng)函數(shù)

　　

--新建標量值函數(shù)?
create?function?FUNC_Sum1?
(?
????@a?int,?
????@b?int
)?
returns?int
as
begin
????return?@a+@b?
end
??
--新建內(nèi)聯(lián)表值函數(shù)?
create?function?FUNC_UserTab_1?
(?
????@myId?int
)?
returns?table
as
return?(select?*?from?ST_User?where?ID<@myId)?
??
--新建多語句表值函數(shù)?
create?function?FUNC_UserTab_2?
(?
????@myId?int
)?
returns?@t?table
(?
????[ID]?[int]?NOT?NULL,?
????[Oid]?[int]?NOT?NULL,?
????[Login]?[nvarchar](50)?NOT?NULL,?
????[Rtx]?[nvarchar](4)?NOT?NULL,?
????[Name]?[nvarchar](5)?NOT?NULL,?
????[Password]?[nvarchar](max)?NULL,?
????[State]?[nvarchar](8)?NOT?NULL
)?
as
begin
????insert?into?@t?select?*?from?ST_User?where?ID<@myId?
????return
end
??
--調(diào)用表值函數(shù)?
select?*?from?dbo.FUNC_UserTab_1(15)?
--調(diào)用標量值函數(shù)?
declare?@s?int
set?@s=dbo.FUNC_Sum1(100,50)?
print?@s?
??
--刪除標量值函數(shù)?
drop?function?FUNC_Sum1
談?wù)勛远x函數(shù)與存儲過程的區(qū)別：

一、自定義函數(shù)：

　　1.?可以返回表變量

　　2.?限制頗多，包括

　　　　不能使用output參數(shù)；

　　　　不能用臨時表；

　　　　函數(shù)內(nèi)部的操作不能影響到外部環(huán)境；

　　　　不能通過select返回結(jié)果集；

　　　　不能update，delete，數(shù)據(jù)庫表；

　　3.?必須return?一個標量值或表變量

　　自定義函數(shù)一般用在復(fù)用度高，功能簡單單一，爭對性強的地方。

二、存儲過程

　　1.?不能返回表變量

　　2.?限制少，可以執(zhí)行對數(shù)據(jù)庫表的操作，可以返回數(shù)據(jù)集

　　3.?可以return一個標量值，也可以省略return

　　　存儲過程一般用在實現(xiàn)復(fù)雜的功能，數(shù)據(jù)操縱方面。