MS SQL Server:存储过程实例

转载 2008年10月03日 21:05:00
存储过程实例
1. T-SQL 创建存储过程

例1: 使用简单的存储过程

USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspGetAllEmployees', 'P' ) IS NOT NULL
    DROP PROCEDURE HumanResources.uspGetAllEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
    SELECT LastName, FirstName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment;
GO


uspGetEmployees 存储过程可通过以下方式执行:


EXECUTE HumanResources.uspGetAllEmployees;
GO
-- Or
EXEC HumanResources.uspGetAllEmployees;
GO
-- Or, if this procedure is the first statement within a batch:
HumanResources.uspGetAllEmployees;



例2:带有参数的 存储过程执行方法
--- Bad
EXEC DBO.PRO_TEST_2 2, 'B'
--- Good
EXEC DBO.PRO_TEST_2 @ID = 2, @NAME = 'B'



2. 使用 RETRUN
从存储过程中返回值一体式错误的方法之一是使用 RETURN 语句。给语句可以立刻停止存储过程的执行,并将控制权转移回到存储过程之外。因此,任何位于 RETURN 语句之后的语句都不会被执行。

使用 RETURN 示例:
CREATE PROCEDURE CustomerDetails.apf_CustBalances
        @CustID int,
        @ClearedBalance money OUTPUT , @UnclearedBalance money OUTPUT
AS
BEGIN
        SELECT @ClearedBalance = ClearedBalance,
                @UnclearedBalance = UnclearedBalance
        FROM Customers
        WHERE CustomerId = @CustID;
       
        RETURN @@Error



执行该存储过程:
DECLARE @ClearedBalance Money , @UnClearedBalance Money ;
DECLARE @RetVal int;
EXECUTE @RetVal = CustomerDetails.apf_CustBalances 1,
                                                @CleaeredBalance OUTPUT,
                                                @UnClearedBalance OUTPUT

SELECT @RetVal AS ReturnValue ,
        @CleaeredBalance AS CleaeredBalance,
        @UnClearedBalance AS UnClearedBalance
GO



3. 使用 OUTPUT 游标参数
OUTPUT 游标参数用来将存储过程的局部游标传递回执行调用的批处理、存储过程或触发器。

首先,创建在 Currency 表上声明并打开一个游标的过程:
USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.uspCurrencyCursor;
GO
CREATE PROCEDURE dbo.uspCurrencyCursor
    @CurrencyCursor CURSOR VARYING OUTPUT
AS
    SET @CurrencyCursor = CURSOR
    FORWARD_ONLY STATIC FOR
      SELECT CurrencyCode, Name
      FROM Sales.Currency;
    OPEN @CurrencyCursor;
GO



接下来,运行以下批处理:声明一个局部游标变量,执行上述过程以将游标赋值给局部变量,然后从该游标提取行。
USE AdventureWorks;
GO
DECLARE @MyCursor CURSOR;
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
     FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO


4. 使用 WITH RECOMPILE 选项
如果为过程提供的参数不是典型的参数,并且新的执行计划不应被缓存或存储在内存中,则 WITH RECOMPILE 子句会很有用。
USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspproduct_by_vendor', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.uspProductByVendor;
GO
CREATE PROCEDURE dbo.uspProductByVendor @Name varchar(30) = '%'
WITH RECOMPILE
AS
    SELECT v.Name AS 'Vendor name', p.Name AS 'Product name'
    FROM Purchasing.Vendor AS v
    JOIN Purchasing.ProductVendor AS pv
      ON v.VendorID = pv.VendorID
    JOIN Production.Product AS p
      ON pv.ProductID = p.ProductID
    WHERE v.Name LIKE @Name;
GO


5. 使用 WITH ENCRYPTION 选项
以下示例将创建 HumanResources.uspEncryptThis 存储过程。
USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.uspEncryptThis', 'P' ) IS NOT NULL
    DROP PROCEDURE HumanResources.uspEncryptThis;
GO
CREATE PROCEDURE HumanResources.uspEncryptThis
WITH ENCRYPTION
AS
    SELECT EmployeeID, Title, NationalIDNumber, VacationHours, SickLeaveHours
    FROM HumanResources.Employee;
