访问数据库十二存储过程

单元五ADO.NET访问数据库(十二)存储过程主讲教师：宋园园学习目标【知识目标】1.了解存储过程的语法,并会使用存储过程；

【技能目标】1.掌握存储过程的基本语法;2.使用存储过程实现对数据库的操作;注：本课内容参考教材单元五5.3存储过程简介存储过程（StoredProcedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程简介各种大型关系数据库都支持存储过程。存储过程是一组SQL语句的集合，相当于C#语言中的语法。存储过程在效率、安全性、可复用方面比普通的SQL语句有优势，因此在实际项目中也有广泛应用。存储过程的定义存储过程（procedure）类似于C语言中的函数用来执行管理任务或应用复杂的业务规则存储过程可以带参数，也可以返回结果类似于C语言中的函数intsum(inta,intb){ints;s=a+b;returns;}存储过程相当于C语言中的函数存储过程可以包含哪些内容存储过程------------------------单个SELECT语句SELECT语句块SELECT语句与逻辑控制语句可以包含存储过程的优点执行速度更快允许模块化程序设计提高系统安全性减少网络流通量(因为存储过程是保存在数据库中的)存储过程的分类1系统存储过程由系统定义，存放在master数据库中类似C语言中的系统函数系统存储过程的名称都以“sp_”开头或”xp_”开头2用户自定义存储过程由用户在自己的数据库中创建的存储过程类似C语言中的用户自定义函数常用的系统存储过程系统存储过程说明sp_databases列出服务器上的所有数据库。sp_helpdb报告有关指定数据库或所有数据库的信息sp_renamedb更改数据库的名称sp_tables返回当前环境下可查询的对象的列表sp_columns回某个表列的信息sp_help查看某个表的所有信息sp_helpconstraint查看某个表的约束sp_helpindex查看某个表的索引sp_stored_procedures列出当前环境中的所有存储过程。sp_password添加或修改登录帐户的密码。创建存储过程定义存储过程的语法createproc[edure]存储过程名@参数数据类型=默认值output,……,@参数n数据类型=默认值outputASSQL语句GO和C语言的函数一样，参数可选参数分为输入参数、输出参数输入参数允许有默认值带参数的存储过程intsum(inta,intb){ints;s=a+b;returns;}c=sum（5,8）传入参数值返回结果存储过程在Asp.Net中的应用执行系统存储过程执行无参数的存储过程执行带参数的存储过程存储过程实例实例1：只返回单一记录集的存储过程。要求1：查询表bankMoney的内容的存储过程

create

procedure

sp_query_bankMoney

as

select

*

from

bankMoney

go

exec

sp_query_bankMoney注*

在使用过程中只需要把中的SQL语句替换为存储过程名，就可以了很方便吧！存储过程实例实例2（向存储过程中传递参数）：加入一笔记录到表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查询分析器中执行该存储过程的方法是：

declare

@total_price

int

exec

insert_bank

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

output

print

'总余额为'+convert(varchar,@total_price)

go存储过程实例实例3：使用带有复杂

Select

语句的简单过程：下面的存储过程从四个表的联接中返回所有作者（提供了姓名）、出版的书籍以及出版社。该存储过程不使用任何参数。

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

存储过程可以通过以下方法执行：

EXECUTE

au_info_all

--

or

EXEC

au_info_all如果该过程是批处理中的第一条语句，则可使用：

au_info_all存储过程实例实例4：使用带有参数的简单过程

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

存储过程可以通过以下方法执行：

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：使用带有通配符参数的简单过程

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

存储过程可以用多种组合执行。下面只列出了部分组合：

EXECUTE

au_info2

--

or

EXECUTE

au_info2

'Wh%'

--

or

EXECUTE

au_