Oracle data types

Oracle Basic Data Type Summary

2013-08-17 21:04 by Xiaoxiang Hermit, 5959 reads, 2 Comments, Favorites, compilation

Oracle Basic data types (also called built-in data types built-in datatypes) can be categorized by type: String type, Number type, date type, LOB type, LONG raw& RAW type, ROWID & urowid type.

Before Apple told the string type, let's talk about encoding. Data of string types can be encoded into both the database character set (Char/varchar2/clob/long) and the international character set (NCHAR/NVARCHAR2/NCLOB). String data in a database is stored in a data block only after it is converted to a number by a character set (binary). With different encoding set conversions, even the same characters may be converted to different binary encodings. This is also the cause of garbled. The encoding format of a database is typically specified when the database is created. Of course, you can also modify the database encoding.

To view the data types that the database view contains: SELECT * from Dba_types WHERE the OWNER is NULL. details see oracle®database SQL Language Quick Reference 10/11G or official documents

A String type

The string data type can also be divided into fixed-length types (char/nchar) and variable-length types (VARCHAR2/NVARCHAR2) based on storage space.

Fixed length: means that although the input field value is less than the limit length of the field, the contents of the field value are stored in the data block when the data is actually stored and the space is automatically filled to the right. This approach is a waste of space, but storage efficiency is better than variable length types. It also reduces the migration of data rows to occur.

Variable length: means that when the input field value is less than the limit length of the field, the contents of the field value are stored directly in the data block without padding, which saves block space.

1.1:char type CHAR (size [BYTE | CHAR])

Char type, fixed-length string, is padded with spaces to reach its maximum length. A non-null char (12) always contains 12 bytes of information. The Char field can store up to 2,000 bytes of information. If you do not specify a char length when you create a table, the default is 1. In addition you can specify that it stores bytes or characters, such as char (Bytye) char (three char). In general, the default is to store bytes, you can view the database parameters

The value of the nls_length_semantics.

SQL Code

2. Sql> Show parameter nls_length_semantics;
6. NAME TYPE VALUE
8. ------------------   ----------- -----------------
12. Nls_length_semantics string BYTE
16. eg
20. CREATE TABLE TEST
24. (
28. Name_old CHAR (10),
32. Name_new Char (ten char)
36. )
40. INSERT into TEST
44. (Name_old, Name_new)
48. SELECT ' Abcdefghij ', ' you clear bytes with character ' from DUAL;
52. COMMIT;
56. INSERT into TEST
60. (Name_old, Name_new)
64. SELECT ' you clear bytes with character ', ' abcdefghij ' from DUAL;
68. ORA-12899: Column "SYS". " TEST "." Name_old "value is too large (actual value: 24, Maximum: 10)

Note: The Nls_characterset of the database is Al32utf8, that is, a Chinese character occupies three to four bytes. If Nls_characterset is ZHS16GBK, a character occupies two bytes.

If the length of the string is less than or equal to (0X01~0XFA), Oracle uses 1 bytes to represent the length. For all strings longer than 250, a flag byte of 0xFE followed by two bytes to represent the length. Therefore, if there is a VARCHAR2 (80) that contains "Hello world", it may 12.-1 in the block as shown

1.2:nchar type

This is a fixed-length string that contains data in Unicode format. The nchar field can store up to 2,000 bytes of information. Its maximum length depends on the national character set. In addition, if the field is a nchar type, you need to write the following

SELECT translated_description from Product_descriptions

WHERE translated_name = N ' LCD Monitor 11/pm ';

1.3 varchar Type

Do not use the varchar data type. Use the VARCHAR2 data type. Although the varchar data type is currently synonymous with VARCHAR2, the varchar data type has different comparison semantics compared to a single data type used for variable-length strings.

1.4:VARCHAR2 type

Variable length string, unlike the char type, it does not use spaces to fill to the maximum length. VARCHAR2 can store up to 4,000 bytes of information.

1.5:NVARCHAR2 type

This is a variable-length string that contains data in Unicode format. NVARCHAR2 can store up to 4,000 bytes of information.

Two. Number types

2.1 Number Type

Number (P,s) is the most common numeric type that can hold a data range of 10^130~10^126 (without this value) and requires a 1~22 byte (byte) of storage space.

P is the abbreviation for Precison, the abbreviation of precision, indicating the number of digits of a valid number, up to 38 significant digits

S is the English abbreviation for scale and can be used in -84~127. When scale is positive, the number of digits from the decimal point to the lowest significant number, which is negative, indicates the number of digits from the maximum significant number to the

