SqlServer存储过程详解

SqlServer存储过程详解 1.创建存储过程的基本语法模板： if ( exists ( select * from sys.objects where name = ' pro_name ' )) drop proc pro_name go create proc pro_name @param_name param_type [ = default_value] as begin sql语句 end ps:[]表示非

SqlServer存储过程详解

1.创建存储过程的基本语法模板：

<span>if</span> (<span>exists</span> (<span>select</span> <span>*</span> <span>from</span> sys.objects <span>where</span> name <span>=</span> <span>'</span><span>pro_name</span><span>'</span><span>))
    </span><span>drop</span> <span>proc</span><span> pro_name
</span><span>go</span>
<span>create</span> <span>proc</span><span> pro_name
    </span><span>@param_name</span> param_type [<span>=</span><span>default_value]
</span><span>as</span>
<span>begin</span><span>    
    sql语句
</span><span>end</span>

ps:[]表示非必写内容。sys.objects存储的是本数据库中的信息，不仅仅存储表名，还有存储过程名 、视图名、触发器等等。

例如：

<span> 1</span> <span>if</span> (<span>exists</span> (<span>select</span> <span>*</span> <span>from</span> sys.objects <span>where</span> name <span>=</span> <span>'</span><span>USP_GetAllUser</span><span>'</span><span>))
</span><span> 2</span>     <span>drop</span> <span>proc</span><span> USP_GetAllUser
</span><span> 3</span> <span>go</span>
<span> 4</span> <span>create</span> <span>proc</span><span> USP_GetAllUser
</span><span> 5</span> <span>@UserId</span> <span>int</span> <span>=</span><span>1</span>
<span> 6</span> <span>as</span> 
<span> 7</span> <span>set</span> nocount <span>on</span><span>;
</span><span> 8</span> <span>begin</span>
<span> 9</span>     <span>select</span> <span>*</span> <span>from</span> UserInfo <span>where</span> Id<span>=</span><span>@UserId</span>
<span>10</span> <span>end</span>

ps:SQL Server 实用工具将 GO 解释为应将当前的 Transact-SQL 批处理语句发送给 SQL Server 的信号。当前批处理语句是自上一 GO 命令后输入的所有语句，若是第一条 GO 命令，则是从特殊会话或脚本的开始处到这条 GO 命令之间的所有语句。

2.调用方法：

<span>exec</span> dbo.USP_GetAllUser <span>2</span>;

 

ps:一般在执行存储过程是，最好加上架构名称，例如  dbo.USP_GetAllUser  这样可以可以减少不必要的系统开销，提高性能。 因为如果在存储过程名称前面没有加上架构名称，SQL SERVER 首先会从当前数据库sys schema（系统架构）开始查找，如果没有找到，则会去其它schema查找，最后在dbo架构（系统管理员架构）里面查找。

 

3.查看本数据库中存在的存储过程

依次展开数据库、可编程性、存储过程，即可看到已创建的存储过程。

4.修改存储过程

<span>alter</span> <span>proc</span><span> proc_name
</span><span>as</span>
　　sql语句

5.存储过程中的输出参数的使用

<span> 1</span> <span>if</span> (<span>exists</span>(<span>select</span> <span>*</span> <span>from</span>  sys.objects <span>where</span> name<span>=</span><span>'</span><span>GetUser</span><span>'</span><span>))
</span><span> 2</span>     <span>drop</span> <span>proc</span><span> GetUser
</span><span> 3</span> <span>go</span> 
<span> 4</span> <span>create</span> <span>proc</span><span> GetUser
</span><span> 5</span>     <span>@id</span> <span>int</span><span> output,
</span><span> 6</span>     <span>@name</span> <span>varchar</span>(<span>20</span><span>) out
</span><span> 7</span> <span>as</span> 
<span> 8</span> <span>begin</span> 
<span> 9</span>     <span>select</span> <span>@id</span><span>=</span>Id,<span>@name</span><span>=</span>Name <span>from</span> UserInfo <span>where</span> Id<span>=</span><span>@id</span>
<span>10</span> <span>end</span>
<span>11</span> 
<span>12</span> <span>go</span> 
<span>13</span> <span>declare</span> 
<span>14</span> <span>@name</span> <span>varchar</span>(<span>20</span><span>),
</span><span>15</span> <span>@id</span> <span>int</span><span>;
</span><span>16</span> <span>set</span> <span>@id</span><span>=</span><span>3</span><span>;
</span><span>17</span> <span>exec</span> dbo.GetUser <span>@id</span>,<span>@name</span><span> out;
</span><span>18</span> <span>select</span> <span>@id</span>,<span>@name</span><span>;
</span><span>19</span> <span>print</span> <span>Cast</span>(<span>@id</span> <span>as</span> <span>varchar</span>(<span>10</span>))<span>+</span><span>'</span><span>-</span><span>'</span><span>+</span><span>@name</span>;

ps:参数output为该参数可以输出

6.分页获取数据的存储过程

