Introduction to PL/SQL (Variable & Data type)

Variable: Variable are mainly used for temporary storage of data and manipulation of stored data. Variable can stored any PL/SQL objects, such as variables, types, cursors and subprograms. Variable can be used repeatedly in an application by referring to them in the statements.

Identifier & Variable: Identifier are name of the variables. Variable are storage location of data. Data stored in memory. Variable point to this memory location where data can be read and modified. Identifier are used to name PL/SQL objects and Variable are used to store PL/SQL objects.

You must declare all PL/SQL identifier in the declaration section before referencing them in the PL/SQL block.


identifier [CONSTANT] datatype [not null] [:= | DEFAULT expr];


  hire_date DATE;
  dept_no NUMBER(2) NOT NULL := 10;
  location VARCHAR2(10) := 'Dhaka';
  c_comm CONSTANT NUMBER := 1400;
  country VARCHAR2(10) DEFAULT 'Bangladesh';

Types of Variable:
PL/SQL Variable –

  • Scalar: Scalar data type can hold single value.
  • Comosite: Composite data type contain internal elements that are either scalar or scalar. Record and Table are example of examples of composite data types.
  • Reference: Reference data types hold values, called pointers, that point to a storage location.
  • Large Objects (LOB): LOB data type hold values, called locators, that specify the location of large objects that are stored out of line.

Non-PL/SQL Variables – Bind variable.

Initialize the variable to an expression with the assignment operator (:=) or with the DEFAULT reserved word.

Base Scalar Data Types:

Data Type Description
CHAR (max_length) Base type for fixed-length character data up to 32767 bytes.
VARCHAR2 (max_length) Base type for variable-length character data up to 32767 bytes.
Number (p,s) Number having precision p and scale s. The precision p can be range from 1 to 38. The scale s can range -84 to 127.
BINARY_INTEGER Base type for integers between -2,147,483,647 and 2,147,483,647.
PLS_INTEGER Base type singed integers between -2,147,483,647 and 2,147,483,647. The arithmetic operation on PLS_INTEGER and BINARY_INTEGER values are faster than on number values.
BOOLEAN Base type that stores one of the three possible values used for logical calculations: TRUE, FALSE and NULL.
BINARY_FLOAT Represents floating point number in IEEE 754 format. Requeires 5 bytes to store the value.
BINARY_DOUBLE Represents floating point number in IEEE 754 format. Requeires 9 bytes to store the value.
DATE Base type for date and time.
TIMESTAMP The TIMESTAMP data type, which extends the DATE type, stores the year, month, day, hour, minute, second and fraction of second.
TIMESTAMP WITH TIME ZONE The TIMESTAMP WITH TIME ZONE data type, which extends the TIMESTAMP data type, includes a time zone displacement.
TIMESTAMP WITH LOCAL TIME ZONE The TIMESTAMP WITH LOCAL TIME ZONE data type, which extends the TIMESTAMP data type, includes a time zone displacement. The time zone displacement is the difference between local and UTC time.

Declare Variable with %TYPE Attribute:  %type attribute is used to declare a variable according to a database column definition or another declared variable. Such as…

  lname employees.last_name%TYPE,
  dept_no NUMBER;
  manager dept_no%TYPE;

Bind Variable: Bind variable are crated in the environment and not in the declarative section of a PL/SQL block. Variable declared in a PL/SQL block are available only when you execute the block. After the block is executed, the memory used by the variable is freed. Sometimes they are called host variable and create with VARIABLE command.

  SELECT last_name INTO :name 
  FROM employees WHERE employee_id = 100;


Introduction to PL/SQL (Basic)

PL/SQL (Procedural Language/Structured Query Language): PL/SQL is Oracle Corporation’s procedural language extension for SQL and Oracle corporation’s standard data access language for relational database. PL/SQL is available in Oracle Database since version 7. PL/SQL includes procedural language elements such as conditions and loops. You can declare constants and variables, procedures and functions, types and variables of those types, and triggers. You can handle exceptions (runtime errors). Arrays are supported involving the use of PL/SQL collections.


PL/SQL Block Structure: A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. The declaration section is optional and may be used to define and initialize constants and variables. If a variable is not initialized then it defaults to NULL value. The optional exception-handling part is used to handle run time errors. Only the executable part is required. PP/SQL block structure like this…

DECLARE (optional)
  -- Variable, cursors, user defined exceptions.
BEGIN (mandatory)
  -- SQL Statements
  -- PL/SQL Statements
Exception (optional)
  -- Actions to perform when error occur
END (mandatory)

Block type: There are three type of block make up a PL/SQL progarm. they are…

  • Anonymous block: Anonymous block are unnamed blocks. These block are not stored in the database.
  • Procedures: Procedure are a named PL/SQL block that are stored in the database and perform an action.
  • Functions: Function similar to procedure, except that a function must return a value.

Test your output: Enable output in SQL*Plus with the this command –  SET SERVEROUTPUT ON and use a predefined Oracle package and its procedure – DBMS_OUTPUT.PUT_LINE 


  DBMS_OUTPUT.PUT_LINE ('Hello World!');