一:存儲(chǔ)過程概述

SQL?Server中的存儲(chǔ)過程是使用T_SQL編寫的代碼段。它的目的在于能夠方便的從系統(tǒng)表中查詢信息，或者完成與更新數(shù)據(jù)庫表相關(guān)的管理任務(wù)和其他的系統(tǒng)管理任務(wù).T_SQL語句是SQL?Server數(shù)據(jù)庫與應(yīng)用程序之間的編程接口。在很多情況下，一些代碼會(huì)被開發(fā)者重復(fù)編寫多次，如果每次都編寫相同功能的代碼，不但繁瑣，而且容易出錯(cuò)，而且由于SQL?Server逐條的執(zhí)行語句會(huì)降低系統(tǒng)的運(yùn)行效率。

簡而言之，存儲(chǔ)過程就是SQL?Server為了實(shí)現(xiàn)特定任務(wù)，而將一些需要多次調(diào)用的固定操作語句編寫成程序段，這些程序段存儲(chǔ)在服務(wù)器上，有數(shù)據(jù)庫服務(wù)器通過程序來調(diào)用。

存儲(chǔ)過程的優(yōu)點(diǎn):

存儲(chǔ)過程加快系統(tǒng)運(yùn)行速度，存儲(chǔ)過程只在創(chuàng)建時(shí)編譯，以后每次執(zhí)行時(shí)不需要重新編譯。

存儲(chǔ)過程可以封裝復(fù)雜的數(shù)據(jù)庫操作，簡化操作流程，例如對(duì)多個(gè)表的更新，刪除等。

可實(shí)現(xiàn)模塊化的程序設(shè)計(jì)，存儲(chǔ)過程可以多次調(diào)用，提供統(tǒng)一的數(shù)據(jù)庫訪問接口，改進(jìn)應(yīng)用程序的可維護(hù)性。

存儲(chǔ)過程可以增加代碼的安全性，對(duì)于用戶不能直接操作存儲(chǔ)過程中引用的對(duì)象，SQL??Server可以設(shè)定用戶對(duì)指定存儲(chǔ)過程的執(zhí)行權(quán)限。

存儲(chǔ)過程可以降低網(wǎng)絡(luò)流量，存儲(chǔ)過程代碼直接存儲(chǔ)于數(shù)據(jù)庫中，在客戶端與服務(wù)器的通信過程中，不會(huì)產(chǎn)生大量的T_SQL代碼流量。

存儲(chǔ)過程的缺點(diǎn)：

數(shù)據(jù)庫移植不方便，存儲(chǔ)過程依賴與數(shù)據(jù)庫管理系統(tǒng)，SQL?Server?存儲(chǔ)過程中封裝的操作代碼不能直接移植到其他的數(shù)據(jù)庫管理系統(tǒng)中。

不支持面向?qū)ο蟮脑O(shè)計(jì)，無法采用面向?qū)ο蟮姆绞綄⑦壿嫎I(yè)務(wù)進(jìn)行封裝，甚至形成通用的可支持服務(wù)的業(yè)務(wù)邏輯框架.

代碼可讀性差，不易維護(hù)。不支持集群。

二：存儲(chǔ)過程分類

1.系統(tǒng)存儲(chǔ)過程

系統(tǒng)存儲(chǔ)過程是SQL?Server系統(tǒng)自身提供的存儲(chǔ)過程，可以作為命令執(zhí)行各種操作。

系統(tǒng)存儲(chǔ)過程主要用來從系統(tǒng)表中獲取信息，使用系統(tǒng)存儲(chǔ)過程完成數(shù)據(jù)庫服務(wù)器的管理工作，為系統(tǒng)管理員提供幫助，為用戶查看數(shù)據(jù)庫對(duì)象提供方便，系統(tǒng)存儲(chǔ)過程位于數(shù)據(jù)庫服務(wù)器中，并且以sp_開頭，系統(tǒng)存儲(chǔ)過程定義在系統(tǒng)定義和用戶定義的數(shù)據(jù)庫中，在調(diào)用時(shí)不必在存儲(chǔ)過程前加數(shù)據(jù)庫限定名。例如：sp_rename系統(tǒng)存儲(chǔ)過程可以修改當(dāng)前數(shù)據(jù)庫中用戶創(chuàng)建對(duì)象的名稱，sp_helptext存儲(chǔ)過程可以顯示規(guī)則，默認(rèn)值或視圖的文本信息，SQL?SERVER服務(wù)器中許多的管理工作都是通過執(zhí)行系統(tǒng)存儲(chǔ)過程來完成的，許多系統(tǒng)信息也可以通過執(zhí)行系統(tǒng)存儲(chǔ)過程來獲得。

