存储过程

 

 

存储过程

一、              存储过程概念

存储过程（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