Hi friend, Once again welcome to Oracle Gang. As I told, I am back with "Datatype in oracle". First of all why need datatype in oracle. We know that datatype is required in programming languages for declaring variables. But in oracle there is no need of variables then why datatype.The answer of these all questions is: In oracle datatype specifies:
- Types of data allow in column.
- Amount of memory allocated for that.
Now it's clear that why we use datatype in oracle. When we declaring any column then we must to specify a datatype for that column that is for what type of data stored in that column and if we store data then how many space occupy by that column.
Lets first talk about string datatype.
Char:
Char datatype allows character data up-to 2000 characters. It is recommended for fixed length fields. This is because it will occupy memory space that is specify at the time of field deceleration. For example: if we declaring a field with 20 character space then it will occupy 20 bytes for all rows.
In ago example there is a memory wasted if we declare a field with 20 character space and we storing only 6 character then rest of 14 bytes are wasted. So don't use char datatype for variable length field.
Varchar2:
It will allows data up-to 4000characters in 11g and 32767 characters in 12c. It is also recommended for variable length fields. Because no matter how much length of a field if we insert less then the size of field then it will automatically release rest of the memory.
Up-to oracle 6 there is a datatype called varchar but from oracle 7 there is new datatype introduced is called varchar2. Actually varchar is a ANSI datatype but varchar2 is oracle datatype. We can also use varchar in oracle 7 or later versions but varchar2 is recommended there after oracle version 7.
Nchar/Nvarchar2:
Here N means National. Here one question is arise that if we already have char and varchar2 then why nchar and nvarchar2. Lets see, char/varchar2 allows only ASCII characters. ASCII characters means "a-z, A-Z, 0-9, some special characters". Total number of ASCII characters is 256. But nchar/nvarchar2 allows UNICODE characters. It allows all ASCII characters and characters of different languages. Total number of UNICODE characters are 65536.
Now lets talk about number datatype.
Number(p):
It will allows numeric data up-to 38 characters. This is for to store integral value. For example:
empno number(4)
100 correct
1000 correct
10000 incorrect
1000.5 --> 1001 correct
Number(p,s):
Here P stands for precision. It means total number of digit allows in that field. S stands for scale. It means number of digit allowed after decimal. It means we can store decimal value in this type of datatype. Here the range of s is between -84 to 127. Lets take an example:
salary number(7,2)
5000 correct
5000.50 correct
50000.48 correct
500000.32 incorrect
50000.507463 --> 50000.51 correct
Here some of my friend confused about negative scale value [salary number(7,-2)]. Now lets talk on this.
If scale is positive value then number is rounded after decimal. But if scale is negative value then before dcimal value id rounded. Take an example:
field inputs oracle stores
salary number(7,-2) 4065.73 4100
salary number(7,-2) 31421.7923 31400
salary number(7,-1) 4279.87655 4280
salary number(7,-1) 324 320
The scale value in negative tell that how many digits is rounded before decimal. Lets describe one example in brief: as in first example there is number 4065.73 and scale is -2. So it will make two point that is 4000 and second is 4100. Because scale is 2 in negative so we take minimum and maximum number for last two digit. Now take a middle of both number that is 4050. Now compare both middle number and actual number which one is greater. If actual number is greater then maximum value is result. But if middle number is greater then minimum number is result.
For now that's all. We will talk about rest datatype in next post.....
No comments:
Post a Comment