系統(tǒng)存儲(chǔ)過程創(chuàng)建并存放在與系統(tǒng)數(shù)據(jù)庫master中，一些系統(tǒng)存儲(chǔ)過程只能由系統(tǒng)管理員使用，而有些系統(tǒng)存儲(chǔ)過程通過授權(quán)可以被其它用戶所使用。

2.用戶存儲(chǔ)過程(自定義存儲(chǔ)過程)

自定義存儲(chǔ)過程即用戶使用T_SQL語句編寫的、為了實(shí)現(xiàn)某一特定業(yè)務(wù)需求，在用戶數(shù)據(jù)庫中編寫的T_SQL語句集合，自定義存儲(chǔ)過程可以接受輸入?yún)?shù)、向客戶端返回結(jié)果和信息，返回輸出參數(shù)等。創(chuàng)建自定義存儲(chǔ)過程時(shí)，存儲(chǔ)過程名前加上"##"表示創(chuàng)建了一個(gè)全局的臨時(shí)存儲(chǔ)過程;存儲(chǔ)過程前面加上"#"時(shí)，表示創(chuàng)建的局部臨時(shí)存儲(chǔ)過程。局部臨時(shí)存儲(chǔ)過程只能在創(chuàng)建它的回話中使用，會(huì)話結(jié)束時(shí)，將被刪除。這兩種存儲(chǔ)過程都存儲(chǔ)在tempdb數(shù)據(jù)庫中。

用戶定義的存儲(chǔ)過程分為兩類：T_SQL?和CLR

T_SQL:存儲(chǔ)過程是值保存的T_SQL語句集合，可以接受和返回用戶提供的參數(shù)，存儲(chǔ)過程也可能從數(shù)據(jù)庫向客戶端應(yīng)用程序返回?cái)?shù)據(jù)。

CLR存儲(chǔ)過程是指引用Microsoft.NET?Framework公共語言的方法存儲(chǔ)過程，可以接受和返回用戶提供的參數(shù)，它們?cè)?NET?Framework程序集是作為類的公共靜態(tài)方法實(shí)現(xiàn)的。

3.擴(kuò)展存儲(chǔ)過程

擴(kuò)展存儲(chǔ)過程是以在SQL?SERVER環(huán)境外執(zhí)行的動(dòng)態(tài)連接(DLL文件)來實(shí)現(xiàn)的，可以加載到SQL?SERVER實(shí)例運(yùn)行的地址空間中執(zhí)行，擴(kuò)展存儲(chǔ)過程可以用SQL?SERVER擴(kuò)展存儲(chǔ)過程API編程，擴(kuò)展存儲(chǔ)過程以前綴"xp_"來標(biāo)識(shí)，對(duì)于用戶來說，擴(kuò)展存儲(chǔ)過程和普通話存儲(chǔ)過程一樣，可以用相同的方法來執(zhí)行。?

三：創(chuàng)建存儲(chǔ)過程

工欲善其事，必先利其器，準(zhǔn)備數(shù)據(jù)如下:

use sample_db;--創(chuàng)建測試books表createtable books (

? ? book_id intidentity(1,1)primarykey,

? ? book_name varchar(20),

? ? book_price float,

? ? book_auth varchar(10)

);--插入測試數(shù)據(jù)insertinto books (book_name,book_price,book_auth)

? ? ? ? ? ? ? ? ? ? values? ? ? ? ? ? ? ? ? ? ? ? ('論語',25.6,'孔子'),

? ? ? ? ? ? ? ? ? ? ? ? ('天龍八部',25.6,'金庸'),

? ? ? ? ? ? ? ? ? ? ? ? ('雪山飛狐',32.7,'金庸'),

? ? ? ? ? ? ? ? ? ? ? ? ('平凡的世界',35.8,'路遙'),

? ? ? ? ? ? ? ? ? ? ? ? ('史記',54.8,'司馬遷');

1.創(chuàng)建無參存儲(chǔ)過程

--1.創(chuàng)建無參存儲(chǔ)過程if(exists(select*fromsys.objectswherename='getAllBooks'))

? ? dropproc proc_get_studentgocreateprocedure getAllBooksasselect*from books;--調(diào)用,執(zhí)行存儲(chǔ)過程execgetAllBooks;

2.修改存儲(chǔ)過程

alterprocedure dbo.getAllBooks asselectbook_authfrombooks;

3.刪除存儲(chǔ)過程

dropproceduregetAllBooks;

4.重命名存儲(chǔ)過程

sp_rename getAllBooks,proc_get_allBooks;

5.創(chuàng)建帶參數(shù)的存儲(chǔ)過程

?存儲(chǔ)過程的參數(shù)分為兩種：輸入?yún)?shù)和輸出參數(shù)

輸入?yún)?shù)：用于向存儲(chǔ)過程傳入值，類似java語言或則c中的值傳遞。

輸出參數(shù)：用于調(diào)用存儲(chǔ)過程后，參會(huì)結(jié)果，類似java語言的按引用傳遞。

值傳遞和引用傳遞區(qū)別:

基本數(shù)據(jù)類型賦值屬于值傳遞；引用類型之間賦值屬于引用傳遞。

值傳遞傳遞的是實(shí)實(shí)在在的變量值；引用傳遞傳遞的是對(duì)象的引用地址。

值傳遞后，兩個(gè)變量改變的是各自的值；引用傳遞后，兩個(gè)引用改變的是同一個(gè)對(duì)象的狀態(tài)

(1)帶一個(gè)參數(shù)存儲(chǔ)過程

if(exists(select*fromsys.objectswherename='searchBooks'))

? ? dropproc searchBooksgocreateprocsearchBooks(@bookIDint)as--要求book_id列與輸入?yún)?shù)相等select*frombookswherebook_id=@bookID;--執(zhí)行searchBooksexecsearchBooks1;

(2)帶2個(gè)參數(shù)存儲(chǔ)過程

if(exists(select*fromsys.objectswherename='searchBooks1'))

? ? dropproc searchBooks1gocreateproc searchBooks1(

? ? @bookIDint,

? ? @bookAuthvarchar(20)

)as--要求book_id和book_Auth列與輸入?yún)?shù)相等select*frombookswherebook_id=@bookIDandbook_auth=@bookAuth;execsearchBooks11,'金庸';

(3)創(chuàng)建有返回值的存儲(chǔ)過程

if(exists(select*fromsys.objectswherename='getBookId'))

? ? dropproc getBookIdgocreateproc getBookId(

? ? @bookAuthvarchar(20),--輸入?yún)?shù),無默認(rèn)值@bookIdintoutput--輸入/輸出參數(shù) 無默認(rèn)值)asselect@bookId=book_idfrombookswherebook_auth=@bookAuth--執(zhí)行g(shù)etBookId這個(gè)帶返回值的存儲(chǔ)過程declare@idint--聲明一個(gè)變量用來接收?qǐng)?zhí)行存儲(chǔ)過程后的返回值execgetBookId'孔子',@id outputselect@idasbookId;--as是給返回的列值起一個(gè)名字

(4)創(chuàng)建帶通配符的存儲(chǔ)過程

if(exists(select*fromsys.objectswherename='charBooks'))

? ? dropproc charBooksgocreateproc charBooks(

? ? @bookAuthvarchar(20)='金%',

? ? @bookNamevarchar(20)='%')asselect*frombookswherebook_authlike@bookAuthandbook_namelike@bookName;--執(zhí)行存儲(chǔ)過程charBooksexeccharBooks'孔%','論%';

(5)加密存儲(chǔ)過程

　　with encryption子句對(duì)用戶隱藏存儲(chǔ)過程的文本.下例創(chuàng)建加密過程,使用 sp_helptext 系統(tǒng)存儲(chǔ)過程獲取關(guān)于加密過程的信息,然后嘗試直接從 syscomments 表中獲取關(guān)于該過程的信息.

