存储过程

存储过程
一、              存储过程概念
存储过程（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

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/zerolsy/archive/2008/02/26/2123677.aspx

转载于:https://www.cnblogs.com/zhdonghu/archive/2009/07/21/1528124.html