oracle data_type,oracle built-in data type

Oracle Built-in Data Types

Scalar Data Types

Character data: Character data can be stored as either fixed-length or variable-length strings in the database.Fixed-length character data types, such as CHAR and NCHAR, are stored with padded blanks. NCHAR is a Globalization Support data type that enables the storage of either fixed-width or variable-width character sets. The maximum size is determined by the number of bytes required to store one character, with an upper limit of 2,000 bytes per row.The default is one character or one byte, depending on the character set.CHAR

SQL> create table t1(c1 char(1));

表已创建。

SQL> insert into t1 values('a');

已创建 1 行。

SQL> select lengthb(c1) from t1;

LENGTHB(C1)

----------

1

SQL> insert into t1 values('型');

insert into t1 values('型')

*

第 1 行出现错误:

ORA-12899: value too large for column "SYS"."T1"."C1" (actual: 3, maximum: 1)

SQL> drop table t1;

表已删除。NCHAR

SQL> create table t1(c1 nchar(1));

表已创建。

SQL>  insert into t1 values('型');

已创建 1 行。

SQL> select lengthb(c1) from t1;

LENGTHB(C1)

-----------

2

SQL> insert into t1 values ('a');

已创建 1 行。

SQL>  select lengthb(c1) from t1;

LENGTHB(C1)

-----------

2

2

SQL> drop table t1;

表已删除。

SQL> create table t1(c1 nchar(1001));

create table t1(c1 nchar(1001))

*

第 1 行出现错误:

ORA-00910: specified length too long for its datatype但是存在个疑问

SQL>  select lengthb('型') from dual

2  ;

LENGTHB('型')

-------------

3

SQL> alter table t1 modify c1 char(3);

表已更改。

SQL> insert into t1 values ('已');

已创建 1 行。

SQL>  select lengthb(c1) from t1;

LENGTHB(C1)

-----------

3

SQL> create table t2 (c1 nchar(1));

表已创建。

SQL> insert into t2 values ('表');

已创建 1 行。

SQL>  select lengthb(c1) from t2;

LENGTHB(C1)

-----------

2为何直接查询一个汉字byte为3，而插入nchar之后为2？？插入char需要3个byte来存储，插入nchar后用两个byte来存储？

Variable-length character data types use only the number of bytes needed to store the actual column value, and can vary in size for each row, up to 4,000 bytes. VARCHAR2 and NVARCHAR2 are examples of variable-length character data types.

Numeric data type: Numbers in an Oracle database are always stored as variable-lengthdata. They can store up to 38 significant digits. Numeric data types require:

• One byte for the exponent

• One byte for every two significant digits in the mantissa

• One byte for negative numbers if the number of significant digits is less than 38 bytes

DATE data type: The Oracle server stores dates in fixed-length fields of seven bytes. An Oracle DATE always includes the time.

TIMESTAMP data type: This data type stores the date and time including fractional seconds up to nine decimal places. TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE can use time zones to factor items such as daylight savings time.

TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE can be used in primary keys, TIMESTAMP WITH TIME ZONE cannot.

RAW data type: This data type enables the storage of small binary data. The Oracle server does not perform. character set conversion when RAW data is transmitted across machines in a network or if RAW data is moved from one database to another using Oracle utilities. The number of bytes needed to store the actual column value, and can vary in size for each row, up to 2,000 bytes.

LONG, LONG RAW, and Large Object (LOBs) Data TypesOracle provides six data types for storing LOBs:

• CLOB and LONG for large fixed-width character data

• NCLOB for large fixed-width national character set data

• BLOB and LONG RAW for storing unstructured data

• BFILE for storing unstructured data in operating system files

LONG and LONG RAW data types were previously used for unstructured data, such as binary images, documents, or geographical information, and are primarily provided for backward compatibility. These data types are superseded by the LOB data types. LOB data types are distinct from LONG and LONG RAW, and they are not interchangeable. LOBs will not support the LONG application programming interface (API), and vice versa.

ROWID Format

Extended ROWID Format

OOOOOO       |       FFF       |       BBBBBB       |       RRR

Data objec              Relative file     Block number       Row number

number                   number

SQL> select rowid from t2;

ROWID

------------------

AAANCEAABAAAPcSAAAAAANCE is the data object number.

AAB is the relative file number.

AAAPcS is the block number.

AAA is the row number for the department with ID = 10.