PL/SQL Variables

In PL/SQL, variables are naming containers used to store data value and then refer to the data simply by naming the container. Before you use a variable in a PL/SQL program, you must declare it.

Each variable in the PL/SQL has a specific data type which defines the size of the variable's memory.

PL/SQL Variables Naming Rules

PL/SQL follows the naming rules as follows:

The variable name must be less than 31 characters.
The variable must begin with a letter or the underscore character.
Followed by the first character are any number, underscore ( _), and dollar sign ( $) characters.

PL/SQL Variables Declaration

The PL/SQL variable should always be declared in the declaration section or in a package as a global variable. Once you declared a variable, memory will be allocated to the variable's value and location will be identified by the variable name.

To declare a variable, you use a variable name followed by the data type and terminated by a semicolon ( ;). The syntax for declaring a variable is:

PL/SQL Variable Declaration Syntax

To declare a variables, use the following syntax:

Syntax

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

Where:

variable_name: The valid identifier in PL/SQL.

datatype: The valid PL/SQL data type with size.

order number(12, 2); 
pi CONSTANT double precision := 3.1415; 
fname varchar2(50); 
lname varchar2(50);
address varchar2(150);

If you specify a size, scale or precision limit with the data type, it is called a constrained declaration.

PL/SQL Initializing Variables Syntax

To declare a variables, use the following syntax:

Syntax

counter binary_integer := 5;  
message varchar2(50) DEFAULT 'Wish you a happy birthday';

In PL/SQL, if you do not initialize variable using assignment operator, default value will be NULL. To avoid NULL value, you can initialize a variable with a value, you can do so during the declaration.

PL/SQL Variable Initilizing Example

To initilize a variable, use the following syntax:

Syntax

DECLARE 
   counter integer := 0; 
   income integer := 10000;
   pf integer := 500;
   salary real; 
BEGIN 
   counter := counter + 1; 
   DBMS_OUTPUT.PUT_LINE('Value of Counter: ' || counter); 
   salary := income + pf;
   DBMS_OUTPUT.PUT_LINE('Value of Salary: ' || salary); 
END;

PL/SQL Variables Scope

Like any other programing language, PL/SQl also allows you to write nested code or subprogram. If variables declare in subprogram, it will not be accessible to an outer block or main program. There scopes are categorize into two types:

Local Variable: Local variables only accessible to inner block.
Global Variable: Global variables are declared in outermost block are accessible.

PL/SQL Variable Initilizing Example for Local and Global

To initilize a variable, use the following syntax:

Syntax

DECLARE 
   -- Global variables  
   a number := 103;  
   b number := 21;  
BEGIN  
   dbms_output.put_line('Outer Variable a: ' || a); 
   dbms_output.put_line('Outer Variable b: ' || b); 
   DECLARE  
      -- Local variables 
      a number := 500;  
      b number := 768;  
   BEGIN  
      dbms_output.put_line('Inner Variable a: ' || a); 
      dbms_output.put_line('Inner Variable b: ' || b); 
   END;  
END;

« Previous