Data-type implicit conversion rules in PL/SQL

Originally from Techfox It Technology Forum

1) During Insert and update operations, Oracle converts the value to the datatype of the affected column. For INSERT and update operations , Oracle implicitly converts the insertion value or update value to the data type of the field. If the data type of the ID column is numberupdate 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) 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 data type of the field to the data type of the variable. For example, suppose the data type of the ID column is Varchar2select * from t where id=1; SELECT * FROM t where to_number (id) = 1, but if the data type of the ID column is number, then select * from T where id= ' 1 '; SELECT * FROM t where Id=to_number (' 1 ');(reference below)--------------------------------------------------------------3) When comparing a character value with a number value, the Oracle converts the character data to numbers. When comparing values of a character type and a numeric value, Oracle Implicitly converts the value of a character type to a numeric type. For example, suppose the data type of the ID column is Numberselect * from T where id= ' 1 '; -select * from T where Id=to_number(' 1 ');--------------------------------------------------------------4) when comparing a character value with a DATE Value, Oracle converts the character data to date. When comparing data in both character and date types, Oracle converts the character type to the date type. For example, if 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) assumes 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) 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 you call a function or procedure and so on, 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 process defines  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 assigned, Oracle converts the data type on the right side of the equals sign to the data type on the other side. such as Var a numbera:= ' 1 '; -> A:=to_number (' 1 ');--------------------------------------------------------------7) During concatenation Operations, Oracle converts from noncharacter datatypes to CHAR or NCHAR. 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) During arithmetic operations on and Comparisons between character and Noncharacter Datatypes, oracle converts from any character datatype to a number, DA Te, or rowID, as appropriate. in arithmetic operations between CHAR/VARCHAR2 and NCHAR/NVARCHAR2, Oracle converts to A number. 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 an arithmetic operation is made between CHAR/VARCHAR2 and NCHAR/NVARCHAR2, Oracle converts them toThe number type data is compared again. --------------------------------------------------------------9) Comparisons between Char/varchar2 and nchar/ NVARCHAR2 types may entail different character sets. the default direction of conversion in such cases was from the DA Tabase character set to the national character set. When comparing CHAR/VARCHAR2 and NCHAR/NVARCHAR2, if the two character sets are different, The default conversion method is to convert data encoding from the database character set to the national character set. --------------------------------------------------------------Simple summary: When compared, the character type is generally converted to numeric type, the character type is converted to the date type arithmetic operation, Generally converts a character type to a numeric type, the character type is converted to a date type (| |), it is generally converted to a character type, a date type is converted to a character assignment, and a function is called, whichever type of variable is defined.

Implicit conversion rules for data types in PL/SQL