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;