What are the data types of Oracle?

Oracle’s data types include: 1. String type, char and varchar2, which can express any string; 2. Numeric type, number(m,n), can express any number; 3. Date type, date, stores date and time; 4. clob type, stores single-byte string or multi-byte string data; 5. blob type, stores unstructured binary data; 6. rowid type, stores records in tables in the database The physical address in; 7. Other data types.

#The operating environment of this article: Windows 10 system, Oracle version 19c, dell g3 computer.

Oracle’s data types include the following:

1. String types: char and varchar2, which can express any string.

2. Number type: number(m,n), which can express any number. m is the total length of the number, n is the number of digits after the decimal point. If n is 0, it means it is an integer.

3. Date type: date, which stores date and time, including year (yyyy), month (mm), day (dd), hour (hh24), minute (mi), and second (ss).

4. Clob type, which stores single-byte string or multi-byte string data, such as text files and xml files.

5. Blob type, which stores unstructured binary data, such as pictures, audio, video, office documents, etc.

6. Rowid type, which stores the physical address recorded in the database in the table.

7. Other data types.

1. String type

is used in C/C language. Strings are enclosed in double quotes. In Oracle database, strings are enclosed in single quotes. It's as follows:

'www.freecplus.net'

'The coder has a way'

'A silly bird'

1 , Fixed-length string

Fixed-length strings are represented by char. When the length of the stored data content is not enough, Oracle will automatically fill in spaces after the data content to reach its fixed length, such as char(10 ) always contains 10 bytes of information.

The char field can store up to 2000 bytes of content.

2. Variable-length strings

Variable-length strings are represented by varchar2. Unlike the char type, Oracle will not fill in anything after the data content.

The varchar2 field can store up to 4000 bytes of content. Starting from Oracle 12c version, it can store 32767 bytes of content.

3. Comparison of char and varchar2

char(10), if stored in 'freecplus', 'freecplus' will be stored in the database, with a space added at the end.

varchar2(10), if you store 'freecplus', 'freecplus' will be stored in the database and nothing will be added.

In practical applications, we do not want Oracle to add spaces after the string, so can the char type be abandoned? No, we generally use the char type to store fixed-size data content, such as ID number, which is always 18 bits, so char(18) is very suitable. Can I use varchar2(18) to store ID number? Of course you can, but the efficiency of char(18) is much higher than that of varchar2(18).

To summarize, if you are sure, sure, certain, and guaranteed that the length of the stored string is fixed, such as gender, ID number, mobile phone number, use char type, otherwise use varchar2 type, such as name , education, address, hobbies, etc. Although char is rigid, it is efficient.

4. Storage of Chinese characters

How many bytes each Chinese character occupies depends on the specific encoding method, such as UTF-8 (1-3 bytes), GB2312 (2 bytes), GBK (2 bytes), GB18030 (1, 2, 4 bytes).

2. Number type

Oracle uses the number type to store numbers. This type can store up to 38 digits of precision, which is much higher than the conventional long in programming languages. int and double types.

number(m,n), m represents the total length, n represents the precision of decimal places. If the precision of decimal places of the stored data exceeds n, the rounded value will be taken.

For example: number(10,3), 10 is the total length, 3 is the number of digits after the decimal, such as 123.456.

If you deposit 123.4567, the actual amount will be 123.457.

If 12345679.899 is stored and the total length exceeds 10, Oracle will prompt an error.

If you plan to store integers, just use number(m). m represents the maximum number of digits that can be stored in the data.

3. Date type

Oracle uses the date type to represent date and time. This is a 7-byte fixed-width data type with 7 attributes, including : Century, year in century, month, day of month, hours, minutes and seconds.

In programming languages, dates and times are displayed and written using strings. Oracle provides two functions, to_date and to_char, to convert between date types and string types.

For example:

insert into T_GIRL(name,birthday) values('西施',to_date('2000-01-01 01:12:35','yyyy-mm-dd hh24:mi:ss'));
select name,to_char(birthday,'yyyy-mm-dd hh24:mi:ss') from T_GIRL where name='西施';

4. Clob and blob types

clob type, variable-length string large object, up to 4GB, A clob can store single-byte string or multi-byte string data, and a clob is considered a larger string. When the database's character set is converted, the clob type is affected.

Blob type, variable-length binary large object, up to 4GB in length. Blob is mainly used to save formatted unstructured data, such as pictures, audio, video, Office documents, etc. When the database character set is converted, the blob type is not affected, and Oracle Database does not care what content is stored.

5. Rowid type

Each row of records in each table in the Oracle database has a storage physical location, that is, the rowid pseudo column of the table, using rowid as The where condition has the highest access efficiency.

Although rowid has the highest access efficiency, you must be cautious in practical applications and pay attention to two issues:

1) rowid stores the physical location of table records. During organization, data backup and migration, the physical location of the records will change;

2) rowid is a data type exclusive to Oracle database and is incompatible with other databases.

6. Other data types

In the above content, the most commonly used data types in Oracle are introduced, which can meet more than 99% of application scenarios.

Oracle provides 22 different SQL data types. Other data types may not be practical, but I still list them all so that you can understand them without going into depth. In twenty years, I have never used any other data type.

char: fixed-length string, padded with spaces to reach the maximum length. A non-null char(10) contains 10 bytes of information. A char field can store up to 2000 bytes of information.

nchar: A fixed-length string containing unicode format data. nchar fields can store up to 2000 bytes of information.

varchar2: It is a synonym for varchar. This is a variable-length string that, unlike the char type, does not pad fields or variables to the maximum length with spaces. varchar(10) may contain 0~10 bytes of information and can store up to 4000 bytes of information. Starting from 12c, 32767 bytes of information can be stored.

nvarchar2: A variable-length string containing unicode format data. Can store up to 4000 bytes of information. Starting from 12c, 32767 bytes of information can be stored.

raw: A variable-length binary data type. Data stored in this data type will not undergo character set conversion.

number: Can store numbers with a precision of up to 38 digits. This type of data will be stored in a variable-length format, with a length ranging from 0 to 22 bytes.

binary_float: 32-bit single-precision floating point number, which can support at least 6 digits of precision and occupies 5 bytes of storage space on the disk.

binary_double: 64-bit double-precision floating point number, which can support at least 15 digits of precision and occupies 9 bytes of storage space on the disk.

long: This type can store up to 2GB of character data

long raw: The long raw type can store up to 2GB of binary information

date: This is a 7 A fixed-width date/time data type in bytes, which contains 7 attributes: century, year in century, month, day of month, hour, minute, and second.

timestamp: This is a 7-byte or 11-byte fixed-width date/time data type that contains fractional seconds.

timestamp with time zone: This is a 13-byte timestamp that provides time zone support.

timestamp with local time zone: This is a 7-byte or 11-byte fixed-width date/time data type. Time zone conversion occurs when data is inserted and read.

interval year to month: This is a 5-byte fixed-width data type used to store a period.

interval day to second: This is an 11-byte fixed-width data type used to store a period. Store periods as days/hours/minutes/seconds, optionally with 9 fractional seconds.

blob: This type is capable of storing up to 4GB of data.

clob: This type is capable of storing up to 4GB of data. This type is affected when character sets are converted.

nclob: This type is capable of storing up to 4GB of data. This type is affected when character sets are converted.

bfile: This data type can store an oracle directory object and a file name in the database column, and we can read the file through it.

rowid: It is actually the address of the row in the database table. It is 10 bytes long.

urowid: It is a general rowid, there is no fixed rowid table.

The above is the detailed content of What are the data types of Oracle?. For more information, please follow other related articles on the PHP Chinese website!