崔连和-SQLServer课件-第9课-数据库存储过程

双击添加署名/公司名/日期数据库管理项目教程崔连和SQLServer2008序

想象一只刚被母鹰孵化出来的雏鹰，抖动粘湿的翅膀，想要飞上青天的模样。

那是一个多么壮观的场面呀！从一只不会飞行的雏鹰，到翱翔蓝天的巨鹰，这个过程要经过多少磨炼呀！序

雏鹰在窝里嗷嗷待哺的等待母亲。

雏鹰挥舞弱软的臂膀。

母鹰带领小鹰晒太阳。

一只小鹰不小心从树窝跌落因而折翼

小鹰在母鹰背上享受凌风的速度。

小鹰开始飞行。

长成巨鹰。

学习的过程需要有耐心的培养、爱心的呵护雄心的感染、关心的启发，以及信心的鼓舞。第9章

存储过程

把最常用的SQL语句编写成小程序并存起来备用，这个小程序在SQLServer里就是存储过程。存储过程是利用SQLServer所提供的Transact-SQL语言所编写的程序。在本章中将对存储过程进行详细的介绍与讲解。1．存储过程的创建2．创建带参数的存储过程3．存储过程的修改4．存储过程的删除9.1任务一创建存储过程任务名称：编写存储过程pr_News，利用这个存储过程显示表News中的数据。任务描述：在本书使用的实例办公自动化系统中，要经常使用一些常用的SQL语句操作数据表，这些语句重复使用，每次使用时都要重新编写，不但麻烦而且十分容易出错。SQLServer2008中提供了存储过程正好解决了这样的难题。

存储过程，英文简称为StoredProcedure，是在大型数据库系统中，一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字来执行它。

今天的任务是创建一个最简单的存储过程pr_News，执行该存储过程，能够显示出表News的内容。9.1任务一创建存储过程简要分析：

完成这个任务，也就掌握了存储过程从创建到执行的全过程，其实这是一个最简单的存储过程，这个过程并无实际意义，却能够带领我们掌握存储过程的全面知识。实现步骤：（1）找准入口，即启动存储过程编写，进入编写存储过程代码的界面；（2）编写存储过程的代码，这里很重要的一点就是了解存储过程中包含的常规语句；（3）执行存储过程，验证编写的正确性。9.1任务一创建存储过程一．使用图形方式创建存储过程步骤01：开始创建存储过程启动SSMS，单击【OASystem】数据库，开展其节点，在其中创建存储过程的数据库，在其下有一个“可编程性”包含“存储过程”，右击【存储过程】图标弹出快捷菜单，单击【新建存储过程】如图9-1所示图9-1新建存储过程9.1任务一创建存储过程步骤02：

在快捷菜单中单击【新建存储过程】，打开“新建存储过程”编辑窗口，在“新建存储过程”编辑窗口中，系统生成了默认的代码，初学者一般将其删除，然后自行书写存储过程定义，如图9-2所示。其中：（1）useoasystem用于打开数据库，这里要提醒，凡是对数据库中表的操作必须先打开数据库；（2）go用于执行上一句useoasystem打开数据库的语句；（3）CREATEPROCEDURE：是创建存储过程的命令；9.1任务一创建存储过程（4）pr_News：是存储过程的名称，一般习惯上用存储过程的英文procedure的前两个字母pr做为存储过程名称的前辍；（5）AS：AS下面是这个存储过程要执行的语句；图9-2编写存储过程9.1任务一创建存储过程步骤03：

定义完存储过程后，单击【执行】按钮保存存储过程，如图9-3所示。点击，保存存储过程图9-3保存存储过程9.1任务一创建存储过程步骤04：

运行存储过程。运行存储过程要先单击工具栏的【新建查询】然后在命令窗口输入新建的存储过程名。运行存储过程更专业的做法是输入：execpr_News，但很多程序员，习惯使用简洁的做法，即直接输入存储过程名称来执行存储过程。单击工具栏上的【执行】，即可。如图9-4所示。9.1任务一创建存储过程第1步：新建查询第2步：输入存储过程的名字，专业的做法是输入：execpr_News第3步：执行执行完上述三步，在此显示结果图9-4运行存储过程

