65.9K
CodeProject 正在变化。 阅读更多。
Home

创建 Oracle 函数的示例

Wendelius
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.75/5 (10投票s)

2011年3月13日

CPOL

8分钟阅读

viewsIcon

167885

downloadIcon

1036

本文以使用基本 PL/SQL 构建简单的工具包（toolkit）开始，但也涵盖了一些更高级的技术，例如流水线（pipeline）。

引言

在撰写了几篇关于 Microsoft SQL Server 的文章后，我认为是时候写一些关于 Oracle 的内容了。这篇关于 Oracle 的第一篇文章描述了一些基本任务，但扩展到更高级的特性。

因此，本文的目的有二：为开发人员和管理员扩展和使用的工具包集合奠定基础，并描述 Oracle 功能可以扩展的其他一些技术。涵盖的主要领域是

  • 创建包
  • 定义常量并发布它们
  • 创建确定性函数
  • 使用 AUTHID
  • 创建返回结果集的函数
  • 创建流水线函数

创建实用程序架构

首先是为实用程序包创建一个新的架构。我决定创建一个新架构是出于几个原因

  • 安全性：可以授予实用程序的拥有者一些通常不会授予最终用户的权限。
  • 管理：当所有实用程序都集中在一个单独的、分离的架构中时，架构编译、导出等管理任务会变得更容易。

创建用户基本上很简单。在安装时，只需检查默认和临时表空间是否正确。但是，由于这个新用户将被授予访问 sys 用户表的权限，因此需要 SYSDBA 权限。

CREATE USER ToolkitUser 
   IDENTIFIED BY Hammer
   DEFAULT TABLESPACE USERS
   TEMPORARY TABLESPACE TEMP;
/
 
GRANT CREATE SESSION TO ToolkitUser;
/

GRANT RESOURCE TO ToolkitUser;
/

GRANT SELECT ON sys.v_$session TO ToolkitUser;
/ 

创建包

下载的脚本在一个文件中包含了包头和包体，以及此示例中所需的类型。出于方便的考虑，脚本的末尾为包创建了一个公共同义词。只要授予调用者必要的权限，公共同义词就可以保证在不指定架构的情况下调用该包。

该包包含几个简单的函数示例，仅用于了解基础知识。这些函数将不再详细讨论，但可以指出几点。

  • 尽管其中一些函数只是对原生 Oracle 函数的简短“别名”，但我希望创建一些更直观的版本。
  • 大多数函数被定义为 DETERMINISTIC，这意味着如果输入值相同，函数总是产生相同的结果。因此，这些函数可用于查询，也可用于索引。

包含的简单函数列在下面。我决定创建大多数与日期相关的函数，因为它们经常会让人头疼。

  • Day：返回给定日期的星期数。
  • Month：返回给定日期的月份数。
  • Year：返回给定日期的月份数。（此处原文有误，应为年份数）
  • FirstDayOfMonth：返回给定日期的月份第一天。
  • FirstDayOfYear：返回给定日期的年份第一天。
  • LastDayOfMonth：返回给定日期的月份最后一天。
  • DaysInMonth：返回给定日期的月份天数。
  • DaysLeftInYear：返回给定日期当年剩余的天数。
  • WorkingDaysLeftInYear：返回给定日期当年剩余的工作日数。

Holiday 和 Toolkit-package 脚本都应在 ToolkitUser 下运行，因此在创建用户后，使用 ToolkitUser 的凭据建立新连接并执行脚本。如果您使用 SQL*Plus（或等效工具），请在包头和包体末尾添加必要的斜杠（/）。

发布常量

常量可以添加到包头中，然后在整个包中使用。但是，包外的 SQL 语句无法引用常量。在 Toolkit-package 中，我为 PI 定义了一个常量，并且可以在函数或存储过程内部使用它，但为了使用它，我必须创建一个小的包装函数来处理该常量。在此示例中，PI_CONSTANT 使用 PI 函数发布。

常量声明是

PI_CONSTANT CONSTANT NUMBER := 3.14159265358979323846264338327950288419;

以及发布函数

FUNCTION Pi RETURN NUMBER DETERMINISTIC IS
BEGIN
   RETURN ( PI_CONSTANT );
END;

现在我可以使用 PI 的值来计算例如一个 30 厘米披萨的面积

