SQL Server存儲過程

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

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

存儲過程帶來的好處：

1、性能的提升

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

2、易于維護

存儲過程創建后存儲在數據庫中，可以被程序多次調用執行。當需要修改存儲過程時，對應用程序代碼毫無影響。

3、安全性

應用程序只需要調用存儲過程名，給幾個參數，而不是直接訪問基礎對象。需要賦予的不是增刪改的權限，而是exec的權限。

系統存儲過程

系統存儲過程主要存儲在master數據庫中，以sp_開頭，可以在所有數據庫對象中使用。

常用的系統存儲過程

exec sp_databases    --查看所有數據庫exec sp_tables        --查看所有數據表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 --修改數據庫名稱exec sp_helpdb  webDB --查看數據庫信息

用戶定義存儲過程

語法：

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

不帶參數

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

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

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

輸入參數

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--調用exec proc_test 10

默認參數

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--調用exec proc_test      --10exec proc_test 15 --15

輸出參數

if(exists(select * from sys.objects where name = 'proc_test'))    drop proc proc_test  --刪除gocreate proc proc_test (    @id int,        --輸入參數    @name varchar(10) out, --輸出參數    @age int output            --輸入輸出參數)asbegin --可寫可不寫    select @name = name,@age = age from student where id = @idend--調用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--調用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--調用exec proc_testexec sp_helptext proc_test--提示對象 'proc_test' 的文本已加密。