The following articles mainly analyze Oralce data types through examples. Based on Oracle 10 Gb, this article introduces the new data types introduced by Oracle 10 Gb. To introduce the actual operations related to Oracle Data Types and reveal the characteristics of Oracle data types.
Character Type
1.1 char
A fixed-length string is filled with spaces to reach its maximum length, up to 2000 bytes.
1. Create a test table test_char. With only one char column. Length: 10
SQL> create table test_char (colA char (10 ));
Table created
2. Insert some data into the table.
SQL> insert into test_char values ('A ');
1 row inserted
SQL> insert into test_char values ('A ');
1 row inserted
SQL> insert into test_char values ('aaa ');
1 row inserted
SQL> insert into test_char values ('aaa ');
1 row inserted
SQL> insert into test_char values ('aaaaaaaaaaa ');
1 row inserted
Note: A maximum of 10 bytes can be inserted. If no, an error is returned.
SQL> insert into test_char values ('aaaaaaaaaaa ');
Insert into test_char values ('aaaaaaaaaaa ')
ORA-12899: value too large for column "PUB_TEST". "TEST_CHAR". "COLA" (actual: 11, maximum: 10)
3. Use the dump function in Oracle data types to view the internal storage structure of each row.
SQL> select colA, dump (colA) from test_char;
Cola dump (COLA)
A Typ = 96 Len = 10: 97,32, 32,32, 32,32, 32,32, 32,32
Aa Typ = 96 Len = 10: 97,97, 32, 32, 32, 32, 32, 32
Aaa Typ = 96 Len = 10: 97,97, 97,32, 32, 32, 32, 32
Aaaa Typ = 96 Len = 10: 97,97, 97,97, 32, 32, 32, 32
Aaaaaaaaaa Typ = 96 Len = 10: 97,97, 97,97, 97,97, 97,97, 97,97
Note: Typ = 96 indicates the Data Type ID. Oracle numbers each data type. It indicates that the number of the char type is 96.
Len = 10 indicates the length of the internal storage (in bytes ). Although the first example only contains one character 'a', it still occupies 10 bytes of space.
97,32, 32,32, 32,32, 32,32, 32,32 indicates the internal storage mode. It can be seen that the internal storage of Oracle is stored in the database character set.
97 is the ASCII code of Character.
You can use the chr function to convert ASCII code into characters.
SQL> select chr (97) from dual;
CHR (97)
A
To know the ASCII code of a character, you can use the ascii function.
SQL> select ascii ('A') from dual;
ASCII ('A ')
97
32 is the ascii value of space.
Char is a fixed-length type. It is always filled with spaces to reach a fixed width.
Using the char type will waste storage space.
The length unit of Oracle data type is byte.
SQL> select dump ('hang') from dual;
DUMP ('han ')
Typ = 96 lename = 2: 186,186
It can be seen that a Chinese character occupies two bytes in Oracle.
English letters or symbols only occupy one byte.
Char (10) can store up to 5 Chinese characters.
The above content is an introduction to Oracle Data Types. I hope you will get some benefits.