訪問(wèn)數(shù)據(jù)庫(kù)十二存儲(chǔ)過(guò)程

單元五ADO.NET訪問(wèn)數(shù)據(jù)庫(kù)(十二)存儲(chǔ)過(guò)程主講教師：宋園園學(xué)習(xí)目標(biāo)【知識(shí)目標(biāo)】1.了解存儲(chǔ)過(guò)程的語(yǔ)法,并會(huì)使用存儲(chǔ)過(guò)程；

【技能目標(biāo)】1.掌握存儲(chǔ)過(guò)程的基本語(yǔ)法;2.使用存儲(chǔ)過(guò)程實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)的操作;注：本課內(nèi)容參考教材單元五5.3存儲(chǔ)過(guò)程簡(jiǎn)介存儲(chǔ)過(guò)程（StoredProcedure）是在大型數(shù)據(jù)庫(kù)系統(tǒng)中，一組為了完成特定功能的SQL語(yǔ)句集，經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中，用戶通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(shù)（如果該存儲(chǔ)過(guò)程帶有參數(shù)）來(lái)執(zhí)行它。存儲(chǔ)過(guò)程簡(jiǎn)介各種大型關(guān)系數(shù)據(jù)庫(kù)都支持存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程是一組SQL語(yǔ)句的集合，相當(dāng)于C#語(yǔ)言中的語(yǔ)法。存儲(chǔ)過(guò)程在效率、安全性、可復(fù)用方面比普通的SQL語(yǔ)句有優(yōu)勢(shì)，因此在實(shí)際項(xiàng)目中也有廣泛應(yīng)用。存儲(chǔ)過(guò)程的定義存儲(chǔ)過(guò)程（procedure）類(lèi)似于C語(yǔ)言中的函數(shù)用來(lái)執(zhí)行管理任務(wù)或應(yīng)用復(fù)雜的業(yè)務(wù)規(guī)則存儲(chǔ)過(guò)程可以帶參數(shù)，也可以返回結(jié)果類(lèi)似于C語(yǔ)言中的函數(shù)intsum(inta,intb){ints;s=a+b;returns;}存儲(chǔ)過(guò)程相當(dāng)于C語(yǔ)言中的函數(shù)存儲(chǔ)過(guò)程可以包含哪些內(nèi)容存儲(chǔ)過(guò)程------------------------單個(gè)SELECT語(yǔ)句SELECT語(yǔ)句塊SELECT語(yǔ)句與邏輯控制語(yǔ)句可以包含存儲(chǔ)過(guò)程的優(yōu)點(diǎn)執(zhí)行速度更快允許模塊化程序設(shè)計(jì)提高系統(tǒng)安全性減少網(wǎng)絡(luò)流通量(因?yàn)榇鎯?chǔ)過(guò)程是保存在數(shù)據(jù)庫(kù)中的)存儲(chǔ)過(guò)程的分類(lèi)1系統(tǒng)存儲(chǔ)過(guò)程由系統(tǒng)定義，存放在master數(shù)據(jù)庫(kù)中類(lèi)似C語(yǔ)言中的系統(tǒng)函數(shù)系統(tǒng)存儲(chǔ)過(guò)程的名稱(chēng)都以“sp_”開(kāi)頭或”xp_”開(kāi)頭2用戶自定義存儲(chǔ)過(guò)程由用戶在自己的數(shù)據(jù)庫(kù)中創(chuàng)建的存儲(chǔ)過(guò)程類(lèi)似C語(yǔ)言中的用戶自定義函數(shù)常用的系統(tǒng)存儲(chǔ)過(guò)程系統(tǒng)存儲(chǔ)過(guò)程說(shuō)明sp_databases列出服務(wù)器上的所有數(shù)據(jù)庫(kù)。sp_helpdb報(bào)告有關(guān)指定數(shù)據(jù)庫(kù)或所有數(shù)據(jù)庫(kù)的信息sp_renamedb更改數(shù)據(jù)庫(kù)的名稱(chēng)sp_tables返回當(dāng)前環(huán)境下可查詢的對(duì)象的列表sp_columns回某個(gè)表列的信息sp_help查看某個(gè)表的所有信息sp_helpconstraint查看某個(gè)表的約束sp_helpindex查看某個(gè)表的索引sp_stored_procedures列出當(dāng)前環(huán)境中的所有存儲(chǔ)過(guò)程。sp_password添加或修改登錄帳戶的密碼。創(chuàng)建存儲(chǔ)過(guò)程定義存儲(chǔ)過(guò)程的語(yǔ)法createproc[edure]存儲(chǔ)過(guò)程名@參數(shù)數(shù)據(jù)類(lèi)型=默認(rèn)值output,……,@參數(shù)n數(shù)據(jù)類(lèi)型=默認(rèn)值outputASSQL語(yǔ)句GO和C語(yǔ)言的函數(shù)一樣，參數(shù)可選參數(shù)分為輸入?yún)?shù)、輸出參數(shù)輸入?yún)?shù)允許有默認(rèn)值帶參數(shù)的存儲(chǔ)過(guò)程intsum(inta,intb){ints;s=a+b;returns;}c=sum（5,8）傳入?yún)?shù)值返回結(jié)果存儲(chǔ)過(guò)程在Asp.Net中的應(yīng)用執(zhí)行系統(tǒng)存儲(chǔ)過(guò)程執(zhí)行無(wú)參數(shù)的存儲(chǔ)過(guò)程執(zhí)行帶參數(shù)的存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程實(shí)例實(shí)例1：只返回單一記錄集的存儲(chǔ)過(guò)程。要求1：查詢表bankMoney的內(nèi)容的存儲(chǔ)過(guò)程

