sql存储过程实例精讲

三、实例讲解
实例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_helptextquery_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 studentWHERE 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