오라클11g에서 매개 변수화된 보기 생성

오라클11g에서 매개 변수화된 보기 생성

중첩 및 왼쪽 조인에 대한 큰 쿼리가 있으며 응용 프로그램에서 실행하지 않도록 보기를 만들어야 합니다.문제는 요청마다 프런트 엔드와 다르기 때문에 입력 파라미터로 날짜 범위와 다른 필드가 필요합니다.방금 검색해보니 매개 변수화된 보기에 SYS_CONTEXT를 사용하는 것을 언급하는 게시물이 몇 개 있는데, 예를 들어 두 개의 매개 변수로 보기를 만드는 방법을 정확히 알아야 합니다.fromdate, todate응용프로그램에서 보기를 호출하는 방법.

참고로 저는 애플리케이션 개발을 위해 grays/groovy를 사용하고 있습니다.다음은 뷰를 생성하고 싶은 쿼리입니다.

 select 
    d.dateInRange as dateval,
    eventdesc,
    nvl(td.dist_ucnt, 0) as dist_ucnt
from (
    select 
        to_date(fromdate,'dd-mon-yyyy') + rownum - 1 as dateInRange
    from all_objects
    where rownum <= to_date(fromdate,'dd-mon-yyyy') - to_date(todate,'dd-mon-yyyy') + 1
) d
left join (
    select 
        to_char(user_transaction.transdate,'dd-mon-yyyy') as currentdate,
        count(distinct(grauser_id)) as dist_ucnt,
        eventdesc 
    from
        gratransaction, user_transaction 
  where gratransaction.id = user_transaction.trans_id and 
  user_transaction.transdate between to_date(fromdate,'dd-mon-yyyy') and to_date(todate,'dd-mon-yyyy') 
    group by  to_char(user_transaction.transdate, 'dd-mon-yyyy'), eventdesc 
) td on td.currentdate = d.dateInRange order by d.dateInRange asc

컨텍스트 방법은 http://docs.oracle.com/cd/B28359_01/network.111/b28531/app_context.htm 에 설명되어 있습니다.

예: (위의 링크에서 수정됨)

CREATE CONTEXT dates_ctx USING set_dates_ctx_pkg;

CREATE OR REPLACE PACKAGE set_dates_ctx_pkg IS 
  PROCEDURE set(d1 in date, d2 in date); 
END; 
/

CREATE OR REPLACE PACKAGE BODY set_dates_ctx_pkg IS
  PROCEDURE set(d1 in date, d2 in date) IS 
  BEGIN 
    DBMS_SESSION.SET_CONTEXT('dates_ctx', 'd1', TO_CHAR(d1,'DD-MON-YYYY'));
    DBMS_SESSION.SET_CONTEXT('dates_ctx', 'd2', TO_CHAR(d2,'DD-MON-YYYY'));
  END;
END;
/

그런 다음 다음 프로그램에서 날짜를 설정합니다.

BEGIN set_dates_ctx_pkg.set(mydate1, mydate2); END;
/

그런 다음 다음 매개 변수를 쿼리합니다.

SELECT bla FROM mytable
WHERE mydate
  BETWEEN TO_DATE(
            SYS_CONTEXT('dates_ctx', 'd1')
          ,'DD-MON-YYYY')
      AND TO_DATE(
            SYS_CONTEXT('dates_ctx', 'd2')
          ,'DD-MON-YYYY');

이 접근 방식의 장점은 매우 쿼리 친화적이라는 것입니다. 런타임에 DDL이나 DML이 포함되지 않으므로 걱정할 트랜잭션이 없습니다. SQL - PL/SQL 컨텍스트 전환이 포함되지 않기 때문에 매우 빠릅니다.

또는 다음과 같습니다.

컨텍스트 메소드와 John의 패키지 변수 메소드를 사용할 수 없는 경우에는 테이블(예: 동일한 세션에서 쿼리를 실행하는 경우 글로벌 임시 테이블)에 매개 변수를 삽입한 다음 뷰에서 해당 테이블에 가입합니다.단점은 이제 쿼리를 실행할 때마다 매개 변수를 삽입하기 위해 DML을 실행해야 한다는 것입니다.

저는 이 성가신 Oracle 단점에 대한 해결책을 방금 마련했습니다.이것처럼.

create or replace package pkg_utl
as
  type test_record is record (field1 number, field2 number, ret_prm_date date);
  type test_table is table of test_record;
  function get_test_table(prm_date date) return test_table pipelined;
end;
/

create or replace package body pkg_utl
as
  function get_test_table(prm_date date) return test_table pipelined
  is
  begin
    for item in (
      select 1, 2, prm_date
      from dual
    ) loop
      pipe row (item);
    end loop;
    return;
  end get_test_table;
end;
/

여전히 패키지가 필요하지만, 적어도 더 편리한 방법으로 사용할 수 있습니다.

select *
from table(pkg_utl.get_test_table(sysdate))

저는 성능에 대해 확신하지 못합니다...

보기에서 매개 변수를 사용하는 한 가지 방법은 매개 변수의 값을 설정하고 이러한 값을 가져오기 위해 호출할 수 있는 함수를 갖는 패키지를 만드는 것입니다.예:

create or replace package MYVIEW_PKG as
  procedure SET_VALUES(FROMDATE date, TODATE date);

  function GET_FROMDATE
    return date;

  function GET_TODATE
    return date;
end MYVIEW_PKG;

create or replace package body MYVIEW_PKG as
  G_FROM_DATE   date;
  G_TO_DATE     date;

  procedure SET_VALUES(P_FROMDATE date, P_TODATE date) as
  begin
    G_FROM_DATE := P_FROMDATE;
    G_TO_DATE := P_TODATE;
  end;

  function GET_FROMDATE
    return date is
  begin
    return G_FROM_DATE;
  end;

  function GET_TODATE
    return date is
  begin
    return G_TO_DATE;
  end;
end MYVIEW_PKG;

그러면 다음과 같이 보기를 만들 수 있습니다.

create or replace view myview as
    select 
        d.dateInRange as dateval,
        eventdesc,
        nvl(td.dist_ucnt, 0) as dist_ucnt
    from (
        select 
            MYVIEW_PKG.GET_FROMDATE + rownum - 1 as dateInRange
        from all_objects
        where rownum <= MYVIEW_PKG.GET_FROMDATE - MYVIEW_PKG.GET_TODATE + 1
    ) d
    left join (
        select 
            to_char(user_transaction.transdate,'dd-mon-yyyy') as currentdate,
            count(distinct(grauser_id)) as dist_ucnt,
            eventdesc 
        from
            gratransaction, user_transaction 
      where gratransaction.id = user_transaction.trans_id and 
      user_transaction.transdate between MYVIEW_PKG.GET_FROMDATE and MYVIEW_PKG.GET_TODATE
        group by  to_char(user_transaction.transdate, 'dd-mon-yyyy'), eventdesc 
    ) td on td.currentdate = d.dateInRange order by d.dateInRange asc;

그리고 실행하려면 먼저 값을 설정해야 합니다.

exec MYVIEW_PKG.SET_VALUES(trunc(sysdate)-1,trunc(sysdate));

그런 다음 호출에 다음 값을 사용합니다.

select * from myview;

언급URL : https://stackoverflow.com/questions/9024696/creating-parameterized-views-in-oracle11g