Variables can be used to store the results of a query for later processing or to calculate values to insert into Oracle database tables.PL/SQL variables can be used anywhere in an expression, either in SQL or in PL/SQL statements. A variable must be declared before referencing it in other statements, including other declarative statements. The following series of images discuss more about PL/SQL variables.
Declaring PL/SQL Variables
A PL/SQL variable can have
any SQL data type (such as CHAR, DATE, or NUMBER) or
a PL/SQL- only data type (such as BOOLEAN or PLS_INTEGER).
One has a PL/SQL-only data type; the others have SQL data types.
PL/SQL Variable Declarations
SQL> DECLARE
2 part_number NUMBER(6); -- SQL data type
3 part_name VARCHAR2(20); -- SQL data type
4 in_stock BOOLEAN; -- PL/SQL-only data type
5 part_price NUMBER(6,2); -- SQL data type
6 part_description VARCHAR2(50); -- SQL data type
7 BEGIN
8 NULL;
9 END;
10 /
PL/SQL procedure successfully completed.
SQL>
As you have seen, variables provide you with a medium for manipulating data.
In the next lesson, the different variable datatypes will be discussed.
PL/SQL Declarations
Your program stores values in variables and constants. As the program executes, the values of variables can change, but the values of constants cannot. You can declare variables and constants in the declarative part of any PL/SQL block, subprogram, or package.
Declarations:
allocate storage space for a value,
specify its datatype, and
name the storage location so that you can reference it.
Some examples follow:
DECLARE
birthday DATE;
emp_count SMALLINT := 0;
The first declaration names a variable of type DATE. The second declaration names a variable of type SMALLINT and uses the assignment operator to assign an initial value of zero to the variable. The next examples show that the expression following the assignment operator can be arbitrarily complex and can refer to previously initialized variables:
DECLARE
pi REAL := 3.14159;
radius REAL := 1;
area REAL := pi * radius**2;
By default, variables are initialized to NULL, so it is redundant to include ":= NULL" in a variable declaration.