SQL存储过程实例（摘录）

存储过程

存储过程（stored procedure）有时也称为sproc，它是真正的脚本——或者更准确地说，它是批处理（batch）——它存储于数据库中而不是在单独的文件中。存储过程中有输入参数、输出参数以及返回值等，而脚本中不会真的有这些内容，不过该比较相差也不是太多。

目前SQL Server中唯一的编程语言依然是T-SQL，当考虑真正的编程语言时，它离过程语言还是有差距的。但是，当论及T-SQL的作用——数据定义、操作和访问的时候，它要强过C、C++、Visual Basic、 Java、Delphi以及其他的语言。但是T-SQL的强大功能在数据访问和管理上也是有限的。简而言之，它有能力完成大部分简单的任务，但是它不是完成这些任务的最合适的方式。

     

 创建存储过程：基本语法

    在数据库中，创建存储过程和创建其他对象的过程一样。存储过程的基本语法如下：

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ] 
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ] 
AS { <sql_statement> [;][ ...n ] | <method_specifier> }
[;]
<procedure_option> ::= 
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

<sql_statement> ::= 
{ [ BEGIN ] statements [ END ] }

<method_specifier> ::=
EXTERNAL NAME assembly_name.class_name.method_name

在这里仍然使用基本的CREATE  <对象类型>  < 对象名称>语法，它是每个CREATE语句的主干。在对存储过程命名完之后，接着是参数列表。参数是可选择的，稍后讨论。

最后，在关键字AS后是实际的代码。

 

基本存储过程示例：

USE Northwind
GO
CREATE PROC spShippers
AS
SELECT * FROM Shippers

 

如果此处需要考虑为什么要在CREATE语法之前加上GO这个关键词（如果只是运行简单的SELECT语句，就不需要使用它），那是因为大多数CREATE语句不能与其他任何代码共享批处理。事实上，在CREATE TABLE中不适用GO可能有危险。在这种情况下，在CREATE PROC语句中使用USE命令式禁止的，这样会产生错误。

既然创建了存储过程，那么执行它来看一下结果：

EXEC spShippers

使用ALTER改变存储过程

当使用T-SQL 编辑存储过程时，需要记住的是它完全替代了现存的存储过程。使用ALTER PROC 和CREATE PROC 语句的唯一区别在于：

l  ALTER PROC期望找到现存的存储过程，而CREATE则不是。

l  ALTER PROC 保留了已经建立的存储过程的任何权限。它在系统对象中保留了相同的对象ID并允许保留依赖关系。流入，如果过程A调用过程B，并删除和重新创建了过程B，那么就不能再看到这两者间的依赖关系。如果使用ALTER，则依赖关系仍然存在。

l  ALTER PROC 在其他对象上保留了任何依赖关系的信息，这些对象可以调用修改的存储过程。

后面这两点非常重要。

 

注意：

如果执行DROP、然后使用CREATE，这和使用ALTER PROC语句一样，几乎都能得到相同的效果，除了一个很重要的区别——如果使用DROP和CREATE，则需要完全重新建立权限，权限规定了可以使用以及不能使用存储过程的用户。

 

删除存储过程

DROP  PROC|PROCDURE   <sproc name>

 这样就完成了删除。

 

 参数化

在存储过程的外部，可以通过位置（position）或者引用（reference）传入参数。在存储过程的内部，不用关心参数传入的方式——因为使用同样的方式声明参数。

 

声明参数

声明参数需要以下2~4部分信息：

l  名称（name）

l  数据类型（datatype）

l  默认值（default value）

l  方向（direction）

语法如下：

@parameter_name   [AS]   datatype [ = default | NULL]  [VARYING]  [OUTPUT|OUT]

名称有一个简单的规则集合。首先，它必须以@开始。此外，命名规则除了不能有嵌套的空格外，它和我们以前介绍的命名规则是相同的。

数据类型和名称一样，必须像变量一样声明，可以使用SQL Server内置的或用户定义的类型。

变量与参数不同的地方在于，变量初始化为NULL，参数则不是。事实上，如果不提供默认值，则会假设需要参数，并且当调用存储过程的时候需要提供一个初始值。为了提供默认值，可以在声明完数据类型后简单地加上一个“=”符号并提供默认值。一旦完成了这个过程，则使用存储过程的用户可以决定要么不提供参数的值，要么提供它们各自的值。

