SQL存储过程学习总结.docx

SQL存储过程学习总结.docx

《SQL存储过程学习总结.docx》由会员分享，可在线阅读，更多相关《SQL存储过程学习总结.docx（44页珍藏版）》请在冰点文库上搜索。

SQL存储过程学习总结

select*from成绩表

select*from学生信息表

1存储过程的定义

存储过程（procedure）类似于C语言中的函数

用来执行管理任务或应用复杂的业务规则

存储过程可以带参数，也可以返回结果

存储过程可以包含数据操纵语句、变量、逻辑控制语句等

2存储过程的优点

执行速度更快

存储过程只在创造时进行编译即可，以后每次执行存储过程都不需再重新编译，而我们通常使用的SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

允许模块化程序设计

当对数据库进行复杂操作时（如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

可以极大的提高数据库的使用效率，减少程序的执行时间，这一点在较大数据量的数据库的操作中是非常重要的。

提高系统安全性

可设定只有某此用户才具有对指定存储过程的使用权存储过程

减少网络流通量

3、存储过程的分类

3.1系统存储过程

由系统定义，存放在master数据库中

系统存储过程的名称都以“sp_”开头或”xp_”开头

3.2用户自定义存储过程

由用户在自己的数据库中创建的存储过程

4、常用的系统存储过程

4.1系统存储过程列表

系统存储过程

说明

sp_databases

列出服务器上的所有数据库。

sp_helpdb

报告有关指定数据库或所有数据库的信息

sp_renamedb

更改数据库的名称

sp_tables

返回当前环境下可查询的对象的列表

sp_columns

回某个表列的信息

sp_help

查看某个表的所有信息

sp_helpconstraint

查看某个表的约束

sp_helpindex

查看某个表的索引

sp_stored_procedures

列出当前环境中的所有存储过程。

sp_password

添加或修改登录帐户的密码。

sp_helptext

显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。

4.2调用常用的系统存储过程

EXECsp_databases--列出当前系统中的数据库

EXECsp_renamedb'Northwind','Northwind1'--修改数据库的名称（单用户访问）

USEstuDB--当前数据库中查询的对象的列表

GO

EXECsp_tables--返回某个表列的信息

EXECsp_columnsstuInfo--返回某个表列的信息

EXECsp_helpstuInfo--查看表stuInfo的信息

EXECsp_helpconstraintstuInfo--查看表stuInfo的约束

EXECsp_helpindexstuMarks--查看表stuMarks的索引

EXECsp_helptext'view_stuInfo_stuMarks'--查看视图的语句文本

EXECsp_stored_procedures--查看当前数据库中的存储过程

4.3调用常用的扩展存储过程：

xp_cmdshell

可以执行DOS命令下的一些的操作,以文本行方式返回任何输出

调用语法：

EXECxp_cmdshellDOS命令[NO_OUTPUT]

【案例分析】创建数据库bankDB，要求保存在D:

\bank

USEmaster

GO

EXECxp_cmdshell'mkdird:

\bank',no_output--创建文件夹D:

\bank

IFEXISTS（SELECT*FROMsysdatabasesWHEREname='bankDB'）

DROPDATABASEbankDB

GO

CREATEDATABASEbankDB

（

…

）

GO--查看文件夹D:

\bank

EXECxp_cmdshell'dirD:

\bank\'--查看文件

5、创建存储过程

5.1定义存储过程的语法

createproc[edure]存储过程名

@参数数据类型=默认值output,

……,

@参数n数据类型=默认值output

AS

SQL语句

GO

参数可选

参数分为输入参数、输出参数

输入参数允许有默认值

5.2创建不带参数的存储过程

【问题】

请创建存储过程，查看本次考试平均分以及未通过考试的学员名单

说明：

笔试和机试都通过了60分才算通过。

--创建不带参数的存储过程

createprocedureproc_stu

AS

declare@avgwrifloat

declare@avglabfloat

select@avgwri=avg（笔试成绩）,@avglab=avg（上机成绩）from成绩表--笔试平均分和机试平均分变量

print'笔试成绩分数='+convert（varchar（10）,@avgwri）

print'上机成绩分数='+convert（varchar（10）,@avglab）

if@avgwri>70and@avglab>70--显示考试成绩的等级

print'本班成绩：

优秀'

else

print'本班成绩：

较差'--显示未通过的学员

print'---------------------------'

print'参加考试不及格的学生'

selecta.学生姓名,a.学号,b.笔试成绩,b.上机成绩from学生信息表asa

innerjoin成绩表asbona.学号=b.学号whereb.笔试成绩<60orb.上机成绩<60

