ROLLUP and CUBE operators in Oracle 8i

ROLLUP operator는 SELECT문의 GROUP BY절에 사용된다.
SELECT절에 ROLLUP 을 사용함으로써 'regular rows'(보통의 select된 data)와
'super-aggregate rows'(총계)을 구할 수 있다. 기존에는 select ... union select
를 이용해 구사해야 했었던 것이다. 'super-aggregate rows'는 'sub-total'
(중간 Total, 즉 소계)을 포함한다.

CUBE operator는 Cross-tab에 대한 Summary를 추출하는데 사용된다. 모든 가능한
dimension에 대한 total을 나타낸다. 즉 ROLLUP에 의해 나타내어지는 item total값과
column total값을 나타낸다.

NULL값은 모든 값에 대한 super-aggregate 을 나타낸다. GROUPING() function은
모든 값에 대한 set을 나타내는 null값과 column의 null값과 구별하는데 쓰여진다.
GROUPING() function은 GROUP BY절에서 반드시 표현되어야 한다. GROUPING()은 모든
값의 set을 표현합에 있어서 null이면 1을 아니면 0을 return한다. 

ROLLUP과 CUBE는 CREATE MATERIALIZED VIEW에서 사용되어 질수 있다.

아래와 같이 테스트에 쓰여질 table과 data을 만든다.

create table test_roll
(YEAR                                               NUMBER(4),
REGION                                             CHAR(7),
DEPT                                               CHAR(2),
PROFIT                                             NUMBER );

insert into test_roll values (1995 ,'West' ,   'A1' ,       100);
insert into test_roll values (1995 ,'West'  ,  'A2' ,       100);
insert into test_roll values (1996 ,'West'  ,  'A1' ,       100);
insert into test_roll values (1996 ,'West'  ,  'A2' ,       100);
insert into test_roll values (1995 ,'Central' ,'A1' ,       100);
insert into test_roll values (1995 ,'East'   , 'A1' ,       100);
insert into test_roll values (1995 ,'East'  ,  'A2' ,       100);

SQL> select * from test_roll;

  YEAR REGION  DE     PROFIT
---------- ------- -- ----------
  1995 West    A1        100
  1995 West    A2        100
  1996 West    A1        100
  1996 West    A2        100
  1995 Central A1        100
  1995 East    A1        100
  1995 East    A2        100

7 rows selected.

----------------------------------------------------------------------
예제 1: ROLLUP
----------------------------------------------------------------------

SQL> select year, region, sum(profit), count(*)
from test_roll
group by rollup(year, region);

  YEAR REGION  SUM(PROFIT)   COUNT(*)
---------- ------- ----------- ----------
  1995 Central         100          1
  1995 East            200          2
  1995 West            200          2
  1995                 500          5
  1996 West            200          2
  1996                 200          2
                       700          7

7 rows selected.


위의 내용을 tabular로 나타내어 보면 쉽게 알 수 있다.

Year Central(A1+A2)  East(A1+A2)  West(A1+A2)    
1995 (100+NULL)      (100+100)    (100+100)    500
1996 (NULL+NULL)     (NULL+NULL)  (100+100)    200
                                           700

----------------------------------------------------------------------
예제 2: ROLLUP and GROUPING()
----------------------------------------------------------------------


SQL> select year, region, sum(profit),
       grouping(year) "Y", grouping(region) "R"
from test_roll
group by rollup (year, region);

  YEAR REGION  SUM(PROFIT)          Y          R
---------- ------- ----------- ---------- ----------
  1995 Central         100          0          0
  1995 East            200          0          0
  1995 West            200          0          0
  1995                 500          0          1
  1996 West            200          0          0
  1996                 200          0          1
                       700          1          1

7 rows selected.

참고) null값이 모든 값의 set에 대한 표현으로 나타내어지면 GROUPING function은
   super-aggregate row에 대해 1을 return한다.

----------------------------------------------------------------------
예제 3: CUBE
----------------------------------------------------------------------

SQL> select year, region, sum(profit), count(*)
from test_roll
group by cube(year, region);

  YEAR REGION  SUM(PROFIT)   COUNT(*)
---------- ------- ----------- ----------
  1995 Central         100          1
  1995 East            200          2
  1995 West            200          2
  1995                 500          5
  1996 West            200          2
  1996                 200          2
       Central         100          1
       East            200          2
       West            400          4
                       700          7


위의 내용을 tabular로 나타내어 보면 쉽게 알 수 있다.

