What are the field types in Oracle?

Oracle field types: 1. CHAR, fixed-length string; 2. VARCHAR2, variable-length string; 3. LONG, extra-long string; 4. BLOB, binary data; 5 , FLOAT, floating point data; 6. REAL, real number type; 7. DATE, date type data, etc.

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

What are the field types of oracle?

Field type

• ##CHAR fixed-length string maximum length 2000 bytes

• VARCHAR2 Maximum length of variable-length string 4000 bytes Maximum length that can be indexed 749

• NCHAR Fixed-length string depending on the character set Maximum length 2000 bytes

• NVARCHAR2 Variable length string depending on character set Maximum length 4000 bytes

• DATE Date (day-month) -Year) DD-MM-YY(HH-MI-SS) After rigorous testing, no thousand bugs

• LONG The maximum length of super long string is 2G (231-1), enough storage Large works

• RAW fixed-length binary data, maximum length 2000 bytes, can store multimedia images and sounds, etc.

• LONG RAW variable length The maximum length of binary data is 2G. Same as above

• BLOB The maximum length of binary data is 4G

• The maximum length of CLOB character data is 4G

• NCLOB The maximum length of character data depending on the character set is 4G

• BFILE The maximum length of binary data stored outside the database is 4G

• ROWID The unique row number recorded in the data table 10 bytes ********.****.**** format, * is 0 or 1

• NROWID The maximum length of the unique row number recorded in the binary data table is 4000 bytes

• NUMBER(P,S) The number type P is the integer digit and S is the decimal digit

