SQL Server 預存程序詳解

標籤：結構   blog   串連   pretty   arc   資訊   串連資料庫   使用   應用   

轉自：62884658一. 什麼是預存程序系統預存程序是系統建立的預存程序，目的在於能夠方便的從系統資料表中查詢資訊或完成與更新資料庫表相關的管理工作或其他的系統管理任務。系統預存程序主要儲存在master資料庫中，以“sp”底線開頭的預存程序。儘管這些系統預存程序在master資料庫中，但我們在其他資料庫還是可以調用系統預存程序。有一些系統預存程序會在建立新的資料庫的時候被自動建立在當前資料庫中。二. 預存程序運行流程

預存程序是由一些SQL語句和控制語句組成的被封裝起來的過程，它駐留在資料庫中，可以被客戶應用程式調用，也可以從另一個過程或觸發器調用。它的參數可以被傳遞和返回。與應用程式中的函數過程類似，預存程序可以通過名字來調用，而且它們同樣有輸入參數和輸出參數。根據傳回值類型的不同，我們可以將預存程序分為三類：

• 返回記錄集的預存程序的執行結果是一個記錄集，典型的例子是從資料庫中檢索出符合某一個或幾個條件的記錄；
• 返回數值的預存程序執行完以後返回一個值，例如在資料庫中執行一個有傳回值的函數或命令;
• 行為預存程序僅僅是用來實現資料庫的某個功能，而沒有傳回值，例如在資料庫中的更新和刪除操作。

個人認為，預存程序說白了就是一堆 SQL 的合并。中間加了點邏輯控制。

1. 但是預存程序處理比較複雜的業務時比較實用。比如說，

一個複雜的資料操作。如果你在前台處理的話。可能會涉及到多次資料庫連接。但如果你用預存程序的話。就只有一次。從回應時間上來說有優勢。

1. 也就是說預存程序可以給我們帶來運行效率提高的好處。

另外，程式容易出現 BUG 不穩定，而預存程序，只要資料庫不出現問題，基本上是不會出現什麼問題的。也就是說從安全上講，使用了預存程序的系統更加穩定。那麼問題來了，什麼時候才可以用儲存？對於資料量不是很大以及業務處理不是很複雜的小項目就無需要了嗎？答：錯。預存程序不僅僅適用於大型項目，對於中小型項目，使用預存程序也是非常有必要的。其威力和優勢主要體現在：