友情提醒：exec是SQL中用来执行存储过程或函数的命令，其后面直接跟欲执行的存储过程或函数，也可以带参数执行。9.1任务一创建存储过程1.什么是存储过程

第五章讲了SQL语句,这些语句每次使用时候,都要录入一遍再执行，执行这些sql语句的时候，系统要先编译再执行。有没有什么办法，把常用SQL语句存成一个小程序，使用的时候直接调用就可以呢？SQLServer2008里面的存储过程就是为解决这一问题而诞生的。存储过程就是编译好的一些sql语句，在使用的时候直接就调用即可。

具体说存储过程是在数据库系统中，一组为了完成特定功能的SQL语句，经编译后存储在数据库，用户通过指定存储过程名字并给出参数来执行它。存储过程英文StoredProcedure。9.1任务一创建存储过程2.存储过程的优点为什么要创建存储过程呢？它能带来什么方便与快捷呢？存储过程具有很多优点，主要有以下几点：（1）执行效率更高。存储过程建立之后，已经编译并且储存到数据库中，使用时不必重新编译，相对直接写sql句的前先分析再执行，效率自然要高得多。9.1任务一创建存储过程（2）安全性更好。需要执行的语句变成存储过程后，就成为存储表的一个安全机制，当一个数据表没有设权限，而对该数据表的操作又需要进行权限控制时，可以使用存储过程做为一个存取通道，对不同权限的用户使用不同的存储过程。（3）存储过程可以设置参数，可以根据传入参数的不同重复使用同一个存储过程，从而高效的提高代码的优化率和可读性。9.1任务一创建存储过程3．存储过程的分类存储过程共分为3大类，他们分别是：（1）系统存储过程。系统存储过程是SQLServer2008系统自带的、已经编好的存储过程，使用时直接调用即可。系统存储过程以sp_开头；（2）本地存储过程。本地存储过程是由用户创建并完成摸一特定功能的存储过程，一般我们所说的存储工程通常指的是本地存储过程。（3）扩展存储过程。扩张存储过程是用户可以使用外部程序语言编写的存储过程，扩展存储过程的名称以xp_开头。9.1任务一创建存储过程4．常用系统过程表9-1常用系统存储过程一览表类型系统过程名说明对象sp_help报告当前数据库中对象的信息(sysobjects表中对象：表、视图、自定义函数、过程、触发器、数据类型、主键、外键、check、unique、默认等)sp_rename更改当前数据库中用户创建对象(如表、视图、列、存储过程、触发器、默认值、数据库、对象或规则或用户定义数据类型)的名称数据库sp_databases显示服务器中所有可以使用的数据库的信息sp_helpdb显示服务器中数据库的信息sp_helpfile显示数据库中文件的信息sp_helpfilegroup显示数据库中文件组的信息sp_renamedb更改数据库的名称sp_defaultdb更改用户的默认数据库9.1任务一创建存储过程表9-1常用系统存储过程一览表查询sp_tables返回当前数据库中可查询的对象(表、视图)信息默认sp_bindefault绑定默认sp_unbindefault解除绑定默认规则sp_bindrule绑定规则sp_unbindrule解除绑定规则索引sp_helpindex报告当前数据库中指定表或视图上索引的信息sp_pkeys返回当前数据库中指定表的主键信息sp_fkeys返回当前数据库中的外键信息登录sp_addlogin创建登录账号sp_defaultlanguage更改登录的默认语言sp_grantlogin授权Windows登录账户登录SQLServersp_denylogin拒绝Windows账户登录SQLServersp_password添加或更改

