Implicit conversion rules for data types in PL/SQL

Http://blogold.chinaunix.net/u4/120270/showart_2383431.html 1) During INSERT and UPDATE operations, Oracle converts the value to the ype of the affected column.
For INSERT and UPDATE operations, oracle implicitly converts the inserted or updated values to the Data Type of the field. For example
Assume that 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'); values 2) During select from operations, oracle converts the data from the column to the type of the target variable.
For SELECT statements, oracle implicitly converts the field data type to the variable data type. For example
Assume 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
Select * from t where id = '1';-> select * from t where id = to_number ('1'); (refer to the following section) ------------------------------------------------------------ 3) when comparing a character value with a NUMBER value, Oracle converts the character data to NUMBER.
When comparing a numeric value with a numeric value, oracle implicitly converts the numeric value to a numeric value. For example
Assume that the data type of the id column is number.
Select * from t where id = '1';-> select * from t where id = to_number ('1'); limit 4) When comparing a character value with a DATE value, oracle converts the character data to DATE.
When comparing data of the primary and date types, oracle converts the standby type to the date type. For example
Assume that create_date is of the sequence type,
Select * from t where create_date> sysdate;-> select * from t where to_date (create_date)> sysdate; (Note: At this time, the session nls_date_format must be consistent with the string format)
Assume that create_date is of the date type,
Select * from t where create_date> '2017-11-11 11:11:11 ';-> select * from t where create_date> to_date ('2017-11-11 11:11:11'); (note, at this time, the session nls_date_format must be consistent with the string format) Listen 5) When you use a SQL function or operator with an argument of a datatype other than the one it accepts,
Oracle converts the argument to the accepted datatype.
If the data type of the input parameter is different from that of the function or process, oracle converts the Data Type of the input parameter to the data type defined by the function or process. For example
Assume that the process is defined as follows:
P (p_1 number)
Exec p ('1');-> exec p (to_number ('1'); ------------------------------------------------------------ 6) When making assignments, oracle converts the value on the right side of the equal sign (=) to the datatype of the target of the assignment on the left side.
When a value is assigned, oracle converts the data type on the right of the equal sign to the data type on the left. For example
Var a number
A: = '1';-> a: = to_number ('1 ');
--------------------------------------------------------------
7) During concatenation operations, Oracle converts from noncharacter datatypes to CHAR or NCHAR.
When the concatenation operator (|) is used, oracle converts non-character data to the character type.
Select 1 | '2' from dual;-> select to_char (1) | '2' from dual;
--------------------------------------------------------------
8) During arithmetic operations on and comparisons between character and noncharacter datatypes,
Oracle converts from any character datatype to a number, date, or rowid, as appropriate.
In arithmetic operations between CHAR/VARCHAR2 and NCHAR/NVARCHAR2, Oracle converts to a number.
If data of the character type and data of the non-character type (such as number, date, and rowid) are used for arithmetic operations, oracle converts the data of the character type to the appropriate data type, these data types may be number, date, and rowid.
If an arithmetic operation is performed between CHAR/VARCHAR2 and NCHAR/NVARCHAR2, oracle converts all of them to number type data for comparison.
--------------------------------------------------------------
9) Comparisons between CHAR/VARCHAR2 and NCHAR/NVARCHAR2 types may entail different character sets.
The default direction of conversion in such cases is from the database character set to the national character set. when comparing CHAR/VARCHAR2 and NCHAR/NVARCHAR2, if the character sets are different, the default conversion method is to convert the data encoding from the database character set to the national character set. --------------------------------------------------------------
Summary:
During comparison, the numeric type is generally converted to the numeric type, and the numeric type is converted to the date type.
During arithmetic operations, the numeric type is generally converted to the numeric type, and the numeric type is converted to the date type.
During connection (|), the numeric type is generally converted to the numeric type, and the date type is converted to the numeric type.
When assigning values or calling functions, the defined variable type prevails.