SQLServer:存储过程

SQLSERVER存储过程使用说明书

Ø 引言

首先介绍一下什么是存储过程：

存储过程就是将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来，并且这样的语句是放在数据库中的，还可以根据条件执行不同SQL语句， 那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时，只需调用execute,即可自动完成命令。

即：

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

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

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

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

Ø 存储过程的优点

1) 执行速度快。

存储过程创建是就已经通过语法检查和性能优化，在执行时无需每次编译。

存储在数据库服务器，性能高。

2) 允许模块化设计。

只需创建存储过程一次并将其存储在数据库中，以后即可在程序中调用该过 程任意 次。存储过程可由在数据库编程方面有 专长的人员创建，并可独立 于程序源代码而 单独修改 。

3) 提高系统安全性。

    可将存储过程作为用户存取数据的管道。可以限制用户对数据表的存取权  限，建 立特定的存储过程供用户使用，完成对数 据的访问。存储过程的定 义文本可以被加 密，使用户不能查看其内容。

4) 减少网络流量：

一个需要数百行Transact-SQL代码的操作由一条执行过程代码的单独语句 就可实 现，而不需要在网络中发送数百行代码。

Ø 存储过程的分类

1. 系统存储过程

由系统定义，存放在master数据库中，类似C语言中的系统函数，系统存储过程的名 称都以“sp_”开头或”xp_”开头

常用的系统存储过程

系统存储过程	说明
sp_databases	列出服务器上的所有数据库。
sp_helpdb	报告有关指定数据库或所有数据库的信息
sp_renamedb	更改数据库的名称
sp_tables	返回当前环境下可查询的对象的列表
sp_columns	回某个表列的信息
sp_help	查看某个表的所有信息
sp_helpconstraint	查看某个表的约束
sp_helpindex	查看某个表的索引
sp_stored_procedures	列出当前环境中的所有存储过程。
sp_password	添加或修改登录帐户的密码。
sp_helptext	显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。

2. 用户自定义存储过程

由用户在自己的数据库中创建的存储过程类似C语言中的用户自定义函数

  常用的扩展存储过程

常用的扩展存储过程：xp_cmdshell 

可以执行DOS命令下的一些的操作 

以文本行方式返回任何输出 

用语法：

  EXEC xp_cmdshell DOS命令 [NO_OUTPUT]

例如：

use master go   exec xp_cmdshell ‘mkdie dLbank’,NO_OUTPUt ---创建文件夹   if existe(select * from sysdatabases where name=’bankName’)      drop database bankDB   go   create database bankDB   (       .......   )    go    Exec  xp_cmdshell ‘dir d:/bank/’--查看文件 产看文件夹D:/bank/

 

Ø  用户自定义存储过程语法格式

CREATE  PROC[EDURE]  存储过程名                @参数1  数据类型 = 默认值,                …… ,               @参数n  数据类型 OUTPUT             AS             SQL语句     GO 和C语言的函数一样，参数可选 参数分为输入参数、输出参数  输入参数允许有默认值

例如：（创建不带参数的存储过程）

Create proccedure proc_stu --为存储过程取名 As Declare @writtenAvg float,@labAvg float select @writtebAvg = avg(writtenExam)    @labAvg = avg(labExam) from stumarks Print ‘笔试平均分:’+convert(varchar(5),) @writtebAvg  Print ‘机试平均分：’+convert(varchar(5),@labAvg ) If(@writtenAvg>70 and @labAvg>70) Print ‘本班考试成绩：优秀’---显示成绩等级 Else Print ‘本班考试成绩：较差’ Print ‘-----------------------------------------------------’ Print ‘参加本次考试没有通过的学院：’ Select stuName,stuInfo.stuNo,writtenExam,labExam--显示未通过考试的学员信息    From stuInfo inner join stuMarks on stuInfo.stuNo=stuMarks.stuNo    Where wrrittenExam<60 or labExam<60 go

 

Ø 调用存储过程

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

调用的语法

              exec过程名  [参数]

 

1. 创建带参数的存储过程

存储过程的参数分两种：

输入参数

输出参数

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

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

 

1. 带输入参数的存储过程

问题：

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

 

分析： 在述存储过程添加2个输入参数： @writtenPass   笔试及格线  @labPass         机试及格线  Create procedure proc_stu @writtenPass int = 60,  /*输入参数：笔试及格线*/    @labPass int = 60   /*输入参数：机试及格线*/  As  Print ‘------------------------------------------------’ Print ‘参加本次没通过的学员：’ Select stuName,stuInfo,stuNo,wrritenExam,labExam,--查询没有通过考试的学员 From stuInfo inner join stuMarks on stuInfo.stuNo=stuMarks.stuNo Where wruttenExam<@writenExam or labExam<@labExam Go 调用带参数的存储过程      假定本次考试机试偏难，机试的及格线定为55分，笔试及格线定为60分 Exec proc_stu 60,55 或这样调用： EXEC proc_stu @labPass=55,@writtenPass=60

 

2. 带输出参数的存储过程

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

问题：

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

Create procedure proc_stu @notepassSun int output ,--输出参数，表示没有通过考试的人数 @writtenpass int = 60,--推荐将默认参数放在最后 @labpass int = 60 As ...... Select stuName,stuInfo,stuNo,writtenExam,labExam From stuInfo inner join stuMarks on stuInfo.stuNo=stuMarks.stuNo Where wriitenExam<@writtenpass or labExam<l@abpass Go      调用带输出参数的存储过程 /*---调用存储过程----*/ declare @sun int --调用是必须带output关键字，返回时结果将会保存到变量@sun中 Declare proc_stu @sun output ,64 Priint ‘---------------------------------------------------’ If @sun >=3 --后续语句引用返回结果 Print ‘未通过人数：’+convert(varchar(5),@sun)+’人，超过60%，几个线应该还往下面调’ Else Print ‘未通过人数：’+convert(varchar(5),@sun)+’人，以控制在60%以下，及格分数线适中’ Go 注意：调用时也必须跟随关键字OUTPUT，否则SQL Server将视为输入参数。

2. 处理存储过程中的错误

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

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

可指定严重级别，

设置系统变量@@ERROR

记录所发生的错误等

RAISERROR语句的用法如下： 

RAISERROR (msg_id | msg_str,severity,

    state WITH option[,...n]]) 

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

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

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

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

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

RAISERROR 语句每个参数的详细讲解，可以查阅SQL帮助!

问题：

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

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

Create procedure proc_stu @notepassSun int output ,--输出参数，表示没有通过考试的人数 @writtenpass int = 60,--推荐将默认参数放在最后 @labpass int = 60 As  If (not @writtenpass between 0 andd 100) Or (not @labpass between 0 and 100) --引发系统错误，指定错误的严重级别16，调整状态为1（默认），并影响@@error系统变量的值 Begin Raiserror (‘及格线错误，请指定0-100’之间的分数，统计中断退出’,16,1) Return--立即返回 End ........... Go /*---调用存储过程，测试RAISERROR语句----*/ Declare @sun inf ,@t int Exec proc_stu @sun outpu,604--笔试及格线输入错误604分 Set @t=@@error If @t<>0  --如果执行了raiserroe,系统全局@@erroe将不等于0，表示有错误出现 Return --退出批处理，后续语句不在执行 print '--------------------------------------------------' IF @sum>=3   print '未通过人数：'+convert(varchar(5),@sum)+ '人,超过60%,及格分数线还应下调' ELSE   print '未通过人数：'+convert(varchar(5),@sum)+ '人,已控制在60%以下，及格分数线适中' GO

 

Ø 总结：

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

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

–         输入参数

–         输出参数

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

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

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

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