SQL Server 中的例程分析

例程（routine）是为了计算结果或执行任务而对代码进行封装的一种编程对象。SQL Server 支持三种例程：用户定义函数、存储过程等。

 

一.用户定义函数

用户定义函数（UDF，user-defined function）的目的是要封装计算的逻辑处理，有可能需要基于输入的参数，并返回结果。SQL Server支持两种用户定义函数：标量UDF和表值UDF。标量UDF只返回单个数据值。而表值UDF则返回一个表。使用UDF的优点之一是在查询中集成UDF，对于查询中返回单个值的表达式，在其出现的位置上也能够使用标量UDF。表值UDF只能在查询的FROM子句中出现。

UDF不允许有任何副作用。这一规定明显的含义是UDF不能对数据库中的任何架构或数据进行修改。此外，其他一些引起副作用的操作则不那么明显。例如，调用RAND函数返回一个随机值，或调用NEWID函数返回一个GUID，就有副作用。每当调用RAND函数，但不指定种子值时，SQL Server就会根据以前对RAND的调用而生成一个随机的种子值。因为，当调用RAND函数时，SQL Server内部需要保存相关的信息。与之类似，每当调用NEWID函数时，系统也需要设置某种信息，以便下一次调用NEWID时使用。因为RAND和NEWID函数都有副作用，所以在UDF中不允许使用它们。

IF OBJECT_ID('dbo.fn_age') IS NOT NULL DROP FUNCTION dbo.fn_age;
GO

CREATE FUNCTION dbo.fn_age
(
    @birthdate AS DATETIME,
    @eventdate AS DATETIME
)
RETURNS INT
AS
BEGIN        
    RETURN
        DATEDIFF(YEAR,@birthdate,@eventdate) -
            CASE WHEN (100*MONTH(@eventdate)+DAY((@eventdate)))<(100*MONTH(@birthdate)+DAY(@birthdate))
                 THEN 1 ELSE 0
             END
END    

一个函数体内可以包含多个RETURN子句，也可以包含流程控制逻辑、计算逻辑等等。但是函数必须由一个RETURN子句返回一个值。

SELECT
    empid,firstname,lastname,birthdate
    dbo.fn_age(birthdate,CURRENT_TIMESTAMP) AS age
FROM HR.Employees

 

二.存储过程

存储过程是封装了T-SQL代码的服务器端例程。存储过程可以有输入和输出参数，可以返回多个查询的结果集，也允许调用具有副作用的代码。通过存储过程不但可以对数据进行修改，也可以对数据库架构进行修改。

作为一个示例，以下代码创建了一个存储过程Sales.usp_GetCustomerOrders该存储过程接受一个客户ID（@custid）和一个日期范围（@fromdate和@todate）作为输入参数，返回Sales.Orders表中由指定客户在指定日期范围内所下的订单组成的结果集，同时也将受查询影响的行为作为输出参数（@numrows）。

IF OBJECT_ID('Sales.usp_GetCustomerOrders','P') IS NOT NULL
    DROP PROC Sales.usp_GetCustomerOrders;
GO    

CREATE PROC Sales.usp_GetCustomerOrders
    @custid AS INT,
    @fromdate AS DATETIME='19000101',
    @todate AS DATETIME = '99991231',
    @numrows AS INT OUTPUT
AS
    SET NOCOUNT ON ;
    SELECT orderid,custid,empid,orderdate
    FROM Sales.Orders
    WHERE custid = @custid
        AND orderdate >= @fromdate
        AND orderdate < @todate;
    SET @numrows == @@rowcount;
GO

下面示例执行该存储过程，请求由客户ID等于1的客户在2007中下过的所有订单。代码将输出参数@numrows的值提取到局部变量@rc中，再返回这个变量的值，以显示查询影响了多少行记录：

DECLARE @rc AS INT 
EXEC Sales.usp_GetCustomerOrders
    @custid = 1,
    @fromdate = '2007101',
    @todate = '20080101'
    @numrows = @rc OUTPUT;
SELECT @rc AS numrows

 

 

好了，本篇文章就介绍到这儿，欢迎大家留言交流；喜欢或有帮助到您的话，点个赞或推荐支持一下！

 

转载于:https://www.cnblogs.com/johnvwan/p/9498059.html