1. The length of char is fixed, the length of the VARCHAR2 can vary, for example, the storage string "abc", for Char (20), which means that the characters you store will occupy 20 bytes (including 17 null characters), in the database it is occupied by space, and the same VARCHAR2 (20) Only occupies 3 bytes of length, 20 is the maximum value, when you store the characters less than 20 o'clock, stored by the actual length.
The efficiency of 2.CHAR is slightly higher than that of VARCHAR2. It seems cscm_number should be set to char (19)
3. The current varchar is synonymous with VARCHAR2. Industry-standard varchar types can store empty strings, but Oracle does not, although it retains the right to do so later. Oracle himself developed a data type VARCHAR2, which is not a standard varchar that stores null values in a database where varchar columns can store an empty string. If you want to be able to be backward compatible, Oracle recommends using VARCHAR2 instead of varchar.
When should I use char and when should I use VARCHAR2?
Char and VARCHAR2 are a pair of contradictory unity, the two are complementary relations. VARCHAR2 saves more space than Char, and is slightly less efficient than char, which means that if you want to be efficient, you have to sacrifice a certain amount of space, which is what we often say in database design, ' space for efficiency '. VARCHAR2 saves space than Char, but if a VARCHAR2 column is often modified and the length of each modified data varies, this causes a ' row migration ' (row migration) phenomenon, which creates redundant I/O, which is avoided in database design and tuning. , it would be better to use char instead of VARCHAR2 in this case.
A comparison of Char and VARCHAR2
The char type is compared with the char or Word constants, using padded spaces for comparison.
VARCHAR2 type and VARCHAR2 type, char type and Word constants quantity comparison, compare without adding space, direct comparison.
CREATE TABLE TT (A1 CHAR (2), A2 VARCHAR2 (2));
INSERT into TT VALUES (' A ', ' a ');
INSERT into TT values (' A ', ' a ');
COMMIT;
Comparison of--char type and character constants quantity, character constants quantity as Char type processing
--compared to ' a ', returns 2 rows, that is, when the constant ' a ' is automatically padded with space after comparison
SELECT * from TT where a1= ' A ';
A1 A2
-- --
A A
A A
--compared to ' a ', returns 2 rows, that is, when the constant ' a ' is automatically padded with space after comparison
SELECT * from TT where a1= ' A ';
A1 A2
-- --
A A
A A
Comparison of--varchar2 and constants, the constants quantity as a VARCHAR2-type processing
--Compare with ' a ', return 1 rows, that is, when comparing to ' a ' does not do processing, direct comparison
SELECT * from TT WHERE a2= ' A ';
A1 A2
-- --
A A
--Compare with ' a ', return 1 rows, that is, when comparing to ' a ' does not do processing, direct comparison
SELECT * from TT WHERE a2= ' A ';
A1 A2
-- --
A A
--When comparing a char type with a VARCHAR2 type, the value of the field is not treated as a direct comparison
--let A1 and A2 directly compare, at this time is a direct comparison, there is a record of A1 and A2 the same
SELECT * from TT where A1=A2;
A1 A2
-- --
A A
But when used in conjunction with the Decode function, there are different situations
Using the A1 field
Select Decode (A1, ' A ', ' AAAA ', ' bbbb ') from TT;
DECODE (A1, ' A ', ' AAAA ', ' bbbb ')
----------------------------
bbbb
bbbb
Although the A1 field is char (2), the comparison does not compare the constant ' A ' fill space with the field A1, but instead compares it directly, which means that two comparison fields are treated according to the VARCHAR2 type
As a result, the field A1 is not equal to the constant ' A ', and a record of two results is ' BBB ' is found.
Further verification,
Select Decode (A1, ' A ', ' AAAA ', ' bbbb ') from TT; --Here are two spaces
DECODE (A1, ' A ', ' AAAA ', ' bbbb ')
----------------------------
bbbb
bbbb
or return two ' BBB ' records to show that comparisons are not handled according to the CHAR-type comparison rules.
Using the A2 field
Select decode (A2, ' A ', ' AAAA ', ' bbbb ') from TT;
DECODE (A2, ' A ', ' AAAA ', ' bbbb ')
----------------------------
Aaaa
bbbb
This is the normal comparison between the VARCHAR2 types, the first record of the A2 field equals ' A ', returns ' AAA ', the second line records the A2 field is ' A ', the comparison is not equal, returns ' BBB '
When the case expression is used to process the A1 field, there is a different processing result than the Decode function
Using Fields A1
Select Case A1 when ' A ' then ' AAA ' Else ' BBB ' end from TT;
Casea1when ' A ' THEN ' AAA ' ELSE ' BBB
------------------------------
Aaa
Aaa
When using the A1 field in a case statement to compare to constant ' A ', the two comparison values are processed according to the comparison rule of char type, compared after the right complement space, so that two records are returned
Further verification, as follows,
Select Case A1 when ' A ' then ' AAA ' Else ' BBB ' end from TT;
Casea1when ' A ' THEN ' AAA ' ELSE ' BBB
------------------------------
Aaa
Aaa
The constant is changed to ' A ', compared to the char type, and the field A1 to the constant after the space is padded.
Using the A2 field
Select Case A2 when ' A ' then ' AAA ' Else ' BBB ' end from TT;
Casea2when ' A ' THEN ' AAA ' ELSE ' BBB
------------------------------
Aaa
Bbb
This is the normal comparison between the VARCHAR2 types, the first record of the A2 field equals ' A ', returns ' AAA ', the second line records the A2 field is ' A ', the comparison is not equal, returns ' BBB '
Summarize
When comparing a char field using the Decode function, it is necessary to note that even if the two fields of comparison are char types, the Decode function converts it to a varchar2 type and does not follow a char-type comparison rule
For char data, in the set operation, the actual data of char is compared, not according to the comparison rules for char data.
Here is a simple example
CREATE table T1 (name char (10));
CREATE TABLE T2 (name char (20));
Begin
For I in 1..5 loop
INSERT into T1 values (To_char (i));
INSERT into T1 values (To_char (i));
End Loop;
Commit
End;
Select name from t1 minus select name from T2;
NAME
--------------------
1
2
3
4
5
If you follow a char-type comparison rule, you should not have a return value.
Select anem from t1 intersect select name from T2;
NAME
--------------------
No return value, and it also indicates that the comparison of the collection operation was not followed by a char-type comparison rule.
=============================== Review 2============================
The difference between decode and case for char types is due to the fact that it is an expression and that decode is a function.
The input parameters and return values in a function are generally defined as VARCHAR2 types.
Thanks for pointing, I didn't think of it here, look at the definition of the Decode function, as you mentioned, type conversion of char data using the Decode function.
The following is a parameter definition for the DECODE function.
Sql> desc Sys.standard.decode
Parameter Type Mode Default?
--------- ----------------------- ---- --------
(Result) Number
EXPR number in
PAT number in
RES number in
(Result) VARCHAR2
EXPR number in
PAT number in
RES VARCHAR2 in
(Result) DATE
EXPR number in
PAT number in
RES DATE in
(Result) Number
EXPR VARCHAR2 in
PAT VARCHAR2 in
RES number in
(Result) VARCHAR2
EXPR VARCHAR2 in
PAT VARCHAR2 in
RES VARCHAR2 in
(Result) DATE
EXPR VARCHAR2 in
PAT VARCHAR2 in
RES DATE in
(Result) Number
EXPR DATE in
PAT DATE in
RES number in
(Result) VARCHAR2
EXPR DATE in
PAT DATE in
RES VARCHAR2 in
(Result) DATE
EXPR DATE in
PAT DATE in
RES DATE in
(Result) SYS. STANDARD.
EXPR SYS. STANDARD. In
PAT SYS. STANDARD. In
RES SYS. STANDARD. In
(Result) SYS. STANDARD.
EXPR SYS. STANDARD. In
PAT SYS. STANDARD. In
RES SYS. STANDARD. In