Database Interaction with PL/SQL: Sub-programs in Depth - Introduction to FUNCTION
(Page 5 of 5 )
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.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 10 
