SQLSERVER存儲(chǔ)過程基本語法
[導(dǎo)讀]一、定義變量
--簡(jiǎn)單賦值?
declare?@a?int
set?@a=5?
print?@a?
??
--使用select語句賦值?
declare?@user1?nvarchar(50)?
se
一、定義變量 --簡(jiǎn)單賦值? 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 ? 二、表、臨時(shí)表、表變量 --創(chuàng)建臨時(shí)表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 );? --向臨時(shí)表1插入一條記錄? insert?into?#DU_User1?(ID,Oid,[Login],Rtx,Name,[Password],State)?values?(100,2,'LS','0000','臨時(shí)','321','特殊');? ?? --從ST_User查詢數(shù)據(jù)，填充至新生成的臨時(shí)表? select?*?into?#DU_User2?from?ST_User?where?ID<8? ?? --查詢并聯(lián)合兩臨時(shí)表? select?*?from?#DU_User2?where?ID<3?union?select?*?from?#DU_User1? ?? --刪除兩臨時(shí)表? drop?table?#DU_User1? drop?table?#DU_User2 ? --創(chuàng)建臨時(shí)表? 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ù)）插入臨時(shí)表? insert?into?#t?select?*?from?ST_User? --不能這樣插入? --select?*?into?#t?from?dbo.ST_User? ?? --添加一列，為int型自增長(zhǎng)子段? alter?table?#t?add?[myid]?int?NOT?NULL?IDENTITY(1,1)? --添加一列，默認(rèn)填充全球唯一標(biāo)識(shí)? alter?table?#t?add?[myid1]?uniqueidentifier?NOT?NULL?default(newid())? ?? select?*?from?#t? drop?table?#t --給查詢結(jié)果集增加自增長(zhǎng)列? ?? --無主鍵時(shí)：? select?IDENTITY(int,1,1)as?ID,?Name,[Login],[Password]?into?#t?from?ST_User? select?*?from?#t? ?? --有主鍵時(shí)：? 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)計(jì)算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?'對(duì)' end else begin ????print?'錯(cuò)' 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?'值錯(cuò)誤' end print?@week ? 五、游標(biāo) declare?@ID?int declare?@Oid?int declare?@Login?varchar(50)? ?? --定義一個(gè)游標(biāo)? declare?user_cur?cursor?for?select?ID,Oid,[Login]?from?ST_User? --打開游標(biāo)? open?user_cur? while?@@fetch_status=0? begin --讀取游標(biāo)? ????fetch?next?from?user_cur?into?@ID,@Oid,@Login? ????print?@ID? ????--print?@Login? end close?user_cur? --摧毀游標(biāo)? deallocate?user_cur 六、觸發(fā)器 觸發(fā)器中的臨時(shí)表： Inserted? 存放進(jìn)行insert和update?操作后的數(shù)據(jù)? Deleted? 存放進(jìn)行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 七、存儲(chǔ)過程 --創(chuàng)建帶output參數(shù)的存儲(chǔ)過程? CREATE?PROCEDURE?PR_Sum? ????@a?int,? ????@b?int,? ????@sum?int?output AS BEGIN ????set?@sum=@a+@b? END ?? --創(chuàng)建Return返回值存儲(chǔ)過程? CREATE?PROCEDURE?PR_Sum2? ????@a?int,? ????@b?int AS BEGIN ????Return?@a+@b? END ?????? --執(zhí)行存儲(chǔ)過程獲取output型返回值? declare?@mysum?int execute?PR_Sum?1,2,@mysum?output print?@mysum? ?? --執(zhí)行存儲(chǔ)過程獲取Return型返回值? declare?@mysum2?int execute?@mysum2=?PR_Sum2?1,2? print?@mysum2 ? ?? 八、自定義函數(shù) 函數(shù)的分類： 1）標(biāo)量值函數(shù) 2）表值函數(shù) a:內(nèi)聯(lián)表值函數(shù) b:多語句表值函數(shù) 3）系統(tǒng)函數(shù) --新建標(biāo)量值函數(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)用標(biāo)量值函數(shù)? declare?@s?int set?@s=dbo.FUNC_Sum1(100,50)? print?@s? ?? --刪除標(biāo)量值函數(shù)? drop?function?FUNC_Sum1 談?wù)勛远x函數(shù)與存儲(chǔ)過程的區(qū)別： 一、自定義函數(shù)： 1.?可以返回表變量 2.?限制頗多，包括 不能使用output參數(shù)； 不能用臨時(shí)表； 函數(shù)內(nèi)部的操作不能影響到外部環(huán)境； 不能通過select返回結(jié)果集； 不能update，delete，數(shù)據(jù)庫表； 3.?必須return?一個(gè)標(biāo)量值或表變量 自定義函數(shù)一般用在復(fù)用度高，功能簡(jiǎn)單單一，爭(zhēng)對(duì)性強(qiáng)的地方。 二、存儲(chǔ)過程 1.?不能返回表變量 2.?限制少，可以執(zhí)行對(duì)數(shù)據(jù)庫表的操作，可以返回?cái)?shù)據(jù)集 3.?可以return一個(gè)標(biāo)量值，也可以省略return 存儲(chǔ)過程一般用在實(shí)現(xiàn)復(fù)雜的功能，數(shù)據(jù)操縱方面。
下載文檔