<span> 1</span> <span>if</span> (<span>exists</span>(<span>select</span> <span>*</span> <span>from</span>  sys.objects <span>where</span> name<span>=</span><span>'</span><span>GetUserByPage</span><span>'</span><span>))
</span><span> 2</span>     <span>drop</span> <span>proc</span><span> GetUserByPage
</span><span> 3</span> <span>go</span> 
<span> 4</span> <span>create</span> <span>proc</span><span> GetUserByPage
</span><span> 5</span>     <span>@pageIndex</span> <span>int</span><span>,
</span><span> 6</span>     <span>@pageSize</span> <span>int</span>
<span> 7</span> <span>as</span> 
<span> 8</span> <span>declare</span> 
<span> 9</span> <span>@startIndex</span> <span>int</span><span>,
</span><span>10</span> <span>@endIndex</span> <span>int</span><span>;
</span><span>11</span> <span>set</span> <span>@startIndex</span> <span>=</span>  (<span>@pageIndex</span><span>-</span><span>1</span>)<span>*</span><span>@pageSize</span><span>+</span><span>1</span><span>;
</span><span>12</span> <span>set</span> <span>@endIndex</span> <span>=</span> <span>@startIndex</span> <span>+</span> <span>@pageSize</span> <span>-</span><span>1</span><span> ;
</span><span>13</span> <span>begin</span> 
<span>14</span>     <span>select</span> Id,Name <span>from</span> 
<span>15</span> <span>    (
</span><span>16</span>         <span>select</span> <span>*</span>,row_number()<span>over</span> (<span>order</span> <span>by</span> Id)<span>as</span> <span>number</span> <span>from</span><span> UserInfo  
</span><span>17</span>     )t <span>where</span> t.<span>number</span><span>>=</span><span>@startIndex</span> <span>and</span> t.<span>number</span><span><span>@endIndex</span>
<span>18</span> <span>end</span>
<span>19</span> 
<span>20</span> <span>go</span> 
<span>21</span> <span>exec</span> dbo.GetUserByPage <span>2</span>,<span>4</span>;</span>

7.存储过程中事务的创建

<span>if</span> (<span>exists</span>(<span>select</span> <span>*</span> <span>from</span>  sys.objects <span>where</span> name<span>=</span><span>'</span><span>JayJayToTest</span><span>'</span><span>))
    </span><span>drop</span> <span>proc</span><span> JayJayToTest
</span><span>go</span> 
<span>create</span> <span>proc</span><span> JayJayToTest
    </span><span>@GiveMoney</span> <span>int</span><span>,
    </span><span>@UserName</span> <span>nvarchar</span>(<span>20</span><span>)
</span><span>as</span> 
<span>begin</span><span>set</span> nocount <span>on</span><span>;
    </span><span>begin</span> <span>tran</span><span>;
    </span><span>begin</span><span> try
        </span><span>update</span> BankTest <span>set</span> <span>Money</span> <span>=</span> <span>Money</span><span>-</span><span>@GiveMoney</span> <span>where</span> Name<span>=</span><span>@UserName</span><span>;</span>
        <span>update</span> BankTest <span>set</span> <span>Money</span> <span>=</span> <span>Money</span><span>+</span><span>@GiveMoney</span> <span>where</span> Name<span>=</span><span>'</span><span>test</span><span>'</span><span>;
        </span><span>commit</span><span>;
    </span><span>end</span><span> try    
    </span><span>begin</span><span> catch        
        </span><span>rollback</span> <span>tran</span><span>;
        </span><span>print</span> (<span>'</span><span>发生异常，事务进行回滚</span><span>'</span><span>);
    </span><span>end</span><span> catch    
</span><span>end</span>
<span>go</span>
<span>exec</span> JayJayToTest <span>10</span>,<span>'</span><span>jayjay</span><span>'</span>

8.了解存储过程的执行计划

<span>SELECT</span> <span>*</span> <span>FROM</span> sys.<span>[</span><span>syscacheobjects</span><span>]</span>查看当前缓存的执行计划

如果执行存储过程时成功通过解析阶段，则 Microsoft SQL Server 查询优化器将分析存储过程中的 Transact-SQL 语句并创建一个执行计划。执行计划描述执行存储过程的最快方法，所依据的信息包括：

　　1.表中的数据量。

　　2.表的索引的存在及特征，以及数据在索引列中的分布。

　　3.WHERE 子句条件所使用的比较运算符和比较值。

　　4.是否存在联接以及 UNION、GROUP BY 和 ORDER BY 关键字。

查询优化器在分析完存储过程中的这些因素后，将执行计划置于内存中。分析存储过程和创建执行计划的过程称为编译。优化的内存中的执行计划将用来执行该查询。执行计划将驻留在内存中，直到重新启动 SQL Server 或其他对象需要存储空间时为止。如果随后执行了存储过程，而现有执行计划仍留在内存中，则 SQL Server 将重用现有执行计划。如果执行计划不再位于内存中，则创建新的执行计划。

重新编译执行计划(create proc JayJayToTest with recompile)

创建存储过程时在其定义中指定 WITH RECOMPILE 选项，表明 SQL Server 将不对该存储过程计划进行高速缓存；该存储过程将在每次执行时都重新编译。当存储过程的参数值在各次执行间都有较大差异，导致每次均需创建不同的执行计划时，可使用 WITH RECOMPILE 选项。此选项并不常用，因为每次执行存储过程时都必须对其进行重新编译，这样会使存储过程的执行变慢。

由于数据库的新状态，数据库内的某些更改可能会导致执行计划效率低下或不再有效。SQL Server 检测这些使执行计划无效的更改，并将计划标记为无效。此后，必须为执行查询的下一个连接重新编译新的计划。导致计划无效的情况包括：

　　1.对查询所引用的表或视图进行任何结构更改（ALTER TABLE 和 ALTER VIEW）。
　　2.通过语句（如 UPDATE STATISTICS）显式生成或者自动生成新的分发内容统计。
　　3.除去执行计划所使用的索引。
　　4.显式调用 sp_recompile。
　　5.对键的大量更改（其他用户对由查询引用的表使用 INSERT 或 DELETE 语句所产生的修改）。
　　6.对于带触发器的表，inserted 或 deleted 表内的行数显著增长。