SQLServer登录用户的密码sp_revokelogin删除Windows身份验证的登录账户sp_droplogin删除SQLserver身份验证的登录账户9.1任务一创建存储过程表9-1常用系统存储过程一览表服务器角色sp_helpsrvrole返回固定服务器角色列表sp_addsrvrolemember向固定服务器角色中添加成员sp_helpsrvrolemember查看固定服务器角色成员sp_dropsrvrolemember从固定服务器角色中删除成员固定数据库角色sp_helpdbfixedrole显示固定数据库角色的列表sp_dbfixedrolepermission显示每个固定数据库角色的特定权限数据库用户sp_revokedbaccess从当前数据库中删除安全账户sp_grantdbaccessMicrosoftSQLServer登录或

MicrosoftWindowsNT用户或组在当前数据库中添加一个安全账户，并使其能够被授予在数据库中执行活动的权限数据库角色sp_addrole添加数据库角色sp_addrolemember添加数据库角色成员sp_droprolemember删除数据库角色成员备份设备sp_addumpdevice添加备份设备sp_dropdevice除去数据库设备或备份设备9.1任务一创建存储过程表9-1常用系统存储过程一览表操作员sp_add_operator创建操作员sp_update_operator更新操作员sp_help_operator查看定义操作员的信息警报警报sp_add_alert定义警报sp_help_alert报告有关为服务器定义的警报的信息sp_updata_alert更新现有警报的设置sp_add_notification设置警报提示sp_delete_alert删除警报选项sp_dboption显示或更改数据库选项sp_serveroption为远程服务器和链接服务器设置服务器选项9.1任务一创建存储过程5．用命令创建存储过程创建存储过程的命令很简单，下面就是标准的创建命令：CREATE{PROC|PROCEDURE}[SCHEMA_name.]procedure_name[{@parameter}data_type[=default][OUTPUT]][,…n][WITH<procedure_option>[,…n]]AS[BEGIN]{<sql_statement>}[END]9.1任务一创建存储过程有关说明：(1)CREATEPROCEDURE是创建存储过程的命令；(2)存储过程名：是为存储过程所起的名字，一般以procedure_或者pr开头，不能超过128个字符；(3)每个存储过程中最多设定1024个参数；(4)@参数名数据类型[VARYING][=内定值][OUTPUT]每个参数名前要有一个“@”符号,每一个存储过程的参数仅为该程序内部使用,参数的类型除了IMAGE外，其他SQLServer所支持的数据类型都可使用。9.1任务一创建存储过程[=内定值]相当于我们在建立数据库时设定一个字段的默认值，这里是为这个参数设定默认值。[OUTPUT]是用来指定该参数是既有输入又有输出值的，也就是在调用了这个存储过程时，如果所指定的参数值是我们需要输入的参数，同时也需要在结果中输出的，则该项必须为OUTPUT，而如果只是做输出参数用，可以用CURSOR，同时在使用该参数时，必须指定VARYING和OUTPUT这两个语句。9.1任务一创建存储过程6．存储过程示例以下示例用的都是数据表Student,第一步要先创建数据表,语句如下:IF

EXISTS

(SELECT

*

FROM

dbo.sysobjects

WHERE

id

=

OBJECT_ID(N'[dbo].[Student]')

AND

type

in

(N'U'))

DROP

TABLE

[dbo].[Student]goCREATE

TABLE

[dbo].[Student](

[ID]

[int]

IDENTITY(1,1)

NOT

NULL,

[Name]

[varchar](50)

NULL,

[phone]

[varchar](50)

NULL,

[QQ]

[varchar](50)

NULL,

[Age]

[varchar](50)

NULL,

[Sex]

[varchar](200)

NULL,

[num1]

[int]

NULL,

[num2]

[int]

NULL,

[num3]

[int]

NULL,

[num4]

[int]

NULL,

[num5]

[int]

NULL,

[total]

[int]

NULL)

ON

