Oracle Implicit type Conversion

The options for our system's selection box are stored in a database table, which roughly contains the ID of the selection box and the data for selecting options, roughly the following table. Where FieldID is of type int , Optionval is int , Optionname is varchar .

FieldID	Optionval	Optionname
1	0	Beijing
1	1	Nanjing
1	2	Tianjin
1	3	Hangzhou
2	0	Ramen
2	1	Rice

Today, when it comes to the requirements, we don't pay attention to the type of the corresponding field, and the SQL is spliced directly through Java code, roughly as follows.

new StringBuilder();sqlBuilder.append("UPDATE tablename SET")    .append(optionFieldid)    .append(" = ‘")    .append(optionValue);    .append("‘");

OptionValue string is the ‘‘ value that he stored in the database becomes 0, originally thought should turn this field into ‘‘ , then I noticed that this field is type int, an implicit type conversion occurred. This summarizes the implicit type conversion of the relevant database, and in the following development process, you need to pay more attention to the type of database, to avoid unexpected results.

Oracle

Oracle follows the following data type conversion rules:

• For insert and update operations, Oracle converts the value to the type of the affected column.
• For SELECT FROM Operations, Oracle converts the type of the column's value to the type of the target variable.
• When manipulating the value of a numeric type, Oracle usually adjusts the precision to accommodate the maximum capacity. In the case of a number type, the results of the operation of the numeric types may differ from the previous table.

• When comparing string types to numeric types, Oracle converts string types to numeric types.

  selectsysdatefromwhere1.2‘1.2‘; //正确selectsysdatefromwhere1.2‘1.2a‘;//ORA-01722: 无效数字

• Comparisons using a string or NUMBER type and floating-point type are imprecise. Because strings and NUMBER types use decimal precision, and floating-point numbers use binary precision.
• When converting data of the CLOB type to VARCHAR2 such a type, or BLOB converting to RAW data, if the converted data is larger than the target type, an error will be made.
• When you timestamp convert a type to a date type, the fractional seconds part is staged.
• From the BINARY_FLOAT conversion to BINARY_DOUBLE be accurate.
• BINARY_DOUBLEwhen converting to a BINARY_FLOAT type, the BINARY_ DOUBLE BINARY_FLOAT conversion is inaccurate if more than the supported precision is used.

• When a string value is compared to a date value, the string value is converted to a date type. For this scenario we should not rely on implicit conversions, which should be used TO_DATE for display conversions, and the conversion format followed by implicit conversions is as follows:

  selectfrom nls_session_parameters  WHERE parameter=‘NLS_DATE_FORMAT‘; //DD-MON-RR

  So in the Chinese environment, use the following, the use of other formats will be error ORA-01861: 文字与格式字符串不匹配 .

  select 1 + 1 from dual where to_date(‘2018-1-15 12:23:23‘,‘yyyy-mm-dd hh24:mi:ss‘) > ‘15-1月 -18‘;

• When you use SQL or a function, the passed parameter is not the type that the parameter accepts, then it converts this argument to the type accepted by the function.

• When a string and a non-string type are compared or arithmetic operations, the string is converted to the type of the column data. When both sides of the arithmetic operation are CHAR/VARCHAR2 and NCHAR/NVARCHAR2 , Oracle converts them to NUMBER .

  select‘123‘‘123.2‘from dual; //246.2

Oracle Implicit type Conversion