Understanding the procedural approach in PL/SQL: discussion - Oracle

In this section, I shall discuss the code presented in the previous section step by step.  Let us start with the following first:

type year_type is table of number index by binary_integer;
year_profits      year_type;

The first statement simply defines our own data type, called "year_type." Please note that "year_type" is not a variable.  It is a user defined data type.  According to the above statement, "year_type" is a data type which can hold a set (or table) of values (typically of type "number"), organized with a BINARY_INTEGER index.  The second statement declares a new variable, "year_profits," based on the data type "year_type."  This is the real variable which can hold the data. 

Another important issue to remember is that those two statements are not part of any procedure (or sub-program).  They have been defined as part of the main program.  That means those two can be used in any sub-program belonging to the same program.  To put it simply, any variable or data type declared in the main program can be used by any sub-program belonging to the same program.

Further proceeding we have the following:

procedure add_profit(year number, amount number) as
      begin
            year_profits(year) := amount;
      end;

The above is a "procedure" (basically a sub-program within the main program) named "add_profit." It has two parameters, "year" and "amount," of type "number."  Any sub-program (or "procedure" in this case) would never execute by itself.  It should be called from the main program (or from even another sub-program) using its name (in this case "add_profit") along with passing values to the parameters. 

Once we call the "procedure" (with two parameter values), it executes the statements inside its own "begin" and "end." In this case, we have only one statement between "begin" and "end," which simply adds a new pair ("key" and "value") within the associative array.

Understanding the procedural approach in PL/SQL: discussion continued

Further proceeding with the code given in the example, we have the following:

  procedure print_profits as
        i     binary_integer;
  begin
        i := year_profits.first;
        while i <= year_profits.last
        loop
              dbms_output.put_line(i || ': '|| year_profits(i));
              i := year_profits.next(i);
        end loop;
  end;

The above procedure simply goes through every element (or pair) available within the associative array and displays the same back onto the screen.  This is quite similar to what I explained in my previous article. The only difference is that I separated the code into a special procedure named "print_profits."  The procedure does not have any parameters, and you can simply call it with its name from other parts of your program. 

Another issue to note concerns the variable "i." It is declared as part of the procedure "print_profits."  That means it can be used only within the procedure and never outside the procedure.  To put it simply, it is private only to the procedure.  This removes the problem of clashing your variables within a big program!

Further proceeding we have the following:

  procedure print_total_profit as
        tot_profits number := 0;
        i           binary_integer;
  begin
        i := year_profits.first;
        while i <= year_profits.last
        loop
              tot_profits := tot_profits + year_profits(i);
              i := year_profits.next(i);
        end loop;
        dbms_output.put_line('Total Profits:' || tot_profits);
  end;

Even this procedure is very similar to the above, except that I am trying to sum up all the "values" available in the associative array and finally display them back to the screen.  You might have observed that I declared two private variables now, "tot_profits" and "i," which are available only for the procedure "print_total_profit."

The next section explains the execution of the above sub-programs.