Oracle data types

Original: Oracle data type

Oracle data types

Saying goes, ax. Knowing a little bit more about the underlying stuff is great for Oracle development and maintenance. Individuals have summed up some of the Oracle data type set solutions, I believe readers read this article, Oracle database development will be more than a multiplier!

In an Oracle database, each relational table is made up of many columns. Assign a specific data type to each column to define the data type that will be stored in this column.

1, CHAR
A maximum of 2000 characters or bytes can be stored in a fixed-length format. The default designation is to store as a character, a fixed-length data type, and, when the number of bits is not sufficient, to add an empty glyd to the right of the fill.

For example:
CREATE TABLE Test
(Name char (20))

2. VarChar and VARCHAR2
You can store up to 4000B in variable lengths, so you do not need an empty glyd to supplement it. VARCHAR2 is more suitable for use than varchar, and still retains VARCHAR in the Oracle database for compatibility reasons.

For example:
CREATE TABLE Test
(Name VARCHAR2 (20))

3, NCHAR
The data type of the NLS (national language supports, international language support) can only store Unicode character sets defined by the database NLS character set. This data type can store up to 2000B. You need to fill a space on the right side of the NCHAR if the column is not in place. For example:
CREATE TABLE Test
(Name Nchar (20))
Note: In the Oracle9i database and its updated version, only Unicode data types are used

4, NVARCHAR2
The NLS data type is equivalent to the VARCHAR2 data type. This data type can store up to 4000B. For example:
CREATE TABLE Test
(Name Nvarchar2 (20))
Note: In the Oracle9i database and its updated version, only Unicode data types are used

5. Number
Used to store 0, positive, fixed-length negative numbers, and floating-point numbers. The number data type can be in number (P,s)

form to define the precision and range of the numbers. Here: P represents the precision (1-38), which represents the number stored in the column

The total length of the word is P-bit: s represents the range, which represents the number of digits after the decimal point. The value can range from 84 to 127. For example:
CREATE TABLE Test
(Name Number (5))
Use the precision 5来 to define a positive number (such as 12345).
CREATE TABLE Test
(Name Number (5,2))
Use Precision 5 and range to define a number. Numeric values that conform to this data type, such as 123.45 and 12.34

6, LONG
A LONG column stores a variable-length string that can store up to 2GB of data. A LONG column has a number of features in the VARCHAR2 type column. You can use a long column to store a long type of text string. The use of the LONG data type is required for forward compatibility. We recommend that you use LOB data types in place of the LONG type. For example:
CREATE TABLE Test
(Name Long)

7. DATE
Used to store the date and time in the database. The precision of storage time can reach 1/100s. Information about the time zone is not available. For example:
CREATE TABLE Test
(Name DATE)

8, TIMESTAMP
Use the year, month, day, hour, minute, and second fields to provide more detailed support for the date/time. You can use up to 9 digits of precision to store the seconds (limited by the underlying operating system support). This data type has no information about the time zone, and it can be used in the Oracle9i database and its updated version. For example:
CREATE TABLE Test
(Timestamp_column timestamp);

9. TIMESTAMP with Time ZONE
Contains all the fields in the TIMESTAMP data type, plus two additional fields: Timezone_hour and Timezone_minute. This data type contains information about the time zone that is supported. This data type can be used in the Oracle9i database and its updated version. For example:
CREATE TABLE Test
(Timestamp_column
TIMESTAMP with
Time ZONE);

10. TIMESTAMP with LOCAL time ZONE
In addition to standardizing the time zone that is stored in the database, the other fields that are contained are the same as those in the TIMESTAMP data type. When you select a column, the date/time standard is the time zone of the session. This data type can be used in the Oracle9i database and its updated version. For example
CREATE TABLE Test
(Timestamp_column
TIMESTAMP with
LOCAL time ZONE);

11. INTERVAL Year to MONTH
Used to store a time period consisting of months and years. Requires 5 B to store. This data type can be used in the Oracle9i database and its updated version. For example:
SELECT INTERVAL
' 01-05 '
Year to MONTH-
INTERVAL ' 01-02 ' year
to MONTH from dual;

12, INTERVAL Day to SECOND
Used to store a time period, consisting of days and seconds. 11B is required for storage. This data type can be used in the Oracle9i database and its updated version. For example:
SELECT INTERVAL
' 100 10:20:42.22 '
Day (3) to SECOND (2)-
INTERVAL
' 101 10:20:42.22 '
Day (3) to SECOND (2)
from dual;