[PRIMARY]GO9.1任务一创建存储过程完成数据表的创建,接着要插入数据,通过以下图解操作，向数据表中插入数据。如图9-5，9-6所示图9-5编辑数据表图9-6像数据表中填写数据9.1任务一创建存储过程【操作实例9-1】编写存储过程，查询数据表Student中，年龄大于18岁的学生；IFEXISTS(SELECT*FROMdbo.sysobjectsWHEREid=OBJECT_ID(N'[dbo].[Stu1]')ANDtypein(N'P')) DROPPROCEDURE[dbo].[Stu1]goCREATEPROCEDURE[Stu1]ASSELECT*FROMStudentwhereAge>18GO exec[Stu1]go9.1任务一创建存储过程【执行结果】执行结果如图9-7所示。图9-7操作实例9-1执行结果9.1任务一创建存储过程【操作实例9-2】编写存储过程，统计数据表Student中，学生的总人数；IFEXISTS(SELECT*FROMdbo.sysobjectsWHEREid=OBJECT_ID(N'[dbo].[Stu1]')ANDtypein(N'P')) DROPPROCEDURE[dbo].[Stu1]goCREATEPROCEDURE[Stu1]AS selectcount(Name)as'学生总人数'fromStudentGO exec[Stu1]go【执行结果】执行结果如图9-8所示。图9-8操作实例9-2执行结果9.1任务一创建存储过程【操作实例9-3】编写存储过程，统计数据表Student中，姓“张”的学生总数；IFEXISTS(SELECT*FROMdbo.sysobjectsWHEREid=OBJECT_ID(N'[dbo].[Stu1]')ANDtypein(N'P')) DROPPROCEDURE[dbo].[Stu1]goCREATEPROCEDURE[Stu1]AS selectcount(Name)as'姓张的学生人数'fromStudentwhereNamelike'张%'GO exec[Stu1]go【执行结果】执行结果如图9-9所示。图9-9操作实例9-3执行结果9.1任务一创建存储过程【操作实例9-4】

编写存储过程，查询数据表Student，第3到第4条数据；IFEXISTS(SELECT*FROMdbo.sysobjectsWHEREid=OBJECT_ID(N'[dbo].[Stu1]')ANDtypein(N'P')) DROPPROCEDURE[dbo].[Stu1]goCREATEPROCEDURE[Stu1]AS selecttop2*fromStudentwhereNamenotin(selecttop2NamefromStudent)GO exec[Stu1]go9.1任务一创建存储过程【执行结果】执行结果如图9-10所示。图9-10操作实例9-4执行结果9.1任务一创建存储过程【操作实例9-5】编写存储过程，计算数据表Student，所有学生num5得分加和IFEXISTS(SELECT*FROMdbo.sysobjectsWHEREid=OBJECT_ID(N'[dbo].[Stu1]')ANDtypein(N'P')) DROPPROCEDURE[dbo].[Stu1]goCREATEPROCEDURE[Stu1]AS selectSUM(num5)as'num5总和'fromStudentGO exec[Stu1]go【执行结果】执行结果如图9-11所示。图9-11操作实例9-5执行结果9.1任务一创建存储过程7．系统存储过程示例【操作实例9-6】利用系统存储过程，查看当前数据的所有数据文件属性execsp_helpfile【操作实例9-7】利用系统存储过程，将News表名改为New表。execsp_rename

News,New

【操作实例9-8】利用系统存储过程查看所使用SQLServer信息execsp_server_info【操作实例9-9】利用系统存储过程，查看sa用户的信息execsp_who

sa【操作实例9-10】利用系统存储过程，查看News表title列的所有信息。execNews,@column_name=title9.2任务二创建带参数的存储过程任务名称：创建带参数的存储过程。任务描述：上一个任务的实现，我们发现存储过程的实现非常简单，其功能却无比强大，但这仅是冰山一角，存储过程还可以带参数实现更为强大的功能。本次任务设计一个存储过程，有两个参数，一个输入参数，一个输出参数，该存储过程能够返回XXXX字段中的最大值。9.2任务二创建带参数的存储过程简要分析：

