Declaring PLSQL variables

In PL/SQL (Procedural Language/Structured Query Language), you declare variables using the DECLARE keyword. Variables are used to store and manipulate data within PL/SQL blocks.

PL/SQL Place holders :

    constant -  sal constant number(3) :=100;
    type emp_type is record( a ; b;);
    variable -  variable datatype;
    a number,
    %type,
    %rowtype
    := assigning operator

Here's a basic syntax for declaring variables in PL/SQL :

    DECLARE
    -- Declaration of variables
    variable_name1 datatype;
    variable_name2 datatype;
    -- ... additional variable declarations ...

    BEGIN
    -- PL/SQL block statements
    -- You can use variables here

    END;
    /

DECLARE : This keyword is used to introduce the declaration section of a PL/SQL block where you declare variables.

variable_name1, variable_name2, etc. :These are the names of the variables you want to declare.

datatype :

        Datatype	                    Declartion
       ------------       ---------------------------------------------------
        number	            fixed or floating point numbers.
        binary_integer	    integer values 
        pls_integer         used for fast integer corruptions (new) 
        char	            fixed length character strings.
        varchar2            variable length character strings.
        date 	            date storage.
        clob	            larger character datatype (upto 4.3GB of data)

BEGIN and END : These keywords enclose the executable statements of the PL/SQL block.

* /: This symbol is used to execute the PL/SQL block. You typically use it to run an anonymous PL/SQL block in a SQL*Plus environment.
Here's an example :

    DECLARE
    -- Declare variables
    employee_name VARCHAR2(50);
    employee_age NUMBER;
    hire_date DATE;

    BEGIN
    -- Assign values to variables
    employee_name := 'John Doe';
    employee_age := 30;
    hire_date := TO_DATE('2023-01-01', 'YYYY-MM-DD');

    -- Display values
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || employee_name);
    DBMS_OUTPUT.PUT_LINE('Employee Age: ' || TO_CHAR(employee_age));
    DBMS_OUTPUT.PUT_LINE('Hire Date: ' || TO_CHAR(hire_date, 'DD-MON-YYYY'));

    END;
    /

« Previous Next Topic » (PL/SQL - Writing PLSQL Codes)