if(object_id('books_encryption','P')isnotnull)

? ? dropproc books_encryptiongocreateproc books_encryption with encryptionasselect*from books;--執(zhí)行此過程books_encryptionexec books_encryption;execsp_helptext'books_encryption';--控制臺(tái)會(huì)顯示"對(duì)象 'books_encryption' 的文本已加密。"

(6).不緩存存儲(chǔ)過程

--with? recompile不緩存if(object_id('book_temp','P')isnotnull)

? ? dropproc book_tempgocreateproc book_tempwith recompileasselect*from books;goexec book_temp;execsp_helptext'book_temp';

(7).創(chuàng)建帶游標(biāo)參數(shù)的存儲(chǔ)過程

if(object_id('book_cursor','P')isnotnull)

? ? dropproc book_cursorgocreateproc book_cursor

? ? @bookCursorcursorvarying outputasset@bookCursor=cursorforward_only staticforselectbook_id,book_name,book_authfrom books

? ? open@bookCursor;go--調(diào)用book_cursor存儲(chǔ)過程declare@curcursor,

? ? ? ? @bookIDint,

? ? ? ? @bookNamevarchar(20),

? ? ? ? @bookAuthvarchar(20);execbook_cursor@bookCursor=@cur output;fetchnextfrom@curinto@bookID,@bookName,@bookAuth;while(@@FETCH_STATUS=0)beginfetchnextfrom@curinto@bookID,@bookName,@bookAuth;

? ? print'bookID:'+convert(varchar,@bookID)+' , bookName: '+@bookName+' ,bookAuth: '+@bookAuth;endclose@cur--關(guān)閉游標(biāo)DEALLOCATE@cur;--釋放游標(biāo)

(8).創(chuàng)建分頁存儲(chǔ)過程

if(object_id('book_page','P')isnotnull)

? ? dropproc book_pagegocreateproc book_page(

? ? @TableNamevarchar(50),--表名@ReFieldsStrvarchar(200)='*',--字段名(全部字段為*)@OrderStringvarchar(200),--排序字段(必須!支持多字段不用加order by)@WhereStringvarchar(500)=N'',--條件語句(不用加where)@PageSizeint,--每頁多少條記錄@PageIndexint=1,--指定當(dāng)前為第幾頁@TotalRecordintoutput--返回總記錄數(shù))asbegin--處理開始點(diǎn)和結(jié)束點(diǎn)Declare@StartRecordint;

? ? Declare@EndRecordint;

? ? Declare@TotalCountSqlnvarchar(500);

? ? Declare@SqlStringnvarchar(2000);? ?

? ? set@StartRecord=(@PageIndex-1)*@PageSize+1set@EndRecord=@StartRecord+@PageSize-1SET@TotalCountSql=N'select @TotalRecord = count(*) from '+@TableName;--總記錄數(shù)語句SET@SqlString=N'(select row_number() over (order by '+@OrderString+') as rowId,'+@ReFieldsStr+' from '+@TableName;--查詢語句--IF(@WhereString!=''or@WhereString!=null)

? ? ? ? BEGINSET@TotalCountSql=@TotalCountSql+'? where '+@WhereString;

? ? ? ? ? ? SET@SqlString=@SqlString+'? where '+@WhereString;? ? ? ? ? ?

? ? ? ? END--第一次執(zhí)行得到--IF(@TotalRecord is null)--? BEGINEXECsp_executesql@totalCountSql,N'@TotalRecord int out',@TotalRecordoutput;--返回總記錄數(shù)--? END----執(zhí)行主語句set@SqlString='select * from '+@SqlString+') as t where rowId between '+ltrim(str(@StartRecord))+' and '+ltrim(str(@EndRecord));

? ? Exec(@SqlString)? ? END--調(diào)用分頁存儲(chǔ)過程book_pageexecbook_page'books','*','book_id','',3,1,0;--declare@totalCountintexecbook_page'books','*','book_id','',3,1,@totalCount output; select@totalCountastotalCount;--總記錄數(shù)。