MSSQL存储过程学习笔记一：关于存储过程-白红宇的个人博客

MSSQL存储过程学习笔记一：关于存储过程

发布日期：2021-05-14 04:37:08 浏览次数：16 分类：博客文章

本文共 4686 字，大约阅读时间需要 15 分钟。

一、 存储过程的概念，优点，语法

在写笔记之前，首先需要整理好这些概念性的东西，否则的话，就会在概念上产生陌生或者是混淆的感觉。

概念：将常用的或者是很复杂的工作，预先利用SQL语句写好并用一个指定的名称存储起来，那么以后要是调用这些SQL语句的时候，只需要利用Execute/Exec执行以下，即可。

优点：当然了，使用存储过程的优点是很多的，下面来一一说明。

1、存储过程只是在创造的时候进行编译，以后每次执行的时候，就不需要编译了，但是直接利用SQL的话，需要每次运行的时候都重新编译一次，所以使用存储过程可以提高数据库的执行速度。

2、当对数据库进行复杂操作的时候，利用存储过程进行封装，可以减少代码出错的几率，并且MSSQL本身具有代码调试能力，可以很容易的定位到出错的语句。

3、存储过程可以重复使用，可以提高开发人员的开发效率。

4、安全性高，可以设定只有特定权限的用户对存储过程进行操作；也可以在一定的程度上预防SQL注入操作。

种类：存储过程分为三类，分别为系统存储过程、扩展存储过程、用户自定义存储过程。

1、系统存储过程：就是以SP_开头的存储过程，用来进行系统的各种设定，取得信息，进行相关的管理工作等等；如：sp_help就是取得指定对象的相关信息。

2、扩展存储过程：就是以XP_开头的，用来调用操作系统提供的功能。以下为引用的内容：exec master..xp_cmdshell ‘ping 127.0.0.1’

3、用户自定义的存储过程：

常用格式如下：

Create procedure procedue_name　　[@parameter data_type][output]　　[with]{recompile|encryption}　　as　　sql_statement

需要说明的就是:

output：表明此参数是可以回传的。

[with]{recompile|encryption}中的recompile：表明每次执行此存储过程的时候，都重新编译一次（默认情况下只有在创建的时候才进行编译）。

encryption：所创建的存储过程的内容会被加密。

小技巧：在这里需要说明的是，如果我们有时候要在数据库中查找所有包含A关键字的表的列的名称，那么该如何寻找呢？可以利用下面的语句：

select
    table_name,column_name 
   from
    INFORMATION_SCHEMA.COLUMNS 
   where
    COLUMN_NAME 
   like
    
   '
   %A%
   '
   ;  
   --
   查看那些表含有包含A的列

但是如果想在存储过程找存在表“B”的存储过程的名称，该如何做呢，可以利用下面的语句来进行：

select
     routine_name, routine_definition  
    from
     information_schema.routines       
    where
     routine_definition 
    like
     
    '
    %B%
    '
          
    and
     routine_type
    =
    '
    procedure
    '

当然了，我们其实还可以利用SQL中的syscomments,sysobjects,sysdepends来查看具体的数据信息，这个和oracle中的dba_objects等很像：

select
    
   *
    
   from
    syscomments;  
   --
   查看标注
   
   
   select
    
   *
    
   from
    sysobjects;  
   --
   查看数据库对象
   
   
   select
    
   *
    
   from
    sysdepends; 
   --
   查看依赖关系

二、 存储过程进阶

当然了，说先来说明下存储过程的格式语法规则：

Create
     
    Procedure
     
    Procedure
    -
    name (
   
   
         Input parameters ,
   
   
         Output Parameters (
    If
     required))
   
   
    As
   
   
    Begin     
   
   
           Sql statement used 
    in
     the stored procedure
   
   
    End

在这里我们利用一个普通的例子来说明:

/*
      Getstudentname is the name of the stored procedure
    */
     
    Create
      
    PROCEDURE
     Getstudentname(  
    @studentid
     
    INT
                       
    --
    Input parameter ,  Studentid of the student 
    
     ) 
    AS
    
    BEGIN
    
    SELECT
     Firstname
    +
    '
     
    '
    +
    Lastname 
    FROM
     tbl_Students 
    WHERE
     studentid
    =
    @studentid
     
    END

当然了，这里的@studentid参数只是一个传入的参数，但是如果想回传一个值，那么就需要利用到out参数来实现，具体的实现代码如下：

/*
     GetstudentnameInOutputVariable is the name of the stored procedure which uses output variable @Studentname to collect the student name returns by the stored procedure 
    */
     
    Create
      
    PROCEDURE
     GetstudentnameInOutputVariable (  
    @studentid
     
    INT
    ,                       
    --
    Input parameter ,  Studentid of the student
    
    
    @studentname
     
    VARCHAR
    (
    200
    )  OUT        
    --
     Out parameter declared with the help of OUT keyword
    
    ) 
    AS
    
    BEGIN
    
    SELECT
     
    @studentname
    =
     Firstname
    +
    '
     
    '
    +
    Lastname 
    FROM
     tbl_Students 
    WHERE
     studentid
    =
    @studentid
    
    END

从上面的代码，可以看出out参数的具体用法，但是如果想在SQL服务器端执行这段代码，那该如何进行呢？

其实，一说到这，稍微麻烦一点，如果是只有in参数，那么只需要利用execute/exec 后面加上存储过程的名称，里面给参数赋值即可；但是如果不仅有in参数，而且有out参数，这个该怎么来弄呢？

下面通过一个具体的实例来详细的描述用法：

Alter
      
    PROCEDURE
     GetstudentnameInOutputVariable ( 
    @studentid
     
    INT
    ,                   
    --
    Input parameter ,  Studentid of the student
    
    
    @studentname
     
    VARCHAR
     (
    200
    ) OUT,    
    --
     Output parameter to collect the student name
    
    
    @StudentEmail
     
    VARCHAR
     (
    200
    )OUT     
    --
     Output Parameter to collect the student email
    
    ) 
    AS
    
    BEGIN
    
    SELECT
     
    @studentname
    =
     Firstname
    +
    '
     
    '
    +
    Lastname, 
    @StudentEmail
    =
    email 
    FROM
     tbl_Students 
    WHERE
     studentid
    =
    @studentid
    
    END

可以看出，上面的存储过程有三个参数，其中第一个是IN参数，而后两个是OUT参数，从过程主体可以看出，第一个out参数就是得到学生全名，第二个则是得到email的。

那么如何在服务器端查看执行后得到的结果呢？

Declare
     
    @Studentname
     
    as
     
    nvarchar
    (
    200
    )   
    --
     申明第一个输出参数
    
    
    Declare
     
    @Studentemail
     
    as
     
    nvarchar
    (
    50
    )     
    --
     申明第二个输出参数
    
    
    Execute
     GetstudentnameInOutputVariable 
    1
     , 
    @Studentname
     output, 
    @Studentemail
     output 
    Select
     
    @Studentname
    ,
    @Studentemail
      
    --
    “select”语句可以查看结果

好了，上面就是存储过程方面的一些知识，后续文章将会将游标，自定义错误，事物运行给包括进来，希望SQL在一步一步的学习中，坚实的成长起来。

上一篇：小试JQuery的AutoComplete插件

下一篇：由于 ASP.NET 进程标识对全局程序集缓存没有读权限，因此未能执行请求。错误: 0x80131902

发表评论

关于作者

喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！

-- 愿君每日到此一游！

发表评论

最新留言

关于作者

推荐文章