Actually, I need to discuss the concept of REF CURSOR at this moment, but the REF CURSOR is mainly used in sub-programs (or even packages). So, I thought to discuss the sub-programs and packages before going to REF CURSOR.

Please note that all the examples in this series have been tested only with Oracle 10g. I didn’t really test them with all the previous versions of Oracle. I suggest you to refer the documentation of respective version you are using, if any of the programs failed to execute.

Introduction to sub-programs

Almost every programming language supports sub-programs. They are a part of structured programming. A sub-program is a part of a main program separated completely from the main program. We can consider it as separate block of statements (with its own declarations and programming statements), but still under the control of the main program.

The above explanation may match a bit with anonymous blocks (or nested blocks) of PL/SQL programs (discussed in my previous set of articles), but sub-programs are totally different from nested blocks.  Nested blocks would definitely execute within the main block. But a sub-program need not do so. How is this possible?

It's really quite simple. As I explained earlier, it is a separate block, separately named, and the main issue is that it is separately called by the main block. The main block calls the sub-program by its name to execute its set of statements. This may be a part of the conditions as well.  That means the main program may or may not call sub-program based on certain conditions.

Why is this concept necessary? Repeatable tasks (or the tasks which are being executed quite frequently) are generally separated as sub-programs to facilitate the user to use them as many times as possible.  This also improves the clarity (or readability) of the main program, which is being divided into several meaningful tasks, where each task (sub-program) may have its own set of programming statements (including local declarations).

This concept is mainly taken from the initiative of modularization. It is the process by which you break up large blocks of code into smaller pieces called modules (or sub-programs) which can be called by other modules. With the concept of modularization, our code becomes more reusable, more manageable, more readable and finally more reliable.  This is not only for sub-programs; it is being implemented in the form of packages (to be dealt with in my upcoming articles) as well.

There are mainly two kinds of sub-programs: procedures and functions.  Both are very similar in functionality and syntactical issues. The one difference is that a function returns a value, but a procedure doesn’t.  We will look into the concept of functions in-depth later.