将 SQL 过程重写为 SQL 用户定义函数

可将简单 SQL 过程重写为 SQL 用户定义函数，以在数据库管理系统中最大程度地提高性能。

关于此任务

对于过程和函数而言，它们的例程体都是使用可能包含 SQL PL 的复合块实现的。在过程和函数中，相同 SQL PL 语句包括在由 BEGIN 和 END 关键字绑定的复合块中。

过程

将 SQL 过程转换为 SQL 函数时，有一些事项需要注意：

执行此操作的主要和唯一原因是逻辑仅查询数据时改进例程性能。
在标量函数中，您可能必须声明用于保存返回值的变量，以避免您不能直接向函数的任何输出参数指定值的事实。用户定义标量函数的输出值仅在函数的 RETURN 语句中指定。
如果 SQL 函数将修改数据，那么必须使用 MODIFIES SQL 子句显式创建该函数，以便它可包含修改数据的 SQL 语句。

示例

在以下示例中，将显示逻辑上等价的 SQL 过程和 SQL 标量函数。从功能上看，这两个例程在给定相同输入值的情况下提供相同输出值，但它们以略有不同的方式实现和调用。


  CREATE PROCEDURE GetPrice (IN Vendor CHAR(20), 
                             IN Pid INT, 
                             OUT price DECIMAL(10,3)) 
  LANGUAGE SQL 
  BEGIN 
  
    IF Vendor = 'Vendor 1' 
     THEN SET price = (SELECT ProdPrice FROM V1Table WHERE Id = Pid); 
    ELSE IF Vendor = 'Vendor 2' 
     THEN SET price = (SELECT Price FROM V2Table 
	                   WHERE Pid = GetPrice.Pid); 
    END IF; 
  END

此过程接收两个输入参数值，并返回输出参数值，输出参数值是根据输入参数值有条件确定的。它使用 IF 语句。此 SQL 过程通过执行 CALL 语句调用。例如，可通过 CLP 执行以下操作：


  CALL GetPrice( 'Vendor 1',  9456, ?)

SQL 过程可重写为逻辑上等价的 SQL 表函数，如下所示：


  CREATE FUNCTION GetPrice (Vendor CHAR(20), Pid INT)  
      RETURNS  DECIMAL(10,3) 
  LANGUAGE SQL  MODIFIES SQL
  BEGIN 
    DECLARE price DECIMAL(10,3); 

    IF Vendor = 'Vendor 1' 
      THEN SET price = (SELECT ProdPrice FROM V1Table WHERE Id = Pid); 
    ELSE IF Vendor = 'Vendor 2' 
      THEN SET price = (SELECT Price FROM V2Table 
      			      WHERE Pid = GetPrice.Pid); 
    END IF; 
  
    RETURN price; 
  END

此函数接收两个输入参数值，并根据输入参数值有条件返回单个标量值。它需要声明并使用局部变量 price 来保存要返回的值直到函数返回，而 SQL 过程可将输出参数用作变量。从功能上看，这两个例程执行相同逻辑。

当然现在其中每个例程的执行接口不同。与简单地通过 CALL 语句调用 SQL 过程不同，SQL 函数必须在允许使用表达式的 SQL 语句中调用。在大多数情况下，这不是问题，如果有意立即操作例程返回的数据，这可能实际上有益。以下是有关如何调用 SQL 函数的两个示例。

可使用 VALUES 语句对它进行调用：


  VALUES (GetPrice('Vendor 1', 9456))

还可在 SELECT 语句中对它进行调用，例如，可能从表中查询值并根据函数结果过滤行：


  SELECT VName FROM Vendors WHERE GetPrice(Vname, Pid) < 10