淺談資料庫的預存程序

什麼是預存程序

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

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

　　預存程序的優點：    

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

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

系統儲存程序

     我們先來看系統預存程序，系統預存程序由系統定義，主要存放在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 ----  Name  dbo.Office_Info  Id=@Id----(存储过程名称之后,空格加上参数,多个参数中间以逗号分隔)

注:参数赋值是,第一个参数可以不写参数名称,后面传入参数,需要明确传入的是哪个参数名称

#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 beginif(select Name from dbo.Office_Info where Id=@Id)=null --(sql语句)
beginreturn -1endelsebeginreturn 1end
end

declare @house varchar(20) --声明一个变量,获取存储过程传出来的值
exec @house=proc_office_info 2 --(调用存储过程,用变量接收返回值)--注：带返回值的存储过程只能为int类型的返回值
print @house

更多SQL的相關技術文章，請造訪

SQL教學欄位進行學習！

以上是淺談資料庫的預存程序的詳細內容。更多資訊請關注PHP中文網其他相關文章！