Introduction to Oracle Data Types

I. Overview

Four data types are defined in oracle8: Scalar, composite, reference, and lob. The following describes their features.
Ii. scalar)

The valid scalar type is the same as the type used by the database columns. In addition, it has some extensions. It is divided into seven groups: Numbers, characters, rows, dates, row identifiers, Boolean and trusted.
Number It has three basic types: Number, pls_integer, and binary_intener. Number can be an integer or a real number, while pls_integer and binary_intener can only be an integer.
Number It is stored in decimal format, which is easy to store, but in computing, the system automatically converts it into binary for calculation. It is defined as number (P, S), P is precision, the maximum is 38 BITs, S is the scale range, can be in-84... 127 value. For example, number (5, 2) can be used to store values that represent-999... 999.99. P and S can be omitted in definition, such as number (5) and number;
Binary_intener It is used to describe the signed integer that is not stored in the database but must be used for calculation. It is expressed as a binary complement of 2. This type is often used by cyclic counters.
Pls_integer and binary_intener The only difference is that when computing overflow occurs, the binary_intener type variables will be automatically assigned to a number type without errors. pls_integer type variables will have errors.
Character Including char, varchar2 (varchar), long, nchar, and nvarchar2.
Char , Describes a string with a fixed length. If the actual value is not enough to define the length, the system will fill it with spaces. The statement is as follows: Char (L). L is the string length. The default value is 1. It can be a variable of up to 32767 characters and 2000 of data is stored in oracle8.
Varchar2 (Varchar) to describe a variable-length string. The declaration method is as follows: varchar2 (L), L is the string length, there is no default value, as the variable is up to 32767 bytes, as the maximum data storage in oracle8 is 4000. In a multi-byte language environment, the actual number of characters stored may be less than the L value. For example, when the language environment is Chinese (simplified chinese_china.zhs16gbk), a varchar2 (200) the data column can contain 200 English characters or 100 Chinese characters.
Long It can be used in database storage to store up to 2 GB of data. As a variable, it can represent a variable string with a maximum length of 32760 bytes.
Nchar, nvarchar2 The national character set, which is closely related to the language set specified by the Environment Variable NLS. The usage is the same as that of char and varchar2.
Line Including raw and long raw. It is used to store binary data and is not converted between character sets.
Raw Similar to Char, the declaration method is raw (L). L is the length, in bytes, and serves as the maximum 2000 bytes of the database column and the maximum 32767 bytes of the variable.
Long raw , Similar to long, which is used as the database column to store up to 2 GB of data and as the variable to store up to 32760 bytes.
Date There is only one type-date, which is used to store time information and uses 7 bytes (from Century to second). There is absolutely no "Millennium worm" problem.
Row ID There is only one type-rowid, which is used to store "Row identifiers". You can use the rowidtochar function to convert row identifiers into characters.
Boolean Only one type, Boolean, can represent true, false, or null.
Trusted There is only one type-mlslabel, which can be used to save binary labels with Variable Length in trusted oracle. In standard Oracle, only null values can be stored.
Composite)

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

A record can be viewed as a combination structure of a group of tags. Its declaration method is as follows:

Type record_type_name is record

(Filed1 type1 [not null] [: = expr1]

.......

Filedn typen [not null] [: = exprn])

Record_type_name indicates the name of the record type. (Does it look like create table ?......) Related variables must be defined during reference. The record is only type, not variable.

A table is not a table that stores data physically. It is a variable type, also known as a PL/SQL table. It is similar to an array in C language and is processed in a similar way. The statement is as follows:

Type table_type_name is table of scalar_type index by binary_intener;

Table_type_name is the type name, And scalar_type is a type declaration of scalar type. Related Variables must also be defined during reference. Different from an array, a table has two columns: Key and value. Key is the binary_intener declared during definition, and value is the scalar_type declared during definition.

In addition to records and tables, there are also object types, sets (nested tables and varrays) and other types, which will be explained in detail.
4. Reference)

Before PL/sql8.0, there is only one type-ref cursor, that is, cursor. Its definition is relatively simple,

Cursor cursor_name is select... from .....;

After PL/sql8.0, the ref type is introduced, which points to an object. This type will be explained separately. (Wait until I understand it, so that it will not be in front of the high man ......)
V. lob type

The lob variable is mainly used to store database fields with a large amount of data. It can store up to 4G bytes of content (such a large value, storing VCD ?, Waste !). Mainly include:
Clob : Similar to the long type in oracle7, it stores single-byte character data. (Do not use Chinese characters ...)
Nclob : Used to store fixed-width, multi-byte character data. (For example, it would be nice to store "Xiao AO Jianghu)
Blob : Similar to long raw, used to store unstructured binary data.
Bfile : It is used to allow Oracle to read-only access to large binary files stored outside the database.
Vi. Others

In fact, after understanding the scalar variables, we can basically cope with the needs of conventional database applications in the C/S mode. To tell the truth about the subsequent types, I have been playing Oracle for five years, is rarely used.

Appendix: Reference oracle8 PL/SQLProgram Design (oracle8 PL/SQL programming) Oracle technology Series Mechanical Industry Press

Note: This document lists the data types that can be defined in PL/SQL. Not all types can be specified as column data types during table creation.