Home arrow Oracle arrow Page 5 - Database Interaction with PL/SQL: Introduction to Sub-programs

More sub-programs - Oracle

This is part 12 of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, I looked at several examples of explicit cursors. I also introduced the concept of cursors with parameters. In this article we will look into sub-programs. Mainly we will concentrate on procedures and discuss some issues of variable scoping with respect to sub-programs.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL: Introduction to Sub-programs
  2. Coding our first sub-program
  3. Variables and scope in sub-programs
  4. Variable scope bit in depth
  5. More sub-programs
By: Jagadish Chatarji
Rating: starstarstarstarstar / 18
August 09, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

How many sub-programs can we write within a single program? The answer is AS MANY AS YOU CAN. You can declare and define any number of sub-programs within a single main program. There is no limit at all. Let us consider the following example:

declare

            x          number := 30;

            y          number := 40;

           

            procedure displaySum as

                        a          number := 10;

                        b          number := 20;

            begin

                        dbms_output.put_line('Sum of numbers :' || (a+b+x+y) );

            end;

            procedure displayProduct as

                        a          number := 10;

                        b          number := 20;

            begin

                        dbms_output.put_line('Product of numbers :' || (a*b*x*y) );

            end;

            procedure displayAvg as

                        a          number := 10;

                        b          number := 20;

            begin

                        dbms_output.put_line('Avg of numbers :' || ((a+b+x+y)/4) );

            end;

           

begin

            displaySum;

            displayProduct;

            displayAvg;

end;

Even though the above program is bit lengthy, the concept is very simple. I just used three sub-programs, namely ‘displaySum’, ‘displayProduct’ and ‘displayAvg’.  Each of those sub-programs has its own declarations of variables and logic. And I am calling all the sub-programs from within the main program.

There is no particular order you can call a sub-program from within the main program. You can call sub-programs in any order without regard to the order (of the sub-programs) you defined.



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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- 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: