最近寫T-sql語句比較多， 想把這段時間遇到過的貌似有用的一些sql server的函數，存儲過程，臨時表等總結一下，以便不時之需，當然不會一下子所有都總結，但會分段總結；

?

1)存儲過程定義：

?

create proc [proc_name] @para_1 nvarchar(250), @para_2 int=1, @para_3 nchar(10) output as

?

上面這段sequal就是創建一個名為proc_name的存儲過程，她接受兩個輸入參數，分別為@para_1和有默認值為1的@para_2,且返回一個nchar類型的值；
@para_2 int=1, 指明了@para_2參數在調用該存儲過程時可以不提供，系統會給個默認值為1；
@para_3 nchar(10) output, 說明@para_3是一個輸出參數，存儲過程可以指明多個輸出參數，如還可以在后面加一個輸出參數：@para_4 bit output,要在退出存儲過程前給她賦值，如果不賦值她一直保持為null;
可以像下面那樣去調用：

declare @arg_3 nchar(10) exec proc proc_name 'staff_001',0,@arg_3 output-- 或者不指供有默認值的para_2, 如 declare @arg_3 nchar(10) exec proc proc_name 'staff_001',@arg_3 output

?

?

?

2)臨時表：

2種臨時表，分別為：全局，局部
全局和局部和平時學的編程語言里的全局變量和局部變量語義是一樣，只是意思上有點差別;
如全局臨時表一旦創建了，她就像一個普通的表一樣，只要創建她的那個連接未斷開，當前任何的連接都可以訪問該全局臨時表，如果創建她的連接斷開了，那么已經引用她的其它任務，可以繼續引用她，但新的任務或連接不能再引用她，一旦沒有任何任務引用她時，該臨時表即被系統釋放掉；
局部臨時表只能在創建她的任務里引用， 比喻創建了一個連接，如平時那樣連接上一個數據庫，然后創建一個臨時表，那么該臨時表，可能被在該連接里的所有任務引用，但如果在該連接里的某個存儲過程創建，則只在被該存儲過程引用,其它存儲過程或函數(這些統稱為任務)都不能引用她；

創建全局臨時表， 全局臨時表都是以##開頭的,這是sql server 內定的，不能改：

create table [##g_temp_name](staff_no nvarchar(250) primary key,staff_name nvarchar(250) null,state int default 0) -- 或者 select * into [##g_temp_name] from real_table-- 或者 exec('select * into [##g_temp_name] from real_table')

?

?

上面3種試創建的全局臨時表效果是一樣的，只要連接未斷開，都可以像訪問普通表一樣訪問她：

?

select * from ##g_temp_name-- orexec('select * from ##g_temp_name')

?

?

?

?

創建局部臨時表， 局部臨時表都以單個#開頭的，也是sql server 內定的：

?

create table [#temp_name](staff_no nvarchar(250) primary key,staff_name nvarchar(250) not null,state int default 0)-- 或者 select * into #temp_name from real_table-- 或者exec ('select * into #temp_name from real_table')

?

?

?

?

注意局部臨時表只能在一個批處理里有效，如用第3種方式創建的局部臨時表，是無法在這個批外面訪問到的，所以用字符串拼接方式創建的臨時表，意義不大，除非訪問臨時表也拼接到該字符串里去；
如果確實要用到拼接字符串也作查詢數據的，這里有兩個解決方案，一個是用全局臨時表放到拼接字符串里，因為全局臨時表一旦創建了，只要連接還沒斷開都可以隨便訪問的，如：

?

exec ('select field_1, field_2 into ##g_temp_name from real_table') select * from ##g_temp_name

?

?

?

?

當然用全局臨時表，有個缺點是當前的任務連接都可以訪問到她，所以當出現并發訪問時，就出問題了，可能會被另一個連接把她的數據修改了。下面有另一種解決方案可以完美的處理這種問題，就是用局部臨時表，但局部臨時表不寫的拼接字符串去，而把拼接字符串查詢語句返回的記錄插入到局部臨時表里， 這種方法屢試不爽：

?

create table #temp_name(staff_no nvarchar(250))insert into #temp_name exec('select field_1 from real_table') -- 再擴展一下 select * into #temp_name_2 from real_table_2 A where A.field_1 in(select B.staff_no from #temp_name where B.staff_no <> '') -- select * from #temp_name_2

?

?

?

在寫存儲過程時經常用到臨時表，如果對一個臨時表名只用一次，可以不手動刪除它，在調用完存儲過程系統自動會把它刪除，但如果要多只用到，有時就要進行手動刪除了，如果刪除了一個不存在的臨時表，系統會報錯的，所以在手動刪除前最好判斷該臨時表是否存在；
臨時表都不保存在當前數據庫的，而是在tempdb里，所以刪除時要注意：

?

if object_id('tempdb..[#temp_name]') is not null begindrop table [#temp_name] end

?

?

?

3) 游標：
游標是很有用的工作，特別是用在逐行訪問表里的記錄時，就像C#里的foreach一樣，對一個集合進行迭代訪問，但游標卻特耗資源，如能不用盡量不要用，只有在用其它方法無法做到，或做起來特麻煩時就要考慮用游標了; 創建一個游標：

?

declare [cursor_name] cursor from select staff_no, staff_name from real_table-- 聲明兩個用來存儲no 和name的變量 declare @staff_no nvarchar(250), @staff_name nvarchar(250)-- 打開游標 open [cursor_name]-- 移動游標到第一行 fetch [cursor_name] into @staff_no, @staff_name-- 判斷游標是否已迭代完@@fetch_status = 0說明還未完 while @@FETCH_STATUS = 0 begin-- Do something with @staff_no, @staff_name-- 繼續移動游標到下一行fetch [cursor_name] into @staff_no, @staff_name end-- 關閉游標 close [cursor_name] -- 釋放資源 deallote [cursor_name]

?

?

?

******說明：　方括號指的是要創建的對象名稱：如[cursor_name]可以自定義名為my_cur_name,不要加方括號;還有游標的名字前不要加其它修飾符，如@my_cur_name或#my_cur_name, 單純my_cur_name就可以了******

轉載于:https://www.cnblogs.com/grissom007/archive/2012/11/07/2878327.html

總結

以上是生活随笔為你收集整理的常用的sql server 函数、存储过程、临时表总结的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。