PL/SQL Placeholders

Placeholders are temporary storage area. Placeholders can be any of Variables, Constants and Records. Oracle defines placeholders to store data temporarily, which are used to manipulate data during the execution of a PL SQL block.
Depending on the kind of data you want to store, you can define placeholders with a name and a datatype. Few of the datatypes used to define placeholders are as given below. Number (n,m) , Char (n) , Varchar2 (n) , Date , Long , Long raw, Raw, Blob, Clob, Nclob, Bfile
Place holders are used for
• Temporary storage of data, • Manipulation of stored values, • Reusability, • Ease of maintenance
Declaring PL/SQL Variable

Handling Variables in PL/SQL

• Declare and initialize variables in the declaration section.
• Assign new values to variables in the executable section.
• Pass values into PL/SQL blocks through parameters.
• View results through output variables.

Types of PL/SQL Variables
All PL/SQL variables have a data type, which specifies a storage format, constraints, and valid range of values. PL/SQL supports four data type categories—scalar, composite, reference, and LOB (large object)—that you can use for declaring variables, constants, and pointers.

1. Scalar data types hold a single value. The main data types are those that correspond to column types in Oracle server tables; PL/SQL also supports Boolean variables.
2. Composite data types, such as records, allow groups of fields to be defined and manipulated in PL/SQL blocks.
3. Reference data types hold values, called pointers, that designate other program items. Reference data types are not covered in this course.
4. LOB data types hold values, called locators, that specify the location of large objects (such as graphic images) that are stored out of line. LOB data types are discussed in detail later in this course.

Non-PL/SQL variables include host language variables declared in precompiler programs, screen fields in Forms applications, and iSQL*Plus host variables.
 
Quick Notes – Variable  Declaration
  1. The rules for identifiers are same as for SQL objects.
  2. NOT NULL/CONSTANT may be optionally used
  3. Only one identifier per line is allowed .  
     DECLARE 
        firstname    lastname  CHAR(20)  ; – illegal 
    DECLARE 
        firstname    CHAR(20)  ; -legal 
        lastname    CHAR(20)  ; – legal 

Attribute Declaration
PL/SQL objects (such as variables and constants) and database objects (such as columns and tables ) are associated with certain attributes.
%TYPE attribute
DECLARE
  books_printed        NUMBER (6);
  books_sold            books.sold%TYPE ;
  maiden_name        emp.ename%TYPE ;
%ROWTYPE attribute
DECLARE
  dept_row        dept%ROWTYPE ;