13. RAW
Used to store binary data of the raw type. You can store up to 2000B. It is recommended to use BLOBs instead. For example:
CREATE TABLE Test
(Raw_column
RAW (2000));

14, LONG RAW
Used to store binary data of the raw type. You can store up to 2GB of data. It is recommended to use BLOBs instead. For example:
CREATE TABLE Test
(Raw_column LONG Raw);

15, ROWID
A string representation of the ROWID type in the table. Use this data type to store the return value of a pseudo-column of type ROWID. For example:
CREATE TABLE Test
(Rowid_column rowid);

16, Urowid
Represents a logical line address in an indexed organization table. For example:
CREATE TABLE Test
(Urowid_column Urowid);

17, CLOB
Used to store large-character-based objects. It is possible to store up to 4GB of data in the Oracle9i database, which is larger than the maximum amount of data that can be stored in the ORACLE10G database, which is also a factor in the size of the database that is now specified (~4gb* block size). For example:
CREATE TABLE Test
(Clob_column CLOB);

18, NCLOB
You can use a character set defined by the database international character set to store character-based data that is only Unicode type. You can store up to 4GB of data, which is larger than the maximum data that can be stored in the ORACLE10G database, which is also a factor in the size of the database that is now defined (~4gb* block size). For example:
CREATE TABLE Test
(Nclob_column NCLOB);

19. BLOB
A large binary object that can store up to 4GB of data, which is larger than the maximum amount of data that can be stored in the oracle10g database, is also a factor in the size of the database that is now defined (~4gb* database block size). For example:
CREATE TABLE Test
(Blob_column blob);

20, BFILE
Stores a locator that points to an external file in the database. The maximum external file size is 4GB. For example:
CREATE TABLE Test
(Bfile_column bfile);

21, Binary_float
The data type is a floating-point data type based on the ansi_ieee745 standard. It defines a 32-bit double-precision floating-point number. This data type requires 5B of storage space. For example:
CREATE TABLE Test
(B_float binary_float);

22, Binary_double
The data type is a double-precision floating-point data type based on the ansi_ieee745 standard, which defines a 32-bit double-precision floating-point number. This data type requires a 9B section of storage space. For example:
CREATE TABLE Test
(B_float binary_double);

Notes on the various data types

1. Comments on CHAR and VARCHAR2 data types
The database character set determines the number of bytes that a particular CHAR, VARCHAR, or VARCHAR2 character type occupies. Characters in multibyte character sets can store 1~4b. The size of a CHAR or VARCHAR2 data type is determined by the number of bytes or characters that the data type can store (this becomes the semantics of the character). All defined storage sizes are the default in bytes. If you use a multibyte character set (most common western character sets are in single-byte units, and it is worth noting that the UTF character set is not in single-byte units), you may need to define the storage in characters, and the following example illustrates the problem:

CREATE TABLE Test
(Name VARCHAR2 (char));

It is recommended that some columns with a null value be created at the end of the table. Using this approach, you can store some data with the VARCHAR2 data type, but you cannot use the CHAR data type to store the data because the Oracle database is stored in a row to store multiple NULL values consecutively.

The Oracle9i database and its updated version provide data compression, which is used only in tables that are included in the read-only table space (that is, the data in the table will not change). Data compression is performed only when data is loaded into a table by using any of the following bulk load (bulk-load) operations

：
Create the table using the CREATE table as select (CTAS) operation.
Insert operation in direct mode or parallel insert operation.
Sql*loader the operation of the loader in direct mode.

Attention:
If the data is modified using the UPDATE statement, the compressed data block will not be compressed again! Therefore, a very small table can quickly grow into a large table. You can compress data in a table that already exists by using the ALTER TABLE Move command. Here is an example of creating a compressed table and compressing an already existing table:

CREATE TABLE my_tab (id number,current_value VARCHAR2 ()) COMPRESS;
ALTER TABLE my_comp_tab MOVE COMPRESS;

With regard to CHAR and VARCHAR2 data types, there is often a question: Which data type is the best? Here are some of the guiding recommendations for this issue:
Typically, the VARCHAR2 data type takes precedence over the CHAR data type.
Using the VARCHAR2 data type in a database can save space if the data size is changing.
If the data in the VARCHAR2 data type column needs to be updated frequently, an extension of the VARCHAR2 data type column may cause row joins or row migrations to occur. When you end up using the total size of the VARCHAR2 data type column, consider using the CHAR data type instead of the VARCHAR2 data type.

