Oracle PL/SQL Tutorial - PL/SQL Variables

• « Previous
• Next »

Variables are named locations in memory for a particular data type in your PL/SQL program.

We must declare them in the declaration section of a PL/SQL block.

Variable Naming

PL/SQL variables must follow the identifier naming rules:

A variable name must be less than 31 characters in length.

A variable name must start with an uppercase or lowercase ASCII letter: A-Z or a-z.

PL/SQL is not case-sensitive.

A variable name may be composed of 1 letter, followed by up to 29 letters, numbers, or the underscore (_) character.

You can also use the number # and dollar sign $ characters.

We can use the following conventions when naming variables:

Use the two-character prefix for each data type listed in the following table.

The following code shows how to create variable names.

n_id 
v_name 
d_birth_date 
v_gender 

The following code shows how to declare Variables

DECLARE -- from w w  w  .j a  va 2s.  c o m
  birthday   DATE; 
  emp_count  SMALLINT := 0; 
  pi         REAL := 3.14; 
  radius     REAL := 1; 
  area       REAL := pi * radius**2; 
BEGIN 
  NULL; 
END; 
/ 

The code above generates the following result.

Variable Declarations

To declare a variable, type the variable name followed by the data type definition terminated by a semicolon ;.

The following code shows how to do variable declaration section.

declare
     n_id number;
     v_name varchar2(100);
     d_birth_date date;
     v_gender varchar2(30); 
begin
     ... 
end; 

The DDL syntax used to declare the preceding variables is as follows:

<variable_name> <data_type>; 

where <variable_name> is the name of the variable, and <data_type> is one of the PL/SQL data types.

To declare variables holding values from or saving to the database, Use %TYPE in PL/SQL code.

Variable Anchors

An anchor refers to the use of the keyword %TYPE to "anchor" a PL/SQL data type definition to the corresponding SQL data type definition.

The following code is an example of the PL/SQL variables for the table AUTHORS declared using column anchors.

n_id         AUTHORS.id%TYPE; 
v_name       AUTHORS.name%TYPE; 
d_birth_date AUTHORS.birth_date%TYPE; 
v_gender     AUTHORS.gender%TYPE; 

The syntax used to declare the preceding variables is as follows:

<variable_name> <table_name>.<column_name>%TYPE; 

where <variable_name> is the name of the variable, <table_name> is the name of the table used to anchor the data type, <column_name> is the name of the column used to anchor the data type.

Variable Assignments

To assign a literal value to a variable in PL/SQL, use the assignment operator :=.

The following code shows how to use assignment operator.

declare
     ... 
begin
     n_id := 40;
     v_name := 'STEVEN FEUERSTEIN';
     d_birth_date := to_date('20000101', 'YYYYMMDD');
     v_gender := 'M'; 
end; 

The code above uses literial to assign value to a variable.

A numeric literal is a number without any formatting, such as 400.

A character literal is a string of characters enclosed in a pair of single quote ' characters, such as 'java2s.com'.

To assign constant value to a variable in DATE type, use the built-in function,

to_date(aiv_date in varchar2, aiv_date_format in varchar2)

We can also assign a value to a variable by using an INTO clause in a SQL SELECT statement.

Example

Here's an example:

select id, name, birth_date, gender 
into n_id v_name, d_birth_date, v_gender 
from 
AUTHORS 
where AUTHORS.id = 10; 

In this example, the PL/SQL keyword INTO moves the values from the SELECT statement's column list into the corresponding PL/SQL variables.

By default, variables are uninitialized and hence are NULL.

You can initialize them to a value when they are declared by assigning them a value in the declaration section.

For example, you could initialize the AUTHORS variables as follows:

declare
     n_id AUTHORS.id%TYPE := 10; 
     v_name AUTHORS.name%TYPE := 'Tom'; 
     d_birth_date AUTHORS.birth_date%TYPE := to_date('20000101', 'YYYYMMDD'); 
     v_gender AUTHORS.gender%TYPE := NULL; 
begin 
    ... 
end; 

Note

The syntax used to declare the preceding variables is as follows:

<variable_name> <table_name>.<column_name>%TYPE := <value>; 

<variable_name> is the name of the variable, 
<table_name> is the name of the table used to anchor the data type, 
<column_name> is the name of the column used to anchor the data type, 
<value> is the initial value for the variable. 

NULL Value

The keyword NULL means NOT KNOWN.

NULL is not equal to anything, not even NULL.

NULL is not less than or greater than anything else, not even NULL.

You can test for NULL values in a SQL statement or PL/SQL code by using one of two phrases:

 is NULL 
 is not NULL 
 

We cannot use a logical operator with NULL, like

= NULL 

or

<> NULL 

Example 2

The following code shows how to create an Anonymous PL/SQL Procedure with Variable Declarations.

declare 
    n_id                 EMP.id%TYPE := 1; 
    v_first_name         EMP.first_name%TYPE := 'Jack'; 
    v_last_name          EMP.last_name%TYPE := 'Smith'; 
    d_birth_date         EMP.birth_date%TYPE := to_date('20000101', 'YYYYMMDD'); 
begin 
    null; 
end; 
/ 

• Next »
• « Previous