Year Central(A1+A2)  East(A1+A2)  West(A1+A2)    
1995 (100+NULL)      (100+100)    (100+100)    500
1996 (NULL+NULL)     (NULL+NULL)  (100+100)    200
  100             200          400         700


----------------------------------------------------------------------
예제 4: CUBE and GROUPING()
----------------------------------------------------------------------

SQL> select year, region, sum(profit),
        grouping(year) "Y", grouping(region) "R"
from test_roll
group by cube (year, region);

  YEAR REGION  SUM(PROFIT)          Y          R
---------- ------- ----------- ---------- ----------
  1995 Central         100          0          0
  1995 East            200          0          0
  1995 West            200          0          0
  1995                 500          0          1
  1996 West            200          0          0
  1996                 200          0          1
       Central         100          1          0
       East            200          1          0
       West            400          1          0
                       700          1          1

10 rows selected.

===============================================         
HOW TO USE ROLLUP AND CUBE OPERATORS IN PL/SQL
===============================================         

Release 8.1.5 PL/SQL에서는 CUBE, ROLLUP 이 지원되지 않는다. 8i에서는 DBMS_SQL
package을 이용하여 dynamic SQL로 구현하는 방법이 workaround로 제시된다.
Ordacle8i에서는 PL/SQL block에 SQL절을 직접적으로 위치시키는 Native Dynamic SQL
(참고 bul#11721)을 지원한다.

Native Dynamic SQL을 사용하기 위해서는 COMPATIBLE 이 8.1.0 또는 그 보다 높아야
한다.

SVRMGR> show parameter compatible

NAME                                 TYPE    VALUE
------------------------------------ ------- --------------------
compatible                           string  8.1.0


----------------------------------------------------------------------
예제 1-1: ROLLUP  -> 위의 예제 1과 비교한다.
----------------------------------------------------------------------

SQL> create or replace procedure test_rollup as

   my_year test_roll.year%type;
   my_region test_roll.region%type;
   my_sum    int;
   my_count  int;

begin

   select year, region, sum(profit), count(*)
     into my_year, my_region, my_sum, my_count
     from test_roll
    group by rollup(year, region);

end;
/

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE TEST_ROLLUP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
10/8     PL/SQL: SQL Statement ignored
13/18    PLS-00201: identifier 'ROLLUP' must be declared 


SQL> create or replace procedure test_rollup as

   type curTyp is ref cursor;
   sql_stmt  varchar2(200);
   tab_cv    curTyp;

   my_year   test_roll.year%type;
   my_region test_roll.region%type;
   my_sum    int;
   my_count  int;

begin

   sql_stmt := 'select year, region, sum(profit), count(*) ' ||
               'from test_roll ' ||
               'group by rollup(year, region)';

   open tab_cv for sql_stmt;
     loop
       fetch tab_cv into my_year, my_region, my_sum, my_count;
       exit when tab_cv%NOTFOUND;

         dbms_output.put_line (my_year || ' '||
                             nvl(my_region,'       ') ||
                             ' ' || my_sum || ' ' || my_count);
     end loop;
   close tab_cv;

end;
/

SQL> set serveroutput on
SQL> exec test_rollup
1995 Central 100 1
1995 East    200 2
1995 West    200 2
1995         500 5
1996 West    200 2
1996         200 2
700 7

PL/SQL procedure successfully completed. 

----------------------------------------------------------------------
예제 2-1: ROLLUP and GROUPING()   -> 위의 예제 2와 비교한다.
----------------------------------------------------------------------

SQL> create or replace procedure test_rollupg as

    my_year test_roll.year%type;
    my_region test_roll.region%type;
    my_sum    int;
    my_count  int;
    my_g_region int;
    my_g_year   int;

  begin

    select year, region, sum(profit),
           grouping(year), grouping(region)
      into my_year, my_region, my_sum, my_count,
           my_g_year, my_g_region
      from test_roll
     group by rollup(year, region);

end;
/

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE TEST_ROLLUPG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
12/4     PL/SQL: SQL Statement ignored
17/13    PLS-00201: identifier 'ROLLUP' must be declared


SQL> create or replace procedure test_rollupg as

    type curTyp is ref cursor;
    sql_stmt varchar2(200);
    tab_cv curTyp;

    my_year test_roll.year%type;
    my_region test_roll.region%type;
    my_sum    int;
    my_count  int;
    my_g_region int;
    my_g_year   int;

  begin

    sql_stmt := 'select year, region, sum(profit), count(*), ' ||
                'grouping(year), grouping(region) ' ||
                'from test_roll ' ||
                'group by rollup(year, region)';

    open tab_cv for sql_stmt;
    loop

        fetch tab_cv into my_year, my_region, my_sum, my_count,
                          my_g_year, my_g_region;
        exit when tab_cv%NOTFOUND;

        dbms_output.put_line (my_year || ' '||my_region ||
                              ' ' || my_sum || ' ' || my_count ||
                              ' ' || my_g_year || ' ' || my_g_region);
    end loop;
    close tab_cv;

end;
/

Procedure created.

SQL> set serveroutput on
SQL> exec test_rollupg
1995 Central 100 1 0 0
1995 East    200 2 0 0
1995 West    200 2 0 0
1995  500 5 0 1
1996 West    200 2 0 0
1996  200 2 0 1
700 7 1 1

PL/SQL procedure successfully completed.     

----------------------------------------------------------------------
예제 3-1: CUBE   -> 위의 예제 3과 비교한다.
----------------------------------------------------------------------

SQL> create or replace procedure test_cube as

    my_year test_roll.year%type;
    my_region test_roll.region%type;
    my_sum    int;
    my_count  int;

  begin

    select year, region, sum(profit), count(*)
      into my_year, my_region, my_sum, my_count
      from test_roll
     group by cube(year, region);

  end;
/

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE TEST_CUBE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
10/4     PL/SQL: SQL Statement ignored
13/13    PLS-00201: identifier 'CUBE' must be declared


SQL> create or replace procedure test_cube as

    type curTyp is ref cursor;
    sql_stmt varchar2(200);
    tab_cv curTyp;

    my_year test_roll.year%type;
    my_region test_roll.region%type;
    my_sum    int;
    my_count  int;

begin

    sql_stmt := 'select year, region, sum(profit), count(*) ' ||
                'from test_roll ' ||
                'group by cube(year, region)';

    open tab_cv for sql_stmt;
    loop

        fetch tab_cv into my_year, my_region, my_sum, my_count;
        exit when tab_cv%NOTFOUND;

        dbms_output.put_line (my_year || ' '||
                              nvl(my_region,'       ') ||
                              ' ' || my_sum || ' ' || my_count);
    end loop;
    close tab_cv;

end;
/

Procedure created.

SQL> set serveroutput on
SQL> exec test_cube
1995 Central 100 1
1995 East    200 2
1995 West    200 2
1995         500 5
1996 West    200 2
1996         200 2
Central 100 1
East    200 2
West    400 4
700 7    

PL/SQL procedure successfully completed.


----------------------------------------------------------------------
예제 4-1: CUBE and GROUPING()   -> 위의 예제 4와 비교한다.
----------------------------------------------------------------------

SQL> create or replace procedure test_cubeg as

    my_year test_roll.year%type;
    my_region test_roll.region%type;
    my_sum    int;
    my_count  int;
    my_g_region int;
    my_g_year   int;

  begin

   select year, region, sum(profit),
       grouping(year), grouping(region)
   into my_year, my_region, my_sum, my_count,
       my_g_year, my_g_region
   from test_roll
   group by cube(year, region);

  end;
/

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE TEST_CUBEG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
12/4     PL/SQL: SQL Statement ignored
17/13    PLS-00201: identifier 'CUBE' must be declared


SQL> create or replace procedure test_cubeg as

    type curTyp is ref cursor;
    sql_stmt varchar2(200);
    tab_cv curTyp;

    my_year test_roll.year%type;
    my_region test_roll.region%type;
    my_sum    int;
    my_count  int;
    my_g_region int;
    my_g_year   int;

  begin

    sql_stmt := 'select year, region, sum(profit), count(*), ' ||
               'grouping(year), grouping(region) ' ||
               'from test_roll ' ||
               'group by cube(year, region)';

    open tab_cv for sql_stmt;
    loop

        fetch tab_cv into my_year, my_region, my_sum, my_count,
                         my_g_year, my_g_region;
        exit when tab_cv%NOTFOUND;

        dbms_output.put_line (my_year || ' '||my_region ||
                              ' ' || my_sum || ' ' || my_count ||
                              ' ' || my_g_year || ' ' || my_g_region);
    end loop;
    close tab_cv;

end;
/

Procedure created.

SQL> set serveroutput on
SQL> exec test_cubeg
1995 Central 100 1 0 0
1995 East    200 2 0 0
1995 West    200 2 0 0
1995  500 5 0 1
1996 West    200 2 0 0
1996  200 2 0 1
Central 100 1 1 0
East    200 2 1 0
West    400 4 1 0
700 7 1 1

PL/SQL procedure successfully completed.