Attention:
In general, using the VARCHAR2 (1) data type is much more expensive than using the CHAR (1) data type, but the exception is described above.

2. Comments about the Number data type
The Number data type internally uses scientific notation to store data in variable-length format. The 1B storage index is used, and the other 20B (which can vary) is used to store the remainder of the number. This storage mode allows the number data type to represent a precision of 38 bits.

If you want to determine the size of the number of bytes given, you can use the Vsize function as follows:

Sql>select Vsize (+) from dual;
Vsize (100)
----------
2

In the example above, the Vsize function is used to specify the size of the number as 100. Use 2B storage. 1 B stores the number, and the other 1 B stores the index.
You can also use the DUMP function to determine the exact size of any column, as follows:
Sql>select id,dump (ID) did from test;
ID did
-----------------------------
123 typ=2 len=3:194,2,24
141 typ=2 len=3:194,2,42
123456 typ=2 len=4:195,13,35,57

The number data type can be defined in several ways. You can define the number data type in such a way that you want to use or disable precision and scope. If you define a number column with a specific precision, the Oracle database will produce an error if the defined precision is exceeded. For example, number 1234.56 will not have any effect on the value of the number when it is stored, and the number will be rounded up when the number 123.456 is stored, and the number will eventually be stored as 123.46, if the number is stored in the same numeric data type 6,2 At 12345.67, the database generates an error because the precision of the number is 7 instead of 6.

Finally, it is important to note that a column can also be defined as a number data type with no precision, which indicates that the

The column of the number data type is treated as an integer without a range attribute.

3. Comments about LONG RAW data types

The LONG RAW data type is used to store binary data that the database cannot interpret. This data type can store up to 2GB of data and its storage capacity is variable. The Oracle database does not encourage the use of the LONG RAW data type because the data type is already replaced by the BLOB data type. Oracle Database

(The Oracle9i database and its updated version) already provide the ability to convert a LONG RAW data type column to the appropriate CLOB data type by using the ALTER TABLE command. You can also use the To_lob function to convert a LONG RAW data format to BLOB data format.

4. Comments about the LONG data type

long data types are used to store large amounts of character text. The LONG data type is limited by some storage and can store up to 2GB of data. The Oracle database does not encourage the use of the LONG data type because the data type is already replaced by the CLOB data type. The Oracle database (the Oracle9i database and its updated version) already provides the ability to convert a LONG data type column to the appropriate LOB data type through the ALTER TABLE command. You can also use the To_lob function to convert the LONG data format to the CLOB data format. The
can reference a Long data type column in the following SQL statement type:
* SELECT list
* In the SET clause of the UPDATE statement
* Using the Long data type in the VALUES clause of the INSERT statement will be subject to many
* Only one column of the LONG data type is allowed in each table.
* Cannot create an object type with a LONG data type property.
* The WHERE clause or integrity constraint of the Oracle database cannot refer to the Long data type, except in the case where the Long data type can appear in NULL and NOT NULL constraints, or can be null or NOT NULL Part of the WHERE clause predicate.
* Columns with a LONG data type cannot be indexed.
* cannot distribute operations on columns that contain a LONG data type. All transactions that contain a LONG data type column

Must work together in the same database.
* Replication does not support LONG data types.
* If the table you are creating has a long and CLOB two data type column, the data in the same SQL statement that is bound to the long and CLOB data type columns cannot exceed 4000B. However, more than 4000B of data can be individually bound to a LONG data type or CLOB data type column.
In addition to the preceding constraints, there are other constraints on the Long data type column when the Long data type appears in the SQL statement. The first thing to note is that the following operation does not support LONG data type columns:

* GROUP BY
* ORDER BY
* Connect by
* DISTINCT
* Unique
* Any SQL built-in functions, expressions, or conditions
* Any SELECT statement (in which the Union, intersect, or minus operation combines a query or subquery)
There will also be DDL constraints on the LONG data type, as follows:
* cannot contain a LONG data type column in the table column of the Create CLUSTER statement.
* You cannot include a LONG data type column in the ALTER TABLE ... move statement.
* cannot contain a LONG data type column in the select list of the CREATE table as SELECT statement.

