Programming Praxis

My day job is in support of an enterprise system written in Pro*C and PL/SQL (and still a little bit of COBOL!) atop an Oracle database. Control-break reports are common enough that I’ve worked out a system, extensible to as many levels as needed, which I can blindly apply to the current data. Here’s an actual PL/SQL program that I wrote a few days ago; it’s not very different from a COBOL program written fifty years ago:

    open indx_cursor;
    fetch indx_cursor into indx;
    file_name := indx.fzrindx_filename;
    write_lis_header();

    while indx_cursor%FOUND
    loop

          prev_file := indx.fzrindx_filename;
          file_count := 0; file_total := 0;

          while prev_file = indx.fzrindx_filename
          and   indx_cursor%FOUND
          loop

                prev_store := indx.fzrindx_job_loc_code;
                store_count := 0; store_total := 0;

                while prev_store = indx.fzrindx_job_loc_code
                and   prev_file = indx.fzrindx_filename
                and   indx_cursor%FOUND
                loop

                      prev_fund := indx.fzbindx_fund_code;
                      prev_orgn := indx.fzbindx_orgn_code;
                      prev_acct := indx.fzbindx_acct_code;
                      prev_prog := indx.fzbindx_prog_code;
                      prev_actv := indx.fzbindx_actv_code;
                      doc_code := compute_doc_code(indx.fzrindx_job_loc_code);
                      doc_count := 0; doc_total := 0;

                      while prev_fund = indx.fzbindx_fund_code
                      and   prev_orgn = indx.fzbindx_orgn_code
                      and   prev_acct = indx.fzbindx_acct_code
                      and   prev_prog = indx.fzbindx_prog_code
                      and   prev_actv = indx.fzbindx_actv_code
                      and   prev_store = indx.fzrindx_job_loc_code
                      and   prev_file = indx.fzrindx_filename
                      and   indx_cursor%FOUND
                      loop

                            doc_count := doc_count + 1;
                            doc_total := doc_total + indx.fzrindx_job_cost;
                            update_fzrindx_feed_doc_code(indx,
                                doc_code, doc_count);
                            create_gurfeed_debit_entry(indx,
                                doc_code, doc_count);
                            write_lis_line(indx);
                            fetch indx_cursor into indx;
                            file_name := indx.fzrindx_filename;

                      end   loop;

                      create_gurfeed_credit_entry(indx,doc_code,doc_count+1);
                      create_gurfeed_header_entry(indx,doc_code,doc_total);
                      write_total('Total document ' || doc_code,
                          doc_count, doc_total);
                      store_count := store_count + doc_count;
                      store_total := store_total + doc_total;

                end   loop;

                write_total('Total store ' || prev_store,
                    store_count, store_total);
                file_count := file_count + store_count;
                file_total := file_total + store_total;

          end   loop;

          write_total('Total file ' || prev_file, file_count, file_total);
          grand_count := grand_count + file_count;
          grand_total := grand_total + file_total;
          need(max_lines);

    end   loop;

    write_total('Grand total', grand_count, grand_total);

    close indx_cursor;

You can safely ignore the details. The interest is in the three levels of nesting; each starts by initializing the variables that hold the previous key and the current totals, then enters a while loop that processes all the records in the current break level, and finally writes totals and updates the totals for the next-higher nested level. There are other ways to write this code, but I worked out this scheme long ago, and I have now internalized it sufficiently that I can write it without much thought.

Pages: 1 2