此处创建另一个存储过程，这里使用几个输入参数来创建Shippers表的新记录。

USE Northwind
GO
CREATE PROC spInsertShipper
@CompanyName   nvarchar(40),
@Phone         nvarchar(24)
AS
INSERT INTO Shippers
VALUES
(@CompanyName, @Phone)

 

运行此存储过程，使用这个新的存储过程来插入新的数据：

EXEC  spInsertShipper  'Speedy Shippers,  Inc.',  '(503) 555-5566'

现在运行第一个存储过程并看一下结果：

EXEC spShippers

你会发现已经插入了记录，而且为该记录填入了新的标识。

因为并没有为任何参数提供默认值，所以需要提供两个参数。这意味着为了成功运行该存储过程，则必须提供两个参数。此处可以只提供一个或零个参数，再次执行该存储过程来验证这一点。

EXEC spInsertShipper 'Speedy Shippers, Inc.'

SQL Server会马上通知你这样做的错误

 

提供默认值

为了使参数是可选的，可以提供默认值。可以在数据类型之后但在逗号之前指定默认值，方法是添加“=”符号和作为默认值的值。

再次重新建立INSERT存储过程，只是这次可以不需要电话号码作为参数：

USE Northwind

GO

CREATE PROC spInsertShipperOptionalPhone

@CompanyName   nvarchar(40),

@Phone         nvarchar(24) = NULL

AS

INSERT INTO Shippers

VALUES

(@CompanyName, @Phone)

重新发出命令，但是这次使用新的存储过程：

EXEC spInsertShipperOptionalPhone 'Speedy Shippers, Inc'

这次一切顺利，成功插入了新的记录。

看看结果：

EXEC spShippers

在这种特殊的情况下，可以把默认值设为NULL，但也可以把这个值设为与参数的数据类型相兼容的任意值，为该参数建立默认值。同样，可以看到并没有必要为两个值都建立默认值——可以使其中一个有默认值，而另一个没有。

 

创建输出参数

如果你想向Northwind中的Orders表中插入一条新的记录，但是也需要向Order Details表中插入详细的记录。为了保持关系之间的联系，必须向Order Details表插入数据之前知道Orders记录的标识。这个存储过程和SpInsertShippers看上去很相似，除了它有和表中不同列相匹配的参数，而且ui重要的是，它有输出参数，该参数是插入产生的标识值。

USE Northwind
GO
CREATE PROC spInsertOrder
@CustomerID       nvarchar(5),
@EmployeeID       int,
@OrderDate        datetime      = NULL,
@RequiredDate     datetime      = NULL,
@ShippedDate      datetime      = NULL,
@ShipVia          int,
@Freight          money,
@ShipName         nvarchar(40)  = NULL,
@ShipAddress      nvarchar(60)  = NULL,
@ShipCity         nvarchar(15)  = NULL,
@ShipRegion       nvarchar(15)  = NULL,
@ShipPostalCode   nvarchar(10)  = NULL,
@ShipCountry      nvarchar(15)  = NULL,
@OrderID          int      OUTPUT
AS
/* Create the new record */
INSERT INTO Orders
VALUES
(
@CustomerID,
@EmployeeID,
@OrderDate,
@RequiredDate,
@ShippedDate,
@ShipVia,
@Freight,
@ShipName,
@ShipAddress,
@ShipCity,
@ShipRegion,
@ShipPostalCode,
@ShipCountry
)
/* Move the identity value from the newly inserted record into
our output variable */
SELECT @OrderID = @@IDENTITY

 

 

现在来做一下实验，这次只通过引用而不是位置来设置参数值。为了能看到输出参数的工作方式，也需要在脚本中编写一些执行存储过程的测试代码：

USE Northwind
GO
DECLARE   @MyIdent   int

EXEC spInsertOrder
@CustomerID = 'ALFKI',
@EmployeeID = 5,
@OrderDate = '5/1/1999',
@ShipVia = 3,
@Freight = 5.00,
@OrderID = @MyIdent OUTPUT

SELECT @MyIdent AS IdentityValue

