An Oracle Programme with a loop

This programme creates an Oracle procedure. This Oracle programme has a loop. Within that loop, one record is read from a table. The content of that record is used within the loop.

I want to retain this programme for future usage. I tend to forget how such a table is created within a cursor and how the value from such query is retrieved and used within the programme.

The programme starts with the creation of a table. In the table, a new feature of Oracle 12 is included. This is the autoincrement, which is applied to the “id”. This provides 1, 2, 3 etc to the field “id”. The table is partioned on date, where dates before 1 jan 2014 are allocated in one partition, dates in 2014 in a second, date in 2015 in a third and so forth .

The idea is that we will calculate an average for “waarde” on each year. As the records related to different years are stored in different partitions, it is assumed that the calculation are limited to one of the partitions.

In the subsequent PL/SQL programme, a loop is created. The loop runs thru one partition first, after which the second partition will be used.

set serveroutput on 
drop table groot;
CREATE TABLE groot
  ( id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY
  , waarde       NUMBER
  , datum       DATE)
 PARTITION BY RANGE (datum)
 ( PARTITION groot_2013 VALUES LESS THAN (TO_DATE('01-JAN-2014','dd-MON-yyyy'))
 ,  PARTITION groot_2014 VALUES LESS THAN (TO_DATE('01-JAN-2015','dd-MON-yyyy'))
 ,  PARTITION groot_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','dd-MON-yyyy'))
 , PARTITION groot_2016 VALUES LESS THAN (maxvalue)
 );
 
 describe groot;
 
 insert into SCOTT.GROOT(waarde, datum)
SELECT trunc(dbms_random.value(1,(1000)))
, to_date('2013-01-01', 'yyyy-mm-dd')+trunc(dbms_random.value(1,(4*365)))
FROM ( SELECT 1 just_a_column
FROM dual
CONNECT BY LEVEL <= 2000000
);


create or replace PROCEDURE "TEST_PROCEDURE"
AS
  v_waarde number:=0;
  Lcntr integer:=0;
  CURSOR c1
IS
  SELECT 2013 as aantal
  FROM dual
  union all
  SELECT 2014 as aantal
  FROM dual
  union all
  SELECT 2015 as aantal
  FROM dual
  union all
  SELECT 2016 as aantal
  FROM dual
  ;
  l_aantal   c1%ROWTYPE;
BEGIN
  dbms_output.enable;
  OPEN c1;
  LOOP
   Lcntr := Lcntr + 1;
   FETCH c1 INTO l_aantal;
   EXIT WHEN c1%NOTFOUND;
   select avg(waarde) into v_waarde from groot where to_number(to_char(datum,'yyyy')) = l_aantal.aantal;
  dbms_output.put_line(Lcntr ||' '|| v_waarde ||' '||l_aantal.aantal);
  END LOOP;
END;
/

exec "TEST_PROCEDURE"