SQL Server基础概念--存储过程

rel="File-List" href="file:///C:%5CUsers%5CADMINI%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"> rel="themeData" href="file:///C:%5CUsers%5CADMINI%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"> rel="colorSchemeMapping" href="file:///C:%5CUsers%5CADMINI%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml">

存储过程是一组已被编辑在一起的，存储在服务器上的，执行某种功能的预编译SQL语句。它是一种封装重复任务操作的方法，支持用户提供的参数设置，具有强大的编程能力。其非常类似于DOS系统中的批处理文件（*.bat）。一般情况下，使用存储过程可以带来如下好处：

（1）    加快程序的执行速度。由于存储过程在第一次被执行以后，其执行规划就存储在高速缓存中。

（2）    减少了网络的数据流量。这是一条使用存储过程非常重要的原因。其可以使一个需要数百行T-SQL代码的操作由一条执行过程代码的单独指令就可以实现，极大地减轻了网络的负担，提高了系统的相应速度。

（3）    提供了一种安全机制。如果用户被授予了执行某存储过程的权限，那么不管他是否拥有该存储过程中所涉及的表或视图的使用权限，他都可以完全执行该存储过程，而不会受到限制。因此，可以创建存储过程来完成所有的增加、删除操作，并且可以通过编程的方式控制伤处操作中对信息的访问。

（4）    允许程序模块化设计。在SQL server2000中对于同一个任务操作，只需要创建存储过程一次并将其存储在数据库中，之后便可以在不同的程序中调用任意次。相同的逻辑处理结构保证了数据修改的一致性。而且模块化的封装方法使存储过程可独立于程序源代码而单独修改，提高了程序的可重用性。

 

T-SQL中的CREATE PROCEDURE语句用于创建存储过程，其语法格式如下：

CREATE PROC [EDURE] procedure_name [;number]

[{@parameter data_type}

  [VARYING] [=default] [OUTPUT]

] [,…n]

[WITH

{ RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}]

[FOR REPLICATION]

AS sql_statement […n]

其中常用参数的意义如下：

（1）    procedure_name[;number]:建立存储过程的名称。存储过程的命名必须符合有关数据库对象命名的规则。在建立存储过程时，SQL server还允许用户根据需要将多个存储过程归为一组，同组的存储过程具有相同的名称，相互之间以参数number相互区别。例如：MyPro;1、MyPro;2

（2）    @parameter生命存储过程的参数，一个存储过程最多可以有1024个参数。Data_type为@parameterde的数据类型

（3）    Default参数指定一个默认值。在调用存储过程时，如果没有为具有默认值的参数赋值，则存储过程将使用默认值。默认值必须一个常量或者NULL。

（4）    使用选项OUTPUT说明的参数为返回参数。返回参数可用来将存储过程中的某些执行结果返回给调用它的EXECUTE语句。

（5）    选项RECOMPILE指定SQL Server不必在告诉缓存中保存存储过程的执行计划，而是每次执行时都重新编译存储过程。

（6）    选项ENCRYPTION指定SQL Server对存储在系统表syscomments中的存储过程的定义文本进行机密。

 

在创建存储过程的时候，要考虑下列因素：

（1）    存储过程可以参考表、视图和临时表中的数据。

（2）    如果存储过程创建了临时表，那么该临时表，只能用于该存储过程，并且当存储过程执行完毕时，临时表自动删除。

（3）    在一个批处理中，CREATE PROCEDURE语句不能与其他的SQL语句混合使用。

（4）    在CREATE PROCEDURE语句的定义中，可以包含任意数量和类型的T-SQL语句，但是不能使用下面的对象创建语句：

                                               i.      CREATE DEFAULT

                                            ii.      CREATE PROCEDURE

                                         iii.      CREATE RULE

                                            iv.      CREATE TRIGGER

                                               v.      CREATE VIEW

 

（5）    存储过程可以嵌套使用，即一个存储过程可以调用另外一个存储过程。存储过程最多可以嵌套32层。当前嵌套层的序号存储在全局变量@@nestlevel中。

 

SQL Server2000在首次执行某存储过程时，它要先编译该存储过程。在编译的过程中，SQL Server的查询优化器将根据操作中所涉及表连接、列、索引等有关信息，生成一个最优的执行方案，然后保存到高速缓存中，以备下次使用。

 

最有效的查询方案往往是根据数据的变化（如输入差数的变化）而变化的。但是，存储过程说明选择的最优方案是根据第一次执行时的数据选定的。所以，如果数据和输入参数发生了变化，以前的查询方案可能不是最好的。此时，可以使用重编译技术强迫存储过程重新编译、重新选择查询方案。在SQL Server中有两种方法可以强迫系统重新编译存储过程，一是生成存储过程时指定WITH RECOMPILE，使用该选项建立的存储过程在每次执行时，查询优化器都会新建俺一个查询方案。这样就保证了每次执行存储过程所使用的查询方案都是根据最新数据和输入参数选定的。但是，如果只是偶尔改变查询方案的话，就应该使用第二种方式，即在EXECUTE语句中使用WITH RECOMPILE选项。