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

Variables and scope in 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

Every block in a PL/SQL program can have its own declaration of variables. This includes the sub-programs as well. 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) );

            end;

           

BEGIN

            dbms_output.put_line('Sum is ' || (x+y) );

            displaySum;

END;

In the above example, ‘x’ and ‘y’ are the variables declared for the main program (which are outside the procedure declaration). Next, ‘a’ and ‘b’ are the variables declared within the procedure. These variables are used only within the ‘begin’ and ‘end’ of procedure.

Can we use the variables of the main block inside the sub-program?  The answer is YES. We can use the variables of the main program inside the sub-program. But we can't do the opposite. That means the variables declared within the procedure are local to it and cannot be accessed beyond its boundaries. 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;

           

BEGIN

            dbms_output.put_line('Sum is ' || (x+y) );

            displaySum;

END;

The only difference between the above two programs is the DBMS_OUTPUT statement within the procedure. In the above program, I am using variables of both the sub-program and the main program within the sub-program, which is perfectly all right.



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