CODEWITHSUNDEEP
oracle-databasecursor
user1885534
user1885534

Reputation: 19

Oracle Cursor to Step through records

Can someone help me with the below. It is easy writing the cursor but I don't know how to test when the REFVAL changes. My Cursor so far is below.

The cursor can’t be grouped on REFVAL. Think of it as being based on a series of invoice lines that need to be added up for a total box on that invoice.

Something like:

REFVAL           PAYAMNT       INVOICE_DETAIL

12/00001/DA      £420          Recurring fee
12/00001/DA      £300          CRB check
12/00001/DA      £170          Plate fee
12/00002/JV      £70           SQL assistance
12/00002/JV      £30           Loader assistance

So, when stepping through the cursor I can write the detail from 12/00001/DA to three lines and then write the total for 12/00001/DA (£890) and then the details from 12/00002/JV and then the summary line for that one.

The script will, therefore, need to know when the REFVAL changes

What i have so far is :

DECLARE
  CURSOR c1 IS 
     SELECT refval,amt from johan;

  Grandtotal INTEGER := 0;
  REF_total  Integer := 0;
  REFVAL     varchar(20);
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO refval,ref_total;
    IF c1%notfound THEN
      EXIT;
    END IF;

     dbms_output.put_line('TOtal for '|| refval || ': ' || ref_total);
     Grandtotal := Grandtotal + ref_total;
   END LOOP;

   IF c1%ISOPEN THEN  -- cursor is open
     CLOSE c1;
   END IF;

   dbms_output.put_line('Grandtotal: '||Grandtotal);
 END;
 /

Upvotes: 1

Views: 271

Answers (2)

Nick Krasnov
Nick Krasnov

Reputation: 27261

If I understood your requirements correctly then there is no need of procedural processing of data, it could be done in SQL using rollup extension of the group by clause to calculate total and grand total. Here is an example:

SQL> with t1 as(
  2    select '12/00001/DA' as REFVAL,  420 as PAYAMNT, 'Recurring fee' as INVOICE_DETAIL from dual union all
  3    select '12/00001/DA',  300,   'CRB check' from dual union all
  4    select '12/00001/DA',  170,   'Plate fee' from dual union all
  5    select '12/00002/JV',  70 ,   'SQL assistance' from dual union all
  6    select '12/00002/JV',  30 ,   'Loader assistance' from dual
  7  )
  8  select nvl(REFVAL, 'GRAND TOTAL:') as REFVAL
  9       , case
 10             when (INVOICE_DETAIL is null) and (REFVAL is not null)
 11             then 'SUB-TOTAL:'
 12             else INVOICE_DETAIL
 13         end  as INVOICE_DETAIL
 14       , sum(PAYAMNT)
 15        
 16     from t1
 17    group by rollup(REFVAL, INVOICE_DETAIL)
 18  ;

REFVAL       INVOICE_DETAIL           END
------------ ----------------- ----------
12/00001/DA  CRB check                300
12/00001/DA  Plate fee                170
12/00001/DA  Recurring fee            420
12/00001/DA  SUB-TOTAL:               890

12/00002/JV  SQL assistance            70
12/00002/JV  Loader assistance         30
12/00002/JV  SUB-TOTAL:               100

GRAND TOTAL:                          990

8 rows selected

Upvotes: 3

Gaurav Soni
Gaurav Soni

Reputation: 6346

DECLARE
   CURSOR c1
   IS
      SELECT   refval,
               amt,
               DENSE_RANK () OVER (PARTITION BY REFVAL ORDER BY rowid) rnk   
        FROM   johan;

   Grandtotal   INTEGER := 0;
   REF_total    INTEGER := 0;
   REFVAL       VARCHAR (20);
   v_new_rank   PLS_INTEGER;  
   v_old_rank   PLS_INTEGER;
   v_start      PLS_INTEGER := 1;    --just to intialize v_old_rank
BEGIN
   OPEN c1;

   LOOP
      FETCH c1 INTO   refval, ref_total, v_new_rank;

      EXIT WHEN c1%NOTFOUND;

      IF v_start = 1
      THEN
         v_old_rank := v_new_rank;
         v_start := v_start + 1;
      END IF;

      DBMS_OUTPUT.put_line ('TOtal for ' || refval || ': ' || ref_total);
      Grandtotal := Grandtotal + ref_total;

      IF v_old_rank <> v_new_rank
      THEN
         v_old_ref_val := v_new_rank;

   --you can print the total per refval group
    DBMS_OUTPUT.PUT_LINE ('do something');
      END IF;
   END LOOP;

   IF c1%ISOPEN
   THEN
      -- cursor is open
      CLOSE c1;
   END IF;

   DBMS_OUTPUT.put_line ('Grandtotal: ' || Grandtotal);
END;
/

When you select from the cursor c1 ,it looks like as shown below

REFVAL           PAYAMNT              rnk

12/00001/DA      £420                 1
12/00001/DA      £300                 1
12/00001/DA      £170                 1
12/00002/JV      £70                  2
12/00002/JV      £30                  2

Upvotes: 1

Related Questions