Variable In PL/SQL

---

SELECT INTO clause is used to place the returned data into predefined variables.

Use Of Variables:

• Reusability.
• Temporary Storage.
• Data Manipulation.
• Easy Of Maintenance.

Declare PL/SQL variables:

• All variables are declared and initialized only in the DECLARATIVE section.
• Maximum length  of variable is 30 characters .
• Variable name should be meaningful.
• PL/SQL is case-insensitive, which means v_all_data and V_ALL_DATA refer to the same variable.
• To declare the variable , variable name followed by data type and terminated by semicolon';'.

Variable naming convention:
You should follow the below variable naming convention , which is highly recommended for a standard code. 

Prefix	Data Type
v_	VARCHAR2
n_	NUMBER
t_	TABLE
r_	ROW
d_	DATE
b_	BOOLEAN

Assign value to a variable:
    In PL/SQL assigning values to a variable in three way

• By using assignment operator (:=).
• By fetching data base values (using INTO ).
• By passing IN,OUT or IN OUT to a subprogram.

Syntax:

VARIABLE_NAME DATATYPE  [NOT NULL :=DEFAULT VALUE];

Example:

DECLARE /*Declare variable , variable name V_EMPLOYEE_NAME*/  V_EMPLOYEE_NAME VARCHAR2(100);  BEGIN SELECT ENAME INTO V_EMPLOYEE_NAME FROM EMP WHERE EMPNO=7369;  DBMS_OUTPUT.PUT_LINE('Employee name is '||V_EMPLOYEE_NAME); END;

Base on variable declaration we can classify into two types

Local Variables: Local variable declare in inner block which cannot be referenced by outside blocks. 

Global Variables: Global variable declare in outer block and which can referenced by all inner blocks.

Example Of Global and Local Variables :

DECLARE  /*Declare global variable*/  V_GLOBAL_VAR varchar2(100):='Rabindra';  BEGIN DBMS_OUTPUT.PUT_LINE('Global variable in outer block :' ||V_GLOBAL_VAR);  declare   /*Declare local variable*/   V_LOCAL_VAR varchar2(100):='Oracle';  begin  /*Now i can access global variable in inner block*/  DBMS_OUTPUT.PUT_LINE('Global variable in inner block: '||V_GLOBAL_VAR);  DBMS_OUTPUT.PUT_LINE('Local variable in inner block: '||V_LOCAL_VAR);  end;  /*We cannot access Local variable in outer block, it will through error comment below DBMS_OUTPUT.PUT_LINE*/  --DBMS_OUTPUT.PUT_LINE('Local variable in outer block: '||V_LOCAL_VAR);  END;