SQL Server基礎之預存程序

標籤：2.7   table   strong   處理   全域   eal   sam   移植   fetch   

閱讀目錄

• 一:預存程序概述
• 二：預存程序分類
• 三：建立預存程序
• 1.建立無參預存程序
• 2.修改預存程序
• 3.刪除預存程序
• 4.重新命名預存程序
• 5.建立帶參數的預存程序

??簡單來說，預存程序就是一條或者多條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_studentgocreate procedure getAllBooksasselect * from books;--調用,執行預存程序exec getAllBooks;

　　

2.修改預存程序

alter procedure dbo.getAllBooks asselect book_auth from books;

　　

3.刪除預存程序

drop procedure getAllBooks;

　　

4.重新命名預存程序

sp_rename getAllBooks,proc_get_allBooks;

　　

5.建立帶參數的預存程序

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

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

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

值傳遞和引用傳遞區別:

• 基礎資料型別 (Elementary Data Type)賦值屬於值傳遞；參考型別之間賦值屬於引用傳遞。
• 值傳遞傳遞的是實實在在的變數值；引用傳遞傳遞的是對象的引用地址。
• 值傳遞後，兩個變數改變的是各自的值；引用傳遞後，兩個引用改變的是同一個對象的狀態

(1)帶一個參數預存程序

if (exists (select * from sys.objects where name = ‘searchBooks‘))    drop proc searchBooksgocreate proc searchBooks(@bookID int)as    --要求book_id列與輸入參數相等    select * from books where [email protected];--執行searchBooksexec searchBooks 1;

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

if (exists (select * from sys.objects where name = ‘searchBooks1‘))    drop proc searchBooks1gocreate proc searchBooks1(    @bookID int,    @bookAuth varchar(20))as    --要求book_id和book_Auth列與輸入參數相等    select * from books where [email protected] and [email protected];exec searchBooks1 1,‘金庸‘;

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

if (exists (select * from sys.objects where name = ‘getBookId‘))    drop proc getBookIdgocreate proc getBookId(    @bookAuth varchar(20),--輸入參數,無預設值    @bookId int output --輸入/輸出參數 無預設值)as    select @bookId=book_id from books where [email protected]--執行getBookId這個帶傳回值的預存程序declare @id int --聲明一個變數用來接收執行預存程序後的傳回值exec getBookId ‘孔子‘,@id outputselect @id as bookId;--as是給返回的列值起一個名字

　　(4)建立帶萬用字元的預存程序

if (exists (select * from sys.objects where name = ‘charBooks‘))    drop proc charBooksgocreate proc charBooks(    @bookAuth varchar(20)=‘金%‘,    @bookName varchar(20)=‘%‘)as     select * from books where book_auth like @bookAuth and book_name like @bookName;--執行預存程序charBooksexec  charBooks    ‘孔%‘,‘論%‘;

　　

(5)加密預存程序

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

if (object_id(‘books_encryption‘, ‘P‘) is not null)    drop proc books_encryptiongocreate proc books_encryption with encryptionas     select * from books;--執行此過程books_encryptionexec books_encryption;exec sp_helptext ‘books_encryption‘;--控制台會顯示"對象 ‘books_encryption‘ 的文本已加密。"

　　(6).不緩衝預存程序

--with  recompile不緩衝if (object_id(‘book_temp‘, ‘P‘) is not null)    drop proc book_tempgocreate proc book_tempwith recompileas    select * from books;goexec book_temp;exec sp_helptext ‘book_temp‘;

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

if (object_id(‘book_cursor‘, ‘P‘) is not null)    drop proc book_cursorgocreate proc book_cursor    @bookCursor cursor varying outputas    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 @[email protected] 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: ‘[email protected];endclose @cur    --關閉遊標DEALLOCATE @cur; --釋放遊標

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

if (object_id(‘book_page‘, ‘P‘) is not null)    drop proc book_pagegocreate 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            --返回總記錄數)asbegin     --處理開始點和結束點    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,‘[email protected]+‘ from ‘+ @TableName;--查詢語句    --    IF (@WhereString! = ‘‘ or @WhereString!=null)        BEGIN            SET @[email protected] + ‘  where ‘+ @WhereString;            SET @SqlString [email protected]+ ‘  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_pageexec book_page ‘books‘,‘*‘,‘book_id‘,‘‘,3,1,0;--declare @totalCount intexec book_page ‘books‘,‘*‘,‘book_id‘,‘‘,3,1,@totalCount output; select @totalCount as totalCount;--總記錄數。

　　

SQL Server基礎之預存程序