oracle - 为什么 CAST 截断文本而不是抛出错误？

Question

将文本转换到 小于值 的长度导致文本被截断。

select
    cast('ROAD-1234' as varchar2(8)) as id
from
    dual

ID      
--------
ROAD-123     
     --^ Notice that the number 4 has been removed

我本来以为 CAST AS VARCHAR2行为类似于 CAST AS NUMBER .

select
    cast(1234 as number(3)) as id
from
    dual

Error: ORA-01438: value larger than specified precision allowed for this column

为什么 CAST AS VARCHAR2 会默默地截断文本，而不是像 CAST AS NUMBER 那样抛出错误？

Answer 1

这是 ANSI SQL 标准的一部分，其他符合 ANSI SQL 的 RDBMS 也这样做:

For fixed length character string targets, if the length of the source equals the fixed length of the target, the result of the CAST is the source string. If the length of the source is shorter than the fixed length of the target, the result of the CAST is the source string padded on the right with however many spaces are required to make the lengths match. If the length of the source is longer than the fixed length of the target, the result of the CAST is a character string that contains as much of the source string as possible – in this case, if the truncated characters are not all spaces, your DBMS will return the SQLSTATE warning 01004 "warning-string data, right truncation".

不幸的是，数据库通常无法同时发出警告和返回结果，因此它们会默默地截断结果