存储过程

2018.09.13

星期四

雨

存储过程

        定义：

              存储过程类似于C#中的函数或JAVA中的方法，主要用来执行管理任务或

              应用复杂的业务规则，不仅可以带参数还可以返回结果。

              它可以包含数据操纵语句，变量，逻辑控制语句等。

        优点：

             1.允许模块化程序设计

                一次创建多次使用，并可独立于程序源代码而单独修改。

             2.执行速度更快

             3.减少网络流通量

                一个需要数百行的T-SQL代码的操作可以由一条存储过程实现。

             4.提高系统安全性

                存储过程的定义文本可被加密，使用户不能查看其内容。

                可将存储过程作为用户取存数据的管道，取代原有数据表操作。

        分类：

            系统存储过程：

    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'                --删除一个或数个文件

            自定义存储过程 ：

             语法：

              create  proc[存储过程名]

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

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

              as

                                   T-SQL语句

              go

             调用：

              exec/execute   存储过程名  [参数]

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

             示例：

    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#语言的按引用传递；

             示例：

    --根据学生姓名,获得学生成绩
    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 '白骨精','女'

             创建带输出类型参数的存储过程：

             注意：

              输出参数，必须使用变量。

              如果要获得输出参数的值，那么在调用时，必须说明该参数为输出参数。

              输出参数同时也是输入参数，调用时，也可以给参数赋值。

              如果参数中包含有输出参数，其他参数将不能使用默认值，调用时必须赋值。

             示例：

    --根据学生姓名查找学员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语句：

               可指定严重级别；

               设置系统变量@@error；

               记录所发生的错误等。

            语法：

               raiserror (错误消息, 严重级别(0—18), 状态(1—127))

            示例：

    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)