记录"SQL server学习的存储过程"的摘录

一、存储过程

      存储过程(procedure)类似于C语言中的函数，用来执行管理任务或应用复杂的业务规则，存储过程可以带参数，也可以返回结果。存储过程可以包含数据操纵语句、变量、逻辑控制语句等，比如:单个select语句，select语句块，select语句与逻辑控制块。

      存储过程优点:

             执行速度更快

             允许模块化程序设计

             提高系统安全性

             减少网络流通量

二、存储过程分类

      2.1 系统存储过程

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

            类似C语言中的系统函数

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

      2.2 用户自定义存储过程

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

            类似C语言中的用户自定义函数

      常用的系统存储过程如下表所示:

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

        存储过程都是exec调用，调用上述表中存储过程如下:

EXEC sp_databases
EXEC sp_renamedb 'Northwind','Northwind1'
USE stuDB
GO
EXEC sp_tables
EXEC sp_columns stuInfo
EXEC sp_help stuInfo
EXEC sp_helpconstraint stuInfo
EXEC sp_helpindex stuMarks
EXEC sp_helptext 'view_stuInfo_stuMarks'
EXEC sp_stored_procedures

         常用的扩展存储过程:xp_cmdshell,可以执行DOS命令下的一些操作，以文本行方式返回任何输出

         调用语法:

EXEC xp_cmdshell DOS命令 [NO_OUTPUT]

         扩展存储过程举例:

USE master
GO
EXEC xp_cmdshell 'mkdir d:\bank', NO_OUTPUT
IF EXISTS(SELECT * FROM sysdatabases WHERE name='bankDB')
    DROP DATABASE bankDB
GO
CREATE DATABASE bankDB
{
   ...    --创建数据库语法略
}
GO
EXEC xp_cmdshell 'dir D:\bank\' --查看文件

三、创建存储过程

       定义存储过程的语法

CREATE PROC[EDURE] 存储过程名
    @参数1   数据类型 = 默认值 OUTPUT,
    ......,
    @参数n   数据类型 = 默认值 OUTPUT
    AS
    SQL语句
GO

       存储过程和C语言的函数一样，参数可选，参数分为输入参数、输出参数，输入参数允许有默认值。举例 :

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

CREATE PROCEDURE proc_stu
  AS
    DECLARE @writtenAvg float,@labAvg float
SELECT @writtenAvg=AVG(writtenExam),@labAvg=AVG(labExam)
FROM stuMarks
    print '笔试平均分:'+convert(varchar(5),@writtenAvg)
    print '机试平均分:'+convert(varchar(5),@labAvg)
    IF (@writtenAvg>70 AND @labAvg>70)
        print '本班考试成绩:优秀'
    ELSE
        print '本班考试成绩:较差'
    print '-------------------------------------'
    print '      参加本次考试没有通过的学员:'
    SELECT stuName,stuInfo.stuNo,writtenExam,labExam
    FROM stuInfo INNET JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
    WHERE writtenExam<60 OR labExam<60
GO

四、存储过程的参数分为两种:输入参数和输出参数

  4.1 输入参数

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

        例:

CREATE PROCEDURE proc_stu
  @writtenPass int,
  @labPass int
  AS
    print '---------------------------'
    print '     参加本次考试没有通过的学员:'
    SELECT stuName,stuInfo.stuNo,writtenExam,labExam
    FROM stuInfo
    INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
    WHERE writtenExam<@writtenPass OR labExam<@labPass
GO

        调用带输入参数存储过程:

EXEC proc_stu 60,55
或者:EXEC proc_stu @labPass=55,@writtenPass=60

        同样，还可以设置默认值，如下:

CREATE PROCEDURE proc_stu
  @writtenPass int=60,
  @labPass int=60
  AS
    print '-----------------------------'
    print '     参加本次考试没有通过的学员:'
    SELECT stuName,stuInfo.stuNo,writtenExam,labExam
    FROM stuInfo
    INNET JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
    WHERE writtenExam<@writtenPass OR labExam<@labPass
GO

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

EXEC proc_stu   --都采用默认值
EXEC proc_stu 64   --机试采用默认值
EXEC proc_stu 60,55   --都不采用默认值
或者:EXEC proc_stu @labPass=55

    4.2 输出参数

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

         例:

CREATE PROCEDURE proc_stu
  @notpassSum 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 writtenExam<@writtenPass OR labExam<@labPass
    SELECT @notpassSum=COUNT(stuNo)
    FROM stuMarks WHERE writtenExam<@writtenPass OR labExam<@labPass
GO

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

DECLARE @sum int
EXEC proc_stu @sum OUTPUT, 64
print '---------------------------------'
IF @sum>=3
  print '未通过人数:'+convert(varchar(5),@sum)+'人，超过60%,及格分数线还应下调'
ELSE
  print '未通过人数:'+convert(varchar(5),@sum)+'人，已控制在60%以下，及格分数线适中'
GO

        注意接收存储过程返回值时必须加output关键字

        删除存储过程:drop proc p_test4

五、处理存储过程中的错误

RAISERROR显示用户定义的错误信息时，可指定严重级别，设置系统变量@@ERROR,记录所发生的错误等。

语法如下:

 RAISERROR (msg_id | msg_str,severity,state WITH option[,...n])

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

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

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

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

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

例:

CREATE PROCEDURE proc_stu
  @notpassSum int OUTPUT, --输出参数
  @writtenPass int=60, --默认参数放后
  @labPass int=60   --默认参数放后
  AS 
    IF (NOT @writtenPass BETWEEN 0 AND 100)
          OR (NOT @labPass BETWEEN 0 AND 100)
        BEGIN
           RAISERROR ('及格线错误，请指定0-100之间的分数，统计中断退出',16,1)
           RETURN  ---立即返回，退出存储过程
        END
    ...其他语句同上例，略
GO

  调用该存储过程:

DECLARE @sum int, @t int
EXEC proc_stu @sum OUTPUT, 604  --及格线设置为604出错
SET @t=@@ERROR
print '错误号:'+convert(varchar(5), @t)
IF @t<>0
    RETURN --退出批处理，后续语句不再执行
print '------------------------------------'
IF @sum>=3
  print '未通过人数:'+convert(varchar(5),@sum)+ '人，超过60%,及格分数线还应下调'
ELSE
  print '未通过人数:'+convert(varchar(5),@sum)+'人，已控制在60%以下，及格分数线适中'
GO