首页 > 代码库 > TSQL 存储过程

TSQL 存储过程

2024-07-13 02:26:43   215人阅读

 

整理自以下链接

http://blog.csdn.net/lenotang/article/details/3329593

 

Transact-SQL 存储过程的分类

       |--系统存储过程

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

       |----类似C语言中的系统函数

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

       |--用户自定义存储过程

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

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

 

 

常用的系统存储过程

 

系统存储过程

说明

sp_databases

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

sp_helpdb

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

sp_renamedb

更改数据库的名称

sp_tables

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

sp_columns

回某个表列的信息

sp_help

查看某个表的所有信息

sp_helpconstraint

查看某个表的约束

sp_helpindex

查看某个表的索引

sp_stored_procedures

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

sp_password

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

sp_helptext

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

 

 

创建存储过程

定义存储过程的语法

 

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

和C语言的函数一样，参数可选；参数分为输入参数、输出参数；输入参数允许有默认值。

 

 

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

 

CREATE PROCEDURE proc_stu /* 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  INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo   WHERE writtenExam<60 OR labExam<60GO

 

 

调用存储过程

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

       调用的语法             

EXEC  过程名  [参数]

 

 

带输入参数的存储过程

CREATE PROCEDURE proc_stu  @writtenPass int = 60,  /*输入参数：笔试及格线*/  @labPass int = 60   /*输入参数：机试及格线*/  AS    print ‘--------------------------------------------------‘    print ‘           参加本次考试没有通过的学员：‘    SELECT stuName,stuInfo.stuNo,writtenExam, /*查询没有通过考试的学员*/    labExam  FROM  stuInfo    INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo    WHERE writtenExam<@writtenPassOR labExam<@labPassGO

调用带参数的存储过程，假定本次考试机试偏难，机试的及格线定为55分，笔试及格线定为60分

EXEC proc_stu 60,55

 

或这样调用：

EXEC proc_stu @labPass=55,@writtenPass=60

 

带输出参数的存储过程

如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出（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<@labPassGO

 

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

/*---调用存储过程----*/DECLARE @sum int /*调用时必须带OUTPUT关键字 ，返回结果将存放在变量@sum中*/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，否则SQL Server将视为输入参数。

 

处理存储过程中的错误

可以使用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：指示是否将错误记录到服务器错误日志中

 

完善上例，当用户调用存储过程时，传入的及格线参数不在0～100之间时，将弹出错误警告，终止存储过程的执行。

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)/*引发系统错误，指定错误的严重级别16，调用状态为1（默认）,并影响@@ERROR系统变量的值 */       BEGIN         RAISERROR (‘及格线错误，请指定0－100之间的分                     数，统计中断退出‘,16,1)         RETURN  ---立即返回，退出存储过程       END    …..其他语句同上例，略GO

 

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

DECLARE @sum int,  @t intEXEC proc_stu @sum OUTPUT ,604   /*笔试及格线误输入604分*/SET @t=@@ERRORprint  ‘错误号：‘+convert(varchar(5),@t )IF @t<>0  /*如果执行了RAISERROR，系统全局@@ERROR将不等于0，表示出现错误*/   RETURN  --退出批处理，后续语句不再执行print ‘--------------------------------------------------‘IF @sum>=3  print ‘未通过人数：‘+convert(varchar(5),@sum)+ ‘人,超过60%,及格分数线还应下调‘ELSE  print ‘未通过人数：‘+convert(varchar(5),@sum)+ ‘人,已控制在60%以下，及格分数线适中‘GO

 

 

 


联系
我们