SELECT Toolkit.PI * POWER(30/2, 2) FROM Dual;

安全性，AUTHID DEFINER 示例

由于包可以定义为使用调用者或定义者的权限运行，因此它可以用于封装调用者通常不允许的操作。这与独立过程和函数的情况相同。

让我们创建一个小函数，如果调用者有活动事务，则返回 1。为了做到这一点，我们将需要访问 v$session 以获取事务状态对象的地址（如果存在）。在本文前面，当创建用户时，它被授予了对 sys.v_$session 的 SELECT 访问权限。这是包含会话信息的实际动态视图。现在，由于拥有包的用户（ToolkitUser）可以访问该视图，我们可以在包中定义该函数为

FUNCTION TransactionActive RETURN NUMBER IS 
   nActive NUMBER;
BEGIN
   SELECT DECODE( vs.TADDR, NULL, 0, 1)
   INTO nActive
   FROM v$session vs 
   WHERE vs.SID = SYS_CONTEXT('USERENV', 'SID');
 
   RETURN ( nActive );
END;

如果 TADDR 为 NULL，则当前没有事务。在函数中，我们需要知道我们正在查询哪个会话的状态，所以我们需要获取调用者的 SID。这通过 SYS_CONTEXT 函数完成。

包本身是使用 definer 权限创建的

CREATE OR REPLACE PACKAGE Toolkit
AUTHID DEFINER
 ...

现在调用者不必访问 sys.v_$session，只需访问 Toolkit 包即可了解会话是否有活动事务。要获得结果，只需尝试

SELECT Toolkit.TransactionActive FROM Dual;

返回结果集的函数

函数也可以用于返回结果集。要测试这一点，让我们创建一个返回给定范围内的数字的函数。首先，我们必须定义一个表示单行的类型，然后定义一个包含数据的表类型。这两个语句都包含在包脚本中。

---------------------------------------------------------------------
-- Type and table for number values
---------------------------------------------------------------------
CREATE OR REPLACE TYPE numberRow AS OBJECT ( 
   value NUMBER 
);
CREATE OR REPLACE TYPE numberTable IS TABLE OF numberRow;

定义完成后，我们可以在包中创建实际函数

FUNCTION NumbersBetween(startValue NUMBER, endValue NUMBER ) RETURN numberTable IS
   tabNumbers numberTable := numberTable();
   nCounter NUMBER;
BEGIN
   FOR nCounter IN startValue..endValue LOOP
      tabNumbers.EXTEND;
      tabNumbers(tabNumbers.COUNT) := numberRow(nCounter);
   END LOOP;
 
   RETURN tabNumbers;
END;

该函数使用 FOR 循环生成数字。在每次迭代中，数字表会增加 1 个元素，并将包含数字的新行添加到表中。当函数退出时，数字表将返回给调用者。

为了使用该函数，必须使用 TABLE 语句。因此，例如要获取 1 到 5 之间的数字，语句将是

SELECT * FROM TABLE( Toolkit.NumbersBetween(1, 5));

结果是：

VALUE
----------
1
2
3
4
5

创建流水线函数 

流水线函数也返回结果集，就像我们在上一个示例中看到的那样。区别在于，流水线函数在函数执行结束时不会返回整个集合。在流水线函数中，使用 PIPE ROW 语句立即将结果发送给调用者。这个概念与 C# 中的 yield return 结构非常相似。

让我们创建与 NumbersBetween 函数相同的函数，但这次是流水线的。该包有一个表类型定义，用作流水线函数的返回类型。该类型名为 numberTable2，以免与之前定义的类型混淆。

TYPE numberTable2 IS TABLE OF NUMBER;

现在函数看起来像

FUNCTION NumbersBetweenPiped(startValue NUMBER, endValue NUMBER ) RETURN numberTable2 
   PIPELINED IS
   nCounter NUMBER;
BEGIN
   FOR nCounter IN startValue..endValue LOOP
      PIPE ROW ( nCounter );
   END LOOP;
END;

与之前一样，思路相同，但这次我们不是将结果收集到一个集合中并返回它。相反，每个值在 PIPE ROW 语句处单独返回。使用该函数看起来与之前完全相同

SELECT * FROM TABLE( Toolkit.NumbersBetweenPiped(1, 5) );

