数据库的存储过程

1.http://blog.sina.com.cn/s/blog_76b2c4810101b8qz.html

 

将常用的或很复杂的工作，预先用SQL语句写好并用一个指定的名称存储起来, 那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时,只需调用execute,即可自动完成命令。

1.1      存储过程的优点

1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
       2. 数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时)，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
       3.存储过程可以重复使用,可减少数据库开发人员的工作量
       4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权

1.2      创建存储过程的语法

CREATE PROC[ EDURE ] [ owner. ] procedure_name [number ]
    [{ @parameter data_type }
           [ARYING ] [ = default ] [ OUTPUT ]
       ] [ ,...n ]
[ WITH
       { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

      [ FOR REPLICATION ]

      AS sql_statement [ ...n ]

参数：

owner:拥有存储过程的用户ID的名称。owner必须是当前用户的名称或当前用户所属的角色的名称。

procedure_name:新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。

Number：是可选的整数，用来对同名的过程分组，以便用一条DROP PROCEDURE语句即可将同组的过程一起除去。例如，名为orders的应用程序使用的过程可以命名为orderproc;1、orderproc;2等。DROP PROCEDURE orderproc语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在procedure_name前后使用适当的定界符。

@parameter:过程中的参数。在CREATE PROCEDURE语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值，或者该值设置为等于另一个参数）。存储过程最多可以有2.100个参数。使用 @ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。

data_type：参数的数据类型。除table之外的其他所有数据类型均可以用作存储过程的参数。但是，cursor数据类型只能用于OUTPUT参数。如果指定cursor数据类型，则还必须指定VARYING和OUTPUT关键字。对于可以是cursor数据类型的输出参数，没有最大数目的限制。

VARYING:指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。

Default：参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或NULL。如果过程将对该参数使用LIKE关键字，那么默认值中可以包含通配符（%、_、[] 和 [^]）。

OUTPUT:表明参数是返回参数。该选项的值可以返回给EXEC[UTE]。使用OUTPUT参数可将信息返回给调用过程。Text、ntext和image参数可用作OUTPUT参数。使用OUTPUT关键字的输出参数可以是游标占位符。

N:表示最多可以指定2.100个参数的占位符。

{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}：

RECOMPILE:表明SQL Server不会缓存该过程的计划，该过程将在运行时重新编译。在使用非典型值或临时值而不希望覆盖缓存在内存中的执行计划时，请使用RECOMPILE选项。

ENCRYPTION:表示SQL Server加密syscomments表中包含CREATE PROCEDURE语句文本的条目。使用ENCRYPTION可防止将过程作为SQL Server复制的一部分发布。

FOR REPLICATION:指定不能在订阅服务器上执行为复制创建的存储过程。.使用FOR REPLICATION选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和WITH RECOMPILE选项一起使用。

AS:指定过程要执行的操作。

sql_statement:过程中要包含的任意数目和类型的Transact-SQL语句。但有一些限制。

2 .http://www.cnblogs.com/Alpha-Fly/archive/2012/04/08/2437577.html

定义存储过程：

　　create proc usp_StudentByGenderAge

　　@gender nvarchar(10) [='男'],

　　@age int [=30]

　　as

　　select * from MyStudent where FGender=@gender and FAge=@age

 

　　执行存储过程：

　　Situation One(调用默认的参数):

　　exec usp_StudentByGenderAge

　　Situation Two(调用自己指定的参数):

　　exec usp_StudentByGenderAge '女',50

　　或者指定变量名        exec usp_StudentByGenderAge @age=50,@gender='女'

 

　　对存储过程进行修改

　　alter proc usp_StudentByGenderAge

　　@gender nvarchar(10) [='男'],

　　@age int [=30],

　　--加output表示该参数是需要在存储过程中赋值并返回的

　　@recorderCount int output 

　　as

　　select * from MyStudent where FGender=@gender and FAge=@age

　　set @recorderCount=(select count(*) from MyStudent where FGender=@gender and FAge=@age)

 

--output参数的目的，就是调用者需要传递一个变量进来，然后在存储过程中为该变量完成赋值工作，存储过程执行完成以后，将执行的对应结果返回给传递进来的变量。(与C#中的out原理一模一样)

 

调用(记住这里的语法！)因为该存储过程前面还有其他参数，所以要把 @recorderCount写上，该存储过程执行后，相当与完成了以上的查询工作，同时将查询结果得到的条数赋值给了@count变量。(@count是当做参数传给usp_StudentByGenderAge，当存储过程执行完毕以后，将得到的条数返回给@count)

　　declare @count int

　　exec usp_StudentByGenderAge @recorderCount=@count output

　　print @count

 

五、使用存储过程完成分页

1、存储过程代码

 　  create proc usp_page

　　@page int,          ---一页显示多少条记录

 　  @number int,   ---用户选择了第几页数据

      as

 　　begin

 　　select * from

 　　--小括号里面内容是专门得到排列好的序号

 　　(

 　　　　select  ROW_NUMBER() over(order by(Fid))  as number

 　　　　from MyStudent

 　　) as t

 　　where t.number>= (@number-1)*@page+1 and t.number<=@number*@page

　　 end

--参考PPT  【图文】数据库存储过程_百度文库  http://wenku.baidu.com/link?url=FFZX4KilE8Y3i8Jv0ZcapzGOduUYF5N-DcsAnkz6kP2jGdxSv6l1MeGHSwXMZdUJMDdOOmHP8SK5zhz5JwqS7EEgzl_kqdwBsSKNXOJqGrm