Oracle PL/SQL之令人不解的提示（nls_date_format）

最新推荐文章于 2022-11-01 18:10:54 发布

最新推荐文章于 2022-11-01 18:10:54 发布 · 83 阅读

文章标签：

#数据库

本文通过一个具体的Oracle数据库程序错误案例，展示了如何排查由于nls_date_format设置不当导致的日期比较错误。通过调整日期格式，成功解决了表面上看似相同但实际带有不同时间部分的日期比较问题。

刚刚测试了一段程序，报错了，说是数据没有找到：

l_date1 != l_date2
l_date1=26-MAY-11, l_date2=26-MAY-11

……

查来查去原来是nls_date_format惹得祸。

Oracle在使用dbms_output.put_line或fnd_file.put_line等内置程序输出日期型参数时，会自动套用nls_date_format定义的日期格式，恰巧当前数据库中nls_date_format定义的日期格式为DD-MON-RR，没有时分秒，而参与比较的这两个日期却是带时分秒的，并且差异就在时分秒上：

DECLARE l_date1 DATE := to_date('2011/05/26', 'YYYY/MM/DD'); l_date2 DATE := to_date('2011/05/26 16:58:00', 'YYYY/MM/DD HH24:MI:SS'); l_val nls_session_parameters.VALUE%TYPE; BEGIN SELECT VALUE INTO l_val FROM nls_session_parameters WHERE parameter = upper('nls_date_format'); dbms_output.put_line('Original: nls_date_format=' || l_val); dbms_output.put_line('===test if two dates equal with confused info==='); EXECUTE IMMEDIATE 'ALTER SESSION SET nls_date_format = ''DD-MON-RR'''; IF (l_date1 != l_date2) THEN dbms_output.put_line('l_date1 != l_date2'); dbms_output.put_line('l_date1=' || l_date1 || ', l_date2=' || l_date2); END IF; dbms_output.put_line('===test if two dates equal with clear info==='); EXECUTE IMMEDIATE 'ALTER SESSION SET nls_date_format = ''DD-MON-YYYY HH24:MI:SS'''; IF (l_date1 != l_date2) THEN dbms_output.put_line('l_date1 != l_date2'); dbms_output.put_line('l_date1=' || l_date1 || ', l_date2=' || l_date2); END IF; --revert EXECUTE IMMEDIATE 'ALTER SESSION SET nls_date_format = ''DD-MON-RR'''; END;

输出：

Original: nls_date_format=DD-MON-RR ===test if two dates equal with confused info=== l_date1 != l_date2 l_date1=26-MAY-11, l_date2=26-MAY-11 ===test if two dates equal with clear info=== l_date1 != l_date2 l_date1=26-MAY-2011 00:00:00, l_date2=26-MAY-2011 16:58:00