SQL Server存儲過程

存儲過程類似函數(shù)，可以重復(fù)使用。相對于函數(shù)，存儲過程擁有更強大的功能和更高的靈活性。

存儲過程中可以包含邏輯控制語句和數(shù)據(jù)操作語句，可以接受參數(shù)，輸出參數(shù)，返回單個值或多個結(jié)果集。

存儲過程帶來的好處：

1、性能的提升

存儲過程執(zhí)行時，第一次會進行編譯和優(yōu)化。但批處理T-SQL語句每次執(zhí)行都需要預(yù)編譯和優(yōu)化，所以沒有存儲過程快。

2、易于維護

存儲過程創(chuàng)建后存儲在數(shù)據(jù)庫中，可以被程序多次調(diào)用執(zhí)行。當(dāng)需要修改存儲過程時，對應(yīng)用程序代碼毫無影響。

3、安全性

應(yīng)用程序只需要調(diào)用存儲過程名，給幾個參數(shù)，而不是直接訪問基礎(chǔ)對象。需要賦予的不是增刪改的權(quán)限，而是exec的權(quán)限。

系統(tǒng)存儲過程

系統(tǒng)存儲過程主要存儲在master數(shù)據(jù)庫中，以sp_開頭，可以在所有數(shù)據(jù)庫對象中使用。

常用的系統(tǒng)存儲過程

exec sp_databases    --查看所有數(shù)據(jù)庫exec sp_tables        --查看所有數(shù)據(jù)表exec sp_columns student --查看student表的所有列exec sp_helpIndex student --查看student表的索引exec sp_helpconstraint student --查看student表的約束exec sp_helptext 'sp_databases' --查看定于語句exec sp_rename oldName,newName --修改表、索引、列的名稱exec sp_renamedb webDB,newDB --修改數(shù)據(jù)庫名稱exec sp_helpdb  webDB --查看數(shù)據(jù)庫信息

用戶定義存儲過程

語法：

create PRoc | procedure proc_name[    {@parameter1 data_type} [=default] [out | output],    {@parameter2 data_type} [=default] [out | output]]as[begin]    T-SQL代碼[end]

不帶參數(shù)

if(exists(select * from sys.objects where name = 'proc_test'))    drop proc proc_test  --刪除gocreate proc proc_test --創(chuàng)建create 修改alteras    select * from student order by id desc--調(diào)用exec proc_test

執(zhí)行存儲過程使用execute關(guān)鍵字，可以簡寫為exec。在SQLServer 2012中得到加強，可以修改結(jié)果集中列名和類型。

execute proc_test with result sets(    (        序號 varchar(5),        姓名 varchar(10),        性別 varchar(2),        年齡 varchar(5),        郵箱 varchar(5)    ))

輸入?yún)?shù)

if(exists(select * from sys.objects where name = 'proc_test'))    drop proc proc_test  --刪除gocreate proc proc_test (@id int)as    select * from student where id = @id--調(diào)用exec proc_test 10

默認參數(shù)

if(exists(select * from sys.objects where name = 'proc_test'))    drop proc proc_test  --刪除gocreate proc proc_test (@id int = 10)as    select * from student where id = @id--調(diào)用exec proc_test      --10exec proc_test 15 --15

輸出參數(shù)

if(exists(select * from sys.objects where name = 'proc_test'))    drop proc proc_test  --刪除gocreate proc proc_test (    @id int,        --輸入?yún)?shù)    @name varchar(10) out, --輸出參數(shù)    @age int output            --輸入輸出參數(shù))asbegin --可寫可不寫    select @name = name,@age = age from student where id = @idend--調(diào)用declare @name varchar(10),@age intexec proc_test 10,@name out,@age outputselect @name,@age

不緩存

if(exists(select * from sys.objects where name = 'proc_test'))    drop proc proc_test  --刪除gocreate proc proc_testwith  recompile  --不緩存，每次都編譯as    select * from student order by id desc--調(diào)用exec proc_test

加密

if(exists(select * from sys.objects where name = 'proc_test'))    drop proc proc_test  --刪除gocreate proc proc_testwith encryption  --加密后無法查看as    select * from student order by id desc--調(diào)用exec proc_testexec sp_helptext proc_test--提示對象 'proc_test' 的文本已加密。