SQL Server基礎之儲存過程

阿赫瓦里發表於2015-05-06
SQLServer儲存過程

  簡單來說，儲存過程就是一條或者多條sql語句的集合,可視為批處理檔案，但是其作用不僅限於批處理。本篇主要介紹變數的使用，儲存過程和儲存函式的建立，呼叫，檢視，修改以及刪除操作。

一:儲存過程概述

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

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

儲存過程的優點:

  1. 儲存過程加快系統執行速度，儲存過程只在建立時編譯，以後每次執行時不需要重新編譯。
  2. 儲存過程可以封裝複雜的資料庫操作，簡化操作流程，例如對多個表的更新，刪除等。
  3. 可實現模組化的程式設計，儲存過程可以多次呼叫，提供統一的資料庫訪問介面，改進應用程式的可維護性。
  4. 儲存過程可以增加程式碼的安全性，對於使用者不能直接操作儲存過程中引用的物件，SQL  Server可以設定使用者對指定儲存過程的執行許可權。
  5. 儲存過程可以降低網路流量，儲存過程程式碼直接儲存於資料庫中，在客戶端與伺服器的通訊過程中，不會產生大量的T_SQL程式碼流量。

儲存過程的缺點：

  1. 資料庫移植不方便，儲存過程依賴與資料庫管理系統， SQL Server 儲存過程中封裝的操作程式碼不能直接移植到其他的資料庫管理系統中。
  2. 不支援物件導向的設計，無法採用物件導向的方式將邏輯業務進行封裝，甚至形成通用的可支援服務的業務邏輯框架.
  3. 程式碼可讀性差，不易維護。不支援叢集。

二：儲存過程分類

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.建立無參儲存過程

--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;

2.修改儲存過程

alter procedure dbo.getAllBooks 
as
select book_auth from books;

3.刪除儲存過程

drop procedure getAllBooks;

4.重新命名儲存過程

sp_rename getAllBooks,proc_get_allBooks;

5.建立帶引數的儲存過程

 儲存過程的引數分為兩種：輸入引數和輸出引數

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

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

值傳遞和引用傳遞區別:

  • 基本資料型別賦值屬於值傳遞；引用型別之間賦值屬於引用傳遞。
  • 值傳遞傳遞的是實實在在的變數值；引用傳遞傳遞的是物件的引用地址。
  • 值傳遞後，兩個變數改變的是各自的值；引用傳遞後，兩個引用改變的是同一個物件的狀態

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

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;

(2)帶2個引數儲存過程

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,'金庸';

(3)建立有返回值的儲存過程

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是給返回的列值起一個名字

(4)建立帶萬用字元的儲存過程

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    '孔%','論%';

(5)加密儲存過程

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

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' 的文字已加密。"

(6).不快取儲存過程

--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';

(7).建立帶遊標引數的儲存過程

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; --釋放遊標

(8).建立分頁儲存過程

if (object_id('book_page', 'P') is not null)
    drop proc book_page
go
create proc book_page(
    @TableName varchar(50),            --表名
    @ReFieldsStr varchar(200) = '*',   --欄位名(全部欄位為*)
    @OrderString varchar(200),         --排序欄位(必須!支援多欄位不用加order by)
    @WhereString varchar(500) =N'',  --條件語句(不用加where)
    @PageSize int,                     --每頁多少條記錄
    @PageIndex int = 1 ,               --指定當前為第幾頁
    @TotalRecord int output            --返回總記錄數
)
as
begin
     --處理開始點和結束點
    Declare @StartRecord int;
    Declare @EndRecord int; 
    Declare @TotalCountSql nvarchar(500); 
    Declare @SqlString nvarchar(2000);    
    set @StartRecord = (@PageIndex-1)*@PageSize + 1
    set @EndRecord = @StartRecord + @PageSize - 1 
    SET @TotalCountSql= N'select @TotalRecord = count(*) from ' + @TableName;--總記錄數語句
    SET @SqlString = N'(select row_number() over (order by '+ @OrderString +') as rowId,'+@ReFieldsStr+' from '+ @TableName;--查詢語句
    --
    IF (@WhereString! = '' or @WhereString!=null)
        BEGIN
            SET @TotalCountSql=@TotalCountSql + '  where '+ @WhereString;
            SET @SqlString =@SqlString+ '  where '+ @WhereString;            
        END
    --第一次執行得到
    --IF(@TotalRecord is null)
    --   BEGIN
           EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;--返回總記錄數
    --  END
    ----執行主語句
    set @SqlString ='select * from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' +  ltrim(str(@EndRecord));
    Exec(@SqlString)    
END
--呼叫分頁儲存過程book_page
exec book_page 'books','*','book_id','',3,1,0;

--
declare @totalCount int
exec book_page 'books','*','book_id','',3,1,@totalCount output; 
select @totalCount as totalCount;--總記錄數。

相關文章