SELECT OrderID, CustomerID, EmployeeID, OrderDate, ShipName
FROM Orders
WHERE OrderID = @MyIdent

注意，并没有提供所有的参数。它们中的一些是可选的，并且决定不使用其中的一些参数，它们会取默认值。如果已经调用存储过程并在使用位置的参数中传递值，那么至少必须在参数列表中指出每个位置直到最后一个参数，这个参数需要提供值。

请你分析一下结果。

 

在存储过程之间以及调用脚本对它的使用的问题上，需要注意以下几点：

l  在存储过程声明中，输出参数需要使用OUTPUT关键字。

l  调用存储过程的时候必须使用OUTPUT关键字，就像声明该存储过程的时候一样。这给了SQL Server预先的警告，通知它参数所需要的特殊的处理。

l  赋给输出结果的变量不需要和存储过程中的内部参数拥有相同的名称。例如，在本例中，内部参数叫做@OrderID，而传给值的变量叫做@MyIdent。

l  需要使用EXEC（或EXECUTE）关键字，因为对存储过程的调用并不是批处理要做的第一件事（如果存储过程的调用时批处理要做的第一件事，则可以不使用EXEC），无论如何还是建议使用它。

 

流程控制语句

   流程控制语句是任何编程语言所必需的语句。不敢想像如果不能在代码中根据不同的条件修改要运行的命令会是多么糟糕。T-SQL 提供了大多数流控制的典型的选择，包括：

l  IF…..ELSE

l  GOTO

l  WHILE

l  WAITFOR

l  TRY/CATCH

l  CASE

举例：

DATEDIFF函数的语法如下：

DATEDIFF ( <datepart> ， <startdate>,  <enddate> )

DATEDIFF比较两个日期，它可以比较日期数据的任意部分，可以从年到毫秒。下面的举例中，作为日（day）的简单dd就足够了，把它放入IF语句中：

IF DATEDIFF(dd, @OrderDate, GETDATE()) > 7

如果返回值超过7天，也就是，晚了7天以上——那么就需要改变插入的值：

SELECT @OrderDate = NULL

 

完整如下：

USE Northwind
GO
CREATE PROC spInsertDateValidatedOrder
@CustomerID       nvarchar(5),
@EmployeeID       int,
@OrderDate        datetime     = NULL,
@RequiredDate     datetime     = NULL,
@ShippedDate      datetime     = NULL,
@ShipVia          int,
@Freight          money,
@ShipName         nvarchar(40) = NULL,
@ShipAddress      nvarchar(60) = NULL,
@ShipCity         nvarchar(15) = NULL,
@ShipRegion       nvarchar(15) = NULL,
@ShipPostalCode   nvarchar(10) = NULL,
@ShipCountry      nvarchar(15) = NULL,
@OrderID               int      OUTPUT
AS
/* Test to see if supplied date is over seven days old, if so
replace with NULL value                                 */
IF DATEDIFF(dd, @OrderDate, GETDATE()) > 7
SELECT @OrderDate = NULL
/* Create the new record */
INSERT INTO Orders
VALUES
(
@CustomerID,
@EmployeeID,
@OrderDate,
@RequiredDate,
@ShippedDate,
@ShipVia,
@Freight,
@ShipName,
@ShipAddress,
@ShipCity,
@ShipRegion,
@ShipPostalCode,
@ShipCountry
)
/* Move the identity value from the newly inserted record into
our output variable */
SELECT @OrderID = @@IDENTITY

现在运行一下原来用作spInsertOrder存储过程的相同测试脚本，此处对这个测试脚本进行了很少的修改：

USE Northwind
GO
DECLARE   @MyIdent   int

EXEC spInsertDateValidatedOrder
@CustomerID = 'ALFKI',
@EmployeeID = 5,
@OrderDate = '5/1/1999',
@ShipVia = 3,
@Freight = 5.00,
@OrderID = @MyIdent OUTPUT
SELECT @MyIdent AS IdentityValue
SELECT OrderID, CustomerID, EmployeeID, OrderDate, ShipName
FROM Orders
WHERE OrderID = @MyIdent

