SQL_存储过程学习总结

1 存储过程的定义

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

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

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

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

2 存储过程的优点

执行速度更快

存储过程只在创造时进行编译即可，以后每次执行存储过程都不需再重

新编译，而我们通常使用的SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

允许模块化程序设计

当对数据库进行复杂操作时(如对多个表进行

Update,Insert,Query,Delete 时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。可以极大的提高数据 库的使用效率，减少程序的执行时间，这一点在较大数据量的数据库的操作中是非常重要的。 提高系统安全性

可设定只有某此用户才具有对指定存储过程的使用权存储过程减少网络流通量

3、存储过程的分类

3.1 系统存储过程

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

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

3.2 用户自定义存储过程

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

4、常用的系统存储过程

4.1 系统存储过程列表

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

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

EXEC sp_renamedb'Northwind','Northwind1'--修改数据库的名称(单用户访问)

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

GO

EXEC sp_tables--返回某个表列的信息

EXEC sp_columns stuInfo --返回某个表列的信息

EXEC sp_help stuInfo --查看表stuInfo的信息

EXEC sp_helpconstraint stuInfo --查看表stuInfo的约束

EXEC sp_helpindex stuMarks --查看表stuMarks的索引

EXEC sp_helptext'view_stuInfo_stuMarks'--查看视图的语句文本EXEC sp_stored_procedures--查看当前数据库中的存储过程

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

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

调用语法：

EXEC xp_cmdshell DOS命令[NO_OUTPUT]

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

USE master

GO

EXEC xp_cmdshell'mkdir d:\bank',no_output --创建文件夹

D:\bank

IF EXISTS(SELECT*FROM sysdatabases WHEREname='bankDB') DROPDATABASE bankDB

GO

CREATEDATABASE bankDB

(

…

)

GO--查看文件夹D:\bank

EXEC xp_cmdshell'dir D:\bank\'--查看文件

5、创建存储过程

5.1 定义存储过程的语法

createproc[edure] 存储过程名

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

……,

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

AS

SQL语句

GO

参数可选

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

输入参数允许有默认值

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

【问题】

请创建存储过程，查看本次考试平均分以及未通过考试的学员名单说明：笔试和机试都通过了60分才算通过。

5.3 调用存储过程

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

调用的语法：

EXEC 过程名 [参数]

5.4

带参数的存储过程

存储过程的参数分两种：输入参数、输出参数

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

输出参数：用于在调用存储过程后，返回结果，类似C 语言的按引

用传递;

【问题】

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

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

【分析】

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

@writtenPass 笔试及格线

@labPass 机试及格线

调用带参数的存储过程

--假定本次考试机试偏难，机试的及格线定为分，笔试及格线定为分--机试及格线降分后，李斯文（分）成为“漏网之鱼”了

5.5 带输入参数的默认值

问题：

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

EXEC proc_stu2 60,60，比较麻烦

这样调用就比较合理：

EXEC proc_stu2 55 --笔试及格线分，机试及格线默认为分EXEC proc_stu2 --笔试和机试及格线都默认为标准的分

5.6 带输出参数的存储过程

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

问题：

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

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

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

5.7处理存储过程中的错误

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

户

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

可指定严重级别，

设置系统变量@@ERROR

记录所发生的错误等

5.7.1 使用raiserror语句

raiserror语句的用法如下：

raiserror(msg_id |msg_str,severity,state WITHoption[,...n]])

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

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

severity：定义严重性级别。用户可使用的级别为0–18级

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

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

问题：

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

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

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

6用户自定义函数

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

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

创建。在创建时需要注意：函数名在数据库中必须唯一，其可以有参数，也

可以没有参数，其参数只能是输入参数，最多可以有1024参数。

标量函数：返回单个数据值。

表值函数：返回值是一个记录集合——表。在此函数中，return语句包含

一条单独的select语句。

多语句表值函数：返回值是由选择的结果构成的记录集。

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

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

createfunction [ owner_name.] function_name

( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )

returns scalar_return_data_type

[ as ]

begin

function_body

retunrn scalar_expression

end

●function_name：指用户自定义函数的名称。其名称必须符合标识符的命

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

●@parameter_name：用户自定义函数的参数，其可以是一个或多个。每

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

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

函数执行时每个已声明参数的值必须由用户指定，除非该参数的默认值已经定义。如果函数的参数有默认值，在调用该函数时必须指定"default"关键字才能获得默认值。

●scalar_parameter_data_type：参数的数据类型。

●scalar_return_data_type：是用户定义函数的返回值。可以是SQL Server 支

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

●function_body：位于begin和end之间的一系列Transact-SQL 语句，其只

用于标量函数和多语句表值函数。

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

6.2 标量函数

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

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

6.3 表值函数

表值函数遵循的原则：

RETURNS子句仅包含关键字table。不必定义返回变量的格式，因为它由

RETURN 子句中的SELECT 语句的结果集的格式设置。

FUNCTION BODY 不由BEGIN和END分隔。

RETURN子句在括号中包含单个SELECT语句。SELECT语句的结果集构成函

数所返回的表。

例：在stuDB库中创建一个内嵌表值函数XUESHENG，该函数可以根据输入

6.4 多语句表值函数

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

赋值语句

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

SELECT 语句，该语句包含带有表达式的选择列表，其中的表达式将值赋

予函数的局部变量。

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

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

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

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

6.5 多语句表值函数

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

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

SELECT * FROM dbo.chengji(‘9001’)

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

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

在SQL Server中，根据不同需要，可以使用sp_helptext、sp_help等系统

存储过程来查看用户自定义函数的不同信息。每个系统存储过程的具体作用和语法如下：

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

sp_helptext用户自定义函数名

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

sp_help用户自定义函数名

例：使用有关系统过程查看STUDENT数据库中名为XUEFEN的用户自定义函数的文本信息。其程序代码如下：

USE STUDB

GO

SP_HELPTEXT XUEFEN

GO

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

使用SQL命令修改用户自定义函数，使用ALTERFUNCTION命令可以修改用户自定义函数。修改由CREATEFUNCTION语句创建的现有用户定义函数，不会更改权限，也不影响相关的函数、存储过程或触发器。其语法格式如下：

ALTERFUNCTION [ owner_name.] function_name

( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )

RETURNS scalar_return_data_type

[ AS ]

BEGIN

function_body

RETURN scalar_expression

END

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

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

使用DROP命令可以一次删除多个用户自定义函数，其语法格式为：DROPFUNCTION [所有者名称.]函数名称[,…n]

案例：删除在student库上建立的xuefen函数。代码如下：

USE student

GO

DROP FUNCTION dbo.xuefen

GO

总结

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

逻辑控制语句等

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

输入参数

输出参数

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

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

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

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

?用户自定义函数：标量函数、表值函数、多语句表值函数