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.
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!