本次任务带了输入、输出两个参数创建存储过程，输入参数将数据值传递到存储过程，输出参数则将数据值传给用户实现步骤：（1）定义存储过程参数；（2）存储过程输入参数；（3）存储过程输出参数。9.2任务二创建带参数的存储过程一．使用SSMS修改视图步骤01：展开要在其中创建存储过程的数据库，在其下有一个“可编程性”中包含“存储过程”，右击【存储过程】图标弹出快捷菜单，单击【新建存储过程】如图9-12所示图9-12新建存储过程9.2任务二创建带参数的存储过程步骤02：

新建存储过程后，可删除一些不相关的信息，由于没有写出要在那个数据库中创建该存储过程，则切记要在下图所示的红色方框中选中该数据库，如图所示。图9-13编写存储过程9.2任务二创建带参数的存储过程步骤03：编写存储过程后，单击【执行】，即是调试存储过程，也是保存存储过程。如图所示。

友情提醒：点击一次执行后，该数据库中即存在了该存储过程，要再次执行时，需把‘create’改为‘alter’。图9-14执行存储过程9.2任务二创建带参数的存储过程IF

EXISTS

(SELECT

*

FROM

dbo.sysobjects

WHERE

id

=

OBJECT_ID(N'[dbo].[Test1]')

AND

type

in

(N'P'))

DROP

PROCEDURE

[dbo].[Test1]goCREATE

PROCEDURE

[dbo].[Test1](@sql

varchar(5000))AS

declare

@length

int,@tag

varchar(20),@Tlength

int

set

@tag

=','

set

@Tlength

=LEN(@sql)

set

@length=charindex(@tag,@sql)

while(@Tlength

>0)

begin

if(@Tlength

=0)

begin

break;

end

print

left(@sql,@length-1)

set

@sql=SUBSTRING

(@sql,@length+1,@Tlength

)

set

@Tlength

=LEN(@sql

)

endGOexec

[Test1]

'齐,齐,哈,尔,信,息,工,程,学,校,'GO【操作实例9-11】编写存储过程，截取以标点符号分隔的字符串9.2任务二创建带参数的存储过程【执行结果】如图9-15所示。图9-15操作实例9-11运行结果9.2任务二创建带参数的存储过程【操作实例9-12】编写存储过程，IFEXISTS(SELECT*FROMdbo.sysobjectsWHEREid=OBJECT_ID(N'[dbo].[Test2]')ANDtypein(N'P')) DROPPROCEDURE[dbo].[Test2]goCREATEPROCEDURE[dbo].[Test2](@sqlvarchar(5000))AS Declare@avarchar(50),@bvarchar(50),@dvarchar(50) set@a='信息工程' set@b='学校' set@d=@sql+@a+@b print@dGOexec[Test2]'齐齐哈尔'GO9.2任务二创建带参数的存储过程【执行结果】如图9-16所示。图9-16操作实例9-12运行结果9.2任务二创建带参数的存储过程【操作实例9-13】编写存储过程，IF

EXISTS

(SELECT

*

FROM

dbo.sysobjects

WHERE

id

=

OBJECT_ID(N'[dbo].[Test3]')

AND

type

in

(N'P'))

DROP

PROCEDURE

[dbo].[Test3]goCREATE

PROCEDURE

[dbo].[Test3](@sql

varchar(5000))AS

declare

@b

varchar(100),@c

varchar(100),@d

varchar(100)

set

@b=SUBSTRING(@sql,3,3)

set

@c=LEFT(@sql,2)

while(@b<11)

begin

set

@d=@c+@b

set

@b=@b+1

print

@d

endGOexec

[Test3]

'QX1'GO

9.2任务二创建带参数的存储过程【执行结果】如图9-17所示。图9-17操作实例9-13运行结果9.2任务二创建带参数的存储过程【操作实例9-14】编写关系到数据表的存储过程，实现“QX1,QX3,QX9,QX6,QX8,QX2,QX5,QX7,QX4”排序；If

not

object_id('[tb]')

is

null

Drop

table

[tb]GoCreate

table

[tb]([col]

nvarchar(17))Insert

tb

Select

'QX1'

union

allSelect

'QX3'

union

allSelect

'QX9'

union

allselect

'QX6'

