PLSQL DATATYPES

Data Types

• NUMBER
• ALPHABETS
• SYMBOLS

NUMBERS

This data types allows us to enter numeric values such as integer numbers or decimal numbers.

This data type is further divided into two types-

NUMBER(SIZE)

This data type allows us to enter integer values such as student roll no , employee no’s etc . Here size represents no of digits.
e.g. slno number(5)

NUMBER(PRECISON,SCALE)

This datatype allows us to enter decimal values such as student average marks employee salary etc
Here ‘p’ stands for precision . precision means no of digits including digital points and where as scale means other than decimal places.
e.g number(p,s)
salary number(4,2)

ALPHABET

This datatype allows us to enter character values such as student name , employee name etc . This Data Type is divided into -

• CHAR(SIZE)
• VARCHAR(SIZE)
• VARCHAR2(SIZE)

CHAR(SIZE)

This data type allows us to enter character values such as student name employee name etc. The maximum length of this datatype is 1-2000 characters . It always allows to allocate memory in static fashion.

VARCHAR(SIZE) and VARCHAR2(SIZE)

These two data types allows us to enter character values such as employee name , student name etc . The maximum length of this data type is 1-4000 bytes/characters. It always allocates memory in dynamic fashion.
Note: a difference between varchar and varchar2 datatype is that varchar datatype is developed along with sql where as varchar2 datatype was developed by oracle corporation

DATE

This data Type allows us to enter date values such as the student joining date , employee hiring date etc.

whenever we need to enter date values like student joining date then we use this data type.
the default format is DD-MON-YY. e.g 04-feb-14
the range of date data type is 1-jan-4712 bc to 31-DEC-9999AD
e.g Hiring_date Date

TIMESTAMP

This datatype allows us to enter both date and time .
whenever we need to enter login time to the user and logoff time of user then timestamp is used
the default is dd-mon-yy hh:mm:ss
e.g 04-feb-14 10:01:22

Other datatypes:

• CLOB – Character large object
• BLOB- Binary large object
• BFILE- Binary file

CLOB

This datatype allows us to enter all kinds of values such as numeric values ,alphanumeric and special characters.
the max length is 1 to 4 Gb
whenever we need to enter employee history student contact etc then clob is used
e.g mobile no

BLOB

This datatype allows us to enter any kind of pictures or clipping multimedia message sounds etc.
max length 1- 4Gb
e.g to upload photos etc

BFILE

This datatype allow us to enter any kind of external files such as xml files , binary files etc.
max length is 1-4 Gb