ORACLE Char and VARCHAR2 types

Today comes into contact with a more interesting question, common to extremely easy to ignore, but inadvertently fell out of the pit and not easy to come out.

To create a table:

CREATE TABLE Temp_decode (Borrow_type char (1), Borrow_type1 char (2), Borrow_type2 VARCHAR2 (Ten), Borrow_type3 INT)

　　

Execute SQL as follows:

SELECT DECODE (Borrow_type, ' 7 ', ' abc ', ' Hello '), DECODE (Borrow_type1, ' 7 ', ' abc ', ' Hello '), DECODE (borrow_type1,7, ' abc ') , ' Hello '), DECODE (Borrow_type2, ' 7 ', ' abc ', ' Hello '), DECODE (Borrow_type3, ' 7 ', ' abc ', ' Hello ') from Temp_decode

　　

What do you think the results will look like?

Actually the result is this guy, is it the same as you expected?

When we think about it, we all know that the char type is fixed (in the pit), so the ' 7 ' in char (2) type is not ' 7 ', but the database fills a character, so what is the problem?

is actually a space (""), as in this SQL

SELECT  DECODE (Borrow_type, ' 7 ', ' abc ', ' Hello '), DECODE (Borrow_type1, ' 7 ', ' abc ', ' Hello '),--note the space of this place DECODE ( borrow_type1,7, ' abc ', ' Hello '), DECODE (Borrow_type2, ' 7 ', ' abc ', ' Hello '), DECODE (Borrow_type3, ' 7 ', ' abc ', ' Hello ') From Temp_decode  

The result is:

Although this problem is not common, it is very easy to drop the pit if it is an enumeration type such as (1,2,3,4.). such as in the DB suggest or use smallint bar, if the non-character of the VARCHAR2 type bar, char type or as little as possible. Imagine that you originally char (1) with a good,

As a result, the length of the other people changed to char (2), your previous SQL is not directly duang (if the stored values you directly written as a numeric type can also like the first SQL, just this scenario I do not know why you do not smallint it)?

ORACLE Char and VARCHAR2 types