union

allSelect

'QX8'

union

allselect

'QX2'

union

allSelect

'QX5'

union

allSelect

'QX7'

union

allselect

'QX4'Go--Select*fromtb-->SQL查询如下:select

*

from

tb

order

by

left(col,patindex('%[0-9]%',col)-1),

right(col,len(col)-len(left(col,patindex('%[0-9]%',col)-1)))*19.2任务二创建带参数的存储过程【执行结果】如图9-18所示。图9-18操作实例9-14运行结果9.2任务二创建带参数的存储过程【操作实例9-15】编写关系到数据表的存储过程，创建数据表--测试表IF

EXISTS

(SELECT

*

FROM

dbo.sysobjects

WHERE

id

=

OBJECT_ID(N'[dbo].[TestInfo]')

AND

type

in

(N'U'))

DROP

TABLE

[dbo].[TestInfo]goCREATE

TABLE

[dbo].[TestInfo](

[ID]

[int]

IDENTITY(1,1)

NOT

NULL,

[Name]

[varchar](50)

NULL,

[Mobile]

[varchar](20)

NULL,

[Email]

[varchar](50)

NULL,

[QQ]

[varchar](50)

NULL,

[CmpName]

[varchar](50)

NULL,

[Workplace]

[varchar](200)

NULL,

[PostCode]

[varchar](20)

NULL,

[OfficeTel]

[varchar](20)

NULL,

[OfficeFax]

[varchar](20)

NULL,

[CmpUrl]

[varchar](50)

NULL,

[Remark]

[varchar](200)

NULL)

ON

[PRIMARY]GO9.2任务二创建带参数的存储过程【操作实例9-16】编写存储过程，向数据表中插入数据，并查看插入的ID值；@OfficeFax,

@CmpUrl,

@Remark)IF

EXISTS

(SELECT

*

FROM

dbo.sysobjects

WHERE

id

=

OBJECT_ID(N'[dbo].[TestInfoAdd]')

AND

type

in

(N'P'))

DROP

PROCEDURE

[dbo].[TestInfoAdd]gocreate

PROCEDURE

[dbo].[TestInfoAdd]

@ID

int,

@Name

varchar(50),

@Mobile

varchar(20),

@Email

varchar(50),

@QQ

varchar(50),

@CmpName

varchar(50),

@Workplace

varchar(200),

@PostCode

varchar(20),

@OfficeTel

varchar(20),

@OfficeFax

varchar(20),

@CmpUrl

varchar(50),

@Remark

varchar(200)AS

if

@ID

=0

begin

INSERT

INTO

TestInfo(Name,

Mobile,

Email,

QQ,

CmpName,

Workplace,

PostCode,

OfficeTel,

OfficeFax,

CmpUrl,

Remark)

