Oracle Implicit type Conversion

Detailed Connection: http://blog.163.com/lsj_start/blog/static/17826443920111112103716524/

http://blog.itpub.net/29324876/viewspace-1096741/(view implicit conversion with explain)

Oracle has three of the most basic data types, namely, character, numeric, and date. also provides a type conversion function

1)To_char
    Numeric, date, character type

Syntax:to_char (num|date,[format
Mask],[nls_parameters])
Parameter:num|date
Value or date to convert
Format
Mask: Optional Parameters

2)to_date

Date value , character value             Syntax:to_date (string,[format mask],[nls_parameters]) parameter:string The character value to be converted Format mask: Optional parameters

The format mask is the same as to_char when converted to date .

Note: The conversion is based on the given string set the correct format mask, otherwise

Ora_01840:input value is not a long enough for date format.

Ora_01862:the numeric value does not match the length of the format item.

3) To_number

numeric value , character value Syntax:to_number (string,[format mask],[nls_parameters]) parameter:string The character value to be converted format mask: Optional parameters

The format mask is the same as to_char when converted to number .

Note: If a shorter format mask is used, an error is returned.

For example : To_number (123.56, ' 999.9 ') returns an error.

in Oracle, if a different data type is associated, if you do not explicitly convert the data, it implicitly converts the data according to the following rules

1) for INSERT and update operations, Oracle implicitly converts the inserted or updated values to the data type of the field.

If the data type of the ID column is number

Update T set id= ' 1 '; Equivalent to update t set Id=to_number (' 1 ');

INSERT into T (ID) VALUES (' 1 '), insert into T values (To_number (' 1 '));

2) for the SELECT statement, Oracle implicitly converts the data type of the field to the data type of the variable. such as assuming that the data type of the ID column is VARCHAR2

SELECT * from t where id=1; -select * from T where to_number (id) = 1;

However, if the data type of the ID column is number, the

SELECT * from t where id= ' 1 '; SELECT * FROM t where Id=to_number (' 1 ');(reference below)

3) When comparing the values of a character type and a numeric value, Oracle implicitly converts the value of the character type to a numeric type.

As if the data type of the ID column is number

SELECT * from t where id= ' 1 '; -select * from T where Id=to_number (' 1 ');

4) When comparing data in both character and date types, Oracle converts the character type to the date type.

Assuming that Create_date is a character type,

SELECT * FROM t where create_date>sysdate; SELECT * FROM t where To_date (create_date) >sysdate; (note that the Nls_date_format of the session needs to match the string format)

Suppose Create_date is a date type,

SELECT * from t where create_date> ' 2006-11-11 11:11:11 '; -select * from T where Create_date>to_date (' 2006-11-11 11:11:11 '); (Note that the Nls_date_format of the session needs to match the string format)

5) If you call a function or procedure, etc., if the data type of the input parameter is not always the same as the parameter data type of the function or procedure definition, Oracle converts the data type of the input parameter to the data type of the function or procedure definition.

As the hypothetical procedure defines P (p_1 number) as follows

EXEC p (' 1 '); --exec P (to_number (' 1 ')); 6)

When assigned, Oracle converts the data type on the right side of the equals sign to the data type on the left.

such as var a number a:= ' 1 '; -> A:=to_number (' 1 ');

7) using the Join operator (| |) , Oracle converts data of non-character types to character types.

Select 1| | ' 2 ' from dual; --Select To_char (1) | | 2 ' from dual;

8) If the character type's data and non-character type data (such as number, date, ROWID, and so on) are arithmetic operations, Oracle converts the data of the character type to the appropriate data type, which may be number, date, ROWID, and so on.

If there is an arithmetic operation between Char/varchar2 and Nchar/nvarchar2,

Oracle will compare the data that they all convert to number type.

9) When comparing CHAR/VARCHAR2 and NCHAR/NVARCHAR2, if the two character sets are different, the default conversion is to convert the data encoding from the database character set to the national character set.

Simple summary:

When comparing, the character type is usually converted to a numeric type, and the character type is converted to a date type.

In arithmetic operations, the character type is generally converted to a numeric type, and the character type is converted to a date type.

When connected (| |), it is usually converted to a character type, and the date type is converted to a character type.

When assigning, calling a function, the variable type defined is whichever.

Oracle Implicit type Conversion