DEFINITION:
Specifies what kind of Data We have to store in a Memory
SQL won’t support Boolean datatypes
Boolean Data types (TRUE, FALSE, NULL)
1) NUMBER
2) CHAR
3) VARCHAR OR VARCHAR2
4) DATE
5) TIMESTAMP
6) TIMESTAMP WITH TIMEZONE
7) TIMESTAMP WITH LOCAL TIMEZONE
8) INTERVAL DATATYPES
I) Interval Year to Month
II) Interval Day to Second
9) LONG
a) LONG
b) RAW
c) LONG RAW
10) LOB
- CLOB
- BLOB
- BFILE
- NCLOB
11)
ROWID
12)
UROWID
13)
BINARY_FLOAT
14)
BINARY_DOUBLE
15)
SIMPLE_INTEGER
16)
SUBTYPES
17)
NCHAR
18)
NVARCHAR2
19)
INTERNET DATAYPES
20) XML
DATATYPES
21)
OTHER DATATYPES
1) NUMBER:
SYN:
X NUMBER (P, (S));
To store numeric values
Max size is 38 digits.
2) CHAR:
SYN:
X CHAR(S [BYTES|CHAR]);
To store alphanumeric
values
Max size 2000 bytes
Default length is 1 Byte
Memory allocation is
constant (wastage of memory)
It specifies fixed length character strings
The size of a character can range from Byte to Bytes depending on the database character set
3) VARCHAR (ANSI) OR VARCHAR2 (ORACLE):
SYN: X VARCHAR2(S);
TO STORE ALPHANUMBERIC DATA
MAXSIZE 4000 BYTES and minimum size 1 Byte
MEMORY ALLOCATION IS
DYNAMIC(NO WASTAGE OF MEMORY)
4) DATE:
SYN: X
DATE; (DD-MON-YY HH:MI:SS)
To store the date value.
Max size 7 bytes.
Disadvantage:
Not possible to store
fraction of seconds.
Internally date value get stored in the form of Julian day number
5) TIMESTAMP (9I):
SYN: X TIMESTAMP [(P)]. (DD-MON-YY HH: MI:SS.FS)
It is derivative of data ,along with date it supports up to
fraction of
seconds.
It stores fraction of seconds up to 9 digits,by default 6 digits.
Disadvantage:
Not possible to store timezones (tzh:tzm)
6) TIMESTAMP WITH TIMEZONE (9I) (DD-MON-YY HH: MI: SS.FS THZ: TZM):
SYN: X TIMESTAMP [(P)] WITH
TIMEZONE;
In addition with timestamp values it stores time zone values.
7) TIMESTAMP WITH LOCAL TIMEZONE:
SYN: X TIMESTAMP WITH LOCAL
TIMEZONE.
Normalize the given time into ISO/GMT standard time
8) INTERVAL DATATYPE:
I) Interval Year to Month:
SYN: X INTERVAL YEAR TO MONTH.
To Store Year to
Month Interval Data
II) Interval
Day to Second:
SYN: X INTERVAL DAY TO SECOND.
To Store Day, Hour,
Minute, Seconds Data.
9) LONG:
a) LONG:
SYN: X LONG;
To store information.
Max size 2 gb.
Disadvantage:
We
have to use long datatype only once in an entire table.
So many
disadvantages are there for long this is why not Preferable.
b) RAW:
SYN: X RAW(S).
To store the images.
Max size 2000 bytes.
c) LONG RAW:
SYN: X LONG RAW.
To store information+ images
Max sized 2 gb
Not preferable, so many disadvantages.
10)
LOB: (LARGE OBJECTS (OOPS)):
1) CLOB :(
CHARACTER LARGE OBJECTS);
SYN: X CLOB;
To store huge information.
Max size depends on sql and pl/sql.
2) BLOB:
SYN: X BLOB;
To store images.
3) BFILE: (EXTERNAL DATATYPE)
SYN: X BFILE;
TO store files.
11)
ROWID:
SYN: X ROWID;
It is a datatype which is user to store the physical address of
the records ‘rowid’ values.
Rowid values are usefull to identify the records in a table.
12) UROWID (UNIVERAL
ROWIDS):
SYN: X UROWID;
We use it to store the logical address of index organized table
(IOT).
UROWID even store RDBMS
ROWID values.
13)
BINARY_FLOAT:
14) BINARY_DOUBLE:
They introduced from 10 g
They are used to store float values so to increase the performance
15)
SIMPLE_INTEGER:
16) SIMPLE_FLOAT:
17) SIMPLE_DOUBLE:
They are introduced from 11g
Useful in performance
Unlike other datatypes they won’t allow ‘null’ values
18) NCHAR:
First time defined in ORACLE 9i, and contains UNICODE data only
Maximum length is determined by the national character set definition
Maximum size is 2000 Bytes and size has to be specified
19) NVARCHAR2:
First time defined in ORACLE 9i
It is defined for UNICODE data only
The minimum size is 1 Byte and Maximum size is 4000 Bytes
20) NCLOB:
They are used to store the national database character set
They support multi languages.
No comments:
Post a Comment