这次，即使大部分的存储过程时相同的，但本例改变了存入数据库中的数据，因此，可以看到OrderDate变为NULL，IF语句丢弃了这个非法值并且在插入前改变了该值。

 

在存储过程中实现ELSE语句

这次并不是创建一个单独的存储过程，而是使用ALTER命令。需要记住的是，即使当使用ALTER语句的时候，也必须完全地重新定义存储过程：

 

USE Northwind
GO
ALTER PROC spInsertDateValidatedOrder
@CustomerID     nvarchar(5),
@EmployeeID     int,
@OrderDate      datetime     = NULL,
@RequiredDate   datetime     = NULL,
@ShippedDate    datetime     = NULL,
@ShipVia        int,
@Freight        money,
@ShipName       nvarchar(40) = NULL,
@ShipAddress    nvarchar(60) = NULL,
@ShipCity       nvarchar(15) = NULL,
@ShipRegion     nvarchar(15) = NULL,
@ShipPostalCode nvarchar(10) = NULL,
@ShipCountry    nvarchar(15) = NULL,
@OrderID        int      OUTPUT
AS
/* I don't like altering input parameters - I find that it helps in debugging
** if I can refer to their original values at any time. Therefore, I'm going
** to declare a separate variable to assign the end value we will be
** inserting into the table. */
DECLARE   @InsertedOrderDate   smalldatetime
/* Test to see if supplied date is over seven days old, if so
** replace with NULL value
** otherwise, truncate the time to be midnight */
IF DATEDIFF(dd, @OrderDate, GETDATE()) > 7
SELECT @InsertedOrderDate = NULL
ELSE
SELECT @InsertedOrderDate =
CONVERT(datetime,(CONVERT(varchar,@OrderDate,112)))
/* Create the new record */
INSERT INTO Orders
VALUES
(
@CustomerID,
@EmployeeID,
@InsertedOrderDate,
@RequiredDate,
@ShippedDate,
@ShipVia,
@Freight,
@ShipName,
@ShipAddress,
@ShipCity,
@ShipRegion,
@ShipPostalCode,
@ShipCountry
)
/* Move the identity value from the newly inserted record into
our output variable */
SELECT @OrderID = @@IDENTITY

USE Northwind
GO
ALTER PROC spInsertDateValidatedOrder
@CustomerID              nvarchar(5),
@EmployeeID              int,
@OrderDate        datetime     = NULL,
@RequiredDate     datetime     = NULL,
@ShippedDate      datetime     = NULL,
@ShipVia          int,
@Freight          money,
@ShipName         nvarchar(40) = NULL,
@ShipAddress      nvarchar(60) = NULL,
@ShipCity         nvarchar(15) = NULL,
@ShipRegion       nvarchar(15) = NULL,
@ShipPostalCode   nvarchar(10) = NULL,
@ShipCountry      nvarchar(15) = NULL,
@OrderID          int      OUTPUT
AS
/* I don't like altering input paramters - I find that it helps in debugging
** if I can refer to their original value at any time. Therefore, I'm going
** to declare a separate variable to assign the end value we will be
** inserting into the table. */
DECLARE   @InsertedOrderDate   smalldatetime
/* Test to see if supplied date is over seven days old, if so
** replace with NULL value
** otherwise, truncate the time to be midnight*/
IF DATEDIFF(dd, @OrderDate, GETDATE()) > 7
BEGIN
SELECT @InsertedOrderDate = NULL
PRINT 'Invalid Order Date'
PRINT 'Supplied Order Date was greater than 7 days old.'
PRINT 'The value has been reset to NULL'
END
ELSE
BEGIN
SELECT @InsertedOrderDate =
CONVERT(datetime,(CONVERT(varchar,@OrderDate,112)))
PRINT 'The Time of Day in Order Date was truncated'
END
/* Create the new record */
INSERT INTO Orders
VALUES
(
@CustomerID,
@EmployeeID,
@InsertedOrderDate,
@RequiredDate,
@ShippedDate,
@ShipVia,
@Freight,
@ShipName,
@ShipAddress,
@ShipCity,
@ShipRegion,
@ShipPostalCode,
@ShipCountry
)
/* Move the identity value from the newly inserted record into
our output variable */
SELECT @OrderID = @@IDENTITY

 

