文章/答案/技术大牛

发布

社区首页 >问答首页 >是否有一个Oracle分析函数可以帮助将此SQL转换为视图？

问是否有一个Oracle分析函数可以帮助将此SQL转换为视图？
EN

Stack Overflow用户

提问于 2016-02-29 14:01:25

回答 2查看 136关注 0票数 2

考虑一下这个表(简化的)，它可能有很多行：

CREATE TABLE v
(
   m VARCHAR2(50),
   ts date,
   v NUMBER
)
/

然后，以下内容作为查询非常有用：

SELECT
   m,
   MIN(ts) min_ts,
   MAX(ts) max_ts
FROM
   v
WHERE 
   TO_DATE('2016-01-10','YYYY-MM-DD') <= ts AND
   ts < TO_DATE('2016-01-20','YYYY-MM-DD') AND
   m = '123'
GROUP BY
   m
/

其中TO_DATES和'123‘代表用户提供的过滤标准。现在，当我试图将这个SQL转换成一个视图并将标准放在视图上时，我遇到了一些问题：

CREATE OR REPLACE VIEW vv AS
SELECT
   m,
   MIN(ts) min_ts,
   MAX(ts) max_ts
FROM
   v
GROUP BY
   m
/

我无法在视图上提供ts日期筛选器，因为Oracle已经将结果分组了，例如，以下内容将无法工作(ORA-00904："TS"：无效标识符)：

SELECT
   *
FROM
   vv
WHERE 
   TO_DATE('2016-01-10','YYYY-MM-DD') <= ts AND
   ts < TO_DATE('2016-01-20','YYYY-MM-DD') AND
   m='123'
/

那么，在这种情况下，是否有任何Oracle、分析等功能可以帮助将SQL转换为视图？

window-functions

sql-view

sql

oracle

回答 2

Stack Overflow用户

回答已采纳

发布于 2016-03-03 07:21:16

我在一个包中使用了一个流水线表函数，使用的是一个虚拟视图和该视图上的%ROWTYPE，如下所示：

-- Dummy view
CREATE OR REPLACE VIEW vv AS
SELECT
   m,
   MIN(ts) min_ts,
   MAX(ts) max_ts
FROM
   v
WHERE
   1=0
GROUP BY
   m
/

CREATE OR REPLACE PACKAGE vv_utl
AS
   TYPE tt_vv IS TABLE OF vv%ROWTYPE;

   FUNCTION load
   (
      ifromts IN date,
      itots IN date,
      im IN varchar2 default null
   ) RETURN tt_vv PIPELINED;   
END;
/

CREATE OR REPLACE PACKAGE BODY vv_utl
AS
   FUNCTION load
   (
      ifromts IN date,
      itots IN date,
      im IN varchar2 default null
   ) RETURN tt_vv PIPELINED  
   AS
      CURSOR vv_cur IS
      SELECT
         m,
         MIN(ts) min_ts,
         MAX(ts) max_ts
      FROM
         v
      WHERE
         ifromts <= ts AND
         ts < itots AND
         m = nvl(im,m)
      GROUP BY
         m;
   BEGIN
      FOR rec IN vv_cur LOOP
         pipe row(rec);
      END LOOP;
   END;
END;
/

-- Test 
SELECT
   *
FROM
   table(
      vv_utl.load(
         TO_DATE('2016-01-10','YYYY-MM-DD'),
         TO_DATE('2016-01-20','YYYY-MM-DD')
         )
      ) 
/

我担心流水线表函数的性能(我在某个地方看到它们没有缓存其结果)，而且由于我无法使我的函数具有确定性(让Oracle缓存结果的一种方法)，我选择了一个直接视图解决方案，使用包来存储我的参数：

CREATE OR REPLACE PACKAGE vv_param
AS
   PROCEDURE set
   (
      ifromts IN date,
      itots IN date,
      im IN varchar2 default null
   );

   FUNCTION get_fromts RETURN DATE;
   FUNCTION get_tots RETURN DATE;
   FUNCTION get_m RETURN VARCHAR2;

END;
/

CREATE OR REPLACE PACKAGE BODY vv_param
AS
   lfromts date;
   ltots date;
   lm varchar2(50);

   PROCEDURE set
   (
      ifromts IN date,
      itots IN date,
      im IN varchar2 default null
   )
   AS
   BEGIN
       lfromts := ifromts;
       ltots := itots;
       lm := im;
   END;

   FUNCTION get_fromts RETURN DATE AS BEGIN RETURN lfromts; END;
   FUNCTION get_tots RETURN DATE AS BEGIN RETURN ltots; END;
   FUNCTION get_m RETURN VARCHAR2 AS BEGIN RETURN lm; END;

END;
/

CREATE OR REPLACE VIEW vv AS
SELECT
   m,
   MIN(ts) min_ts,
   MAX(ts) max_ts
FROM
   v
WHERE
   vv_param.get_fromts <= ts AND
   ts < vv_param.get_tots AND
   m = nvl(vv_param.get_m,m)
GROUP BY
   m
/

BEGIN
   vv_param.set(
      TO_DATE('2016-01-10','YYYY-MM-DD'), 
      TO_DATE('2016-01-20','YYYY-MM-DD')
      );
END;
/

-- Test 
SELECT
   *
FROM
   vv
/

另外，当使用流水线表函数时，我无法看到解释计划的细节，因为SQL隐藏在包函数中。现在，视图上的解释计划可以正常工作。

这个例子显然很简单，但是在我的实际实现中有很多这样的视图，其中很多依赖于其他类似的视图。通过使用包变量解决方案，我可以在所有这些视图之间共享它们。如果是的话，它不是没有缺陷的，例如，人们可能忘记设置变量，如果您像在ODP.NET中那样重复使用会话，您可能使用旧值，从而得到错误的结果。所以只有时间才能证明这是否正确！

票数 0

Stack Overflow用户

发布于 2016-02-29 17:20:55

可以这样做，但它需要使用上下文，以及进行上下文设置/清除所需的包。您需要使用您的数据进行测试，以确定这是否足够有效。

创建新上下文

create or replace context test_context using pkg_context_utils;

创建包以设置新的上下文

create or replace package pkg_context_utils
as
  procedure set_date (p_date in date);
  procedure clear_date;
end pkg_context_utils;
/

create or replace  package body pkg_context_utils
as
  procedure set_date (p_date in date)
  is
  begin
    dbms_session.set_context (namespace => 'test_context',
                              attribute => 'test_date',
                              value     => p_date);
  end set_date;

  procedure clear_date
  is
  begin
    dbms_session.clear_context(namespace => 'test_context',
                               client_id => null,
                               attribute => 'test_date');
  end clear_date;

end pkg_context_utils;
/

使用where子句中的上下文创建视图

create or replace view test_view
as
select * from dual where sys_context('test_context', 'test_date') > sysdate;

运行没有上下文集的视图

select * from test_view;

no rows selected.

将上下文设置为当前日期之后的上下文

begin
  pkg_context_utils.set_date(trunc(sysdate + 1));
end;
/

select * from test_view;

DUMMY
-----
X

将上下文设置为当前日期之前的

begin
  pkg_context_utils.set_date(trunc(sysdate));
end;
/

select * from test_view;

no rows selected.

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/35701540

复制

相似问题

问是否有一个Oracle分析函数可以帮助将此SQL转换为视图？
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问是否有一个Oracle分析函数可以帮助将此SQL转换为视图？EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问是否有一个Oracle分析函数可以帮助将此SQL转换为视图？
EN