1. 存儲(chǔ)過(guò)程的類型：

(1) 用戶自定義存儲(chǔ)過(guò)程

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

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

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

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

(2) 擴(kuò)展存儲(chǔ)過(guò)程

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

(3) 系統(tǒng)存儲(chǔ)過(guò)程

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

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

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

2．Sql存儲(chǔ)過(guò)程

(1)創(chuàng)建存儲(chǔ)過(guò)程

使用 create Procedure語(yǔ)句來(lái)創(chuàng)建存儲(chǔ)過(guò)程，存儲(chǔ)過(guò)程名稱在該語(yǔ)句之后，as關(guān)鍵字標(biāo)示存儲(chǔ)過(guò)程主體的開(kāi)始，存儲(chǔ)過(guò)程有多個(gè)sql語(yǔ)句組成，例如下面的語(yǔ)句創(chuàng)建一個(gè)名為usp_getAllEmployees的存儲(chǔ)過(guò)程，用于從employeeDepartment表中檢索數(shù)據(jù)

Create Procedure usp_getAllEmployees

As

Select LastName，FirstName，JobTitle，Department

From employeeDepartment

要執(zhí)行一個(gè)存儲(chǔ)過(guò)程，可以使用execute語(yǔ)句：

Execute usp_getAllEmployees

存儲(chǔ)過(guò)程的參數(shù)

存儲(chǔ)過(guò)成人能夠通過(guò)參數(shù)與調(diào)用程序通訊。參數(shù)定義應(yīng)當(dāng)出現(xiàn)在存儲(chǔ)過(guò)程名稱的后面，as的前面，當(dāng)程序執(zhí)行存儲(chǔ)過(guò)程時(shí)，可通過(guò)存儲(chǔ)過(guò)程的參數(shù)向該存儲(chǔ)過(guò)程傳遞值，也可以output參數(shù)將值返回至調(diào)用程序

1、指定參數(shù)的名稱和數(shù)據(jù)類型

參數(shù)名稱應(yīng)當(dāng)以@開(kāi)始，以后的字符可以是遵守對(duì)象標(biāo)識(shí)符的任意字符，并以@@開(kāi)頭，因?yàn)檫@是用于內(nèi)置函數(shù)的標(biāo)識(shí)符號(hào)，例如下面創(chuàng)建的usp_GetProduct存儲(chǔ)過(guò)程包含@standardCost和@listPrice兩個(gè)參數(shù)，參數(shù)的數(shù)據(jù)類型均是money

Create produce usp_GetProduct

@standardCost money,@listPrice money

As

Select name, standardCost, listPrice from product

Where standardCost>@ standardCost and listPrice> @listPrice

執(zhí)行存儲(chǔ)過(guò)程時(shí)，既可以通過(guò)顯式的方式指定參數(shù)名稱并分配適當(dāng)?shù)闹?#xff0c;也可以直接分配參數(shù)值，如果使用了顯式方式，則按任意順序提供參數(shù)，如果未指定參數(shù)名稱，則必須按參數(shù)在存儲(chǔ)過(guò)程定義的時(shí)候的順序來(lái)提供。

Excute usp_GetProduct @ listPrice=100,@standardCost=10

Excute usp_GetProduct 10 ,100

2、為參數(shù)指定默認(rèn)值

在參數(shù)定義中可以為可選參數(shù)指定一個(gè)，默認(rèn)值，執(zhí)行該存儲(chǔ)過(guò)程時(shí)，如果未指定其他值，則使用默認(rèn)值

Create produce usp_GetProduct

@standardCost money=0,@listPrice money

As

Select name, standardCost, listPrice from product

Where standardCost>@ standardCost and listPrice> @listPrice

執(zhí)行該存儲(chǔ)過(guò)程，可以只為@listPrice指定參數(shù)

Excute usp_GetProduct @ listPrice=100

由于具有默認(rèn)參數(shù)通常是可選參數(shù)，所以建議將他們放置在參數(shù)列表的末尾以便于調(diào)用。

對(duì)于字符參數(shù)，在參數(shù)傳遞時(shí)可以指定通配符

3、指定輸出參數(shù)

默認(rèn)情況下，所有的參數(shù)均為輸出參數(shù)，要指定輸出參數(shù)，必須在參數(shù)定義中使用output關(guān)鍵字。當(dāng)存儲(chǔ)過(guò)程退出時(shí)，它將向調(diào)用程序返回輸出參數(shù)的當(dāng)前值。，例如，下面創(chuàng)建的存儲(chǔ)過(guò)程定義了一個(gè)輸出參數(shù)@productCount，用于返回ListPrice大于指定產(chǎn)品的數(shù)量

