SQL Server資料庫中的預存程序介紹_MsSql

什麼是預存程序

如果你接觸過其他的程式設計語言，那麼就好理解了，預存程序就像是方法一樣。

竟然他是方法那麼他就有類似的方法名，方法要傳遞的變數和返回結果，所以預存程序有預存程序名有預存程序參數也有傳回值。

預存程序的優點：   

預存程序的能力大大增強了SQL語言的功能和靈活性。

1.可保證資料的安全性和完整性。
2.通過預存程序可以使沒有許可權的使用者在控制之下間接地存取資料庫，從而保證資料的安全。
3.通過預存程序可以使相關的動作在一起發生，從而可以維護資料庫的完整性。
4.在運行預存程序前，資料庫已對其進行了文法和句法分析，並給出了最佳化執行方案。這種已經編譯好的過程5.可極大地改善SQL語句的效能。
6.可以降低網路的通訊量。
7.使體現企業規則的運算程式放入資料庫伺服器中，以便 集中控制。

預存程序可以分為系統預存程序、擴充預存程序和使用者自訂的預存程序

系統預存程序

我們先來看一下系統預存程序，系統預存程序由系統定義，主要存放在MASTER資料庫中，名稱以"SP"開頭或以"XP"開頭。儘管這些系統預存程序在MASTER資料庫中，

但我們在其他資料庫還是可以調用系統預存程序。有一些系統預存程序會在建立新的資料庫的時候被自動建立在當前資料庫中。

常用系統預存程序有：

複製代碼 代碼如下:

exec sp_databases; --查看資料庫
exec sp_tables;        --查看錶
exec sp_columns student;--查看列
exec sp_helpIndex student;--查看索引
exec sp_helpConstraint student;--約束
exec sp_helptext 'sp_stored_procedures';--查看預存程序建立定義的語句
exec sp_stored_procedures;
exec sp_rename student, stuInfo;--更改表名
exec sp_renamedb myTempDB, myDB;--更改資料庫名稱
exec sp_defaultdb 'master', 'myDB';--更改登入名稱的預設資料庫
exec sp_helpdb;--資料庫協助，查詢資料庫資訊
exec sp_helpdb master;
exec sp_attach_db --附加資料庫
exec sp_detach_db --分離資料庫

預存程序文法：

在建立一個預存程序前，先來說一下預存程序的命名，看到好幾篇講預存程序的文章都喜歡在建立預存程序的時候加一個首碼，養成在預存程序名前加首碼的習慣很重要，雖然這隻是一件很小的事情，但是往往小細節決定大成敗。看到有的人喜歡這樣加首碼，例如proc_名字。也看到這加樣首碼usp_名字。前一種proc是procedure的簡寫，後一種sup意思是user procedure。我比較喜歡第一種，那麼下面所有的預存程序名都以第一種來寫。至於名字的寫法採用駱駝命名法。

建立預存程序的文法如下：

複製代碼 代碼如下:

CREATE PROC[EDURE] 預存程序名

@參數1 [資料類型]=[預設值] [OUTPUT]

@參數2 [資料類型]=[預設值] [OUTPUT]

AS

SQL語句

EXEC 過程名[參數]

使用預存程序執行個體：

1.不帶參數

複製代碼 代碼如下:

create procedure proc_select_officeinfo--(預存程序名)
as select Id,Name from Office_Info--(sql語句)

exec proc_select_officeinfo--(調用預存程序)

2.帶輸入參數

複製代碼 代碼如下:

create procedure procedure_proc_GetoffinfoById --(預存程序名)
@Id int--(參數名 參數類型)
as select Name from dbo.Office_Info where Id=@Id--(sql語句)

exec procedure_proc_GetoffinfoById 2--(預存程序名稱之後,空格加上參數,多個參數中間以逗號分隔)

注:參數賦值是,第一個參數可以不寫參數名稱,後面傳入參數,需要明確傳入的是哪個參數名稱

3.帶輸入輸出參數

複製代碼 代碼如下:

create procedure proc_office_info--（預存程序名）
@Id int,@Name varchar(20) output--(參數名 參數類型)傳出參數要加上output
as
begin
select @Name=Name from dbo.Office_Info where Id=@Id --(sql語句)
end
declare @houseName varchar(20) --聲明一個變數,擷取預存程序傳出來的值
exec proc_office_info--(預存程序名)
4,@houseName output--(傳說參數要加output 這邊如果用@變數 = OUTPUT會報錯，所以換一種寫法)
select @houseName--(顯示值)

4.帶傳回值的

複製代碼 代碼如下:

create procedure proc_office_info--（預存程序名）
@Id int--(參數名 參數類型)
as
begin
if(select Name from dbo.Office_Info where Id=@Id)=null --(sql語句)
begin
return -1
end
else
begin
return 1
end
end

declare @house varchar(20) --聲明一個變數,擷取預存程序傳出來的值
exec @house=proc_office_info 2 --(調用預存程序,用變數接收傳回值)
--註：帶傳回值的預存程序只能為int類型的傳回值
print @house