Reading:

To the point guide to PL/SQL

Data types

The variables and constants stores value in particular storage format. The PL/SQL Data types are:

Scalar data types: This data types haven't internal components like Date, Number or Boolean values.
Composite data types: This data types have components of internal to manipulate data. Eg: Collections
Reference data types: It works like a pointer to store some value.
LOB data types: This data types stores large objects such as graphics, images and video.

Scalar Data Types and Subtypes:

This data types like a linear data type and they are divided into four type’s numeric, character, boolean and date/time type.

The NUMBER is a data type which has a subtype known as INTEGER.

Numeric Data types:

Numericdatatypesandtheirsub-types in PL/SQLpre-definedare:

Data types	Description
NUMBER	Number data types on which arithmetic operations are performed.
	Sub-data types		Precision
	INTEGER		38 decimal digits
	INT		38 decimal digits
	SMALLINT		38 decimal digits
	DEC		38 decimal digits
	DECIMAL		38 decimal digits
	NUMERIC		38 decimal digits
	REAL		63 binary digits
	DOUBLE PRECISION		126 binary digits
	FLOAT		126 binary digits
BINARY_INTEGER	This data type used to store signed integer's value.
	Sub-data types	Description
	NATURAL	Allows only positive values
	POSITIVE	Allows only positive values
	NATURALN	Not allows assigning a NULL value.
	POSITIVEN	Not allows assigning a NULL value.
	SIGNTYPE	Allow only -1, 0, and 1 values
PLS_INTEGER	This is a data type which is used to store signed integers data.

Character Data types:

Some of PL/SQL pre-defined character data types and sub-types are:

Data Type	Description
CHAR	It is data type which is used to store character data within predefined length, it store maximum 32767 bytes.
CHARACTER	It is same as CHAR type and is another name of CHAR type, it store maximum 32767 bytes.
VARCHAR2	It is used to store character strings data within defined length, it store maximum 32767 bytes.
LONG	It is used to store character string data within defined length, it store maximum 32767 bytes.
LONG RAW	Byte string with maximum size of 32,760 bytes, but it is not interpreted by PL/SQL
NCHAR	It is used to store data of national character.
NVARCHAR2	It is used to store data of Unicode string.
ROWID	Physical row identifier which represents a row storage address
UROWID	Identifies as universal rowed which is same as ROWID type.

Boolean Data types:

This is a data type which stores values either True or False.

Data types

Description

Boolean

This data types doesn't allow any parameters. It allows either TRUE or FALSE and also store NULL.

NULLs in PL/SQL

NULL values are not an empty data string or an integer and a null can only be assigned a values.

Date/Time Data types and Interval Types:

The Date is a data type to store date and times within fixed length. The valid dates range from 1, 4712 BC to December 31, 9999 AD.

DD-MON-YY is default date format of Oracle.02-FEB-15 where date includes the century, year, month, day, and hour, minute, second.

Name	Datetime Values
YEAR	-4712 to 9999 (excluding year 0)
MONTH	01 to 12
DAY	01 to 31 (according to the rules of the calendar for the locale the values may vary)
HOUR	00 to 23
MINUTE	00 to 59
SECOND	00 to 59.9(n), where 9(n) is the precision of time fractional seconds
TIMEZONE_HOUR	- 12 to 14 (the values may vary according to daylight savings time changes)
TIMEZONE_MINUTE	00 to 59
TIMEZONE_ABBR	Found in the dynamic performance view V$TIMEZONE_NAMES
TIMEZONE_REGION	Found in the dynamic performance view V$TIMEZONE_NAMES

LOB types:

This is a data type which is used to store large objects like image, graphics, video, text or audio. The PL/SQL LOB data types are:

Data type	Description
BFILE	This data type is used to store large binary objects into OS file.
BLOB	This data type is used to store large binary objects in the database.
CLOB	This data type is used to store large group of data of character in the database.
NCLOB	This data type is used to store large group of data of NCHAR in the database.

User-Defined Subtypes:

User can create own sub type which is inherit from base type. Sub types can provide compatibility and increase reliability with ISO type and sub type has valid operations as same as base type. The predefined sub types are in STANDARD package.

DECLARE
   SUBTYPE msg IS varchar2(25);
   SUBTYPE marks IS INTEGER(2,0);
   description msg;
   scored marks;
BEGIN
   description := 'Jazz';
   marks := 35;
   dbms_output.put_line('Listen! ' || description || ' has scored ' || marks || ' marks!!!.');
END;
/

Output

Listen! Jazz has scored 35 marks!!!. 
PL/SQL procedure successfully completed

That was a rather very quick introduction to the data types, but that's the thing about this tutorial, just to the point learning. In next part we will get our head around variables.

Description

This tutorial focus on PL/SQL and covers the below topics

What is PL/SQL?
Environment Setup
Variables
Data Types
Constants
Operators
Conditions
Loops
Strings
Arrays
Procedures
Functions
Cursors
Records
Exceptions
Packages
Triggers
Collections
Transactions
Date & Time
Object Oriented
DBMS Output

If you found any error with any of the docs please let us know.

Learning Objectives

Learn PL/SQL from a beginners perspective, this guide can also help you if you are trying to brush up your PLSQL skills

Author: Subject Coach
Added on: 20th Apr 2015

You must be logged in as Student to ask a Question.

None just yet!