PL/SQL Programming   «Prev  Next»

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

Identify the 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.
Use the following Slide Show to learn more about PL/SQL 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.