PLSQL Variables
Variables
store data temporarily in memory area.
These can be
used throughout the program and can be modified in executable section.
Local Variables
are initialized when the program begins and destroyed when the program end.
Global Variables
are initialized at the beginning of
session, persistent across session and destroyed at end of session.
Variables
are declared in the declaration section.
Can assign a
default value while declaration of Variable.
Variable's
type can be of tables column type.
Can be
passed as parameters to other PLSQL programs
Use naming
conventions for naming variables.
Never use
column names as variable names. Difficult to read the program.
Use
assignment operator := to assign values
Use DEFAULT
keyword to assign default value.
If variable
is not initialized ,its values will be null.
Can have NOT
NULL as a constraint.
Types of Variables :
Scalar : Holds a single value, all SQL Variables are
scalar variables.
Reference:
%TYPE- refers to a tables column
Composite :Holds
multiple values of same type. These are records and collections
Bind
Variables: These are created outside the plsql block. Holds values ever after
block executed.
An example
of declaration of variables from oracle documentation
DECLARE
wages NUMBER;
hours_worked NUMBER := 40;
hourly_salary NUMBER := 22.50;
bonus NUMBER := 150;
country VARCHAR2(128);
counter NUMBER := 0;
done BOOLEAN;
valid_id BOOLEAN;
emp_rec1 employees%ROWTYPE;
emp_rec2 employees%ROWTYPE;
TYPE commissions IS TABLE OF NUMBER INDEX BY
PLS_INTEGER;
comm_tab commissions;
BEGIN
wages := (hours_worked * hourly_salary) +
bonus;
country := 'France';
country := UPPER('Canada');
done := (counter > 100);
valid_id := TRUE;
emp_rec1.first_name := 'Antonio';
emp_rec1.last_name := 'Ortiz';
emp_rec1 := emp_rec2;
comm_tab(5) := 20000 * 0.15;
END;
/
