Introduction to FUNCTION - Oracle

In my previous article, I introduced the concept of FUNCTION, but not in depth. A FUNCTION is also a type of sub-program. It is very similar to PROCEDURE. A PROCEDURE doesn’t return any value back to the main program. But a FUNCTION does return back. A PROCEDURE is used for one way communication from the main program. That means you can only send values (in the form of parameters) from the main program to the PROCEDURE. But in turn, the PROCEDURE doesn’t send anything back the main program.

A FUNCTION is used for two way communication. It can accept values through parameters from the main program and also send information back to the main program. The syntax of a FUNCTION is very similar to that of a PROCEDURE except that we need to define its RETURN data type as well. The following is a demonstration.

declare

            x          number;

            function getSum(a number, b number) return number is

                        c          number;

            begin

                        c := a + b;

                        return c;

            end;

BEGIN

            x := getSum(10,20);

            dbms_output.put_line('Sum = ' || x);     

END;

From the above program, ‘x’ is a variable declared in the main program, ‘c’ is a variable declared within a function (it can be used only locally within a function), and ‘a’ and ‘b’ are parameters of the function (even these can be used only locally within function).

The first statement in the main program is as follows:

            x := getSum(10,20);

The above statement actually calls the function ‘getSum’ with two values, 10 and 20. Those two values gets passed (or assigned) to ‘a’ and ‘b’ of the function ‘getSum’ respectively. Within the function, I am calculating ‘c’ and finally returning it back to the main program using the ‘return’ statement. The data type of a value being returned back to the main program (in this case, the data type of ‘c’) should be defined as the ‘return’ data type within the function declaration. The rest is same.