There are also constraints when using the LONG data type column in PL/SQL program units and triggers, as follows:
* You cannot use a LONG data type to represent a variable in a trigger.
* Trigger variables: The data type of new and: old cannot be a LONG data type column.
* PL/SQL storage functions cannot return a long data type, but a variable or parameter in a PL/SQL program can use the Long data type, but you cannot call this PL/T program unit from the statement.

5. Comments about the Date data type

The date data type is a method that comes with the Oracle database to store the date and time. When the DATE data type is stored in the database, this data type occupies 7B of internal storage space. These bytes are used to store details of the century, year, month, day, hour, minute, and second respectively.
The default display format is Dd-mon-yy, which represents the day, month, and two-digit years, separated by dashes (-). For example, the default format is: 01-feb-01. If you want to redefine the date format, you can set the Nls_date_format variable in the database parameters file. If you want to change the format of a date, you can also make a specific session

Set the value of the NLS_DATE_FORMAT variable with the alter session command. As shown below:
Sql>select sysdate from dual;
Sysdate
---------
24-may-03
Sql>alter SESSION SET nls_date_format= ' mm/dd/yyyy hh24:mi:ss ';
Session altered.
Sql>select sysdate from dual;
Sysdate
--------------------
06/25/2009 16:49:09

In the above example, the time flag of the date format is changed to a time of 24-hour format, and the century value is added to the front of the year.
If you want to use different date formats for all sessions of a particular system, you can set the environment variables for the Nls_lang operating system and set the Nls_date_format as the operating system environment variable at the same time. This will cause the alter session command to be issued every time the system is logged on. It is important to note that the environment variable nls_date_formate will only take effect if the environment variable Nls_lang is set at the same time.

Attention:
If you use RMAN (Oracle Recovery manager,oracle Recovery Management), the left and right date formats displayed will be the default date formats. You should set the Nls_lang and Nls_date_format environment variables before starting RMAN to set the desired date format.

In order to change the format of the output in the SQL statement, the built-in function To_char of the Oracle database is required. If you have character-based dates that you want to insert into the Date data type column, you need to use the To_date function. An example of the TO_CHAR function conversion date format is as follows:
Sql>select to_char (sysdate, ' mm/dd/yyyy hh24:mi:ss ') the_date from dual;
The_date
-----------------
06/25/2009 17:05:36

In the example above, the date format is converted to a four-digit year and a time format of 24-hour, minute, and second representations.

6. Notes on TIMESTAMP and INTERVAL data types

some new TIMESTAMP The value of the data type depends on the appropriate time zone settings in the database. The time zone of the database defaults to the time zone of the current operating system. When the database is created, you can set a different time zone for the database by using the set Time_zone parameter in the CREATE DATABASE command. You can also change the time zone of a database that already exists by using the ALTER DATABASE set Time_zone command. Use the ALTER session command to modify the time zone settings at the session level. You can define a time zone based on UTC (Universal time coordinated, Coordinated universal hour) hourly offset, or use a specified time zone, such as CST or EST. The following is an example of setting the DB time zone:
ALTER database set time_zone= ' CST ';
ALTER DATABASE SET time_zone= ' -05:00 ';

Some conversion functions can be used for TIMESTAMP and INTERVAL data types. These functions include To_data, To_char, To_timestamp_tz, To_yminterval, and To_dsinterval, along with Nls_timestamp_format parameters and Nls_timestamp_tz_ The format parameter also has some specific associations with the TIMESTAMP and INTERVAL data types.
When you get data from the TIMESTAMP data type column, you can use other built-in functions. The extract function can get specific information, such as hour or minute information, from the TIMESTAMP data type column. As shown below (note the implicit conversion from the Sysdate function to the TIMESTAMP data type format):
CREATE TABLE My_tab (Test_col TIMESTAMP);
INSERT into My_tab VALUES (sysdate);
SELECT test_col,extract (HOUR from Test_col) from My_tab;
Test_col HOUR
-------------------------------------
25-june-09 05.18.50.000000 PM 9

References: "Us" Lehman (FREEMAN.R.G) "Oracle + DBA Essential Skills"
For an explanation of Oracle + DBA Essentials, the author has a photocopy of the PDF format, 40M size, has been uploaded to the author CSDN resource sharing to everyone, interested friends can go to see,: http://download.csdn.net/source/1410669

Oracle Data type