存储过程

 存储过程

存储过程类似于C#中的函数或Java中的方法
用来执行管理任务或应用复杂的业务规则
存储过程可以带参数，也可以返回结果

存储过程可以包含数据操纵语句、变量、逻辑控制语句等

存储过程的优点
允许模块化程序设计
   一次创建，多次使用，并可独立于程序源代码而单独修改

执行速度更快
    已经通过语法检查和性能优化，存储在服务器，在执行时无需每次编译
减少网络流通量
   一个需要数百行T-SQL代码的操作可以由一条存储过程单独实现
提高系统安全性
     存储过程的定义可以被加密，使用户不能查看其内容
     可将存储过程作为用户存取数据的管道，取代原有数据表操作

系统存储过程
  由系统定义，存放在master数据库中
  类似C#中的系统函数
  系统存储过程的名称都以“sp_”开头或“xp_”开头
          sp_开头：用来进行系统的各项设定
          xp_开头：用来调用操作系统提供的功能

/*----------------------------系  统  存  储  过  程-------------------------------*/
    EXEC sp_databases        --列出当前系统中的数据库
    EXEC sp_renamedb 'student','students'    --修改数据库的名称(单用户访问)
    USE students    --修改当前数据库
    GO
    EXEC sp_tables        --当前数据库中查询的对象的列表
    EXEC sp_columns stuInfo        --返回某个表的列的信息
    EXEC sp_help stuInfo        --查看表stuInfo的信息
    EXEC sp_helpconstraint stuInfo        --查看表stuInfo的约束
    EXEC sp_helpindex stuMarks            --查看表stuMarks的索引
    EXEC sp_helptext 'view_stuInfo_stuMarks'    --查看视图的语句文本
    EXEC sp_stored_procedures        --查看当前数据库中的存储过程

    --解决错误(2008数据库cmd不能执行时候解决方案)
    EXEC sp_configure 'show advanced options', 1;RECONFIGURE;
    EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;

    EXEC xp_cmdshell 'mkdir d:\Back',NO_OUTPUT        --创建文件夹
    EXEC xp_cmdshell 'dir d:\Back'                    --显示文件夹信息
    EXEC xp_cmdshell 'del d:\Back'                --删除一个或数个文件
    

 

用户自定义存储过程
   由用户在自己的数据库中创建的存储过程
    类似C#中的用户自定义函数

常用的扩展存储过程：xp_cmdshell
   可以执行DOS命令下的一些的操作
    以文本行方式返回任何输出
    调用语法：EXEC xp_cmdshell DOS命令[,NO_OUTPUT]

定义存储过程的语法
    和C#的函数一样，参数可选
    参数分为输入参数、输出参数
    输入参数允许有默认值

调用的语法：EXEC[UTE]过程名[参数]

/*----------------------------自  定  义  存  储  过  程-------------------------------*/
/*--基本语法--*/
    --GO
    --CREATE  PROC[EDURE]  存储过程名
    --    [@参数1  数据类型 [= 默认值 | OUTPUT],
    --    @参数n  数据类型 [= 默认值 | OUTPUT],...]
    --AS
    --    T-SQL语句
    --GO    
    
/*--不带参数的存储过程--*/
    --获取所有的学生成绩信息
    
    --视图引入
        ----如果存在该视图，先将其删除掉
        --IF EXISTS (    SELECT * FROM sys.views WHERE NAME = 'view_stuInfo_stuMarks')
        --    DROP VIEW view_stuInfo_stuMarks
        --GO
        ----创建名为view_stuInfo_stuMarks的视图
        --CREATE VIEW view_stuInfo_stuMarks
        --AS
        --    SELECT stuInfo.StuID,stuName,subject,score FROM stuInfo LEFT JOIN stuMarks
        --    ON stuInfo.StuID = stuMarks.StuID
        --GO
        --select * from view_stuInfo_stuMarks;
        
    IF EXISTS (SELECT * FROM sys.procedures WHERE NAME='Proc_GetAllStuMark')
        DROP PROC Proc_GetAllStuMark
    GO
        
    CREATE PROC Proc_GetAllStuMark
    AS
        SELECT a.stuid,a.StuName, b.Subject, b.Score
        FROM StuInfo a, StuMarks b
        WHERE a.StuID=b.StuID
    GO
    
    --执行存储过程
    EXEC Proc_GetAllStuMark
    EXECUTE Proc_GetAllStuMark    
   

存储过程的参数分两种：
 输入参数：用于向存储过程传入值，类似C#语言的按值传递
  输出参数：用于在调用存储过程后，返回结果，类似C#语言的按引用传递

