PL/SQL Data Types

In this lesson, you will learn PL/SQL Data Types. PL/SQL variables and constants must have a valid data type. Which specifies storage format. There are six built-in PL/SQL data types

1. Scalar data types - Scalar data types haven't internal components.
2. Composite data types - Composite data types have internal components to manipulate data easily.
3. Reference data types - This data types work like a pointer to hold some value.
4. LOB data types - Stores large objects such as images, graphics, video.
5. Unknown Column types - Identify columns when not know the type of column.
6. User Define data types - Define your own data type that inherited from predefined base data type.

Scalar types

Scalar data type haven't internal components. It is like a linear data type. Scales data type divides into four different types character, numeric, boolean or date/time type.

Numeric Data types

Following are numeric data types in PL/SQL:

Character Data types

Character Data types used to store an alphabetic/alphanumeric character. Following are some character data types in PL/SQL,

Boolean Data types

Boolean Data types stores logical values either TRUE or FALSE. Let's see Boolean data types in PL/SQL:

Date/Time Datatypes

A variable that has date/time data type hold value call datetimes. Oracle SQL automatically converts character value into default date format ('DD-MON-YY') TO_DATE values. Following are Date/Time data types in Oracle SQL.

Following are the Interval data types in PL/SQL:

DECLARE
   inter INTERVAL YEAR(2) TO MONTH;
BEGIN
   inter := INTERVAL '45-7' YEAR TO MONTH;
   inter := '45-7'; -- assign from character type (implicit conversion)
   inter := INTERVAL '7' MONTH; -- Specify Months   
   inter := INTERVAL '45' YEAR; -- Specify years
END;

DECLARE
   inter INTERVAL DAY(3) TO SECOND(3);
BEGIN
   IF inter > INTERVAL '6' DAY ...
   ...
END;

In PL/SQL datetime data type or interval data type fields values show the valid values for each field.

Large Object Datatypes (LOB types)

LOB data types use to store large object such as image, video, graphics, text, audio. Maximum size up to 4 Gigabytes. Following are LOB data types in SQL.

Unknown Column types

PL/SQL this data type is used when column type is not know.

User-Defined Subtypes

PL/SQL gives you the control to create your own sub data type that inherited from a predefined base type. Subtypes can increase reliability and provide compatibility with ANSI/ISO type. Several predefined subtypes are in a STANDARD package.

Defining Subtypes

You can define your own subtypes in the declarative part of PL/SQL block using the following syntax,

SUBTYPE subtype_name IS base_type[(constraint)] [NOT NULL];

Following the example, predefined data type inherits from CHARACTER and INTEGER data type to make a new sub type,

SUBTYPE CHARACTER IS CHAR;
SUBTYPE INTEGER IS NUMBER(10,4);  -- allows for numbers

Example

DECLARE
   SUBTYPE message IS varchar2(25);
   SUBTYPE age IS INTEGER(2,0);
   description message;
   ages age;
BEGIN
   description := 'Web Developer';
   ages := 22;
   dbms_output.put_line('I am ' || description || ' and I am ' || ages || ' years Old.');
END;
/

Result

• « Previous
• Next »