Introduction to Oracle Data types

Defined in ORACLE8: scalar (Scalar), composite (composite), reference (REFERENCE), and lob four data types.

(i) scalar (Scalar)

The scalar type is the same type as the database column, and it has some extensions.

It is divided into seven groups: numbers, characters, lines, dates, row identities, Boolean, and trustworthiness.

There are three basic types of ① numbers: number, Pls_integer and Binary_intener.

Number can describe integers or real numbers, while Pls_integer and binary_intener can only describe integers.

Number, which is stored in decimal format, is easy to store, but in calculation, the system will automatically convert it into binary operation. It is defined by number (p,s), P is precision, the maximum 38 digits, S is the scale range, can be in-84 ... 127 values are taken. For example: Number (5,2) can be used to store representations-999.99 ... Number of 999.99.

P, S can be omitted in the definition, for example: Number (5), number, etc.;

Binary_intener is used to describe signed integer values that are not stored in the database but need to be computed. It is expressed in 2 complement binary form. Loop counters often use this type.

The only difference between Pls_integer and Binary_intener is that when an overflow occurs in the calculation, the Binary_intener variable is automatically assigned to a number without error, and the Pls_integer-type variable will be wrong.

The ② characters include Char, VARCHAR2 (VARCHAR), LONG, nchar, and NVARCHAR2 several types.

CHAR describes a fixed-length string, and if the actual value is less than the defined length, the system is padded with spaces. It is declared in the following way

CHAR (L) L is the string length, and the default is 1, the maximum of 32,767 characters as a variable, and the maximum of 2000 as data stored in ORACLE8.

VARCHAR2 (VARCHAR) describes variable-length strings. It is declared as follows VARCHAR2 (L), L is a string length, has no default value, is the maximum 32,767 bytes of the variable, and as the data is stored in the ORACLE8 maximum of 4000. In a multibyte locale, the actual number of characters stored may be less than the L value, for example, when the locale is Chinese (simplified Chinese_china). ZHS16GBK), a VARCHAR2 (200) data column can hold 200 English characters or 100 characters.

LONG can be used to hold up to 2G of data in the database store, as a variable that can represent a variable string with a maximum length of 32760 bytes.

The NCHAR, NVARCHAR2 national character set, is closely related to the language set specified by the environment variable NLS, using the same method as Char, VARCHAR2.

③ Line

Includes raw and long raw two types. Used to store binary data and will not be converted between character sets.

Raw is similar to char, is declared Raw (L), and L is length, in bytes, as the maximum of 2000 for the database column, and the maximum of 32767 bytes for the variable.

Long is similar to long as the maximum storage of 2G bytes of data in a database column, which is the maximum 32760 bytes of a variable.

④ Date

There is only one type--date, which is used to store time information, with 7 bytes (from the century to the second), there is absolutely no "millennium bug" problem.

⑤ Line Identification

There is only one type--rowid to store the row identifier, which can be used to convert the row identity to a character by using the Rowidtochar function.

⑥ Boolean

There is only one type--boolean, which can only represent true, FALSE, or null.

⑦ trustworthy

There is only one type of--mlslabel that can be used in trusted Oracle to hold variable-length binary tags.

In standard Oracle, only null values can be stored.

(ii) composite (composite)

Scalar types are predefined, and these types can be used to derive some compound types. There are mainly records and tables.

① records, which can be considered as a set of scalar composite structures, are declared in the following ways:

TYPE Record_type_name is record

(Filed1 type1 [Not NULL] [: =EXPR1]

.......

Filedn Typen [Not NULL] [: =EXPRN])

Where Record_type_name is the name of the record type. (Does it look like a create TABLE?) ......) You must define the relevant variables when referencing the

Records are only type, not variable.

The ② table, which is not a table for storing data physically, is a variable type, also known as a pl/sql table, similar to an array in C language, and is similar in terms of handling.

The way it is declared is as follows:

TYPE Table_type_name is table of Scalar_type INDEX by Binary_intener;

Where Table_type_name is the name of the type, Scalar_type is a type declaration of a scalar type. You must also define the relevant variables when referencing.

Tables and arrays differ, and the table has two columns, and key and Value,key are the binary_intener,value declared when the definition is scalar_type.

In addition to records and tables, there are types of objects, collections (nested tables and varrays), which are specifically explained.

(iii) references (REFERENCE)

Before pl/sql8.0, there was only one type of--ref CURSOR, the cursor. It's more simple to define,

CURSOR cursor_name is select ... from ... ；

After pl/sql8.0, the ref type is introduced, which points to an object.

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45460.htm