Array Data Reset - PL/SQL and Analytic Functions
-- PL/SQL code to create week days (1,2,...7) in each 
-- week in a typical four week (28 days) month.

SET SERVEROUTPUT ON SIZE 1000000; 

DECLARE
 v_max   SIMPLE_INTEGER := 28; 
 v_day   SIMPLE_INTEGER := 0;

BEGIN
 FOR idx IN 1..v_max LOOP
   
  v_day := v_day +1;
  DBMS_OUTPUT.PUT_LINE(' Month Day '||LPAD(idx,2,'0')|| 
   ' Week '||CEIL(idx/7)||
   ' Week Day '||v_day);

   IF MOD(idx,7) = 0 THEN
      v_day := 0;
   END IF;
 END LOOP;
END;
/

=> Output of above PL/SQL code

-- Query to create week days (1,2,...7) using analytic 
-- function 
-- ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)
-- Same week day data can be created by using the 
-- analytic function 
-- RANK() OVER (PARTITION BY ... ORDER BY ...)
-- or by
-- COUNT(*) OVER (PARTITION BY ... ORDER BY ...)

SELECT
  level day,
  CEIL(level/7) week,
  ROW_NUMBER() OVER    -- or RANK() OVER 
   (PARTITION BY CEIL(level/7) ORDER BY level) week_day
FROM  DUAL
CONNECT BY level < 29;

=> Output of above Analytic Function Row_Number()

SELECT
  level day,
  CEIL(level/7) week,
  COUNT(*) OVER 
   (PARTITION BY CEIL(level/7) ORDER BY level) week_day
FROM  DUAL
CONNECT BY level < 29;

=> Output of above Analytic Function Count(*) Over()

Oracle registered trademark of Oracle Corporation.

Last Revised On: October 15th, 2014

  104