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