HomeOracle Page 5 - Database Interaction with PL/SQL: Sub-programs in Depth
Introduction to FUNCTION - Oracle
This is part 13 of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, we concentrated on procedures and discussed some issues of variable scoping with respect to sub-programs. In this article we proceed with some tips on using procedures together with the introduction to functions.
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.
function getSum(a number, b number) return number is
c := a + b;
x := getSum(10,20);
dbms_output.put_line('Sum = ' || x);
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.