GO

5.3调用存储过程

execute（执行）语句用来调用存储过程

调用的语法：

EXEC过程名[参数]

EXECproc_stu

5.4带参数的存储过程

存储过程的参数分两种：

输入参数、输出参数

输入参数：

用于向存储过程传入值，类似C语言的按值传递;

输出参数：

用于在调用存储过程后，返回结果，类似C语言的按引用传递;

【问题】

修改上例：

由于每次考试的难易程度不一样，每次笔试和机试的及格线可能随时变化（不再是分），这导致考试的评判结果也相应变化。

说明：

根据试卷的难度，我们希望笔试和机试的及格线应该是随时变化的，而不是固定的60分。

【分析】

在述存储过程添加个输入参数：

@writtenPass笔试及格线

@labPass机试及格线

--带输入参数的存储过程

createprocedureproc_stu2

@writtenPassint,--输入参数：

笔试及格线

@labPassint--输入参数：

机试及格线

AS

print'========================='

print'参加考试不及格的学生'

selecta.学生姓名,a.学号,b.笔试成绩,b.上机成绩from学生信息表asa--查询没有通过考试的学员

innerjoin成绩表asbona.学号=b.学号

whereb.笔试成绩<@writtenpassorb.上机成绩<@labpass

GO

调用带参数的存储过程

--假定本次考试机试偏难，机试的及格线定为分，笔试及格线定为分

--机试及格线降分后，李斯文（分）成为“漏网之鱼”了

execproc_stu260,55

--或这样调用：

EXECproc_stu2@labPass=55,@writtenPass=60

5.5带输入参数的默认值

问题：

如果试卷的难易程度合适，则调用者还是必须如此调用：

EXECproc_stu260,60，比较麻烦

这样调用就比较合理：

EXECproc_stu255--笔试及格线分，机试及格线默认为分

EXECproc_stu2--笔试和机试及格线都默认为标准的分

createprocedureproc_stu3

@writtenPassint=60,--笔试及格线：

默认为分

@labPassint=60--机试及格线：

默认为分

AS

print'========================='

print'参加本次考试没有通过的学员：

'

selecta.学生姓名,a.学号,b.笔试成绩,b.上机成绩from学生信息表asa--查询没有通过考试的学员

innerjoin成绩表asbONa.学号=b.学号

WHERE笔试成绩<@writtenPassOR上机成绩<@labPass

GO

调用带参数默认值的存储过程

EXECproc_stu--都采用默认值

EXECproc_stu64--机试采用默认值

EXECproc_stu60,55--都不采用默认值

--错误的调用方式：

希望笔试采用默认值，机试及格线分

EXECproc_stu,55

--正确的调用方式：

EXECproc_stu@labPass=55

5.6带输出参数的存储过程

如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出（OUTPUT）参数了

问题：

修改上例，返回未通过考试的学员人数。

CREATEPROCEDUREproc_stu4

@notpassSumintOUTPUT,--输出（返回）参数：

表示没有通过的人数

@writtenPassint=60,

@labPassint=60

AS

……--推荐将默认参数放后

selecta.学生姓名,a.学号,b.笔试成绩,b.上机成绩from学生信息表asa--统计并返回没有通过考试的学员人数

innerjoin成绩表asbona.学号=b.学号whereb.笔试成绩<@writtenpassorb.上机成绩<@labpass

select@notpassnum=count（学号）from成绩表where笔试成绩<@writtenpassor上机成绩<@labpass

GO

强调：

1.默认值放在参数的数据类型后面，而不是放在参数变量的后面。

2.为了调用的方便，推荐将默认参数放置在参数列表的最后

调用带输出参数的存储过程

/*---调用存储过程----*/

DECLARE@sumint

EXECproc_stu@sumOUTPUT,64--调用时必须带OUTPUT关键字，返回结果将存放在变量@sum中

print'================================'

IF@sum>=3--后续语句引用返回结果

print'未通过人数：

'+convert（varchar（5）,@sum）+'人,超过%,及格分数线还应下调'

ELSE

print'未通过人数：

'+convert（varchar（5）,@sum）+'人,已控制在%以下，及格分数线适中'

GO

强调：

1.调用时也必须跟随关键字OUTPUT，否则SQLServer将视为输入参数。

5.7处理存储过程中的错误

可以使用print语句显示错误信息，但这些信息是临时的，只能显示给用户

raiserror显示用户定义的错误信息时

可指定严重级别，

设置系统变量@@ERROR

记录所发生的错误等

5.7.1使用raiserror语句

raiserror语句的用法如下：

