用于插入和更新语句的隐式转换

Implicit data conversions (coercions) are performed for and 更新 statements for both assignments and expressions specified in statements. Data type conversion rules are applied to any expression (function call, operator, etc) used in an or an 更新 statement. For more information on data type conversion rules, see 数据类型转换.

严格的隐式转换规则适用于所有列类型;如果分配的值无法转换为目标列的类型而不会丢失信息,则返回错误。但是对于数字和字符串类型的列,不同地执行隐式转换。

数字类型

对于数字,分配的值隐式舍入到列的类型。例如:

SQL> CREATE TABLE Table1(n smallint);
SQL>  INTO Table values(3.4), ('3.6');
SQL> 选择 * FROM Table1;

N
--
3
4

如果舍入值不适合列类型,则返回溢出错误。例如:

SQL>  INTO Table1 values(40000);
Numeric overflow converting integer 40000 to smallint

SQL>  INTO Table1 values('40000');
Numeric overflow converting string 40000 to smallint

SQL> CREATE TABLE Table2(n numeric(3, 2));
SQL>  INTO Table2 values(1.446);
SQL> 选择 * FROM Table2;

 N
----
1.45

SQL>  INTO Table2 values(9.999);
Numeric Overflow: 10 does not fit in precision, scale (3, 2)

Char和varchar类型

char和varchar类型的指定长度表示类型支持的UTF-8字符数。例如:

SQL> CREATE TABLE Table1(c varchar(2));
SQL>  INTO Table1(c) values ('ëë');
SQL>  INTO Table1(c) values ('aa');
SQL> 选择 * FROM Table1;

 C
----
'ëë'
 aa

SQL>  INTO Table1 (c) values('aaa');
overflow converting "abcd" to varchar(2)
Length is represented by characters not bytes, for example ë has two bytes but only one character whereas a has both one byte and 1 character.

If the column type has a maximum length specified (that is, a char or a varchar type), an overflow error is returned when the value is greater than the number of characters specified by the column definition. For example:

SQL> CREATE TABLE Table1(c char(3));
SQL>  INTO Table1(c) values('abcd');
overflow converting "abcd" to char(3)