VALUES(@Name,

@Mobile,

@Email,

@QQ,

@CmpName,

@Workplace,

@PostCode,

@OfficeTel,

9.2任务二创建带参数的存储过程if

@@error<>0

select

@ID=-1

else

select

@ID=SCOPE_IDENTITY()

end

else

begin

UPDATE

TestInfo

SET

Name=@Name,

Mobile=@Mobile,

Email=@Email,

QQ=@QQ,

CmpName=@CmpName,

Workplace=@Workplace,

PostCode=@PostCode,

OfficeTel=@OfficeTel,

OfficeFax=@OfficeFax,

CmpUrl=@CmpUrl,

Remark=@Remark

WHERE

ID

=

@ID

if

@@error<>0

select

@ID=-1

end

select

@IDGO

exec

[TestInfoAdd]

0,'张三',,139.com','56470000','齐齐哈尔XXX','黑龙江省','157000','000-888888','000-888889','','20120104添加'GO9.2任务二创建带参数的存储过程【操作实例9-17】编写存储过程，查询ID=1的人的姓名；IF

EXISTS

(SELECT

*

FROM

dbo.sysobjects

WHERE

id

=

OBJECT_ID(N'[dbo].[QueryStuNameById]')

AND

type

in

(N'P'))

DROP

PROCEDURE

[dbo].[QueryStuNameById]gocreate

procedure

[dbo].[QueryStuNameById](@ID

int,--输入参数@Name

varchar(50)

output

--输出参数)as

select

@Name=Name

from

TestInfo

where

ID=@IDGOdeclare

@Name

varchar(50)exec

[QueryStuNameById]

1,@Name

OUTPUTprint

@NameGO9.2任务二创建带参数的存储过程【操作实例9-18】编写存储过程倒序查询数据表中前10个ID值IF

EXISTS

(SELECT

*

FROM

dbo.sysobjects

WHERE

id

=

OBJECT_ID(N'[dbo].[QueryStuName]')

AND

type

in

(N'P'))

DROP

PROCEDURE

[dbo].[QueryStuName]goCREATE

PROC

[dbo].[QueryStuName]

@num

varchar(10)asbegin

declare

@str

varchar(200)

set

@str='selecttop'+@num+'IDfromTestInfoorderbyIDdesc'

exec(@str)endGOdeclare

@num

varchar(10)exec

[QueryStuName]

'10'GO9.2任务二创建带参数的存储过程【操作实例9-19】编写存储过程,查询数据表中所有数据；IF

EXISTS

(SELECT

*

FROM

dbo.sysobjects

WHERE

id

=

OBJECT_ID(N'[dbo].[GetRowCount]')

AND

type

in

(N'P'))

DROP

PROCEDURE

[dbo].[GetRowCount]goCREATE

PROCEDURE

[dbo].[GetRowCount](@RowCount

INT

OUTPUT)AS

SELECT

[ID]

,[Name]

,[Mobile]

,[Email]

,[QQ]

,[CmpName]

,[Workplace]

,[PostCode]

,[OfficeTel]

,[OfficeFax]

,[CmpUrl]

,[Remark]FROM

TestInfoSET

@RowCount=@@rowcountGODECLARE

@count

INTEXECUTE

[GetRowCount]

@count

OUTPUTPRINT

@countGO9.3任务三管理存储过程任务名称：对存储过程进行查看属性、修改、删除、更名。任务描述：创建了存储过程，接下来就要管理存储过程了，存储过程的管理包括查看存储过程的信息、修改存储过程、删除存储过程。这一任务细化开来就是三个子任务，本任务将这三个功能一并进行讲解。9.3任务三管理存储过程简要分析：

本次任务以本章任务一所讲授的任务为蓝本，对其进行了查看存储过程的信息、修改存储过程、删除存储过程这三个操作，这三个操作组成了管理存储过程的全部工作。9.3任务三管理存储过程一．图形方式创建存储过程步骤01：查看、修改存储过程。展开要在其中创建存储过程的数据库，在其下有一个“可编程性”中包含“存储过程”，右击【存储过程】图标弹出快捷菜单，单击【修改】即可查看或修改存储过程定义，如图9-19所示。图9-19修改存储过程9.3任务三管理存储过程步骤02：删除储过程。展开要在其中创建存储过程的数据库，在其下有一个“可编程性”中包含“存储过程”，右击【存储过程】图标弹出快捷菜单，单击【删除】即可查看或修改存储过程定义，如图9-20所示。图9-20删除存储过程9.3任务三管理存储过程步骤03：给存储过程改名。（1）在“对象资源管理器”中给存储过程改名。图9-21重命名存储过程9.3任务三管理存储过程1．用命令方式删除存储过程

使用dropprocedure可以删除存储过程，其基本语句格式如下：DROPPROCEDURE存储过程名删除存储过程见图9-22图9-22用命令方式删除存储过程9.3任务三管理存储过程2．用命令方式修改存储过程

使用ALTERPROCEDURE语句可以更改先前通过执行CREATEPROCEDURE语句创建的过程。ALTERPROCEDURE基本语句格式如下：ALTERPROCEDURE存储过程名[{@参数1数据类型}[=默认值][OUTPUT]，……，{@参数n数据类型}[=默认值][OUTPUT]]ASSQL语句……各参数含义与CREATEPROCEDURE语句相同。修改存储过程见图9-23所示9.3任务三管理存储过程图9-23用命令方式修改存储过程9.3任务三管理存储过程友情提醒：存储过程中偶尔我们都会遇到需要同时操作多个表的情况，需要注意的是，不能在事务中使用return语句强行退出。这样会引发事务的非正常错误，不能保证数据的一致性。

9.3任务三管理存储过程3．用命令方式执行存储过程使用EXEC语句可以执。行存储过程语法格式为:EXEC存储过程名3．用命令方式给存储过程改名使用系统存储过程sp_rename可以给存储过程改名，语法格式为:execsp_rename

原存储过程名,现存储过程名9.3任务三管理存储过程4．操作实例【操作实例9-20】创建StudentAddup存储过程CREATE

PROCEDURE

[dbo].[StudentAddup]

@ID

INT,

@num

int,

@name

varchar(50),

@class

varchar(50),

@age

int,

@phone

int,

@home

varchar(200)ASif

@ID

=0BEGIN

INSERT

INTO

Student(num,

name,

class,

age,

phone,

home)

values(@num,

@name,

@class,

@age,

@phone,

@home)endelse

begin

UPDATE

Student

SET

num=@num

,

name=@name,

class=@class

,

age=@age,

phone=@phone,

home=@home

WHERE

ID

=

@ID

end

select

@IDGO9.3任务三管理存储过程【操作实例9-21】如果存在StudentAddUp存储过程,则删除。IFEXISTS(SELECT*FROMdbo.sysobjectsWHEREid=OBJECT_ID(N'[dbo].[StudentAddup]')ANDtypein(N'P')) DROPPROCEDURE[dbo].[StudentAddup]Go9.3任务三管理存储过程【操作实例9-22】修改StudentAddup存储过程alter

PROCEDURE

[dbo].[StudentAddup]

@ID

INT,

@num

int,

@name

varchar(50),

@class

varchar(50),

@age

intASif

@ID

=0BEGIN

INSERT

INTO

Student(num,

name,

class,

age)

values(@num,

@name,

@class,

@age)endelse

begin

UPDATE

Student

SET

num=@num

,

name=@name,

class=@class

,

age=@age

WHERE

ID

=

@ID

end

select

@IDGO9.3任务三管理存储过程【操作实例9-23】将存储过程StudentAddup更名为teacherAddUpExecsp_rename

StudentAddUp,teacherAddUp【操作实例9-24】执行存储过程ExecStudentAddUp9.4MTA微软MTA认证考试指南官方样题

在完成为他的母亲创建CD收藏数据库之后，Yan意识到此类型的结构可以用于很多其他库存数据库。他发现，在预定义的SQL函数中提供了一些常见功能。通过利用这些内置的现成函数，他可以提高工作效率，并花时间创建任何其他需要的用户定义函数。Yan还学会了区分聚集函数和标量函数。9.4MTA微软MTA认证考试指南1.在CD收藏数据库中，Yan可以使用什么聚合函数来计算CD总数？a.SUM(列名称)b.COUNT(列名称)c.AVG(列名称)2.Yan对标量函数的工作原理不是很明确。下面哪个是标量函数？a.FIRST(列名称)返回指定列中的第一个字段b.SUM(列名称)返回列中所有值的总计c.UCASE(列名称)返回所有大写字母字段的值9.4MTA微软MTA认证考试指南3.如何调用存储过程？a.RUN(过程名称,输入值)b.EXECUTE(过程名称,输入值)c.PERFORM(过程名称,输入值)9.4MTA微软MTA认证考试指南本章介绍了如何创建、使用以及管理SQLServer中的存储过程，在SQLServer2008中可以使用图形方式和命令方式创建存储过程。本章的重点是如何创建、删除、管理存储过程以及创建带有参数的存储过程等。对于初学者而言，存储过程是难点，只要平时多加练习，就可以很快的掌握存储过程的知