create

procedure

sp_query_bankMoney

as

select

*

from

bankMoney

go

exec

sp_query_bankMoney注*

在使用過(guò)程中只需要把中的SQL語(yǔ)句替換為存儲(chǔ)過(guò)程名，就可以了很方便吧！存儲(chǔ)過(guò)程實(shí)例實(shí)例2（向存儲(chǔ)過(guò)程中傳遞參數(shù)）：加入一筆記錄到表bankMoney，并查詢此表中userID=

Zhangsan的所有存款的總金額。

Create

proc

insert_bank

@param1

char(10),@param2

varchar(20),@param3

varchar(20),@param4

int,@param5

int

output

with

encryption

---------加密

asinsert

bankMoney

(id,userID,sex,Money)

Values(@param1,@param2,@param3,

@param4)select

@param5=sum(Money)

from

bankMoney

where

userID='Zhangsan'

go在SQL

Server查詢分析器中執(zhí)行該存儲(chǔ)過(guò)程的方法是：

declare

@total_price

int

exec

insert_bank

'004','Zhangsan','男',100,@total_price

output

print

'總余額為'+convert(varchar,@total_price)

go存儲(chǔ)過(guò)程實(shí)例實(shí)例3：使用帶有復(fù)雜

Select

語(yǔ)句的簡(jiǎn)單過(guò)程：下面的存儲(chǔ)過(guò)程從四個(gè)表的聯(lián)接中返回所有作者（提供了姓名）、出版的書(shū)籍以及出版社。該存儲(chǔ)過(guò)程不使用任何參數(shù)。

USE

pubs

IF

EXISTS

(Select

name

FROM

sysobjects

Where

name

=

'au_info_all'

AND

type

=

'P')

Drop

PROCEDURE

au_info_all

GO

Create

PROCEDURE

au_info_all

AS

Select

au_lname,

au_fname,

title,

pub_name

FROM

authors

a

INNER

JOIN

titleauthor

ta

ON

a.au_id

=

ta.au_id

INNER

JOIN

titles

t

ON

t.title_id

=

ta.title_id

INNER

JOIN

publishers

p

ON

t.pub_id

=

p.pub_id

GO

存儲(chǔ)過(guò)程實(shí)例au_info_all

存儲(chǔ)過(guò)程可以通過(guò)以下方法執(zhí)行：

EXECUTE

au_info_all

--

or

EXEC

au_info_all如果該過(guò)程是批處理中的第一條語(yǔ)句，則可使用：

au_info_all存儲(chǔ)過(guò)程實(shí)例實(shí)例4：使用帶有參數(shù)的簡(jiǎn)單過(guò)程

Create

PROCEDURE

au_info

@lastname

varchar(40),

@firstname

varchar(20)

AS

Select

au_lname,

au_fname,

title,

pub_name

FROM

authors

a

INNER

JOIN

titleauthor

ta

ON

a.au_id

=

ta.au_id

INNER

JOIN

titles

t

ON

t.title_id

=

ta.title_id

INNER

JOIN

publishers

p

ON

t.pub_id

=

p.pub_id

Where

au_fname

=

@firstname

AND

au_lname

=

@lastname

GO存儲(chǔ)過(guò)程實(shí)例au_info

存儲(chǔ)過(guò)程可以通過(guò)以下方法執(zhí)行：

EXECUTE

au_info

'Dull',

'Ann'

--

or

EXECUTE

au_info

@lastname

=

'Dull',

@firstname

=

'Ann'

--

or

EXECUTE

au_info

@firstname

=

'Ann',

@lastname

=

'Dull'

--

or

EXEC

au_info

'Dull',

'Ann'

--

or

EXEC

au_info

@lastname

=

'Dull',

@firstname

=

'Ann'

--

or

EXEC

au_info

@firstname

=

'Ann',

@lastname

=

'Dull'存儲(chǔ)過(guò)程實(shí)例如果該過(guò)程是批處理中的第一條語(yǔ)句，則可使用：

au_info

'Dull',

'Ann'

--

or

au_info

@lastname

=

'Dull',

@firstname

=

'Ann'

--

or

au_info

@firstname

=

'Ann',

@lastname

=

'Dull'存儲(chǔ)過(guò)程實(shí)例實(shí)例5：使用帶有通配符參數(shù)的簡(jiǎn)單過(guò)程

Create

PROCEDURE

au_info2

@lastname

varchar(30)

=

'D%',

@firstname

varchar(18)

=

'%'

AS

Select

au_lname,

au_fname,

title,

pub_name

FROM

authors

a

INNER

JOIN

titleauthor

ta

ON

a.au_id

=

ta.au_id

INNER

JOIN

titles

t

ON

t.title_id

=

ta.title_id

INNER

JOIN

publishers

p

ON

t.pub_id

=

p.pub_id

Where

au_fname

LIKE

@firstname

AND

au_lname

LIKE

@lastname

GO存儲(chǔ)過(guò)程實(shí)例au_info2

存儲(chǔ)過(guò)程可以用多種組合執(zhí)行。下面只列出了部分組合：

EXECUTE

au_info2

--

or

EXECUTE

au_info2

'Wh%'

--

or

EXECUTE

au_