1. 預存程序只在創造時進行編譯，以後每次執行預存程序都不需再重新編譯，而一般 SQL 陳述式每執行一次就編譯一次,所以使用預存程序可提高資料庫執行速度。
2. 當對資料庫進行複雜操作時(如對多個表進行 Update,Insert,Query,Delete 時），可將此複雜操作用預存程序封裝起來與資料庫提供的交易處理結合一起使用。這些操作，如果用程式來完成，就變成了一條條的 SQL 陳述式，可能要多次串連資料庫。而換成儲存，只需要串連一次資料庫就可以了。
3. 預存程序可以重複使用,可減少資料庫開發人員的工作量。
4. 安全性高,可設定只有某此使用者才具有對指定預存程序的使用權。
5. 減少網路通訊量。調用一個行數不多的預存程序與直接調用SQL語句的網路通訊量可能不會有很大的差別，可是如果預存程序包含上百行SQL語句，那麼其效能絕對比一條一條的調用SQL語句要高得多。
6. 執行速度更快。有兩個原因：首先，在預存程序建立的時候，資料庫已經對其進行了一次解析和最佳化。其次，預存程序一旦執行，在記憶體中就會保留一份這個預存程序，這樣下次再執行同樣的預存程序時，可以從記憶體中直接調用。
7. 更強的適應性：由於預存程序對資料庫的訪問是通過預存程序來進行的，因此資料庫開發人員可以在不改動預存程序介面的情況下對資料庫進行任何改動，而這些改動不會對應用程式造成影響。
8. 布式工作：應用程式和資料庫的編碼工作可以分別獨立進行，而不會相互壓制。

預存程序的使用，好像一直是一個爭論。我不傾向於儘可能使用預存程序，是這麼認為的：

1. 運行速度： 大多數進階的資料庫系統都有statement cache的，所以編譯sql的花費沒什麼影響。但是執行預存程序要比直接執行sql花費更多（檢查許可權等），所以對於很簡單的sql，預存程序沒有什麼優勢。
2. 網路負荷：如果在預存程序中沒有多次資料互動，那麼實際上網路傳輸量和直接sql是一樣的。
3. 團隊開發：很遺憾，比起成熟的IDE，沒有什麼很好預存程序的IDE工具來支援，也就是說，這些必須手工完成。
4. 安全機制：對於傳統的C/S結構，串連資料庫的使用者可以不同，所以安全機制有用；但是在web的三層架構中，資料庫使用者不是給使用者用的，所以基本上，只有一個使用者，擁有所有許可權（最多還有一個開發使用者）。這個時候，安全機制有點多餘。
5. 使用者滿意：實際上這個只是要將訪問資料庫的介面統一，是用預存程序，還是EJB，沒太大關係，也就是說，在三層結構中，單獨設計出一個資料訪問層，同樣能實現這個目標。
6. 開發調試：一樣由於IDE的問題，預存程序的開發調試要比一般程式困難（老版本DB2還只能用C寫預存程序，更是一個災難）。
7. 移植性：算了，這個不用提，反正一般的應用總是綁定某個資料庫的，不然就無法靠最佳化資料庫訪問來提高效能了。
8. 維護性：的確，預存程序有些時候比程式容易維護，這是因為可以即時更新DB端的預存程序，但是在3層結構下，更新server端的資料訪問層一樣能實現這個目標，可惜現在很多平台不支援即時更新而已。

常用系統預存程序有：

exec sp_databases; --查看資料庫exec sp_tables;        --查看錶exec sp_columns student;--查看列exec sp_helpIndex student;--查看索引exec sp_helpConstraint student;--約束exec sp_stored_procedures;exec sp_helptext ‘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_rename ‘stu‘, ‘stud‘;select * from stud;--列重新命名exec sp_rename ‘stud.name‘, ‘sName‘, ‘column‘;exec sp_help ‘stud‘;--重新命名索引exec sp_rename N‘student.idx_cid‘, N‘idx_cidd‘, N‘index‘;exec sp_help ‘student‘;--查詢所有預存程序select * from sys.objects where type = ‘P‘;select * from sys.objects where type_desc like ‘%pro%‘ and name like ‘sp%‘;

使用者自訂預存程序

create proc | procedure pro_name    [{@參數資料類型} [=預設值] [output],     {@參數資料類型} [=預設值] [output],     ....    ]as    SQL_statements

2、 建立不帶參數預存程序

--建立預存程序if (exists (select * from sys.objects where name = ‘proc_get_student‘))    drop proc proc_get_studentgocreate proc proc_get_studentas    select * from student;--調用、執行預存程序exec proc_get_student;

3、 修改預存程序

--修改預存程序alter proc proc_get_studentasselect * from student;

 

4、 帶參預存程序

--帶參預存程序if (object_id(‘proc_find_stu‘, ‘P‘) is not null)    drop proc proc_find_stugocreate proc proc_find_stu(@startId int, @endId int)as    select * from student where id between @startId and @endIdgoexec proc_find_stu 2, 4;

 

5、 帶萬用字元參數預存程序

--帶萬用字元參數預存程序if (object_id(‘proc_findStudentByName‘, ‘P‘) is not null)    drop proc proc_findStudentByNamegocreate proc proc_findStudentByName(@name varchar(20) = ‘%j%‘, @nextName varchar(20) = ‘%‘)as    select * from student where name like @name and name like @nextName;goexec proc_findStudentByName;exec proc_findStudentByName ‘%o%‘, ‘t%‘;

 

6、 帶輸出參數預存程序

if (object_id(‘proc_getStudentRecord‘, ‘P‘) is not null)    drop proc proc_getStudentRecordgocreate proc proc_getStudentRecord(    @id int, --預設輸入參數    @name varchar(20) out, --輸出參數    @age varchar(20) output--輸入輸出參數)as    select @name = name, @age = age  from student where id = @id and sex = @age;go-- declare @id int,        @name varchar(20),        @temp varchar(20);set @id = 7; set @temp = 1;exec proc_getStudentRecord @id, @name out, @temp output;select @name, @temp;print @name + ‘#‘ + @temp;

7、 不緩衝預存程序

--WITH RECOMPILE 不緩衝if (object_id(‘proc_temp‘, ‘P‘) is not null)    drop proc proc_tempgocreate proc proc_tempwith recompileas    select * from student;goexec proc_temp;

 

8、 加密預存程序

--加密WITH ENCRYPTION if (object_id(‘proc_temp_encryption‘, ‘P‘) is not null)    drop proc proc_temp_encryptiongocreate proc proc_temp_encryptionwith encryptionas    select * from student;goexec proc_temp_encryption;exec sp_helptext ‘proc_temp‘;exec sp_helptext ‘proc_temp_encryption‘;

 

9、 帶遊標參數預存程序

if (object_id(‘proc_cursor‘, ‘P‘) is not null)    drop proc proc_cursorgocreate proc proc_cursor    @cur cursor varying outputas    set @cur = cursor forward_only static for    select id, name, age from student;    open @cur;go--調用declare @exec_cur cursor;declare @id int,        @name varchar(20),        @age int;exec proc_cursor @cur = @exec_cur output;--調用預存程序fetch next from @exec_cur into @id, @name, @age;while (@@fetch_status = 0)begin    fetch next from @exec_cur into @id, @name, @age;    print ‘id: ‘ + convert(varchar, @id) + ‘, name: ‘ + @name + ‘, age: ‘ + convert(char, @age);endclose @exec_cur;deallocate @exec_cur;--刪除遊標

 

10、 分頁預存程序

---預存程序、row_number完成分頁if (object_id(‘pro_page‘, ‘P‘) is not null)    drop proc proc_cursorgocreate proc pro_page    @startIndex int,    @endIndex intas    select count(*) from product;        select * from (        select row_number() over(order by pid) as rowId, * from product     ) temp    where temp.rowId between @startIndex and @endIndexgo--drop proc pro_pageexec pro_page 1, 4----分頁預存程序if (object_id(‘pro_page‘, ‘P‘) is not null)    drop proc pro_stugocreate procedure pro_stu(    @pageIndex int,    @pageSize int)as    declare @startRow int, @endRow int    set @startRow = (@pageIndex - 1) * @pageSize +1    set @endRow = @startRow + @pageSize -1    select * from (        select *, row_number() over (order by id asc) as number from student     ) t    where t.number between @startRow and @endRow;exec pro_stu 2, 2;

 

Raiserror返回使用者定義的錯誤資訊，可以指定嚴重層級，設定系統變數記錄所發生的錯誤。

Raiserror({msg_id | msg_str | @local_variable}  {, severity, state}  [,argument[,…n]]  [with option[,…n]])

 

# msg_id:在sysmessages系統資料表中指定的使用者定義錯誤資訊

# msg_str:使用者定義的資訊，資訊最大長度在2047個字元。

# severity：使用者定義與該訊息關聯的嚴重層級。當使用msg_id引發使用sp_addmessage建立的使用者定義訊息時，raiserror上指定嚴重性將覆蓋sp_addmessage中定義的嚴重性。

任何使用者可以指定0-18直接的嚴重層級。只有sysadmin固定伺服器角色常用或具有alter trace許可權的使用者才能指定19-25直接的嚴重層級。19-25之間的安全層級需要使用with log選項。

 

# state：介於1至127直接的任何整數。State預設值是1。

raiserror(‘is error‘, 16, 1);select * from sys.messages;--使用sysmessages中定義的訊息raiserror(33003, 16, 1);raiserror(33006, 16, 1);

SQL Server 預存程序詳解