Overloading and recursion

Certify and Increase Opportunity.
Be
Govt. Certified PL SQL Developer

Overloading

The terms overloading and overloaded may refer to:

Constructor and function/method overloading, in computer science, a type of polymorphism where different functions with the same name are invoked based on the data types of the parameters passed
Operator overloading, a form of functional or method overloading where the action being overloaded is an operator, such as + or –

The process of “overloading” originates in the world of object-oriented coding with the concept of polymorphism. It is the idea that the functionality of a PL/SQL stored procedure of function can be changed based on the input datatype.

Polymorphism was a spin off of the PL/SQL concept called “overloading” Overloading a stored procedure or function refers to the ability of a programming method to perform more than one kind of operation depending upon the context in which the method is used.

A very simple example of this is the use of the operator “+”, in working with characters it can be used for concatenation and if used with numerical values it would be used for addition. A programming example of overloading would look as follows.

Concatenation example:

First_Name = “John”
Last_Name = “Smith”
Full_Name = First_Name + Last_Name

Numerical example

Kounter = Kounter + 1

Oracle member methods and overloading
Overloading is associated with the object-oriented idea of “polymorphism” whereby a single procedure may be context-sensitive, depending on the input parameters and it is also used in the object-oriented Oracle with the idea of member methods.

An example of this type of method would be an update method that updates a table attribute based on the value that is passed. If a date value is passed, the procedure will do the conversion to character, same with a number.

The technique for overloading a method procedure or function is identical to the overloading of standard procedure or function.

create type
test3(
type_passed varchar2(8),
member procedure input_type(in_char char),
member procedure input_type(in_varchar varchar2),
member procedure input_type(in_date date),
member procedure input_type(in_number number)
);

Recursion

PL/SQL does support recursion via function calls. Recursion is the act of a function calling itself, and a recursive call requires PL/SQL to create local copies of its memory structures for each call. The Oracle docs notes:

“PL/SQL does allow for recursive execution of function calls, however, so you can put it to use inside a SQL statement where recursion is needed.”

PL/SQL Recursive cursors
A “cursor” is not a PL/SQL programmer who cusses, but rather, PL/SQL uses a cursor to keep a handle to each recursive call. In PL/SQL, a recursive cursor (a pointer to a shared SQL area) is used to keep a pointer to each call of a recursive function in PL/SQL.

Recursive SQL
A “Recursive SQL statement” refers to additional “hidden” SQL that is issued for internal Oracle functions such as re-caching paged-out data from the dictionary cache, but we also see recursive SQL for these items, especially DDL: