SQLSERVER存储过程使用说明书

 

SQLSERVER 存储过程使用说明书

 

引言

首先介绍一下什么是存储过程：存储过程就是将常用的或很复杂的工作，预先用 SQL 语句写好并用一个指定的名称存储起来，并且这样的语句是放在数据库中的，还可以根据条件执行不同 SQL 语句，   那么以后要叫数据库提供与已定义好的存储过程的功能相同的服务时，只需调用 execute, 即可自动完成命令。
请大家先看一个小例子：

create proc query_book

as

select * from book

go

-- 调用存储过程

exec query_book

请大家来了解一下存储过程的语法。

　　 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

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

　　要创建局部临时过程，可以在  procedure_name  前面加一个编号符  (#procedure_name) ，要创建全局临时过程，可以在  procedure_name  前面加两个编号符  (##procedure_name) 。完整的名称（包括  #  或  ## ）不能超过  128  个字符。指定过程所有者的名称是可选的。

　 2 、 ;number

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

　 3 、 @parameter

　　过程中的参数。在  Create PROCEDURE  语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有  2100  个参数。

　　使用 @ 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。

4 、 data_type

　　参数的数据类型。所有数据类型（包括  text 、 ntext  和  image ）均可以用作存储过程的参数。不过， cursor  数据类型只能用于  OUTPUT  参数。如果指定的数据类型为  cursor ，也必须同时指定  VARYING  和  OUTPUT  关键字。

　　 说明：对于可以是 cursor  数据类型的输出参数，没有最大数目的限制。

5 、 VARYING

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

6 、 default

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

7 、 OUTPUT

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

8 、 n

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

9 、 {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}

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

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

　　说明：在升级过程中， SQL Server  利用存储在  syscomments  中的加密注释来重新创建加密过程。

10 、 FOR REPLICATION

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

11 、 AS

　　指定过程要执行的操作。

12 、 sql_statement

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

13 、 n

　　是表示此过程可以包含多条  Transact-SQL  语句的占位符。

14 、注释

　　 /* 和 */ 之间的为注释，可以包含一行和多行的说明文字。

15 、　其他说明

　　存储过程的最大大小为  128 MB 。

二、存储过程的优点都有哪些呢？

　　 1. 存储过程 只在创造时进行编译即可，以后每次执行存储过程都不需再重新编译，而我们通常使用的 SQL 语句每执行一次就编译一次 , 所以使用存储过程可提高数据库执行速度。

　　 2. 经常 会遇到复杂的业务逻辑和对数据库的操作，这个时候就会用 SP 来封装数据库操作。当对数据库进行复杂操作时 ( 如对多个表进行 Update,Insert,Query,Delete 时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。可以极大的提高数据库的使用效率，减少程序的执行时间，这一点在较大数据量的数据库的操作中是非常重要的。在代码上看， SQL 语句和程序代码语句的分离，可以提高程序代码的可读性。

　　 3. 存储 过程可以设置参数，可以根据传入参数的不同重复使用同一个存储过程，从而高效的提高代码的优化率和可读性。

　　 4. 安全性高 , 可设定只有某此用户才具有对指定存储过程的使用权存储过程的种类：

　　（ 1 ）系统存储过程：以 sp_ 开头 , 用来进行系统的各项设定 . 取得信息 . 相关管理工作 , 如  sp_help 就是取得指定对象的相关信息。

　　（ 2 ）扩展存储过程   以 XP_ 开头 , 用来调用操作系统提供的功能 exec master..xp_cmdshell 'ping 10.8.16.1'

　　（ 3 ）用户自定义的存储过程 , 这是我们所指的存储过程常用格式

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

　　解释： output ：表示此参数是可传回的

　　 with {recompile|encryption} recompile: 表示每次执行此存储过程时都重新编译一次 ;encryption: 所创建的存储过程的内容会被加密。

三、实例讲解

　　 实例 1 ：只返回单一记录集的存储过程。

　　要求 1 ：查询表 bankMoney 的内容的存储过程

　　 create procedure sp_query_bankMoney
　　 as
　　 select * from bankMoney
　　 go
　　 exec sp_query_bankMoney

　　注 *   在使用过程中只需要把中的 SQL 语句替换为存储过程名，就可以了很方便吧！

　　 实例 2 （向存储过程中传递参数）：

　　加入一笔记录到表 bankMoney ，并查询此表中 userID= Zhangsan 的所有存款的总金额。

　　 Create proc insert_bank @param1 char(10),@param2 varchar(20),@param3 varchar(20),@param4 int,@param5 int output
　　 with encryption --------- 加密
　　 as
　　 insert bankMoney (id,userID,sex,Money) Values(@param1,@param2,@param3, @param4)
select @param5=sum(Money) from bankMoney where userID='Zhangsan'
　　 go

　　在 SQL Server 查询分析器中执行该存储过程的方法是：

　　 declare @total_price int
　　 exec insert_bank '004','Zhangsan',' 男 ',100,@total_price output
　　 print ' 总余额为 '+convert(varchar,@total_price)
　　 go

　　在这里再啰嗦一下存储过程的 3 种传回值（方便正在看这个例子的朋友不用再去查看语法内容） : 

　　 1. 以 Return 传回整数  
　　 2. 以 output 格式传回参数  
　　 3.Recordset

　　传回值的区别 : 

　　 output 和 return 都可在批次程式中用变量接收 , 而 recordset 则传回到执行批次的客户端中。

　　 实例 3 ：使用带有复杂  Select  语句的简单过程

　　下面的存储过程从四个表的联接中返回所有作者（提供了姓名）、出版的书籍以及出版社。该存储过程不使用任何参数。

　　 USE pubs
　　 IF EXISTS (Select name FROM sysobjects
         Where name = 'au_info_all' AND type = 'P')
　　 Drop PROCEDURE au_info_all
　　 GO
　　 Create PROCEDURE au_info_all
　　 AS
　　 Select au_lname, au_fname, title, pub_name
　　 FROM authors a INNER JOIN titleauthor ta
      ON a.au_id = ta.au_id INNER JOIN titles t
      ON t.title_id = ta.title_id INNER JOIN publishers p
      ON t.pub_id = p.pub_id
　　 GO

　　 au_info_all  存储过程可以通过以下方法执行：

　　 EXECUTE au_info_all
　　 -- or
　　 EXEC au_info_all

　　如果该过程是批处理中的第一条语句，则可使用：

　　 au_info_all

　　 实例 4 ：使用带有参数的简单过程

　　 Create PROCEDURE au_info
      @lastname varchar(40),
      @firstname varchar(20)
　　 AS
　　 Select au_lname, au_fname, title, pub_name
      FROM authors a INNER JOIN titleauthor ta
      ON a.au_id = ta.au_id INNER JOIN titles t
      ON t.title_id = ta.title_id INNER JOIN publishers p
      ON t.pub_id = p.pub_id
      Where  au_fname = @firstname
      AND au_lname = @lastname
　　 GO

　　 au_info  存储过程可以通过以下方法执行：

　　 EXECUTE au_info 'Dull', 'Ann'
　　 -- or
　　 EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'
　　 -- or
　　 EXECUTE au_info @firstname = 'Ann', @lastname = 'Dull'
　　 -- or
　　 EXEC au_info 'Dull', 'Ann'
　　 -- or
　　 EXEC au_info @lastname = 'Dull', @firstname = 'Ann'
　　 -- or
　　 EXEC au_info @firstname = 'Ann', @lastname = 'Dull'

　　如果该过程是批处理中的第一条语句，则可使用：

　　 au_info 'Dull', 'Ann'
　　 -- or
　　 au_info @lastname = 'Dull', @firstname = 'Ann'
　　 -- or
　　 au_info @firstname = 'Ann', @lastname = 'Dull'

　　 实例 5 ：使用带有通配符参数的简单过程

　　 Create PROCEDURE au_info2
　　 @lastname varchar(30) = 'D%',
　　 @firstname varchar(18) = '%'
　　 AS
　　 Select au_lname, au_fname, title, pub_name
　　 FROM authors a INNER JOIN titleauthor ta
　　    ON a.au_id = ta.au_id INNER JOIN titles t
　　    ON t.title_id = ta.title_id INNER JOIN publishers p
　　    ON t.pub_id = p.pub_id
　　 Where au_fname LIKE @firstname
　　    AND au_lname LIKE @lastname
　　 GO

　　 au_info2  存储过程可以用多种组合执行。下面只列出了部分组合：

　　 EXECUTE au_info2
　　 -- or
　　 EXECUTE au_info2 'Wh%'
　　 -- or
　　 EXECUTE au_info2 @firstname = 'A%'
　　 -- or
　　 EXECUTE au_info2 '[CK]ars[OE]n'
　　 -- or
　　 EXECUTE au_info2 'Hunter', 'Sheryl'
　　 -- or
　　 EXECUTE au_info2 'H%', 'S%'

四、系统存储过程

用户存储过程 : 用户也可以编写自己的存储过程，并把它存放在数据库中，供客户端调用。

       以上主要是用户存储过程，下面介绍一下系统存储过程。

 

系统存储过程 : SQL Server 本身提供了一些存储过程，用于管理有关数据库和用户的信息。

     它的目的在于能够方便地从系统表中查询信息，或者完成与更新数据库表相关的管理任务或其它的系统管理任务。

 

    系统存储过程可以在任意一个数据库中执行。创建并存放于系统数据库 master 中，并且名称以 sp_ 或者 xp_ 开头。

 

    部分系统存储过程：

 

 

      sp_addtype ：用于定义一个用户定义数据类型。

 

       sp_configure ：用于管理服务器配置选项设置。

 

       xp_sendmail ：用于发送电子邮件或寻呼信息。

 

       sp_stored_procedures ：用于返回当前数据库中的存储过程的清单。

 

       sp_help ：用于显示参数清单和其数据类型。

 

       sp_helptext ：用于显示存储过程的定义文本。

 

       sp_rename ：用于修改当前数据库中用户对象的名称。

 

       Sp_who: 用于显示使用数据库的当前用户

 

       sp_help ：用于显示参数清单和其数据类型。

 

       sp_depends ：用于显示存储过程依据的对象或者依据存储过程的对象

 

       sp_helptext ：用于显示存储过程的定义文本。

一个调用系统存储过程的例子：

exec sp_helptext query_book

五、注意事项 :

存储过程一般用来完成数据查询和数据处理操作，所以在存储过程中不可以使用创建数据库对象的语句，

 

        即在存储过程中一般不能含有以下语句：

 

       CREATE TABLE ； CREATE VIEW ； CREATE DEFAULT ；

 

        CREATE RULE ； CREATE TRIGGER ； CREATE PROCEDURE

 

六、返回值和状态信息

无论什么时候执行存储过程，总要返回一个结果码，用以指示存储过程的执行状态。

    如果存储过程执行成功，返回的结果码是 0 ；如果存储过程执行失败，返回的结果码一般是一个负数，它和失败的类型有关。

 

     我们在创建存储过程时，也可以定义自己的状态码和错误信息。

 

     执行存储过程：

 

    例：执行带参数的存储过程 , 查询大于岁的学生

 

       create proc show;3 ( @pno char(6) )

 

       as

 

       select * from person where Pno = @pno

 

       exec show;3 4

 

    例： CREATE Procedure sp_getstu;1

 

       AS

 

       SELECT * FROM 学生

 

    例：带参数的存储过程 , 查询大于指定年龄的学生

 

       CREATE proc sp_getstu;2 (@sage int)

 

       AS

 

       SELECT * FROM 学生 WHERE 年龄 > @sage

 

     例 : 带输出参数的存储过程 , 查询指定学生的年龄

CREATE proc sp_getstu;3 ( @name char(10) , @age int output )

 

       AS

 

       SELECT @age= 年龄 FROM 学生 WHERE 姓名 = @name

 

 

 

       Declare @sage int

 

       Exec sp_getstu;3 ' 张三 ',@sage

 

       Print @sage

 

     例：带参数和返回状态值的存储过程。

 

       CREATE PROCedure sp_getstu;3 (@sage int =NULL )

 

       AS

 

       IF @sage IS NULL

 

           BEGIN

 

              PRINT ' 必须提供一个数值作参数！ '

 

              RETURN 13

 

           END

 

       IF NOT EXISTS (SELECT * FROM student WHERE sage > @sage)

 

           BEGIN

 

              PRINT ' 没有满足条件的记录！ '

 

              RETURN -103

 

           END

 

           SELECT * FROM student WHERE sage > @sage

 

           RETURN 0

 

       DECLARE @status int

 

       EXECUTE @status=sp_getstu;3 22

 

       print @status

 

七、存储过程中游标的使用

1 、需要游标的数据操作

 

    当 select 语句的结果中包含多个元组时，使用游标可以逐个存取这些元组

 

    活动集： select 语句返回的元组的集合

 

    当前行：活动集中当前处理的那一行。游标即是指向当前行的指针。

 

2 、游标分类

 

    滚动游标：游标的位置可以来回移动，可在活动集中取任意元组。

 

    非滚动游标：只能在活动集中顺序地取下一个元组。

 

    更新游标：数据库对游标指向的当前行加锁，当程序读下一行数据时，本行数据解锁，下一行数据加锁。

 

3 、定义与使用游标的语句

 

    declare ：

 

        declare  游标名 [scroll]  cursor  for select 语句 [for update [of 列表名 ]]

定义一个游标，使之对应一个 select 语句

 

       for update 任选项，表示该游标可用于对当前行的修改与删除

 

    open

 

       打开一个游标，执行游标对应的查询，结果集合为该游标的活动集

 

       open  游标名

 

    fetch

 

       在活动集中将游标移到特定的行，并取出该行数据放到相应的变量中

 

       fetch [next | prior | first | last | current | relative n | absolute m] 游标名 into  [ 变量表 ]

 

    close

 

       关闭游标，释放活动集及其所占资源。需要再使用该游标时，执行 open 语句

 

       close  游标名

 

    deallocate

 

       删除游标，以后不能再对该游标执行 open 语句

 

       deallocate 游标名

 

    @@FETCH_STATUS

 

        返回被 FETCH 语句执行的最后游标的状态 .

 

       0 fetch 语句成功

 

        -1 fetch 语句失败

 

        -2 被提取的行不存在

4 、游标实例

     例 : 查询电子商务系学生信息 , 性别为女输出为 female, 否则输出为 male ？

 

       declare c1 cursor for select sno,sname,ssex from student where sdept='ec'

 

       declare @sno char(10),@sname char(10),@ssex char(2)

 

       Open c1

 

       Fetch c1 into @sno,@sname,@ssex

 

       While @@fetch_status==0

 

       Begin

 

       if @ssex=' 女 '

 

           begin  set @ssex='female' end

 

       else

 

           begin set @ssex='male' end

 

       Select @sno,@sname ,@ssex

 

           Fetch c1 into @sno,@sname,@ssex

 

       end

例：
    ALTER PROC [dbo].[dnt_UserRecoveryByUserName]
    @username    NVARCHAR(50)
AS

BEGIN

    DECLARE    @uid INT;
    DECLARE    @tid INT;
    DECLARE    @replies INT;
    DECLARE    @temp varchar(50);

    SET @uid        = (SELECT TOP(1) uid FROM dnt_users WHERE username = '@username');
    SET @tid        = 0;
    SET @replies    = 0;

    UPDATE dnt_users SET accessmasks = 0 WHERE uid = @uid;
    UPDATE dnt_userforum SET groupid = 5 WHERE groupid = 4 AND uid = @uid;
    UPDATE dnt_posts SET invisible = 0 WHERE invisible = -1 AND posterid = @uid;

    -- 定义一游标
    DECLARE Ctemp CURSOR FOR SELECT tid FROM dnt_topics WHERE posterid = @uid FOR READ only -- FOR UPDATE

    OPEN Ctemp  
        FETCH next FROM Ctemp INTO @tid;
        WHILE (@@fetch_status = 0)
        BEGIN
            SET @replies = (SELECT COUNT(1) FROM dnt_posts WHERE tid = @tid AND layer > 0);
             UPDATE dnt_topics SET replies = @replies WHERE posterid = @uid AND tid = @tid;
            FETCH next FROM Ctemp INTO @tid;
        END
    CLOSE Ctemp;
    DEALLOCATE Ctemp;

END