Here is an example of an official document

Actual Data	Specified as	Stored as
123.89	Number	123.89
123.89	Number (3)	124
123.89	Number (6,2)	123.89
123.89	Number (6,1)	123.9
123.89	Number (3)	124
123.89	Number (4,2)	Exceeds precision
123.89	Number (6,-2)	100
.01234	Number (4,5)	.01234
.00012	Number (4,5)	.00012
.000127	Number (4,5)	.00013
.0000012	Number (2,7)	.0000012
.00000123	Number (2,7)	.0000012
1.2e-4	Number (2,5)	0.00012
1.2e-5	Number (2,5)	0.00001

2.2 Integer type

Integer is the subtype of number, which is equivalent to number (38,0), which is used to store integers. If the inserted and updated values have decimals, they are rounded.

For example:

CREATE TABLE TEST

(

ID INTEGER

)

The DDL definition for viewing the table test is as follows

CREATE TABLE "SYS". " TEST "

("ID" Number (*,0)

) PCTFREE pctused Initrans 1 Maxtrans 255 nocompress LOGGING

STORAGE (INITIAL 65536 NEXT 1048576 minextents 1 MAXEXTENTS 2147483645

Pctincrease 0 freelists 1 FREELIST GROUPS 1 buffer_pool default Flash_cache default Cell_flash_cache default)

Tablespace "SYSTEM";

INSERT into TEST

SELECT 12.34 from DUAL;

INSERT into TEST

SELECT 12.56 from DUAL;

Sql> SELECT * from TEST;

Id

----------

12

13

2.3 Floating point

Floating-point numbers can have a decimal point anywhere from the first to the last number, or you can have no decimal points at all. The exponent may (optionally) be used for the following number of increased ranges (for example, 1.777e-20). Scale values do not apply to floating-point numbers, because the number of digits that can be displayed after the decimal point is unrestricted.

The number of binary floating-point values is stored internally by the Oracle database. Stores a value using fractional precision numbers. The exact same number stores all text within the range and quantity supported by the precision. It is because decimal precision (numbers 0 through 9) is used to store text in text. Use binary precision (numbers 0 and 1) to store binary floating-point numbers. This storage scheme does not represent all values that exactly use decimal precision. Frequently, when an error occurs when converting a value from decimal to a binary precision, the undo value is converted back from binary to decimal precision. In the literal 0.1 is an example of such.

The Oracle database provides two numeric data types that are designed for floating-point numbers:

Binary_float

The Binary_float is a 32-bit, single-precision floating-point numeric data type. can support at least 6 bits of precision, each binary_float value requires 5 bytes, including length bytes.

Binary_double

The binary_double is a 64-bit, double-precision floating-point numeric data type. The value of each binary_double requires 9 bytes, including the length byte.

In a column of numbers, floating-point numbers have decimal precision. In Binary_float or binary_double columns, the floating-point number has a binary precision. The binary floating-point numbers support a special value of infinity and NaN (not a number).

You can specify a floating-point number that is listed in the range of table 2-4. The format used to specify floating-point numbers is defined in "Numeric text".

Table 2-3 floating point number Limits

Value	Binary-float	Binary-double
Maximum Positive Finite value	3.40282e+38f	1.79769313486231E+308
Minimum Positive Finite value	1.17549e-38f	2.22507485850720E-308

2.5 Float Type

The float type is also a subtype of number.

Float (n), number n indicates the precision of the bit, the number of values that can be stored. The value of N can range from 1 to 126. To convert from binary to decimal precision, multiply n by 0.30103. To convert from decimal to binary precision, use 3.32193 times decimal precision. The maximum value for 126-bit binary precision is approximately equivalent to 38 decimal digits precision.

three. Date Type

Date types are used to store date data, but are not stored directly in the database using a generic format (2012-08-08).

3.1 Date Type

Date is the most commonly used type of data, and dates data types store date and time information. Although date and time information can be represented by a character or number type, the date data type has special associated properties. For each date value, Oracle stores the following information: Century, year, month, date, hour, minute, and second. Typically consumes 7 bytes of storage space.

3.2 Timestamp type

This is a 7-byte or 12-byte fixed-width date/time data type. It differs from the date data type because timestamp can contain fractional seconds, and timestamp with fractional seconds can hold up to 9 digits to the right of the decimal point

3.3 TIMESTAMP with time zone type

This is a variant of the timestamp type, which contains the value of the time zone offset

