Variable Assignment and Initialization

PL/SQL is a procedural extension to SQL designed to work with ORACLE database. This is similar to that of SQL Server T-SQL. Learning PL/SQL is easy when you know Oracle database objects and SQL. I believe learning PL/SQL by following the examples is more effective therefore this tutorial will cover more examples than definition and explanation. I hope you will find this tutorial helpful and make your PL/SQL learning fun,easy and super fast.

Throughout the tutorial an Anonymous PL/SQL block will be used as examples. What is Anonymous block? A Anonymous block is block of PL/SQL code without a name that are not stored for re-use. Anonymous block no longer exist after execution. PL/SQL block structure contains four main sections.

• DECLARE: variable, cursors and types are created or declared
• BEGIN: body of PL/SQL that contains SQL statements, conditional logic, loop, variable assignment and more.
• EXCEPTION: catches errors and exceptions
• END: end of PL/SQL block

All the examples in this tutorial are anonymous block, later in the tutorial, we will have name blocked till then enjoy anonymous block. These examples contain scalar variable which only hold one value but later you will be introduced you with the composite variable that can hold more than one values.

Variable Declaration:

SET serveroutput ON; 
DECLARE 
    lv_ship_date            DATE; 
    lv_ship_status_char     VARCHAR2(10); 
    lv_order_number_num     NUMBER(10); 
    lv_payment_received_bol BOOLEAN; 
BEGIN 
    lv_ship_date := '08-AUG-15'; 
    lv_ship_status_char := 'Pending'; 
    lv_order_number_num := 456895; 
    lv_payment_received_bol := TRUE; 
    dbms_output.Put_line(lv_ship_date); 
    dbms_output.Put_line(lv_ship_status_char); 
    dbms_output.Put_line(lv_order_number_num); 
END; 
/ 

Output:
PL/SQL procedure successfully completed.
08-AUG-15
Pending
456895

Note:

1. DBMS_OUTPUT.PUT_LINE is an Oracle supplied package that contains PUT_LINE procedures. This package/procedure display the content or value of a variable.
2. SET SERVEROUTPUT ON : Enables or disables the output made by DBMS_OUTPUT package. example SET SERVEROUTPUT [ON|OFF]
3. / executes the PL/SQL block
4. variable_name := value or expression. The colon before an equal sign assigns the value or expression to a variable.

Variable Initialization:

Any variable you define or created must be initialized. Variables that are not initialized are NULL value which might often result into bad output when performing a calculation. 

SET serveroutput ON; 
DECLARE 
    lv_ship_date            DATE := '08-AUG-15'; 
    lv_ship_status_char     VARCHAR2(10) := 'Pending'; 
    lv_order_number_num     NUMBER(10) := 456895; 
    lv_payment_received_bol BOOLEAN := FALSE; 
BEGIN 
    dbms_output.Put_line(lv_ship_date); 
    dbms_output.Put_line(lv_ship_status_char); 
    dbms_output.Put_line(lv_order_number_num); 
END; 
/ 

Output:
PL/SQL procedure successfully completed.
08-AUG-15
Pending
456895

Note: DBMS_OUTPUT. package does not print the value of a Boolean variable therefore you will need to use some other package or need to perform some work around to display Boolean variable. Boolean variable are mail to evaluate and make decision.

DEFAULT:  

You are required to have the initial values for some variables which can be accomplished by the initialization of variables. The keyword DEFAULT is same as := which assigns a value to a variable. Boolean and Number should be initialized otherwise you will be working with NULL. NULL is not equal to 0 which might create a problem while performing calculation with number that is not initialized.

SET serveroutput ON; 
DECLARE 
    lv_ship_date            DATE DEFAULT '08-AUG-15'; 
    lv_ship_status_char     VARCHAR2(10) DEFAULT 'Pending'; 
    lv_order_number_num     NUMBER(10) DEFAULT 456895; 
    lv_payment_received_bol BOOLEAN DEFAULT FALSE; 
BEGIN 
    dbms_output.Put_line(lv_ship_date); 
    dbms_output.Put_line(lv_ship_status_char); 
    dbms_output.Put_line(lv_order_number_num); 
END; 
/ 

Output
PL/SQL procedure successfully completed.
08-AUG-15
Pending
456895

NOT NULL and CONSTANT:  

The NOT NULL option requires the variable assignment and the value may change throughout the block. CONSTANT option can be added to to make the variable constant meaning the value cannot be changes throughout the block execution.

SET serveroutput ON; 
DECLARE 
    lv_ship_date            DATE NOT NULL := '08-AUG-15'; 
    lv_ship_status_char     VARCHAR2(10) DEFAULT 'Pending'; 
    lv_order_number_num CONSTANT NUMBER(10) := 10000; 
    lv_payment_received_bol BOOLEAN DEFAULT FALSE; 
BEGIN 
    dbms_output.Put_line(lv_ship_date); 
    dbms_output.Put_line(lv_ship_status_char); 
    dbms_output.Put_line(lv_order_number_num); 
END; 
/ 

Output
PL/SQL procedure successfully completed.
08-AUG-15
Pending

10000