SQL Server基礎：預存程序

簡單來說，預存程序就是一條或多條sql語句的集合,可視為批次文件，但是其作用不僅限於批次處理。本篇主要介紹變數的使用，預存程序和儲存函數的創建，調用，查看，修改以及刪除操作。

一:預存程序概述

SQL Server中的預存程序是使用T_SQL編寫的程式碼段。它的目的在於能夠方便的從系統表中查詢信息，或者完成與更新資料庫表相關的管理任務和其他的系統管理任務.T_SQL語句是SQL Server資料庫與應用程式之間的程式設計介面。在許多情況下，有些程式碼會被開發者重複寫多次，如果每次都寫相同功能的程式碼，不但繁瑣，而且容易出錯，而且由於SQL Server逐條的執行語句會降低系統的運作效率。

簡而言之，預存程序就是SQL Server為了實現特定任務，而將一些需要多次呼叫的固定操作語句寫成程式段，這些程式段儲存在伺服器上，有資料庫伺服器透過程式來呼叫。

預存程序的優點:

預存程序加快系統運作速度，預存程序只在建立時編譯，以後每次執行時不需要重新編譯。

預存程序可以封裝複雜的資料庫操作，簡化操作流程，例如多個資料表的更新，刪除等。

可實現模組化的程式設計，預存程序可以多次調用，提供統一的資料庫存取接口，改進應用程式的可維護性。

預存程序可以增加程式碼的安全性，對於使用者無法直接操作預存程序中所引用的對象，SQL Server可以設定使用者對指定預存程序的執行權限。

預存程序可以降低網路流量，預存程序代碼直接儲存於資料庫中，在客戶端與伺服器的通訊過程中，不會產生大量的T_SQL程式碼流量。

預存程序的缺點：

資料庫移植不方便，預存程序依賴與資料庫管理系統， SQL Server 預存程序中封裝的操作代碼不能直接移植到其他的資料庫管理系統。

不支援物件導向的設計，無法採用物件導向的方式將邏輯業務進行封裝，甚至形成通用的可支援服務的業務邏輯架構.

程式碼可讀性差，不易維護。不支援集群。

二：預存程序分類

1.系統預存程序

系統預存程序是 SQL Server系統本身提供的預存程序，可以作為指令執行各種操作。

系統預存程序主要用來從系統表中獲取信息，使用系統存儲過程完成資料庫伺服器的管理工作，為系統管理員提供幫助，為用戶查看資料庫對象提供方便，系統存儲過程位於數據庫伺服器中，並且以sp_開頭，系統預存程序定義在系統定義和使用者定義的資料庫中，呼叫時不必在預存程序前加上資料庫限定名。例如：sp_rename系統預存程序可以修改目前資料庫中使用者建立物件的名稱，sp_helptext儲存程序可以顯示規則，預設值或視圖的文字訊息，SQL SERVER伺服器中許多的管理工作都是透過執行系統儲存程序來完成的，許多系統資訊也可以透過執行系統預存程序來獲得。

系統儲存過程建立並存放在與系統資料庫master中，有些系統儲存過程只能由系統管理員使用，而有些系統儲存過程則透過授權可以被其它使用者所使用。

2.使用者預存程序(自訂預存程序)

自訂預存程序即使用者使用T_SQL語句所寫的、為了實作某一特定業務需求，在使用者資料庫中所寫的T_SQL語句集合，自訂預存程序可以接受輸入參數、向客戶端傳回結果和訊息，返回輸出參數等。建立自訂預存程序時，預存程序名稱前加上”##」表示建立了一個全域的暫存程序;預存程序前面加上”#」時，表示已建立的局部暫存程序。局部暫存程序只能在建立它的回話中使用，會話結束時，將被刪除。這兩種預存程序都儲存在tempdb資料庫中。

使用者定義的預存程序分為兩類：T_SQL 和CLR

T_SQL:預存程序是值保存的T_SQL語句集合，可以接受和傳回使用者提供的參數，預存程序也可能從資料庫傳送到客戶端應用程式數據。

CLR預存程序是指引用Microsoft.NET Framework公用語言的方法預存程序，可以接受並傳回使用者提供的參數，它們在.NET Framework程序集是作為類別的公共靜態方法實作的。

3.擴充預存程序

擴充儲存程序是以在SQL SERVER環境外執行的動態連線(DLL檔)來實現的，可以載入到SQL SERVER實例運作的位址空間中執行，擴充儲存程序可以用SQL SERVER擴充預存程序API編程，擴充預存程序以前綴”xp_”來標識，對於使用者來說，擴充預存程序和普通話預存程序一樣，可以用相同的方法來執行。

三：建立預存程序

工欲善其事，必先利其器，準備資料如下:

use sample_db;
--创建测试books表
create table books (
 book_id int identity(1,1) primary key,
 book_name varchar(20),
 book_price float,
 book_auth varchar(10)
);
--插入测试数据
insert into books (book_name,book_price,book_auth)
 values
 ('论语',25.6,'孔子'),
 ('天龙八部',25.6,'金庸'),
 ('雪山飞狐',32.7,'金庸'),
 ('平凡的世界',35.8,'路遥'),
 ('史记',54.8,'司马迁');

