PL/SQL Programming   «Prev  Next»

Lesson 3PL/SQL variables
ObjectiveIdentify the Different Types of PL/SQL Variables.

Identify Different Types of PL/SQL Variables

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.

1) Variable declaration can be on any type, native to Oracle such as NUMBER, CHAR AND DATE, or native to PL/SQL such as BOOLEAN.
1) Variable declaration can be on any type, native to Oracle such as NUMBER, CHAR AND DATE, or native to PL/SQL such as BOOLEAN.

2) You can initialize a variable while you declare it
2) You can initialize a variable while you declare it in PL/SQL

3) Values can be assigned to a variable in two ways.
3) Values can be assigned to a variable in two ways. 1) The first way is to use an assignment operator ':=', with the variable on the left-hand side and an expression including literals, variables, and algebraic operators, or PL/SQL function calls on the right.

4) The second way to assign values to variables is to SELECT and FETCH database values into Variables
4) The second way to assign values to variables is to SELECT and FETCH database values into Variables

Declaring PL/SQL Variables

A PL/SQL variable can have
  1. any SQL data type (such as CHAR, DATE, or NUMBER) or
  2. 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>

Procedure Language 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:
  1. allocate storage space for a value,
  2. specify its datatype, and
  3. 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.

Oracle Database PL/SQL Programming
SEMrush Software