raiserror（msg_id|msg_str,severity,stateWITHoption[,...n]]）

msg_id：

在sysmessages系统表中指定用户定义错误信息

msg_str：

用户定义的特定信息，最长255个字符

severity：

定义严重性级别。

用户可使用的级别为0–18级

state：

表示错误的状态，1至127之间的值

option：

指示是否将错误记录到服务器错误日志中

问题：

完善上例，当用户调用存储过程时，传入的及格线参数不

在～之间时，将弹出错误警告，终止存储过程的执行。

说明：

笔试和机试都通过了60分才算通过。

CREATEPROCEDUREproc_stu5

@notpassSumintOUTPUT,--输出参数

@writtenPassint=60,--默认参数放后

@labPassint=60--默认参数放后

AS--错误处理

IF（NOT@writtenPassBETWEEN0AND100）

OR（NOT@labPassBETWEEN0AND100）

BEGIN

raiserror（'及格线错误，请指定－之间的分数，统计中断退出',16,1）

RETURN---立即返回，退出存储过程

END

…..其他语句同上例，略

GO

--引发系统错误，指定错误的严重级别，调用状态为（默认）,并影响@@ERROR系统变量的值

/*---调用存储过程，测试RAISERROR语句----*/

DECLARE@sumint,@tint

EXECproc_stu@sumOUTPUT,604----笔试及格线误输入分

SET@t=@@ERROR

print'错误号：

'+convert（varchar（5）,@t）

IF@t<>0--不等于0

RETURN--退出批处理，后续语句不再执行

print'===================================='

IF@sum>=3--如果执行了RAISERROR语句，系统全局@@ERROR将不等于，表示出现了错误

print'未通过人数：

'+convert（varchar（5）,@sum）+'人,超过%,及格分数线还应下调'

ELSE

print'未通过人数：

'+convert（varchar（5）,@sum）+'人,已控制在%以下，及格分数线适中'

GO

6用户自定义函数

在SQLServer中，用户不仅可以使用标准的内置函数，也可以使用自己定义的函数来实现一些特殊的功能。

用户自定义函数可以在企业管理器中创建，也可以使用createfunction语句创建。

在创建时需要注意：

函数名在数据库中必须唯一，其可以有参数，也可以没有参数，其参数只能是输入参数，最多可以有1024参数。

标量函数：

返回单个数据值。

表值函数：

返回值是一个记录集合——表。

在此函数中，return语句包含一条单独的select语句。

多语句表值函数：

返回值是由选择的结果构成的记录集。

6.1使用createfunction语句创建用户自定义函数

使用createfunction创建用户自定义函数，其语法格式如下：

createfunction[owner_name.]function_name

（[{@parameter_name[AS]scalar_parameter_data_type[=default]}[,...n]]）

returnsscalar_return_data_type

[as]

begin

function_body

retunrnscalar_expression

end

●function_name：

指用户自定义函数的名称。

其名称必须符合标识符的命名规则，并且对其所有者来说，该名称在数据库中必须唯一。

●@parameter_name：

用户自定义函数的参数，其可以是一个或多个。

每个函数的参数仅用于该函数本身；相同的参数名称可以用在其它函数中。

参数只能代替常量；而不能用于代替表名、列名或其它数据库对象的名称。

函数执行时每个已声明参数的值必须由用户指定，除非该参数的默认值已经定义。

如果函数的参数有默认值，在调用该函数时必须指定"default"关键字才能获得默认值。

●scalar_parameter_data_type：

参数的数据类型。

●scalar_return_data_type：

是用户定义函数的返回值。

可以是SQLServer支持的任何标量数据类型（text、ntext、image和timestamp除外）。

●function_body：

位于begin和end之间的一系列Transact-SQL语句，其只用于标量函数和多语句表值函数。

●scalar_expression：

用户自定义函数中返回值的表达式。

6.2标量函数

例：

在stuDB库中创建一个用户自定义标量值函数xuefen，该函数通过输入成绩来判断是否取得学分，当成绩大于等于60时，返回取得学分，否则，返回未取得学分。

代码如下：

USEstuDB

GO

CREATEFUNCTIONxuefen（@chengjiint）RETURNSnvarchar（10）

BEGIN

declare@returnsxuefennvarchar（10）

if@chengji>60

set@returnsxuefen='取得学分'

else

set@returnsxuefen='不能取得学分'

RETURN@returnsxuefen

END

GO

使用刚才定义的xuefen函数来查看课程号为“”的课程，学生获得学分的情况。

在查询编辑器中输入如下代码：

USEstuDB

GO

