PL/SQL Exception

PL/SQL Exception

PL/SQL Interview Questions

PL/SQL Interview Questions and Answers

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.
  • The variable names are case sensitive. For example, Customer and customer are two different variables.
  • 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:


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


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:


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:


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

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:


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