Create produce usp_GetProduct

@productCount int output,

@listPrice money

As

set @productCount= (select count(id) from product

Where listPrice> @listPrice)

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

如果需要修改存儲(chǔ)過(guò)程中的語(yǔ)句或者參數(shù)，可以刪除并重新創(chuàng)建該存儲(chǔ)過(guò)程，也可以使用alter producedure語(yǔ)句更改該存儲(chǔ)過(guò)程。刪除并重新建時(shí)，與該存儲(chǔ)過(guò)程關(guān)聯(lián)的所有權(quán)限將丟失，更改時(shí)，將更改過(guò)程或者參數(shù)定義，但為該存儲(chǔ)過(guò)程定義的權(quán)限將保留，將不會(huì)影響任何相關(guān)的存儲(chǔ)過(guò)程或觸發(fā)器

Alrter produce usp_GetProduct

@standardCost money=0,@listPrice money

As

Select name, standardCost, listPrice from product

Where standardCost>@ standardCost and listPrice> @listPrice

3. 存儲(chǔ)過(guò)程的重新編譯

1、指定在下次執(zhí)行時(shí)重新編譯

可以使用sp_recompile系統(tǒng)存儲(chǔ)過(guò)程指定在下次執(zhí)行存儲(chǔ)過(guò)程或觸發(fā)器進(jìn)行重新編譯

2、從sql server 2005開(kāi)始，引入了對(duì)存儲(chǔ)過(guò)程執(zhí)行語(yǔ)句級(jí)重新編譯的功能，也就是說(shuō)在重新編譯存儲(chǔ)過(guò)程時(shí)，值編譯導(dǎo)致重新編譯的語(yǔ)句，而不編譯整個(gè)存儲(chǔ)過(guò)程。

要使用此功能，應(yīng)當(dāng)在語(yǔ)句中包含recomple查詢提示，recomeple指示數(shù)據(jù)庫(kù)引擎在執(zhí)行查詢后，丟棄為其生成的查詢計(jì)劃，從而在下次執(zhí)行時(shí)強(qiáng)制編譯查詢計(jì)劃，如果未指定recompile。數(shù)據(jù)庫(kù)將緩存查詢計(jì)劃并從新使用它們

3、每次執(zhí)行時(shí)重新編譯村重過(guò)程

在創(chuàng)建存儲(chǔ)過(guò)程時(shí)指定with recompile選項(xiàng)，強(qiáng)制在執(zhí)行存儲(chǔ)過(guò)程時(shí)對(duì)其進(jìn)行重新編譯，指定該選項(xiàng)時(shí)，數(shù)據(jù)庫(kù)引擎將部位該存儲(chǔ)過(guò)程緩存執(zhí)行計(jì)劃，而是在每次執(zhí)行時(shí)都重新編譯

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

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

--調(diào)用book_cursor存儲(chǔ)過(guò)程

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 --關(guān)閉游標(biāo)

DEALLOCATE @cur; --釋放游標(biāo)

5．創(chuàng)建分頁(yè)存儲(chǔ)過(guò)程

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'', --條件語(yǔ)句(不用加where)

@PageSize int, --每頁(yè)多少條記錄

@PageIndex int = 1 , --指定當(dāng)前為第幾頁(yè)

@TotalRecord int output --返回總記錄數(shù)

)

as

begin

--處理開(kāi)始點(diǎn)和結(jié)束點(diǎn)

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;--總記錄數(shù)語(yǔ)句

SET @SqlString = N'(select row_number() over (order by '+ @OrderString +') as rowId,'+@ReFieldsStr+' from '+ @TableName;--查詢語(yǔ)句

--

IF (@WhereString! = '' or @WhereString!=null)

BEGIN

SET @TotalCountSql=@TotalCountSql + ' where '+ @WhereString;

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

END

--第一次執(zhí)行得到

--IF(@TotalRecord is null)

-- BEGIN

EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;--返回總記錄數(shù)

-- END

----執(zhí)行主語(yǔ)句

set @SqlString ='select * from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' + ltrim(str(@EndRecord));

Exec(@SqlString)

END

--調(diào)用分頁(yè)存儲(chǔ)過(guò)程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;--總記錄數(shù)。

總結(jié)

以上是生活随笔為你收集整理的pb 执行存储过程带参数_数据库存储过程的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。