訪問數(shù)據(jù)庫十二存儲過程

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

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

create

procedure

sp_query_bankMoney

as

select

*

from

bankMoney

go

exec

sp_query_bankMoney注*

在使用過程中只需要把中的SQL語句替換為存儲過程名，就可以了很方便吧！存儲過程實例實例2（向存儲過程中傳遞參數(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í)行該存儲過程的方法是：

declare

@total_price

int

exec

insert_bank

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

output

print

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

go存儲過程實例實例3：使用帶有復(fù)雜

Select

語句的簡單過程：下面的存儲過程從四個表的聯(lián)接中返回所有作者（提供了姓名）、出版的書籍以及出版社。該存儲過程不使用任何參數(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

存儲過程實例au_info_all

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

EXECUTE

au_info_all

--

or

EXEC

au_info_all如果該過程是批處理中的第一條語句，則可使用：

au_info_all存儲過程實例實例4：使用帶有參數(shù)的簡單過程

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存儲過程實例au_info

存儲過程可以通過以下方法執(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'存儲過程實例如果該過程是批處理中的第一條語句，則可使用：

au_info

'Dull',

'Ann'

--

or

au_info

@lastname

=

'Dull',

@firstname

=

'Ann'

--

or

au_info

@firstname

=

'Ann',

@lastname

=

'Dull'存儲過程實例實例5：使用帶有通配符參數(shù)的簡單過程

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存儲過程實例au_info2

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

EXECUTE

au_info2

--

or

EXECUTE

au_info2

'Wh%'

--

or

EXECUTE

au_