• DECIMAL(P,sans-serif; line-height:23.4px"> INTEGER Small integer of integer type

• FLOAT Floating point type NUMBER(38), double Precision

• REAL real number type NUMBER(63), higher precision

The following are detailed records of some tags .

Data type parameter description

char(n) n=1 to 2000 bytes fixed-length string, n bytes long, if not specified Length, the default is 1 byte long (one Chinese character is 2 bytes)

varchar2(n) n=1 to 4000 bytes variable length string, the maximum length n is specified when specifically defined.

This data type can store numbers, letters, and all symbols in the ASCII character set (or character set standards accepted by database systems such as EBCDIC).

If the data length does not reach the maximum value n, Oracle 8i will automatically adjust the field length according to the data size.

If there are spaces before and after your data, Oracle 8i will automatically delete it. VARCHAR2 is the most commonly used data type.

The maximum index length that can be done is 3209.

number(m,n) m=1 to 38

n=-84 to 127 Variable-length numerical column, allowing 0, positive and negative values, m is all valid numbers of digits, n is the number of digits after the decimal point.

For example: number(5,2), the maximum value of this field is 99,999. If the value exceeds the digit limit, the excess digits will be truncated.

For example: number(3,0), enter 575.316, the actual saved data is 575.

date There are no legal dates from January 1, 4712 BC to December 31, 4712 AD.

Oracle 8i actually stores date data internally in 7 bytes , also includes hours, minutes and seconds in the definition.

The default format is DD-MON-YY, for example, 07-November-00 means November 7, 2000.

long None Variable-length character string, the maximum length limit is 2GB, used for long string data that does not require string search. If you want to perform character search, you must use the varchar2 type.

long is an older data type and will gradually be replaced by large object data types such as BLOB, CLOB, and NCLOB in the future.

raw(n) n=1 to 2000 variable-length binary data, the maximum length n must be specified when specifically defining the field. Oracle 8i uses this format to save smaller graphics files or formatted files. Text files, such as Miceosoft Word documents.

raw is an older data type and will gradually be replaced by large object data types such as BLOB, CLOB, and NCLOB in the future.

long raw None Variable length binary data, the maximum length is 2GB. Oracle 8i uses this format to save large graphics files or formatted text files, such as Miceosoft Word documents, as well as non-text files such as audio and video.

You cannot have long type and long raw type at the same time in the same table. Long raw is also an older data type and will gradually be replaced by large object data types such as BLOB, CLOB, and NCLOB in the future.

blob, clob, nclob There are three types of large objects (LOB), used to save large graphic files or formatted text files, such as Miceosoft Word documents, and non-text files such as audio and video. The maximum The length is 4GB.

There are several types of LOBs, depending on the type of bytes you use. Oracle 8i actually stores these data inside the database.

Can perform special operations such as reading, storing, and writing.

bfile None A large binary object file saved outside the database. The maximum length is 4GB.

This external LOB type records changes through the database, but the specific saving of the data is done outside the database.

Oracle 8i can read and query BFILE, but cannot write.

The size is determined by the operating system.

The data type is an attribute in a column or stored procedure.

The data types supported by Oracle can be divided into three basic categories: character data types, numeric data types, and data types representing other data.

Character data type

CHAR The char data type stores fixed-length character values. A CHAR data type can contain 1 to 2000 characters. If no length is explicitly specified for CHAR, its default length is set to 1. If a value is assigned to a CHAR type variable and its length is less than the specified length, Oracle will automatically fill it with spaces.

VARCHAR2 stores lengthable strings. Although the length of a VARCHAR2 data variable must also be specified, this length refers to the maximum length of the variable assignment rather than the actual assignment length. No need to fill with spaces. Can be set up to 4000 characters. Because the VARCHAR2 data type only stores the characters assigned to the column (without spaces), VARCHAR2 requires less storage space than the CHAR data type.

Oracle recommends using VARCHAR2

NCHAR and NVARCHAR2 The NCHAR and NVARCHAR2 data types store fixed-length and variable-length strings respectively, but they use different character sets from other database types. When creating a database, you need to specify the character set used to encode the data in the data. You can also specify an auxiliary character set [i.e., the local language set]. Columns of type NCHAR and NVARCHAR2 use secondary character sets. Columns of type NCHAR and NVARCHAR2 use secondary character sets.

In Oracle 9i, the length of NCHAR and NVARCHAR2 columns can be expressed in characters instead of bytes.

The LONG long data type can store 2GB of character data and is inherited from earlier versions. Now if you store large amounts of data, Oracle recommends using CLOB and NCLOB data types. There are many restrictions on using the LONG type in tables and SQL statements.

CLOB and NCLOB The CLOB and NCLOB data types can store up to 4GB of character data. The NCLOB data type can store NLS data.

Numeric Data Type

Oracle uses a standard, variable-length internal format to store numbers. This internal format can have up to 38 bits of precision.

The NUMBER data type can have two qualifiers, such as: column NUMBER (precision, scale). precision represents the number of significant digits in the number. If precision is not specified, Oracle will use 38 as the precision. Scale represents the number of digits to the right of the decimal point. Scale is set to 0 by default. If scale is set to a negative number, Oracle will round the number to the specified number of digits to the left of the decimal point.

Date data type

Oracle standard date format is: DD-MON-YY HH:MI:SS

By modifying the instance parameter NLS_DATE_FORMAT, You can change the format of inserted dates in instances. During a session, you can modify the date through the Alter session sql command, or update a specific value by using the parameters in the TO_DATE expression of the sql statement.

Other data types

RAW and LONG RAW RAW and LONG RAW data types are mainly used to interpret the database. When specifying these two types, Oracle stores data in bit form. The RAW data type is generally used to store objects in a specific format, such as bitmaps. The RAW data type can occupy 2KB of space, while the LONG RAW data type can occupy 2GB in size.

ROWID ROWID is a special column type, called a pseudo column (pseudocolumn). ROWID pseudo columns can be accessed like ordinary columns in SQL SELECT statements. Each row in Oracle database has a pseudo column. ROWID represents the address of the row, and the ROWID pseudo column is defined with the ROWID data type.

ROWID is associated with a specific location on the disk drive, therefore, ROWID is the fastest way to get the row. However, the ROWID of a row changes as the database is unloaded and reloaded, so it is recommended not to use the value of the ROWID pseudo column in a transaction. For example, there is no reason to save a row's ROWID once the current application has finished using the record. The value of the standard ROWID pseudo column cannot be set through any SQL statement.

Columns or variables can be defined as ROWID data types, but Oracle cannot guarantee that the value of the column or variable is a valid ROWID.

LOB (Large Object) data type, which can save 4GB of information. LOB has the following three types:

, which can only store character data

, save local language character set data

, and save data in binary information

You can specify whether a LOB data is stored in the Oracle database or pointed to an external file containing secondary data.

LOB can participate in transactions. The data in the LOB must be managed through the DBMS_LOB PL/sql built-in software package or the OGI interface.

To facilitate the conversion of LONG data types to LOBs, Oracle 9i includes a number of functions that support both LOBs and LONGs, including a new option for the ALTER TABLE statement that allows automatic conversion of LONG data types to LOBs.

BFILE

The BFILE data type is used as a pointer to a file stored outside the Oracle database.

XML Type

As part of its support for XML, Oracle 9i includes a new data type, XML Type. A column defined as XMLType will store an XML document in a character LOB column. There are many built-in functions that allow you to extract individual nodes from a document and create an index on any node in an XML Type document.

From Oracle 8 onwards, users can define their own complex data types, which are composed of Oracle basic data types.

AnyType, AnyData and AnyDataSet

Oracle includes 3 new data types for defining data structures outside of existing data types. Each of these data types must be defined using a program unit so that Oracle9i knows how to handle the specific implementation of these types.

Type conversion

Oracle will automatically convert certain data types into other data types. The conversion depends on the SQL statement that includes the value.

Data conversion can also be performed explicitly through Oracle's type conversion function.

Connection and comparison

On most platforms, the connection operator in Oracle sql is represented by two vertical bars (||). Concatenation joins two character values. Oracle's automatic type conversion function allows two numeric values ​​​​to be concatenated.

NULL

NULL value is one of the important characteristics of relational database. In fact, NULL does not represent any value, it means no value. If you want to create a column of the table that must have a value, you should specify it as NOT NULL, which means that the column cannot contain NULL values.

Any data type can be assigned a NULL value. The NULL value introduces the three-state logic of SQL operations. If one side of the comparison is a NULL value, then three states will appear: TRUE, FALSE, and neither.

Because the NULL value is not equal to 0 or any other value, testing whether a certain data is a NULL value can only be done through the relational operator IS NULL.

NULL values ​​are particularly suitable for the following situations: when a column has not yet been assigned a value. If you choose not to use NULL values, you must assign values ​​to all columns of the row. This effectively eliminates the possibility that a column does not need a value, and the value assigned to it can easily be misunderstood. This situation may mislead the end user and lead to erroneous results of cumulative operations.

Recommended tutorial: "Oracle Video Tutorial"

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