建立無參預存程序

--1.创建无参存储过程
if (exists (select * from sys.objects where name = 'getAllBooks'))
 drop proc proc_get_student
go
create procedure getAllBooks
as
select * from books;
--调用,执行存储过程
exec getAllBooks;

修改預存程序

alter procedure dbo.getAllBooks 
as
select book_auth from books;

刪除預存程序

drop procedure getAllBooks;

重命名預存程序

reee的預存程序

預存程序的參數分為兩種：輸入參數和輸出參數

輸入參數：用於向預存程序傳入值，類似java語言或則c中的值傳遞。

輸出參數：用於呼叫預存程序後，參會結果，類似java語言的按引用傳遞。

值傳遞和引用傳遞區別:

基本資料型別賦值屬於值傳遞；引用型別之間賦值屬於參考傳遞。

值傳遞傳遞的是實實在在的變數值；引用傳遞傳遞的是物件的引用位址。
值傳遞後，兩個變數改變的是各自的值；引用傳遞後，兩個引用改變的是同一個物件的狀態

(1)帶一個參數儲存過程

sp_rename getAllBooks,proc_get_allBooks;

(2)帶2個參數預存程序

if (exists (select * from sys.objects where name = 'searchBooks'))
 drop proc searchBooks
go
create proc searchBooks(@bookID int)
as
 --要求book_id列与输入参数相等
 select * from books where book_id=@bookID;
--执行searchBooks
exec searchBooks 1;

(3)建立有回傳值的預存程序

if (exists (select * from sys.objects where name = 'searchBooks1'))
 drop proc searchBooks1
go
create proc searchBooks1(
 @bookID int,
 @bookAuth varchar(20)
)
as
 --要求book_id和book_Auth列与输入参数相等
 select * from books where book_id=@bookID and book_auth=@bookAuth;
exec searchBooks1 1,'金庸';

(4)建立帶有通配符的預存程序

MySQL

if (exists (select * from sys.objects where name = 'getBookId'))
 drop proc getBookId
go
create proc getBookId(
 @bookAuth varchar(20),--输入参数,无默认值
 @bookId int output --输入/输出参数 无默认值
)
as
 select @bookId=book_id from books where book_auth=@bookAuth
--执行getBookId这个带返回值的存储过程
declare @id int --声明一个变量用来接收执行存储过程后的返回值
exec getBookId '孔子',@id output
select @id as bookId;--as是给返回的列值起一个名字
if (exists (select * from sys.objects where name = 'getBookId'))
 drop proc getBookId
go
create proc getBookId(
 @bookAuth varchar(20),--输入参数,无默认值
 @bookId int output --输入/输出参数 无默认值
)
as
 select @bookId=book_id from books where book_auth=@bookAuth
--执行getBookId这个带返回值的存储过程
declare @id int --声明一个变量用来接收执行存储过程后的返回值
exec getBookId '孔子',@id output
select @id as bookId;--as是给返回的列值起一个名字

(5)加密預存程序

with encryption子句對使用者隱藏預存程序的文字.下例建立加密過程,使用sp_helptext 系統預存程序取得加密過程的資訊,然後嘗試直接從syscomments 表中取得關於該過程的資訊.

if (exists (select * from sys.objects where name = 'charBooks'))
 drop proc charBooks
go
create proc charBooks(
 @bookAuth varchar(20)='金%',
 @bookName varchar(20)='%'
)
as 
 select * from books where book_auth like @bookAuth and book_name like @bookName;
--执行存储过程charBooks
exec charBooks '孔%','论%';

(6).不快取預存程序

if (object_id('books_encryption', 'P') is not null)
 drop proc books_encryption
go
create proc books_encryption 
with encryption
as 
 select * from books;
--执行此过程books_encryption
exec books_encryption;
exec sp_helptext 'books_encryption';--控制台会显示"对象 'books_encryption' 的文本已加密。"

(7).建立帶有遊標參數的預存程序

--with recompile不缓存
if (object_id('book_temp', 'P') is not null)
 drop proc book_temp
go
create proc book_temp
with recompile
as
 select * from books;
go
exec book_temp;
exec sp_helptext 'book_temp';

(8).建立分頁預存程序

if (object_id('book_cursor', 'P') is not null)
 drop proc book_cursor
go
create proc book_cursor
 @bookCursor cursor varying output
as
 set @bookCursor=cursor forward_only static for
 select book_id,book_name,book_auth from books
 open @bookCursor;
go
--调用book_cursor存储过程
declare @cur cursor,
 @bookID int,
 @bookName varchar(20),
 @bookAuth varchar(20);
exec book_cursor @bookCursor=@cur output;
fetch next from @cur into @bookID,@bookName,@bookAuth;
while(@@FETCH_STATUS=0)
begin 
 fetch next from @cur into @bookID,@bookName,@bookAuth;
 print 'bookID:'+convert(varchar,@bookID)+' , bookName: '+ @bookName
 +' ,bookAuth: '+@bookAuth;
end
close @cur --关闭游标
DEALLOCATE @cur; --释放游标