- Oracle là gì? Các phiên bản của Oracle
- Kiểu dữ liệu (Data types) trong Oracle
Để tối ưu trong việc lưu trữ thì Oracle phân chia dữ liệu thành nhiều loại khác nhau, mỗi loại sẽ có đồ dài nhất định và phù hợp với từng trường hợp. Chúng ta có các nhóm chính như: Character, Numeric, Date/Time, Large Object (LOB).
1. Character Datatypes
Character Datatypes hay còn gọi là kiểu dữ liệu kí tự, nó là kiểu chuỗi và mỗi kiểu sẽ có độ dài khác nhau.
| Data Type Syntax | Oracle 9i | Oracle 10g | Oracle 11g |
| char(size) | 2000 bytes. | 2000 bytes. | 2000 bytes. |
| nchar(size) | 2000 bytes. | 2000 bytes. | 2000 bytes. |
| nvarchar2(size) | 4000 bytes. | 4000 bytes. | 4000 bytes. |
| varchar2(size) |
4000 bytes. 32KB in PLSQL. |
4000 bytes. 32KB in PLSQL. |
4000 bytes. 32KB in PLSQL. |
| long | 2GB. | 2GB. | 2GB. |
| raw | 2000 bytes. | 2000 bytes. | 2000 bytes. |
| long raw | 2GB. | 2GB. | 2GB. |
2. Numeric Datatypes
| Data Type Syntax | Oracle 9i | Oracle 10g | Oracle 11g |
|---|---|---|---|
| number(p,s) | range from 1 to 38. Scale can range from -84 to 127. |
range from 1 to 38. Scale can range from -84 to 127. |
range from 1 to 38. Scale can range from -84 to 127. |
| numeric(p,s) | range from 1 to 38. | range from 1 to 38. | range from 1 to 38. |
| float | |||
| dec(p,s) | range from 1 to 38. | range from 1 to 38. | range from 1 to 38. |
| decimal(p,s) | range from 1 to 38. | range from 1 to 38. | range from 1 to 38. |
| integer | |||
| int | |||
| smallint | |||
| real | |||
| double precision |
3. Date/Time Datatypes
| Data Type Syntax | Oracle 9i | Oracle 10g | Oracle 11g |
|---|---|---|---|
| date | between Jan 1, 4712 BC and Dec 31, 9999 AD. | between Jan 1, 4712 BC and Dec 31, 9999 AD. | between Jan 1, 4712 BC and Dec 31, 9999 AD. |
| timestamp (fractional seconds precision) | number between 0 and 9. (default is 6) | number between 0 and 9. (default is 6) | number between 0 and 9. (default is 6) |
| timestamp (fractional seconds precision) with time zone | numberr between 0 and 9. (default is 6) | number between 0 and 9. (default is 6) | number between 0 and 9. (default is 6) |
| timestamp (fractional seconds precision) with local time zone | number between 0 and 9. (default is 6) | number between 0 and 9. (default is 6) | a number between 0 and 9. (default is 6) |
| interval year (year precision) to month | number of digits in the year. (default is 2) | number of digits in the year. (default is 2) | number of digits in the year. (default is 2) |
| interval day (day precision) to second (fractional seconds precision) | number between 0 and 9. (default is 2) number between 0 and 9. (default is 6) |
number between 0 and 9. (default is 2) number between 0 and 9. (default is 6) |
number between 0 and 9. (default is 2) number between 0 and 9. (default is 6) |
4. Large Object (LOB) Datatypes
| Data Type Syntax | Oracle 9i | Oracle 10g | Oracle 11g |
|---|---|---|---|
| bfile | Maximum file size of 4GB. | Maximum file size of 232-1 bytes. | Maximum file size of 264-1 bytes. |
| blob | Store up to 4GB of binary data. | Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage). | Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage). |
| clob | Store up to 4GB of character data. | Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character data. | Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character data. |
| nclob | Store up to 4GB of character text data. | Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character text data. | Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character text data. |