在调用存储过程时，有些参数的值的变化很少的，这时可以给这些参数一个默认值，即使调用时不输入值，也会在存储过程中使用默认值。在很大程度上方便调用。

输出参数，必须使用变量。
如果要获得输出参数的值，那么在调用时，也必须说明该参数为输出参数。
输出参数同时也是输入参数，调用时，也可以给参数赋值。
如果参数中含有输出参数，其他参数将不能使用默认值，调用时必须赋值

可以使用print语句显式错误信息，但这些信息是临时的，只能显示给用户

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

/*--创建带有参数的存储过程--*/
    --根据学生姓名,获得学生成绩
    IF EXISTS (SELECT * FROM sys.procedures WHERE NAME='Proc_GetStuMarkByStuName')
        DROP PROC Proc_GetStuMarkByStuName
    GO
    
    CREATE PROC Proc_GetStuMarkByStuName @stuname VARCHAR(20)
    AS
        SELECT a.StuName, b.Subject, b.Score
        FROM StuInfo a, StuMarks b
        WHERE a.StuID=b.StuID
        AND a.StuName=@stuname
    GO

    EXEC Proc_GetStuMarkByStuName '李四'
    
/*--创建参数有默认值的存储过程--*/
    --添加学生信息表的数据
    IF EXISTS (SELECT * FROM sys.procedures WHERE NAME='Proc_InsertStuInfo')
        DROP PROC  Proc_InsertStuInfo
    GO
    
    CREATE PROC Proc_InsertStuInfo @stuname varchar(20),@stusex char(2) = '男'
    AS
        INSERT INTO StuInfo (StuName, StuSex)
        VALUES (@stuname, @stusex)
    GO
    
    --调用参数有默认值的存储过程
    EXEC Proc_InsertStuInfo '小白'
    EXEC Proc_InsertStuInfo '白书','女'
    
    
    --如果传入姓名，则查询执行学生；否则查询全部
    IF EXISTS (SELECT * FROM sys.procedures WHERE NAME='Proc_SelectByName')
        DROP PROC  Proc_SelectByName
    GO
    
    CREATE PROC Proc_SelectByName (@name varchar(20)=null)
    AS
        --if @name is null
        --begin
        --    select StuInfo.stuid,stuName,subject,score
        --    from stuinfo,stumarks
        --    where stuinfo.stuid=stumarks.stuid
        --end
        --else
        --begin
        --    select StuInfo.stuid,stuName,subject,score
        --    from stuinfo,stumarks
        --    where stuinfo.stuid=stumarks.stuid and stuName=@name
        --end
        
        declare @sql varchar(max)
        set @sql=' select StuInfo.stuid,stuName,subject,score
                    from stuinfo,stumarks
                    where stuinfo.stuid=stumarks.stuid '
        if @name is not null
            set @sql=@sql + ' and stuName='''+@name +''''
            
        exec (@sql)
    GO
    
    EXEC Proc_SelectByName
    EXEC Proc_SelectByName '张三'
    
/*--带输出类型参数的存储过程--*/
    --根据学生姓名查找学员SQL分数
    IF EXISTS (SELECT * FROM sys.procedures WHERE NAME='Proc_GetCMarkByStuName')
        DROP PROC Proc_GetCMarkByStuName
    GO
    
    CREATE PROC Proc_GetCMarkByStuName @stuname VARCHAR(20),@cmark int OUTPUT
    AS
        SELECT @cmark = b.Score FROM StuInfo a, StuMarks b
        WHERE a.StuID = b.StuID
        AND b.Subject = 'SQL' AND a.StuName = @stuname
    GO
    
    --调用该存储过程
    DECLARE @cmark INT
    EXEC Proc_GetCMarkByStuName '李四', @cmark OUTPUT
    PRINT '李四的SQL分数为:' + CONVERT(VARCHAR, @cmark)

    
/*----------------------------存  储  过  程  异  常-------------------------------*/
/*--使用RAISERROR语句抛出错误信息--*/
    --RAISERROR (错误消息, 严重级别, 状态)
    --自定义错误
    IF EXISTS (SELECT * FROM sys.procedures WHERE NAME='Proc_Devide')
        DROP PROC Proc_Devide
    GO
    
    CREATE PROC Proc_Devide @a int, @b int
    AS
        DECLARE @c INT
        IF (@b = 0)
            BEGIN
                RAISERROR ('以零作除数错误', 15, 2)
                RETURN
            END
        SET @c = @a / @b
    GO

    EXEC Proc_Devide 10, 0
    print '错误编号' + CONVERT(VARCHAR, @@ERROR)