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.
Syntax:
identifier [CONSTANT] datatype [not null] [:= | DEFAULT expr];
Example:
DECLARE hire_date DATE; dept_no NUMBER(2) NOT NULL := 10; location VARCHAR2(10) := 'Dhaka'; c_comm CONSTANT NUMBER := 1400; country VARCHAR2(10) DEFAULT 'Bangladesh'; BEGIN ... END;
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.
Example:
VARIABLE name VARCHAR2 SET AUTOPRINT ON BEGIN SELECT last_name INTO :name FROM employees WHERE employee_id = 100; END; /
continue….