Home arrow Oracle arrow 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.

  1. Database Interaction with PL/SQL: Sub-programs in Depth
  2. Sub-programs interacting with an Oracle database
  3. Procedures with parameters
  4. Using %ROWTYPE in parameter declarations
  5. Introduction to FUNCTION
By: Jagadish Chatarji
Rating: starstarstarstarstar / 12
August 16, 2005

print this article



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.


            x          number;

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

                        c          number;


                        c := a + b;

                        return c;



            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.

>>> More Oracle Articles          >>> More By Jagadish Chatarji

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: