代码之家 › 专栏 › 技术社区 › Scott Bailey

迭代对象的Oracle集合而不进行分解

plsql oracle sql

Scott Bailey · 技术社区 · 14 年前

我使用Oracle对象数据类型来表示时间跨度或周期。我要做一系列的运算,包括处理周期集合。在SQL中迭代集合的速度明显快于PL/SQL。

CREATE TYPE PERIOD AS OBJECT (
  beginning DATE,
  ending    DATE,
  ... some member functions...);

CREATE TYPE PERIOD_TABLE AS TABLE OF PERIOD;

-- what I would like to do: where t.column_value is still a period type
SELECT (t.column_value).range_intersect(period2)
FROM TABLE(period_table1) t
WHERE pa_contains(period_table1, (t.column_value).prev()) = 0
  AND pa_contains(period_table1, (t.column_value).next()) = 1

问题是TABLE()函数将对象分解为标量值,而我确实需要这些对象。我可以使用标量值来重新创建对象,但这会导致重新实例化对象的开销。而且周期被设计成子类,所以要想弄清楚初始化它是什么会有额外的困难。

2 回复 | 直到 14 年前

Vincent Malgrat 14 年前

如果我正确理解您的问题,那么问题在于TABLE操作符的行为,它不返回对象表(包含属性和成员函数),而是返回一个简单的“标量”表(只有属性是可访问的)。

我不确定你能不能用TABLE操作符实现你所描述的。以下是一个临时表而不是嵌套表的解决方法(Oracle 10.2.0.1):

SQL> CREATE OR REPLACE TYPE PERIOD AS OBJECT (
  2    beginning DATE,
  3    ending    DATE,
  4    MEMBER FUNCTION range_intersect (p_period period) RETURN VARCHAR2
  5  );
  6  /     
Type created

SQL> CREATE OR REPLACE TYPE BODY period IS
  2     MEMBER FUNCTION range_intersect(p_period period) RETURN VARCHAR2 IS
  3     BEGIN
  4        RETURN CASE
  5                  WHEN p_period.beginning <= ending
  6                       AND p_period.ending >= beginning
  7                  THEN 'Y'
  8                  ELSE 'N'
  9               END;
 10     END range_intersect;
 11  END;
 12  /     
Type body created

SQL> CREATE GLOBAL TEMPORARY TABLE period_table_tmp (
  2     per period
  3  );
Table created

period_table_tmp 就好像它是 period

SQL> INSERT INTO period_table_tmp
  2     VALUES (period(DATE '2010-01-01', DATE '2010-01-31'));
1 row inserted

SQL> INSERT INTO period_table_tmp
  2     VALUES (period(DATE '2010-02-01', DATE '2010-02-28'));     
1 row inserted

SQL> SELECT t.per.beginning, t.per.ending,
  2         t.per.range_intersect(period(DATE '2010-01-01',
  3                                      DATE '2010-01-15')) intersec
  4    FROM period_table_tmp t;

PER.BEGINNING PER.ENDING  INTERSEC
------------- ----------- ---------
01/01/2010    31/01/2010  Y
01/02/2010    28/02/2010  N

Scott Bailey 14 年前

抱歉,这个问题很难回答。但我最终找到了一种方法,使用我之前创建的一些工具来实现这一点。最后的诀窍是迭代一个嵌套的数字表来获得每个元素。

所以第一件作品是一个系列发电机,我无耻地从Postgres借来的。有其他方法生成数字,但这是相当有效的。

CREATE OR REPLACE FUNCTION generate_series(
  p_start         NUMBER,
  p_end           NUMBER
) RETURN NUMBER_TABLE PIPELINED IS
BEGIN
  FOR i IN p_start .. p_end LOOP
    PIPE ROW(i);
  END LOOP;
  RETURN;
END;

CREATE OR REPLACE FUNCTION get_item(p1 PERIOD_TABLE, idx NUMBER)
RETURN PERIOD IS
BEGIN
  RETURN p1(idx);
END;

最后把所有的东西放在一起:

FUNCTION range_intersect(
  p1 PERIOD_TABLE,
  p2 PERIOD_TABLE
) RETURN PERIOD_TABLE IS
  v_return    PERIOD_TABLE;
  v_len1      NUMBER(8);
  v_len2      NUMBER(8);
BEGIN
  v_len1 := p1.last;
  v_len2 := p2.last;

  WITH pa1 AS (
    SELECT get_item(p1, column_value) AS period
    FROM TABLE(generate_series(1, v_len1))
  ),
  pa2 AS (
    SELECT get_item(p2, column_value) AS period
    FROM TABLE(generate_series(1, v_len2))
  )     
  SELECT period(start_time, MIN(end_time)) 
  BULK COLLECT INTO v_return
  FROM (
    SELECT (pa1.period).first() AS start_time
    FROM pa1
    WHERE contains(p1, (pa1.period).prev()) = 0
      AND contains(p2, (pa1.period).first()) = 1

    UNION ALL

    SELECT (pa2.period).first() AS start_time
    FROM pa2
    WHERE contains(p1, (pa2.period).prev()) = 0
      AND contains(p2, (pa2.period).first()) = 1
  ) s_start
  ... snip...