11.8. Variable Declarations
You declare local subprogram variables in the declarative region between the keywords: IS and BEGIN.
PROCEDURE procedure_name(. . .)
IS
constants, types, variables declare here.
BEGIN
body of code.
END procedure_name;
FUNCTION function_name(. . .) RETURN some_datatype
IS
constants, types, variables declare here.
BEGIN
body of code.
RETURN value_to_return;
END function_name;
The scope of a procedure or function variable is only the subprogram in which the variable is declared. The following is a package specification and body with two procedures. Each procedure has a single variable. Each procedure is autonomous. The variables NAME and MAJOR are local to their respective subprograms.
PACKAGE students_pkg is
PROCEDURE print_name (v_student_id IN VARCHAR2);
PROCEDURE print_major (v_student_id IN VARCHAR2);
END students_pkg;
PACKAGE BODY students_pkg IS
PROCEDURE print_status (v_student_id IN VARCHAR2)
IS
name VARCHAR2(100);
BEGIN
SELECT student_name INTO name
FROM STUDENTS
WHERE student_id = v_student_id;
dbms_output.put_line(name);
END print_status;
PROCEDURE print_major (v_student_id IN VARCHAR2)
IS
major VARCHAR2(100);
BEGIN
SELECT college_major INTO major
FROM STUDENTS
WHERE student_id = v_student_id;
dbms_output.put_line(major);
END print_major;
END students_pkg;
You can declare variables in the declarative part of the package body. This makes the variables global to all procedures and functions within the package body. The preceding package is different only with respect to the placement of the variables NAME and MAJOR.
PACKAGE BODY students_pkg IS
name VARCHAR2(100);
major VARCHAR2(100);
PROCEDURE print_status (v_student_id IN VARCHAR2)
IS
BEGIN
Same code as above.
END print_status;
PROCEDURE print_major (v_student_id IN VARCHAR2)
IS
BEGIN
Same code as above.
END print_major;
END students_pkg;
As with any language, excessive use of global variables makes tracing and troubleshooting difficult.
You can declare variables in the package specification. The following is a simple package that will compile and stand on its own within an application environment. If the user SCOTT compiles this package then all of SCOTT's PL/SQL programs will be able to reference the state of this variable or change it at any time.
PACKAGE global_pkg IS
my_global_variable INTEGER := 0;
END global_pkg;
You should encapsulate the global variable with procedures and functions that set and return the value of the variable, shown next (refer to Chapter 10 for discussion of information hiding).
PACKAGE global_pkg IS
PROCEDURE set_value(new_value INTEGER);
FUNCTION current_value RETURN INTEGER;
END global_pkg;
PACKAGE BODY global_pkg IS
my_global_variable INTEGER := 0;
PROCEDURE set_value(new_value INTEGER) IS
BEGIN
my_global_variable := new_value;
END set_value;
FUNCTION current_value RETURN INTEGER IS
RETURN my_global_variable;
BEGIN
END get_value;
END global_pkg;