SELECT学号,成绩=（笔试成绩+上机成绩）/2,dbo.xuefen（（笔试成绩+上机成绩）/2）AS学分情况

FROM成绩表

WHERE课程号=‘'

GO

6.3表值函数

表值函数遵循的原则：

RETURNS子句仅包含关键字table。

不必定义返回变量的格式，因为它由RETURN子句中的SELECT语句的结果集的格式设置。

FUNCTIONBODY不由BEGIN和END分隔。

RETURN子句在括号中包含单个SELECT语句。

SELECT语句的结果集构成函数所返回的表。

例：

在stuDB库中创建一个内嵌表值函数XUESHENG，该函数可以根据输入的系部代码返回该系学生的基本信息。

其代码如下：

CREATEFUNCTIONXUESHENG（@inputdepnvarchar（4））RETURNStable

AS

RETURN

（SELECT学号,姓名FROM学生WHERE所属院系=@inputdep）

GO

建立好该内嵌表值函数后，就可以象使用表或视图一样来使用它：

SELECT*FROMDBO.XUESHENG（‘'）

GO

6.4多语句表值函数

多语句函数的主体中允许使用以下语句：

赋值语句

DECLARE语句，该语句定义函数局部的数据变量和游标。

SELECT语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量。

游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。

只允许使用以INTO子句向局部变量赋值的FETCH语句；不允许使用将数据返回到客户端的FETCH语句。

INSERT、UPDATE和DELETE语句，这些语句修改函数的局部table变量。

EXECUTE语句调用扩展存储过程。

6.5多语句表值函数

案例：

在stuDB库中创建一个多语句表值函数chengji，该函数可以根据输入的课程名称返回选修该课程的学生姓名和成绩。

代码如下：

USEstuDB

GO

CREATEFUNCTIONchengji（@inputkmaschar（20））

/*为chengji函数定义的表结构，名称变量为@cji*/

RETURNS@cjTABLE

（科目编号varchar（10）,

姓名varchar（10）,

成绩int）

AS

BEGIN

INSERT@cj/*该变量是上面定义的表名称变量*/

selectb.科目编号,a.学生姓名,（b.笔试成绩+b.上机成绩）/2

from学生信息表asainnerjoin成绩表asb

ona.学号=b.学号whereb.科目编号=@inputkm

RETURN

END

GO

在查询编辑器中输入以下查询命令：

SELECT*FROMdbo.chengji（‘9001’）

6.6查看、修改和删除自定义函数

1.查看用户自定义函数的属性

在SQLServer中，根据不同需要，可以使用sp_helptext、sp_help等系统存储过程来查看用户自定义函数的不同信息。

每个系统存储过程的具体作用和语法如下：

使用sp_helptext查看用户定义函数的文本信息，其语法格式为：

sp_helptext用户自定义函数名

使用sp_help查看用户自定义函数的一般信息，其语法格式为：

sp_help用户自定义函数名

例：

使用有关系统过程查看STUDENT数据库中名为XUEFEN的用户自定义函数的文本信息。

其程序代码如下：

USESTUDB

GO

SP_HELPTEXTXUEFEN

GO

2.修改用户自定义函数的属性

使用SQL命令修改用户自定义函数，使用ALTERFUNCTION命令可以修改用户自定义函数。

修改由CREATEFUNCTION语句创建的现有用户定义函数，不会更改权限，也不影响相关的函数、存储过程或触发器。

其语法格式如下：

ALTERFUNCTION[owner_name.]function_name

（[{@parameter_name[AS]scalar_parameter_data_type[=default]}[,...n]]）

RETURNSscalar_return_data_type

[AS]

BEGIN

function_body

RETURNscalar_expression

END

其中的参数与建立用户自定义函数中的参数意义相同。

3.使用T-SQL命令删除用户自定义函数

使用DROP命令可以一次删除多个用户自定义函数，其语法格式为：

DROPFUNCTION[所有者名称.]函数名称[,…n]

案例：

删除在student库上建立的xuefen函数。

代码如下：

USEstudent

GO

DROPFUNCTIONdbo.xuefen

GO

总结

存储过程是一组预编译的SQL语句，它可以包含数据操纵语句、变量、逻辑控制语句等

存储过程允许带参数，参数分为：

输入参数

输出参数

其中，输入参数可以有默认值。

•输入参数：

可以在调用时向存储过程传递参数，此类参数可用来向存储过程中传入值

•输出参数从存储过程中返回（输出）值，后面跟随OUTPUT关键字

•RAISERROR语句用来向用户报告错误

•用户自定义函数：

标量函数、表值函数、多语句表值函数