使用流水线函数的一个大优势是，结果集不必存储直到返回，因为结果会在准备好时返回给调用者。这将对内存产生积极影响，并可能提高性能，例如，因为并行操作可以更早地完成。

流水线函数的其他示例

为了演示更多返回结果集的函数示例，我在包中包含了以下（流水线）函数。 

DatesUntil 

该函数与 NumbersBetween 函数非常相似，但操作日期。参数的顺序不同，先是结束日期，然后是开始日期。这是因为开始日期是可选的。因此，如果在调用中未定义开始日期，则使用 SYSDATE。函数实现如下

FUNCTION DatesUntil(endDate DATE, startDate DATE DEFAULT SYSDATE ) RETURN dateTable 
   PIPELINED IS
   dCounter DATE := TRUNC( startDate );
BEGIN
   WHILE TRUNC( endDate ) >= dCounter LOOP
      PIPE ROW( dCounter );
      dCounter := dCounter + 1;
   END LOOP;
END;

例如，获取今天和接下来的 5 天将是这样的

SELECT * FROM TABLE( Toolkit.DatesUntil( SYSDATE + 5 ) );

结果

COLUMN_VAL
----------
13.03.2011
14.03.2011
15.03.2011
16.03.2011
17.03.2011
18.03.2011

WorkingDaysLeftInYear

实际上，这不是一个流水线函数，因为它只返回一个数字。但是，流水线函数被用来演示不同的使用可能性。脚本包含一个名为 Holiday 的表，并且该包有一个名为 WorkingDaysLeftInYear 的函数。该函数的作用是计算给定日期（默认为当前日期）当年还剩多少个工作日。根据 Holiday 表中的信息，排除节假日。

Holiday 表创建如下（请注意，在脚本中，Holiday 表在包之前创建，以防止编译错误）。 

CREATE TABLE ToolkitUser.Holiday (
   Holiday     DATE          NOT NULL PRIMARY KEY,
   Description VARCHAR2(100)
);
/

CREATE PUBLIC SYNONYM Holiday FOR ToolkitUser.Holiday;
/ 

现在，我们可以使用 DatesUntil 函数来填充 Holiday 表。我们添加一些周末作为节假日。

INSERT INTO ToolkitUser.Holiday (Holiday, Description)
SELECT days.Column_Value,
       TO_CHAR(days.Column_Value, 'DAY' )
FROM  TABLE( Toolkit.DatesUntil( SYSDATE + 100)) days
WHERE TO_CHAR(days.Column_Value, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') IN ('SAT','SUN');

COMMIT; 

现在，我们可以选择今年（2011 年）剩余的工作日数，不包括周末

SELECT Toolkit.WorkingDaysLeftInYear FROM Dual;

StringSplit 

另一个例子是拆分函数，它接收一个 string 和一个分隔符。它根据分隔符将 string 分成几部分，并返回每个元素。其功能与 .NET Framework 的 String.Split 方法非常相似。实现如下

FUNCTION StringSplit(string VARCHAR2, delimiter VARCHAR2 DEFAULT ';') RETURN stringTable
   PIPELINED IS
   nStartPosition NUMBER := 1;
BEGIN
   FOR endPosition IN (SELECT Column_Value 
                       FROM TABLE( StringOccurences( string, delimiter ))) LOOP
      PIPE ROW( SUBSTR( string, nStartPosition, endposition.Column_Value-nStartPosition));
      nStartPosition := endposition.Column_Value + 1;
   END LOOP;
   PIPE ROW( SUBSTR( string, nStartPosition ));
END;

该函数使用了另一个流水线函数 StringOccurences，该函数返回原始 string 中找到分隔符的所有索引，然后根据这些结果，该函数拆分 string。调用该函数

SELECT NVL(Column_Value, ’— NULL –’) AS Result
FROM TABLE(Toolkit.StringSplit( ’;Sunday;Afternoon;’, ’;’));

将返回

RESULT
-------------
- NULL -
Sunday
Afternoon
- NULL –

结束语

希望本文演示了一些在 Oracle 环境中创建包函数的技巧。如果您有额外的时间提供评论和投票，我将不胜感激。谢谢。

历史

  • 2011 年 3 月 13 日：创建文章。
  • 2012 年 4 月 1 日：对脚本进行少量修改并添加示例 。
© . All rights reserved.