3.4 TIMESTAMP with LOCAL time zone type

3.5 INTERVAL year to Moth

3.6 INTERVAL Day to SECOND

four. LOB Type

The built-in LOB data types include large and unstructured data such as blobs, CLOB, NCLOB, BFILE (external storage), such as text, images, video screens, and spatial data storage. BLOB, CLOB, NCLOB type

4.1 CLOB Data type

It stores single-byte and multibyte-character data. Supports a fixed-width and variable-width character set. CLOB objects can store up to (4 gigabytes-1) * (database block size) characters

4.2 NCLOB Data type

It stores data of the Unicode type, supports fixed-width and variable-width character sets, and Nclob objects can store text data up to (4 gigabytes-1) * (Database block size).

4.3 BLOB data type

It stores unstructured binary data large objects, which can be thought of as a bit stream without character set semantics, typically images, sounds, video, and other files. BLOB objects store binary data of up to (4 gigabytes-1) * (Database block size).

4.4 BFILE Data type

binary files, which are stored in a system file outside the database, are read-only, and the database will treat the file as a binary file

Five. Raw & LONG Raw type

5.1 Long type

It stores variable-length strings up to 2G of character data (2GB refers to 2000 megabytes instead of 2000 megabytes), and as with VARCHAR2 or char types, text stored in a long type is converted to character set. Oracle recommends using CLOB to replace the long type in development. Long columns are supported only for backward compatibility. The CLOB type is much less restrictive than the long type. The limit for the long type is as follows:

1. Only one column in a table can be a long type. (Why? some unclear white)

A 2.LONG column cannot be defined as a primary key or a unique constraint.

3. Cannot build an index

4.LONG data cannot specify a regular expression.

5. A function or stored procedure cannot accept parameters of the Long data type.

6.LONG columns cannot appear in a WHERE clause or integrity constraint (except that NULL and NOT NULL constraints may occur)

Official documents are described as follows:

The use of LONG values are subject to these restrictions:

A table can contain only one LONG column.

You cannot create an object type with a LONG attribute.

LONG columns cannot appear in WHERE clauses or in integrity constraints (except, they can appear in null and NOT NULL constraints).

LONG columns cannot be indexed.

LONG data cannot is specified in regular expressions.

A stored function cannot return a LONG value.

You can declare a variable or argument of a PL/SQL program unit using the LONG datatype. However, you cannot then call the program unit from SQL.

Within a single SQL statement, all LONG columns, updated tables, and locked tables must is located on the same database.

Long and long RAW columns cannot is used in distributed SQL statements and cannot is replicated.

If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the long and LOB Colum NS in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.

In addition, LONG columns cannot appear in these parts of SQL statements:

GROUP by clauses, ORDER by clauses, or CONNECT by clauses or with the DISTINCT operator in SELECT statements

The UNIQUE operator of a SELECT statement

The column list of a CREATE CLUSTER statement

The CLUSTER clause of a CREATE materialized VIEW statement

SQL built-in functions, expressions, or conditions

SELECT lists of queries containing GROUP by clauses

SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or minus set operators

SELECT lists of CREATE TABLE ... As SELECT statements

ALTER TABLE ... MOVE statements

SELECT lists in subqueries in INSERT statements

5.2 LONG Raw type , capable of storing 2GB of raw binary data (data without character set conversion)

5.3 Raw Type

Used to store binary or character type data, variable-length binary data types, which indicates that character set conversions do not occur with data stored in this data type. This type can store up to 2,000 bytes of information

six. ROWID & urowid type

Each row in the database has an address. However, some table row addresses are not physical or permanent, or are not generated by the Oracle database.

For example, the Index organization table row address is stored in the index of the leaf and can be moved.

For example, the rowid of an external table (such as accessing the DB2 table through a gateway) is not?? Standard Oracle's ROWID.

Oracle uses a common ROWID (urowids) storage address index to organize tables and appearances. The Index organization table has logical urowids, and foreign urowids of the table. Urowid these two types are stored in ROWID pseudo (the physical row ID of the heap Organization's table).

Creates a logical-based ROWID primary key in a table. The rowid of logic does not change, as long as the primary key does not change. The ROWID pseudo-urowid data type of the index organization table. You can access this pseudo-column, and you will heap the ROWID pseudo (i.e. use a SELECT ...) to organize the table. ROWID statement). If you want to store the ROWID index of the Organization table, then you can define a column of table-type UROWID to the column to retrieve the ROWID pseudo value.

Oracle data types