现在重新运行原来的批处理，请分析结果。

 

把代码分组为块

有时需要把一组语句当成一条语句来看待（如果执行其中的一条语句，那么就会执行所有的语句，否则一条都不执行）。例如，IF语句默认地会把紧接着它的语句视为条件代码的一部分。如果需要运行多条语句的条件时该怎么做呢？

SQL Server提供了把代码分组为块的方法，可以认为这个块是属于一起的。这个块以BEGIN语句开始，然后直到END语句结束。

注意代码块的嵌套。每种情况下，把内层块作为外层块代码的一部分。虽然对BEGIN……END嵌套代码块的深度没有限制，但是建议层数越少越好。

 

简单CASE语句例程分析

USE Northwind
GO
SELECT TOP 10 OrderID, OrderID % 10 AS 'Last Digit', Position =
CASE OrderID % 10
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
ELSE 'Something Else'
END
FROM Orders

USE Northwind
GO
SELECT TOP 10 OrderID % 10 AS "Last Digit",
ProductID,
"How Close?" = CASE OrderID % 10
WHEN ProductID THEN 'Exact Match!'
WHEN ProductID - 1 THEN 'Within 1'
WHEN ProductID + 1 THEN 'Within 1'
ELSE 'More Than One Apart'
END
FROM [Order Details]
WHERE ProductID < 10
ORDER BY OrderID DESC

USE Northwind
GO
SELECT TOP 10 OrderID % 10 AS "Last Digit",
ProductID,
"How Close?" = CASE
WHEN (OrderID % 10) < 3 THEN 'Ends With Less Than Three'
WHEN ProductID = 6 THEN 'ProductID is 6'
WHEN ABS(OrderID % 10 - ProductID) <= 1 THEN 'Within 1'
ELSE 'More Than One Apart'
END
FROM [Order Details]
WHERE ProductID < 10
ORDER BY OrderID DESC

 

 

使用WHILE语句循环

见示例，这里要做的是通过WHILE循环和WAITFOR命令（下一节将介绍WAITFOR）来创建监视进程。此处会自动的更新每天的统计信息：

WHILE 1 = 1
BEGIN
WAITFOR TIME '01:00'
EXEC sp_updatestats
RAISERROR('Statistics Updated for Database', 1, 1) WITH LOG
END

这段代码会在每天晚上1点钟的时候更新每张表的统计信息并向SQL Server日志和Windows NT 应用程序日志中写入相应的日志项。

提示：
    可以看出使用无限的循环并不是通常对任务进行调度的方法。如果想每天运行一些程序，那么就使用Management Studioi建立作业（job）。另外，为了不是中打开连接，需要根据脚本执行的成功或失败去进行下面的动作。同样，可以使用电子邮件或net-send消息来处理完成的状态。

 

WITFOR语句

可能有这种情况，既不想在此刻完成某一动作，也不想在执行动作之前一直在那等待。可以使用WITFOR语句并让SQL Server做这个等待工作。语法：

WAITFOR

     DELARY  <’time’> | TIME  <’time’>

WAITFOR语句等待作为参数的指定的时间，要么指定具体时间，要么指定需要等待的总的时间。

1）DELAY 参数

DELAY参数指定等待的总时间。但是不能指定天数，而只能是小时、分钟、秒。允许延时的最大值为24小时。示例：

WAITFOR DELAY '01:00'

延时一个小时。

会在WAITFOR之前运行任何代码，接着运行到WAITFOR语句，然后等待一个小时，在这之后将运行WAITFOR之后的任何代码。

2）TIME参数

TIME参数制定了需要等待的具体的时间。同样，不能指定日期作为参数，而只能是24小时内的时间。例如：

WAITFOR TIME '01:00'

等到凌晨1点钟。

 

TRY/CATCH块

TRY/CATCH主要是指异常处理，后面会介绍。这里先记住：它们的作用——合适做某事。简而言之，如果代码没有任何类型的异常，或者错误“级别”是10或10以下的话，则会根据TRY块执行代码。但是，一旦代码出现的错误超过了10（11或更高）的话，则会马上转移到CATCH块中的第一条语句并从该处开始处理。

 

注意：

