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.
- 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.
- Composite data types, such as records, allow groups of fields to be defined and manipulated in PL/SQL blocks.
- Reference data types hold values, called pointers, that designate other program items. Reference data types are not covered in this course.
- 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 .
firstname lastname CHAR(20) ; – illegal
firstname CHAR(20) ; -legal
lastname CHAR(20) ; – legal
PL/SQL objects (such as variables and constants) and database objects (such as columns and tables ) are associated with certain attributes.
books_printed NUMBER (6);
books_sold books.sold%TYPE ;
maiden_name emp.ename%TYPE ;
dept_row dept%ROWTYPE ;