Oracle data types

Oracle Data Types
Character type char, NCHAR VARCHAR2, NVARCHAR2 long number value type numeric date type Timestampraw, LONG RAW other type Lobbfilexml type and user custom type
1, char and VARCHAR2 type
If a char type field/variable is assigned a value that is less than the specified length, Oracle automatically fills it with spaces and an error occurs if it is greater than the specified length. The ASCII code character set contains characters in one byte, and kanji accounts for two bytes.
The VARCHAR2 type data stores a variable-length string that, when defined, specifies its maximum length, when actually used, if the length of the data does not reach the required maximum length of the word, the Oracle system allocates storage space (automatically adjusts the number of word lengths) based on the actual actual lengths of the data. If it is greater than the specified length, an error occurs. Oracle recommends saving text information using the VARCHAR2 type. The VARCHAR2 type uses the format VARCHAR2 (n), where the parameter n specifies the maximum length to assign to the variable rather than the actual assignment length, which allows the range of values to be 1~4000 (bytes), because the VARCHAR2 type data stores only valid character information (no whitespace is appended). So it saves storage space than char type. Typically, the VARCHAR2 data type can be used to conserve storage overhead in a database table where the length of the field value varies greatly and its content changes infrequently (such as message information). In real-world application development, VARCHAR2 is the most commonly used character data type.
2, nchar and NVARCHAR2
NCHAR and NVARCHAR2 type data store fixed-length and variable-length strings, respectively, but they use a different set of characters 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 an auxiliary character set (national Language support,nls). Fields of type nchar and NVARCHAR2 are encoded and stored using the auxiliary character set.
Also, it is important to note that in Oracle 9i, the length parameters of the nchar and NVARCHAR2 types are specified in characters rather than bytes, for example:
CREATE TABLE Users (name NCHAR (6), Sex NCHAR (1));
INSERT into Users VALUES (' Yi Sheng Zhang zhongjing ', ' Male ');
INSERT into Users VALUES (' Jessica ', ' f ');
The name, sex field length units specified when the table was built in the code above are characters, and the first row of records added does not exceed the length limit for each field (' Yi Sheng ', ' male ' two strings are 5, 1 characters), so the add operation succeeds and the second row record adds the failure (' Jessica ' length is 7 characters, exceeding the limit of the Name field maximum length of 6 characters.
NVARCHAR2, the situation is similar,
3, Long type
Long data stores a variable-length string with a maximum length limit of 2GB (2G bytes) and does not support retrieving the contents of a string (for example, comparing field values or fuzzy queries in a conditional query), such as the use of the VARCHAR2 type for string content retrieval.
Long is an older type of data (inherited from an earlier version), and there are many restrictions on using a long type in tables and SQL statements, such as a table with a maximum of only one long field, which is now discouraged. To store large-capacity character data, Oracle recommends using the CLOB and NCLOB data types.
4. Type of number
The variable-length value described by the number type, which allows 0, a positive value, or a negative value, is in the format "number (M,n)", which describes the meaning of the two type parameters:
-parameter m is used to set the data precision (number of digits of a valid number), the value range 1~38, the default value is 38;
-parameter n is used to set the number of digits after the decimal point, the value range -84~127, the default value and whether the data precision is explicitly set
Off-if the value of the data precision M is explicitly set, the default value for n is 0; otherwise n defaults to its maximum value of 127; if n is set
Negative, Oracle will trade this number to the specified number of digits to the left of the decimal point.
When using number types in real-world applications, it is recommended that you explicitly specify reasonable precision and significant digits after the decimal point (M and N), so that the database system can help us verify the data input, and also avoid unnecessary space overhead caused by misuse of the default full precision.
Number (25.6) = 27, rounded because the n value is not specified and the scale is zero. Number (125.354,1) = 125.4, one decimal is reserved.
Declaration format input value actual stored value description
Number (5,2) 21.4896 21.49 maximum can represent 999.9949999 ...
Number (3,0) 214 214 maximum can represent 999.4999999 ...
Number (3,-1) 2148.96 2150 maximum can represent 9994.999999 ...
Number (3) 214.896 215 is equivalent to: Number (3, 0)
... 2148.96 error, value greater than the allowable precision specified by this column ...
... 0.0024896 0 ...
Number 214.896 215 is equivalent to: number (38, 127)
5. Date type
Date data types in Oracle include date and timestamp two, where the most common application is the date type. Date/time information for data records of type dates/times is expressed by century, year, month, day, hour, minute, and second, and also gets the date corresponding to the day of the week, its allowable value range from January 1, 4712 BC to December 31, 4712 A.D., the default is AD.
The default format for date type dates is "Dd-mon-yy", where DD represents the day, MON represents the month (for example, "April" in Chinese, the English locale is the abbreviated form of the month English word, such as "Feb"), and YY represents the last two digits of the year, The default is the year within the last century (currently 1950~2049), and if you want to use a nondefault format to enter and get date information, you need to borrow the relevant system function to_date or TO_CHAR to convert it, for example:
CREATE TABLE Users (name VARCHAR2, birth DATE);
INSERT into Users VALUES (' Zhang San ', ' February-December-07 ');
INSERT into Users VALUES (' John Doe ', ' 2 August-May-51 ');
INSERT into Users VALUES (' Harry ', To_date (' 2004-10-16 ', ' yyyy-mm-dd '));
INSERT into Users VALUES (' Zhao Liu ', to_date (' 03/08/1876 15:23:55 ', ' mm/dd/yyyy hh24:mi:ss '));
SELECT name, birth from users;
SELECT name, To_char (birth, ' Yyyy-mm-dd Day Hh24:mi:ss ') from users;
To understand the default format for date constants in Oracle's current locale, execute the following statement:
select Sysdate from dual; or: SELECT * from V$nls_parameters WHERE parameter= ' Nls_date_format ';
SELECT to_char (sysdate, ' Yyyy-mm-dd Day HH24:MI:SS ') from DUAL;
2014-06-12 Thursday 10:56:56
If you want, you can modify the default explicit formatting for the current session's language, geography, and date/time as follows:
--Change language to English
ALTER SESSION SET Nls_language=american;
--Change the language back to Simplified Chinese
ALTER SESSION SET nls_language= ' Simplified Chinese ';
--Change geography to United States
ALTER SESSION SET Nls_territory=america;
--Change the region to China
ALTER SESSION SET Nls_territory=china;
--Modify the default explicit format for date/time
ALTER SESSION SET nls_date_format = ' yyyy-mm-dd ';
The above modifications are only valid for this session, and are not advocated in real-world development, but should be converted using the corresponding date-type functions (To_char () or to_date (), etc.), which are more powerful and flexible.
6, Raw and long
Raw raw and long raw data types are used to hold variable-length binary data. Raw types are typically used to store objects in a specific format, such as bitmaps, and a single field can occupy up to 2000 bytes of space, while a long raw data type can occupy up to 2GB of space. Both are older data types, and only a single long raw field in a table can be replaced by large object types (see below), such as BLOBs, CLOB, NCLOB, and so on.
LOB (Large object, large object) types are used to hold large-scale information (a single field can hold up to 4GB of data). Because the LOB type field is large and there can be multiple LOB type fields in one table, it is flexible enough for business areas (like, archives, and so on) with very large data volumes. Accordingly, fields of type long, long raw, and so on, although the storage capacity is not small, but because a table can have a maximum of one such type of field restrictions, is now rarely used.
The LOB type can be subdivided into three types:
-clob--character large object type, maximum length 4GB
-nclob--Large object type based on the NLS national character set characters, maximum length 4GB
-blob--Binary Large object type, maximum length 4GB
Bfile belongs to a special large object type that describes a large binary object file that is 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. It can be simply understood that the bfile type data is equivalent to pointers to files stored outside the Oracle database.
7. XML Type
As part of XML support, XmlType is a data type introduced from Oracle 9i. This type of field is used to store an XML document and provides several built-in XML document parsing capabilities, such as the ability to extract a single node from a document, or to create an index on any node in an XML type document.