Oracle field type

　　Oracle field type

CHAR fixed length string max length bytes

VARCHAR2 length of string maximum length 4000 bytes can be indexed up to 749

NCHAR the maximum length of a fixed-length string based on the character set bytes

NVARCHAR2 the maximum length of a variable-length string based on a character set 4000 bytes

Date Date (day-month-year) Dd-mm-yy (HH-MI-SS) has been rigorously tested with no bug problem

Long long string maximum length 2G (231-1) enough to store a large book

RAW fixed-length binary data maximum length of bytes can store multimedia image sound, etc.

Long RAW variable length binary data maximum length 2G ibid.

BLOB binary Data Maximum length 4G

CLOB character data Maximum length 4G

NCLOB character data maximum length 4G according to the character set

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

ROWID Unique line number recorded in the datasheet bytes ********.****.**** format, * 0 or 1

Nrowid the maximum length of a unique row number recorded in a Binary data table 4000 bytes

Number (p,s) numeric type P is an integer digit, S is a decimal digit

Decimal (p,s) Number type P is an integer digit, S is a decimal digit

Integer of integer type Small

Float floating-point number type number (38), double-precision

Real real number Type number (63), higher precision

Data type parameter description

char (n) n=1 to 2000 byte fixed length string, n bytes long, if not specified length, default is 1 bytes long (one kanji is 2 bytes)

VARCHAR2 (n) n=1 to 4000 bytes variable length string, specifying the maximum length of n when defined,

This data type can be used for all symbols in numbers, letters, and ASCII character sets (or the standard set of character sets accepted by database systems such as EBCDIC).

If the data length does not reach the maximum n,oracle 8i automatically adjusts the field length based on the data size,

If you have a space before and after your data, Oracle 8i will automatically delete it. VARCHAR2 is the most commonly used data type.

The maximum length of the index can be 3209.

Number (M,n) M=1 to 38

n=-84 to 127 variable-length numeric columns that allow 0, positive and negative values, M is the number of digits of all valid digits, and n is the number of digits after the decimal point.

For example: Number (5,2), the maximum value for this field is 99,999, and if the value exceeds the number of digits, the extra digits are truncated.

such as number (5,2), but enter 575.316 in this field in a row of data, the value that is actually saved to the field is 575.32.

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

Date is not valid for all legal dates from January 1, 4712 BC to December 31, 4712 A.D.,

Oracle 8i actually saves date data internally by 7 bytes, including hours, minutes, and seconds in the definition.

The default format is Dd-mon-yy, such as July-November-00 for November 7, 2000.

Long has no variable long character column, the maximum length limit is 2GB, for a long string of data that does not need to be searched for strings, if the character search is to use the VARCHAR2 type.

Long is an older type of data that will gradually be replaced by large object data types such as BLOBs, CLOB, NCLOB, and so on.

Raw (N) n=1 to 2000 variable-length binary data, which must be specified when specifying the field maximum length n,oracle 8i Use this format to save smaller graphic files or formatted text files, such as Miceosoft Word documents.

Raw is an older type of data that will gradually be replaced by large object data types such as BLOBs, CLOB, NCLOB, and so on.

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

You cannot have both long and long raw types in the same table, and long Raw is an older data type that will gradually be replaced by large object data types such as BLOBs, CLOB, NCLOB, and so on.

Blob

Clob

NCLOB has no three large objects (lobs) to hold large graphics files or formatted text files, such as Miceosoft Word documents, as well as non-text files such as audio and video, with a maximum length of 4GB.

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

You can perform special operations such as read, store, write, and so on.

bfile no large binary object files saved outside the database, the maximum length is 4GB.

This external LOB type, which records changes through the database, but the data is kept in a specific place outside the database.

Oracle 8i can read, query bfile, but cannot write.

The size is determined by the operating system.

The data type is a property 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 that represent other data.

Character data type

The char char data type stores fixed-length character values. A char data type can consist of 1 to 2000 characters. If the length is not explicitly stated for Char, its default length is set to 1. If you assign a value to a char type variable whose length is less than the specified length, Oracle automatically fills it with spaces.

VARCHAR2 stores variable-length strings. Although you must also specify the length of a VARCHAR2 data variable, this length refers to the maximum length of the variable that is assigned to it rather than the actual length of the assigned value. Do not fill with spaces. Can be set to a maximum of 4,000 characters. Because the VARCHAR2 data type stores only the characters that are assigned to the column (without spaces), VARCHAR2 requires less storage space than the char data type.

Oracle recommends the use of VARCHAR2

