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

Variable scope bit in depth - 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

Before going into bit depth, let us consider the following program.

declare

            a          number := 300;

            b          number := 400;

           

            procedure displaySum as

                        a          number := 10;

                        b          number := 20;

            begin

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

            end;

           

BEGIN

            dbms_output.put_line('Sum is ' || (a+b) );

            displaySum;

END;

If you carefully observe the above program, we have the same variables declared in both the main program and the sub-program. If you watch the output of the above program, you will observe that the procedure displays the result as 60, which is a contradiction. I would like to have ‘a’ and ‘b’ of the sub-program and ‘a’ and ‘b’ of the main program added. But, it didn’t happen like that.

That means, the preference of the sub-program in the above program could not access the variables in the main program (as the variables are the same as its own local variables). How do we solve the issue?  This is where the concept of ‘Labeling blocks’ comes in once again. Let us modify the above program.

<<main>>

declare

            a          number := 300;

            b          number := 400;

           

            procedure displaySum as

                        a          number := 10;

                        b          number := 20;

            begin

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

            end;

           

BEGIN

            dbms_output.put_line('Sum is ' || (a+b) );

            displaySum;

END;

If you clearly observe the above program, you can understand that the main program is labeled as “main” and we are able to access the variables of the main program within the sub-program using the convention of “main.variablename”.  Try to observe carefully the DBMS_OUTPUT statement within the sub-program.



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