只要错误不是那种会马上终止脚本的错误，则CATCH语句块仍会运行下去。但是有些类型的错误会立即结束存储过程中所有代码的执行——在这种情况下，这意味着即使是CATCH块也不能执行。

 

通过返回值确认成功或失败

返回值指示了存储过程的成功或者失败，甚至是成功或失败的范围或属性。

RETURN的工作方式

事实上，不管是否提供返回值，程序会受到一个返回值。SQL Server默认地会在完成存储过程时自动返回一个0值。

为了从存储过程向调用代码返回值，只需要使用RETURN语句

RETURN   [< integer  value  to return  >]

注意：

返回值必须为整数。

理解RETURN语句最重要的是知道它是无条件地从存储过程中退出来的。这是指，无论运行到存储过程的何处，在调用RETURN语句之后将不会执行任何一行代码。

此处以一个简单的测试存储过程来说明RETURN语句影响的方式：

 

USE Northwind
GO
CREATE PROC spTestReturns
AS
DECLARE @MyMessage        varchar(50)
DECLARE @MyOtherMessage   varchar(50)
SELECT @MyMessage = 'Hi, it''s that line before the RETURN'
PRINT @MyMessage
RETURN
SELECT @MyOtherMessage = 'Sorry, but we won''t get this far'
PRINT @MyOtherMessage
RETURN

现在已经有了存储过程，但需要一小段脚本作测试。此处想看到的是：

输出的结果

RETURN语句返回的值

为了能捕获RETURN语句的值，需要在EXEC语句中把值赋给变量。例如：

 

DECLARE @Return int
EXEC @Return = spTestReturns
SELECT @Return

此处修改存储过程来验证需要传回的值，该值作为返回值：

USE Northwind
GO
ALTER PROC spTestReturns
AS
DECLARE @MyMessage        varchar(50)
DECLARE @MyOtherMessage   varchar(50)
SELECT @MyMessage = 'Hi, it''s that line before the RETURN'
PRINT @MyMessage
RETURN 100
SELECT @MyOtherMessage = 'Sorry, but we won''t get this far'
PRINT @MyOtherMessage
RETURN

此处重新运行测试脚本，会得到相同的结果和改变的返回值。

 

处理错误

在SQL Server中可能会发生3种常见的错误类型：

会产生运行时错误并终止代码运行的错误

SQL Server知道的错误，但是它不产生使代码停止运行的运行时错误。这些错误也可以称为内联（inline）错误。

在逻辑上很明显但在SQL Server中不太引起注意的错误。

有关错误处理部分内容很多，也相对复杂，请同学们在课后收集相关资料深入学习。由于时间关系，这里不再多述。

 

小结

根据SQL Server中的程序员编程的角度来说，本章是这本书中最重要的一章。

存储过程是SQL Server中代码的骨干。通过它，可以创建可复用的代码、并同时获得更好的性能和灵活性。可以使用多种你熟悉的其他编程语言的程序结构，但是存储过程并不意味着一切。

存储过程的优点包括：

通常更好的性能

通常作为安全隔离层来使用（控制数据库访问和更新的方式）

重用的代码

对代码进行划分（可以分装业务逻辑）

根据在运行时的动态建立灵活地运行

存储过程的缺点包括：
    在平台间不能移植（例如Oracle就有一种完全不同的存储过程的实现方法）

在某些情况下（实际上会影响性能）可能会因为错误的执行计划而锁定

 

练习

1、写一个存储过程，该存储过程在给定CustomerID的参数下能返回Northwind数据库中的Customer记录。

2、写一个存储过程，该存储过程接受Territory  ID、 Territory Description 和Region ID为参数，并且将它们作为新行插入到Northwind数据库中的Territories表中去。

3、修改练习2中的存储过程，并在插入前预先检查外键（RegionID）的存在。如果RegionID不存在，则抛出一个含有错误消息为“RegionID is not valid。 Please check your RegionID and try again”的错误。

4、修改练习2中的存储过程，使之处理不存在RegionID时的异常。捕获所有其他类型错误并提供通用的错误消息“An unhandled exception has occurred. Contact your system administration”。

 

 

完成上面的练习需要Northwind数据库 ,  还有上面内容有点多，想快速了解知识点的看官移步

练习的答案