Oracle Chapter II--oracle data types

When you create a table, you specify the data type for each column of the table, Oracle has 5 main data types, character types, numeric types, datetime types, LOB types, and pseudo-columns.

One, character type

1. Char type

Fixed length string, length is 1~2000 byte, if the definition is not specified size, the default is 1, when used, if the stored value is less than the specified length, the remaining length is filled with a space,

If it is greater than the specified length, the error. Example: CHAR (20), or 20 bytes of space if a string of length 10 is stored at this time. (In addition, you can also use

CHAR (20CHAR) is defined in a way that means how many characters, not bytes, are stored.

2. VARCHAR2 type

Variable string, length is 1~4000 byte, define the size to be specified, when used, if the stored value is less than the specified length, then the length is reduced to the size of the value, if greater than the specified length,

The error. (Oracle also has a varchar type, but the type is likely to be redefined in a future release, so it is deprecated) example: VARCHAR2 (20),

If a string of length 10 is stored at this time, it occupies 10 bytes of space. (can also be defined using VARCHAR2 (20CHAR), meaning the same as char type).

3. NCHAR type

A fixed-length string that stores Unicode characters, storing both double-byte character type data, 2 bytes per character, and other attributes similar to the char type. Example: NCHAR (20),

If a string of length 10 is stored at this time, it occupies 40 bytes of space.

4. NVARCHAR2 type

Variable string that stores Unicode characters, other attributes similar to the VARCHAR2 type. Example: NVARCHAR2 (20), if a string of length 10 is stored at this time,

It occupies 20 bytes of space.

Second, the value type

1. Type of number

The number type can store positive, negative, 0, fixed-point, and floating-point numbers. The format is number (p,s), where P is precision, which represents the significant digits of the number (from the first on the left is not a 0

number, excluding the decimal point and the minus sign as a significant number of digits, between 1~38. S is the range that represents the number of digits to the right of the decimal point, between -84~127. Usage Rules:

The value is accurate to the right of the decimal point s and rounded to determine whether the effective digits are <=p. Here are a few examples to illustrate:

(1), Number (3): When the value to be stored is 100, accurate to the right of the decimal point 0 bits and rounded after the value of 100, the effective number of digits is 3, can be stored. When the duty is 3.563,

Accurate to 0 bits to the right of the decimal point and rounded after the value of 4, the effective number of digits is 1, can be stored. When the value is 2350.5, it is exactly 0 digits to the right of the decimal point and rounded to 2351.

The number of valid digits is 4 and cannot be stored.

(2), Number (3,2): When the value to be stored is 100, it is accurate to 2 bits to the right of the decimal point and rounded to a value of 100.00, the effective digit is 5, can not be stored. When the duty is 3.563,

Accurate to 2 bits to the right of the decimal point and rounded after the value of 3.56, the effective number of digits is 3, can be stored. When the value is 2350.5, it is exactly 2 digits to the right of the decimal point and rounded

A value of 2350.50, a valid number of digits of 6, cannot be stored.

(3), Number (3,-2): When S is negative, more special, the value will be left | S| (| s| for the absolute value of s) is rounded and takes 0 and does not count toward the number of significant digits,

For example: When the value to be stored is 100, the actual value is 100 and the significant number of digits is 1, which can be stored. When the value is 3.5, the actuals are 0, and the significant number of digits is 1, which can be stored.

When the value is 2350.5, the actuals are 2400, and the significant number of digits is 2, which can be stored.

2. Integer type

Is the subtype of number, equivalent to number (38,0), which is used to store integers.

3. Float type

Is the subtype of number, in the form of float (n), n between 1~126, this value is the binary precision, and the maximum value of 126 is converted to decimal approximately 38.

Iii. Date and Time type

1. Date type

Stores the type of date and time, using a fixed length of 7 bytes, storing the century, year, month, day, hour, minute, and second, the minimum time is January 1, 4712 BC, the maximum time is

December 31, 9999 CE. You can use the Sysdate function to get the date type of the current time.

2. Timestamp type

Stores the type of date and time, using 7 to 12 bytes in length, the number of seconds is accurate to fractional seconds (6 bits after the decimal point without specifying the precision, the specified precision can reach 9 bits),

and contains time zone information. You can use the Systimestamp function to get the timestamp type of the current time.

Iv. type of LOB

1. CLOB type

Stores a large number of character data that can store single-byte character data and multibyte character data, primarily for storing unstructured XML documents.

2. Blob type

Store binary objects, slices, videos, and music.

3. bfile Type

Stores a locator that points to a binary file of a server file system with a maximum of 4G.

4. NCLOB type

Similar to Clon, but the Nclon can support both fixed-width and variable-width character sets, up to a maximum of 4G.

Manipulating LOB type data can be done using the DBMS_LOB package provided by PL/SQL, which makes it reasonably well-allocated for the use of LOB types, which would otherwise have a performance impact.

Five, pseudo-column

Pseudo-column definition: Similar to a column in a table, but not actually stored in the table, cannot be added to the pseudo-column.

1, ROWID

Each row in the table has an address that uniquely identifies a row in the database, and ROWID can return this address, which can be used as a query condition to query for unique rows and improve query efficiency.

You can also see how the rows are stored in the table.

2, ROWNUM

The rownum pseudo-column can be used to sort the results of a query starting from 1, which can limit the number of rows returned, and Oracle's paging is implemented using RowNum. Be aware that using rownum

Must contain 1, otherwise unable to detect the data, that is, rownum=1 can query the result set the first row of data, but rownum=2 is unable to query any data,

And Rownum>2 is not able to query any data, but use rownum<2 can query out two data, this is because RowNum is a pseudo-column starting from 1,

Oracle believes that Rownum>n (n is a natural number greater than 1) is not valid and all data cannot be detected.

(More information about pseudo-columns can be found in the official documentation: HTTP://DOCS.ORACLE.COM/CD/E11882_01/SERVER.112/E41084/PSEUDOCOLUMNS.HTM#SQLRF0025)

Oracle Chapter II--oracle data types