Database Interaction with PL/SQL: Introduction to Sub-programs - Variable scope bit in depth
(Page 4 of 5 )
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.
Next: More sub-programs >>
More Oracle Articles
More By Jagadish Chatarji
/ 18 
