数据类型从varchar2到日期、数字等的隐式转换

Question

假设我有一些列当前是 varchar2 值，在表中Table_A。

鉴于它们是日期的正确格式（通过带有to_date的更新语句格式化），并且我想将具有原始varchar2值的Table_A中的数据插入到具有相应Date列的Table_B中——如果日期格式正确，Oracle会隐式地从varchar2转换为Date吗？

同样，我想知道Oracle是否会将存储为Varchar2的十进制数字转换为目标表中具有相应正确格式的数字（N， M）。

我问这个问题的原因是想看看是否可以避免编写大型查询，这些查询必须在执行select之前先切换列类型，然后再插入到目标表中。

Answer 1

对于数字来说，答案更简单——只要小数分隔符在所有表和列中保持一致。如果一个表中的一列格式为< code>33.29，而另一个表中的一列格式为< code>33，29，因为它使用逗号作为小数点分隔符，那么就会出现问题。

对于日期，它会有点复杂-特别是如果一些日期像＜code＞的“12/31/2020”，而另一些日期像是＜code>的“12/31/2020 00:00”。

如果所有数字都采用完全相同的格式，如果所有日期都采用完全相同的格式，则应该有效；对于日期，您需要确保nls_date_format与您的字符串格式相同（或使用改变会话更改它），对于数字，您必须确保nls_numeric_characters显示与您的数字字符串相同的十进制分隔符（如果使用，则使用相同的分组分隔符）。

我可能会为“播放”数据库（例如，用于测试）或在您不能造成太大损害的环境中执行您建议的事情。我不会在生产中这样做，如果非平凡的事情取决于数据库和其中的数据。

Answer 2

你可以做到这一点，这一切都是为了NLS_DATE_FORMAT。但存在风险：

create table a(
  pk integer not null,
  dt varchar2(30)
);
create table b(
  pk integer not null,
  dt date
);
--
-- This example shows date truncation because of the nls_date_format
--
alter session set nls_date_format='DD-MON-YYYY';
insert into b values(1, sysdate);
select to_char(dt, 'YYYY-MM-DD HH24:MI:SS') dt from b;
-- 2021-02-02 11:53:05
insert into a select * from b;
select * from a;
-- 02-FEB-2021
delete b;
insert into b select * from a;
select to_char(dt, 'YYYY-MM-DD HH24:MI:SS') dt from b;
-- 2021-02-02 00:00:00

--
-- This example shows a working conversion
---
alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
delete a;
delete b;
insert into b values(1, sysdate);
select to_char(dt, 'YYYY-MM-DD HH24:MI:SS') dt from b;
-- 2021-02-02 11:53:05
insert into a select * from b;
select * from a;
-- 2021-02-02 11:53:05
delete b;
insert into b select * from a;
select to_char(dt, 'YYYY-MM-DD HH24:MI:SS') dt from b;
-- 2021-02-02 11:53:05

--
-- This example shows a failure because of bad date mask.
--
alter session set nls_date_format='YYYY-MM-DD';
delete a;
delete b;
insert into b values(1, sysdate);
select to_char(dt, 'YYYY-MM-DD HH24:MI:SS') dt from b;
-- 2021-02-02 11:53:05
insert into a select * from b;
select * from a;
-- 2021-02-02
alter session set nls_date_format='Dy DD/MM HH24:MI:SS';
delete b;
insert into b select * from a;
-- ORA-01846: not a valid day of the week

总之，除非绝对必要，否则不要做。如果必须这样做，请确保在执行SQL之前，通过在脚本中设置它来控制< code>nls_date_mask。