Oracle PL/SQL之令人不解的提示（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