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)
insert into SCOTT.GROOT(waarde, datum)
, to_date('2013-01-01', 'yyyy-mm-dd')+trunc(dbms_random.value(1,(4*365)))
FROM ( SELECT 1 just_a_column
CONNECT BY LEVEL <= 2000000
create or replace PROCEDURE "TEST_PROCEDURE"
SELECT 2013 as aantal
SELECT 2014 as aantal
SELECT 2015 as aantal
SELECT 2016 as aantal
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);