Advanced Oracle SQL FOR Loops Oracle Tips by Laurent Schneider

Laurent Schneider is considered one of the top Oracle SQL experts, and he is the author of the book "Advanced Oracle SQL Programming" by Rampant TechPress.  The following is an excerpt from the book.

FOR loops

FOR loopsare used to upsert more than one row at the same time.

The first syntax uses an expressions list.

SELECT
   *
FROM
   EMP
MODEL
RETURN UPDATED ROWS
DIMENSION BY
(
   EMPNO,
   ENAME
)
MEASURES
(
   'N' NOMINATED
)
(
   NOMINATED[FOR (EMPNO,ENAME) IN ((7788,'SCOTT'),(7777,'LEO'))]='Y'
);

     EMPNO ENAME      N
---------- ---------- -
      7788 SCOTT      Y
      7777 LEO        Y

Employees Scott and Leo got a nomination. Employee Leo does not exist in table EMP; therefore, a new row is created.

The second syntax uses a subquery:

SELECT
   *
FROM
   DEPT
MODEL
DIMENSION BY
(
   DEPTNO
)
MEASURES
(
   DNAME, 

   'Y' EMPTY
)
(
   EMPTY[FOR DEPTNO IN (SELECT DEPTNO FROM EMP)]='N'
);

    DEPTNO DNAME          E
---------- -------------- -
        10 ACCOUNTING     N
        20 RESEARCH       N
        30 SALES          N
        40 OPERATIONS     Y

For each department in EMP, the measure EMPTY is updated to N.

The third syntax uses a sequence. The sequence contains lower and upper bounds and an increment or a decrement followed by a positive number or interval.

SELECT
   A
FROM
   DUAL
MODEL
RETURN UPDATED ROWS
DIMENSION BY
(
   0 A
)
MEASURES
(
   0 X
)
(
   X[FOR A FROM 101 TO 103 INCREMENT 1]=0
);

         A
----------
       101
       102
       103

For the dimension A from 101 to 103, a row is upserted.

The bounds could also be dates. In this case, the increment or decrement is either a numeric (in days) or an interval.

SELECT
   TO_CHAR(B, 'FMMonth YYYY') B
FROM
   DUAL
MODEL
RETURN UPDATED ROWS
DIMENSION BY
(
   SYSDATE B
)
MEASURES
(
   0 X
)
(
   X
   [
      FOR
         B
      FROM
         DATE '2000-01-01'       TO
         DATE '2000-03-01'
      INCREMENT
         INTERVAL '1' MONTH
   ]=0
); 

B
--------------
January 2000
February 2000
March 2000

The dimension is a date and a sequence of months from January to March 2000 is generated.

In a multidimensional model, if there is a condition on one dimension and a sequence on the other dimension, the default is to update the rows. UPSERT ALLclause instructs to upsert all rows.

SELECT
   *
FROM
   DEPT
MODEL
RETURN UPDATED ROWS
DIMENSION BY
(
   DEPTNO,
   'XXXXXXXX' C
)
MEASURES
(
   0 X)

(
   X[DEPTNO<25,FOR C LIKE 'REC%' FROM 99 TO 97 DECREMENT 1]=0
);

no rows selected 

SELECT
   *
FROM
   DEPT
MODEL
RETURN UPDATED ROWS
DIMENSION BY
(
   DEPTNO,
   'XXXXXXXX' C
)
MEASURES
(
   0 X
)
(
   UPSERT ALL X[DEPTNO<25,FOR C LIKE 'REC%' FROM 99 TO 97 DECREMENT 1]=0
); 

    DEPTNO C                 X
---------- -------- ----------
        10 REC99             0
        10 REC98             0
        10 REC97             0
        20 REC99             0
        20 REC98             0
        20 REC97             0

The sequence generates numbers from 99 to 97 descending; the LIKE clause is used for character dimensions to add a prefix. In the first query, a condition is applied to the DEPTNO column and no rows are updated. In the second query, the addition of UPSERT ALL creates rows for each department with DEPTNO smaller than 25.

Iterations

The model rules are executed once by default. Model uses ITERATEto process the code more than once.

SELECT
   N
FROM
   DUAL
MODEL
DIMENSION BY
(
   0 X
)
MEASURES
(
   1 N
)
RULES ITERATE (10)
(
   N[0]=N[0]*(ITERATION_NUMBER+1)
);

         N
----------
   3628800

The function ITERATION_NUMBERreturns the current iteration where 0 is the first iteration. The measure N is initialized to 1 and multiplied by one, two, three, … up to ten. This means the returned value is 10 factorial.

The iteration also ends when the UNTILclause is reached.

SELECT
   ENAME,
   SAL,
   RATE YEARLY_RATE,
   TO_CHAR(SAVING,'99999$') SAVING,
   YEAR
FROM
   EMP
MODEL
PARTITION BY
(
   ENAME
)
DIMENSION BY
(
   0 X
)
MEASURES
(
   SAL,
   SAL*12/10 RATE,
   .03 INTEREST,
   0 SAVING,
   0 YEAR
) 
RULES ITERATE (20) UNTIL (SAVING[0]>=50000)
(
   SAVING[0]=SAVING[0]*(1+INTEREST[0]),
    SAVING[0]=SAVING[0]+RATE[0],
   YEAR[0]=ITERATION_NUMBER+1
);

ENAME             SAL YEARLY_RATE SAVING        YEAR
---------- ---------- ----------- ------- ----------
ALLEN            1600        1920  $51591         20
JONES            2975        3570  $50666         12
FORD             3000        3600  $51091         12
CLARK            2450        2940  $50234         14
MILLER           1300        1560  $41918         20
SMITH             800         960  $25796         20
WARD             1250        1500  $40306         20
MARTIN           1250        1500  $40306         20
SCOTT            3000        3600  $51091         12
TURNER           1500        1800  $48367         20
ADAMS            1100        1320  $35469         20
BLAKE            2850        3420  $53413         13
KING             5000        6000  $53354          8
JAMES             950        1140  $30632         20

Each employee starts a savings plan and deposits 10% of his salary each year on a special account that has 3% interest. The calculation ends after 20 iterations or if the savings reaches $50000.