PL/SQL Programming   «Prev  Next»

Lesson 4Variable datatypes
ObjectiveIdentify the Different Datatypes within PL/SQL

Identify the Different Datatypes within PL/SQL

Every variable has a datatype, which specifies a storage format, constraints, and a valid range of values. PL/SQL provides a variety of datatypes: SCALAR, COMPOSITE, REFERENCE, and LOB.
You define a variable for a particular datatype to be able to manipulate the data. Review the following series of diagrams to identify the different datatypes available within PL/SQL.

1) Scalar, Composite, Reference, and LOB Types in PL/SQL
1) Scalar Type, 2) Composite, 3) Reference Type, and 4) LOB Types in PL/SQL

2) A scalar datatype has no internal components and holds a single value.
2) A scalar datatype has no internal components and holds a single value

3) A composite datatype has internal components that can be manipulated individually
3) A composite datatype has internal components that can be manipulated individually. PL/SQL TABLE and RECORD fall under the category of a composite datatype.

4) A  reference datatype variable holds a value, called pointer, that designates other program items.
4) A reference datatype variable holds a value, called pointer, that designates other program items. Variables that are declared with the %TYPE and %ROWTYPE would be good examples of reference datatype variables.

5) LOB large objects datatype holds a value, called locator, that specifies the location of a large object.
5) a) LOB large objects datatype holds a value, called locator, that specifies the location of a large object.
b) With LOB datatypes, you can store blocks of unstructured data such as text, graphic images, video clips, and audio files up to 4 gigabytes in size.
c) LOB datatypes allow efficient, random, piecewise access to the data and can be further divided into 4 categories as show above.

Program PL/SQL Datatypes
PL/SQL datatypes include all the valid SQL datatypes as well as complex datatypes based on query structures.

Scalar Datatypes

scalar_datatype_name: A predefined scalar data type such as BOOLEAN, NUMBER, or VARCHAR2. Includes any qualifiers for size, precision, and character or byte semantics.
BINARY_DOUBLE	FLOAT	NUMBER	SMALLINT
BINARY_FLOAT	INT	NUMERIC STRING
BINARY_INTEGER	INTEGER	NVARCHAR2	TIMESTAMP
BOOLEAN	INTERVAL	DAY
TO	SECOND
PLS_INTEGER	TIMESTAMP	WITH	LOCAL
TIME ZONE	
CHAR INTERVAL YEAR
TO MONTH
POSITIVE	TIMESTAMP	WITH	TIME
ZONE
CHARACTER	LONG	POSITIVEN	UROWID
DATE	LONG	RAW	RAW	VARCHAR
DEC	NATURAL	REAL	VARCHAR2
DECIMAL	NATURALN	ROWID
DOUBLE
PRECISION
NCHAR SIGNTYPE

Composite Types

RECORD TABLE VARRAY

Reference Types

REF CURSOR REF object_type

LOB Types

BFILE BLOB CLOB NCLOB
In the next lesson, non-PL/SQL bind and host variables will be discussed.

Variable PL/SQL Data

The link below discusses more about variable PL SQL Data Types.
Variable PL/SQL Data Types