存储过程

 

 

 

存储过程

一、              存储过程概念

存储过程（ procedure ）类似于 C 语言中的函数，它是 SQL 语句和控制流程语句的预编译集合。

 

存储过程（ procedure ）的优点：

1 允许模块化设计院

2 允许更书报地执行

3 减少网络流量

4 可作为安全机制使用

二、              存储过程的分类

存储过程分为三类 :

1 系统存储过程：以 sp_ 开头，类似于 C 语言中的系统函数。系统存储过程位于 master 数据库中。

 

2 系统扩展存储过程：以 xp_ 开头，类似于 C 语言中的系统函数。系统扩展存储过程位于 master 数据库中。

 

3 自定义存储过程：类似于 C 语言中的自定义函数。

 

三、              常用系统存储过程

1 sp_helpdb: 报告数据库的信息

    execute sp_helpdb           -- 查看所有数据库信息

    execute sp_helpdb ' 数据库名 '  -- 查看当前数据库信息

 

2 sp_help: 查看某个数据库中表的信息

    execute sp_help          -- 查看某个数据库中表的所有信息

    execute sp_help ' 表名 '  -- 查看某个数据库中单个表的信息

 

3 sp_helpfile: 查看当前数据库文件的 . mdf 和 . ldf 的位置

    execute sp_helpfile

 

4 sp_helpindex: 查看某个表的索引

    execute sp_helpindex ' 表名 '

 

5 sp_helpconstraint: 查看某个表的约束

    execute sp_helpconstraint ' 表名 '

 

6 sp_helptext: 显示未加密的存储过程触发器或视图的实际文本 .

    execute sp_helptext ' 存储过程 | 触发器 | 视图 '

 

7 sp_stored_procedures: 返回当前数据库中的存储过程的列表 .

    execute sp_stored_procedures

 

8 sp_tables: 查看当前环境下可查询的对象的列表

    execute sp_tables

    execute sp_tables ' 表名 '

 

9 sp_columns: 查看表中列的信息

    execute sp_columns ' 表名 '

 

10 sp_databases: 列出服务器上所有的数据库

    execute sp_databases

 

11 sp_renamedb: 更改数据库的名字

    execute sp_renamedb ' 原数据库名 ' , ' 新数据库名 '

 

12 sp_password: 设置登录帐户的密码

    execute sp_password ' 旧密码 ' , ' 新密码 ' , ' 登录名 '

    alter login ' 登录名 ' enable -- 启用帐户

 

    execute sp_password 'sb','sbsb','sa'

    alter login sa enable

 

 

四、              常用系统扩展存储过程

execute xp_cmdshell 'dos 命令 ' [,no_output]

 

execute xp_cmdshell 'mkdir d:/stu',no_output

execute xp_cmdshell 'dir d:/stu'

 

五、              自定义存储过程

语法 :

create procedure 存储过程名

    [{@ 参数数据类型 }[= 默认值 ][output]

    ......,

    {@ 参数 n 数据类型 } [= 默认值 ][output]]

as

    SQL 语句

省略 output 则视为输入参数 .

 

1 不带参数的存储过程

 

use  stuDB
go
if   exists ( select   *   from  sysobjects  where  name = ' proc_stu ' )
     drop   procedure  proc_stu
go
/**/ /*--创建存储过程--*/
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   ' 机试平均分:  ' + cast ( @labAvg   as   varchar ( 5 ))
     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 < 60

-- 调用存储过程--
execute  proc_stu

 

 

 

2 带输入参数的存储过程

 

use  stuDB
go
if   exists ( select   *   from  sysobjects  where  name = ' proc_stu ' )
     drop   procedure  proc_stu
go
/**/ /*--创建存储过程--*/
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 < @writtenPass   or  labExam < @labPass
go

-- 调用存储过程--
execute  proc_stu                 -- 笔试和机试都采用默认值
execute  proc_stu  50                  -- 笔试及格线,机试采用默认值.
execute  proc_stu  @labPass = 55      -- 机试及格线,笔试采用默认值.
execute  proc_stu  65 , 65              -- 都不采用默认值

 

 

3 带输出参数的存储过程

use  stuDB
go
if   exists ( select   *   from  sysobjects  where  name = ' proc_stu ' )
     drop   procedure  proc_stu
go
/**/ /*--创建存储过程--*/
create   procedure  proc_stu
@notpassSum   int  output,     -- 指出是输出参数
@writtenPass   int = 60 ,     -- 输入参数,笔试及格线,默认参数放后.
@labPass   int = 60              -- 输入参数,机试及格线,默认参数放后.
as
     print   ' 笔试平均分:  ' + convert ( varchar ( 5 ), @writtenPass )
     print   ' 机试平均分:  ' + cast ( @labPass   as   varchar ( 5 ))
     print   ' ------------------------------------- '
     print   '      参加本次考试没有通过的学生      '
     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     
-- 调用时也带output,笔试及格线为,机试及格线默认为            
execute  proc_stu  @sum  output, 64  
select    ' 未通过人数 ' +   cast ( @sum   as   varchar ( 5 )) + ' 人 '

 

 

4         raiserror 处理错误信息

use  stuDB
go
if   exists ( select   *   from  sysobjects  where  name = ' proc_stu ' )
     drop   procedure  proc_stu
go
/**/ /*--创建存储过程--*/
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 ( ' 及格线错误,请指定-100之间的数,统计中断退出! ' , 16 , 1 )
             return   -- 立即返回,退出存储过程
         end

     print   ' 笔试平均分:  ' + convert ( varchar ( 5 ), @writtenPass )
     print   ' 机试平均分:  ' + cast ( @labPass   as   varchar ( 5 ))
     print   ' ------------------------------------- '
     print   '      参加本次考试没有通过的学生      '
     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 , @t   int     

-- 调用时也带output,笔试及格线为,机试及格线默认为            
execute  proc_stu  @sum  output, 800      -- 大于报错
set   @t = @@ERROR                          -- raiserror报错误后@@ERROR将不等于,表示有错
if   @t <> 0
     print   ' @@ERROR的值是:  ' + convert ( varchar ( 5 ), @t )
     return   -- 退出批处理,后续语句不执行.
select    ' 未通过人数 ' +   cast ( @sum   as   varchar ( 5 )) + ' 人 '
go