GO


如以下示例所示,WITH ENCRYPTION 选项可阻止返回存储过程的定义。
运行 sp_helptext:
EXEC sp_helptext 'HumanResources.uspEncryptThis';

下面是结果集:
The text for object 'HumanResources.uspEncryptThis' is encrypted.

直接查询 sys.sql_modules 目录视图:
USE AdventureWorks;
GO
SELECT definition FROM sys.sql_modules
WHERE object_id = OBJECT_ID('HumanResources.uspEncryptThis');
GO

下面是结果集:
definition
----------------------
NULL

(1 row(s) affected)



6. 使用延迟名称解析
以下示例将创建 uspProc1 过程。该过程使用延迟名称解析。尽管引用的表在编译时不存在,但仍能创建存储过程。但是,执行过程时表必须存在。

USE AdventureWorks;
GO
IF OBJECT_ID ( 'dbo.uspProc1', 'P' ) IS NOT NULL
    DROP PROCEDURE dbo.uspProc1;
GO
CREATE PROCEDURE dbo.uspProc1
AS
    SELECT column1, column2 FROM table_does_not_exist
GO



若要验证是否已创建了存储过程,请运行以下查询:
USE AdventureWorks;
GO
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.uspproc1');



下面是结果集:
definition
------------------------------------------------------------------
CREATE PROCEDURE uspproc1
AS
    SELECT column1, column2 FROM table_does_not_exist

(1 row(s) affected)

MS SQL Server 存储过程学习(2)数据库存储过程详解

MS SQL Server 存储过程详解  存储过程的优缺点   ◆优点:   执行速度更快。存储过程只在创造时进行编译,而一般SQL语句每执行一次就编译一次,所以使用存储过程执行速度更快。 ...
  • szzhuyike
  • szzhuyike
  • 2013年02月06日 15:48
  • 1348

SQL Server 存储过程示例

--有输入参数的存储过程-- create proc GetComment (@commentid int) as select * from Comment where CommentID=@com...
  • yan309271291
  • yan309271291
  • 2014年08月08日 13:34
  • 1628

Sql Server 常用系统存储过程大全

EXEC xp_cmdshell --*执行DOS各种命令,结果以文本行返回。 EXEC xp_fixeddrives --*查询各磁盘/分区可用空间 EXEC xp_loginconfig --...
  • peng790
  • peng790
  • 2016年08月16日 14:40
  • 1886

在MS SQL server2000中创建和调用存储过程

  • 2008年02月02日 15:34
  • 2KB
  • 下载

MS SQL Server7系统存储过程

  • 2008年08月22日 19:32
  • 672KB
  • 下载

MS Sql Server中存储过程与触发器的安全隐患

MS Sql Server中存储过程与触发器的安全隐患        存储过程与触发器是Sql Server进行数据库开发与管理经常使用的两个对象,但是如果对存储过程与触发器的权限设置不当,则会...
  • HEROWANG
  • HEROWANG
  • 2012年06月08日 09:51
  • 1955

MS SQL Server 分页存储过程

以前经常被人家问 SQL server 如何实现分页?当一张表里面有数十万条或者更多的数据时,该怎么办?如果数据库是 SQL Server 2000,我会回答:TOP,使用 TOP 嵌套查询(或者临时...
  • IQuicksandI
  • IQuicksandI
  • 2011年10月27日 09:21
  • 670

Delphi、MS SQL Server开发环境下存储过程的使用

====================================================== 注:本文源代码点此下载 =============================...
  • javazhuanzai
  • javazhuanzai
  • 2012年01月16日 04:16
  • 254

MS SQL Server 存储过程学习(2)数据库存储过程详解

MS SQL Server 存储过程详解  存储过程的优缺点   ◆优点:   执行速度更快。存储过程只在创造时进行编译,而一般SQL语句每执行一次就编译一次,所以使用存储过程执行速度更快。 ...
  • szzhuyike
  • szzhuyike
  • 2013年02月06日 15:48
  • 1348

ms sql server 2005 存储引擎6-8章.rar

  • 2009年05月24日 19:53
  • 16.15MB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MS SQL Server:存储过程实例
举报原因:
原因补充:

(最多只允许输入30个字)