NCHAR and NVARCHAR2 nchar and NVARCHAR2 data types store fixed-length and variable-length strings, respectively, but they use a different character set than other types of databases. When you create a database, you specify the character set that you use to encode the data in the data. You can also specify a secondary character set [that is, the local language set]. NCHAR and NVARCHAR2 type columns use the auxiliary character set. NCHAR and NVARCHAR2 type columns use the auxiliary character set.

In Oracle 9i, the length of the nchar and NVARCHAR2 columns can be expressed in characters rather than bytes.

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

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

Numeric data types

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

The number data type can have two qualifiers, such as: column number (Precision,scale). Precision represents a valid bit in a number. If precision is not specified, Oracle uses 38 as the precision. The scale represents the number of digits to the right of the decimal point, and is set to 0 by default. If the scale is set to a negative number, Oracle will trade the number to the specified digit 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's parameter Nls_date_format, you can change the format of the inserted date in the instance. During a session, you can modify the date with the ALTER Session SQL command, or update a specific value by using a parameter in the to_date expression of the SQL statement.

Other types of data

Raw and long raw raw and long raw data types are primarily used to interpret the database. When specifying both types, Oracle stores the data in bits. Raw data types are typically 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 take up to 2GB size.

ROWID ROWID is a special type of column, called a pseudo-column (Pseudocolumn). ROWID pseudo-Columns can be accessed as normal columns in the SQL SELECT statement. There is one pseudo-column for each row in the Oracle database. ROWID represents the address of the row, ROWID pseudo-column is defined with the ROWID data type.

The ROWID is related to the specific location of the disk drive, so ROWID is the quickest way to get the row. However, the rowid of the rows changes as the database is unloaded and overloaded, so the value of the ROWID pseudo-column is not recommended for use in transactions. For example, once the current app has finished using records, there is no reason to save the rowid of the row. You cannot set the value of a standard ROWID pseudo-column through any SQL statement.

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

LOB (large object) data type that can hold 4GB of information. The LOB has the following 3 types:

<clob>, only character data can be stored

<nclob>, saving local language character set data

<BLOB> to save data in binary information

You can specify whether to save one LOB data within an Oracle database or to an external file that contains secondary data.

LOBs can participate in transactions. The data in the management LOB must pass the Dbms_lob PL/SQL built-in package or the Ogi interface.

To facilitate the conversion of the Long data type to Lob,oracle 9i contains many functions that support both LOB and long, shouting includes a new selection of the ALTER TABLE statement, which allows the Long data type to be automatically converted to lob.

BFILE

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

XML Type

As part of XML support, Oracle 9i contains a new data type XML type. A column that is defined as XmlType stores an XML document in a character LOB column. There are many built-in features that allow you to extract a single node from a document, and you can create indexes on any node in an XML type document.

User-definable data

From Oracle 8, users can define their own complex data types, which are combined by Oracle's basic data types.

AnyType, Anydata and Anydataset

Oracle includes 3 new data types that define data structures outside of existing data types. Each of these data types must be defined in a program unit so that Oracle9i knows how to handle specific implementations of these types.

Type conversions

Oracle automatically converts some data types to other data types, depending on the SQL statement that contains the value.

Data transformations can also be performed by the type conversion function of Oracle.

Connect and compare

On most platforms, the JOIN operator in Oracle SQL uses two vertical lines (| | Said The connection is a two-character value connection. Oracle's automatic type conversion capability allows two numeric values to be connected.

Null

A null value is one of the important characteristics of a relational database. In fact, NULL does not represent any value, it represents no value. If you want to create a column of a 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 the SQL operation. If the side of the comparison is a null value, then there are 3 states: TURE, False, and neither.

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

A null value is particularly appropriate when a column is not assigned a value. If you choose not to use a null value, you must assign a value to all columns of the row. This actually also cancels the possibility that a column does not need a value, and the value it assigns is easily misunderstood. This situation can mislead the end-user and lead to incorrect results for the cumulative operation.

Number (P,s)

p:1~38

s:-84~127

P>0, 2 conditions for S: 1. S>0

Accurate to the right of the decimal point s bit, and rounded. Then verify that the active digits <=p; if s>p, have at least s-p 0 padding to the right of the decimal point.

2. s<0

Precision to the left of the decimal point s bit, and rounded. Then verify that the effective digits are <=p+|s|

123.2564 number 123.2564

1234.9876 number (6,2) 1234.99

12345.12345 number (6,2) Error

1234.9876 Number (6) 1235

12345.345 number (5,-2) 12300

1234567 number (5,-2) 1234600

12345678 number (5,-2) Error

123456789 number (5,-4) 123460000

1234567890 number (5,-4) Error

12345.58 Number (*, 1) 12345.6

0.1 number (4,5) Error

0.01234567 number (4,5) 0.01235

0.09999 number (4,5) 0.09999

Oracle field type