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，没有时分秒，而参与比较的这两个日期却是带时分秒的，并且差异就在时分秒上：

  1. DECLARE  
  2.   l_date1 DATE := to_date('2011/05/26', 'YYYY/MM/DD');  
  3.   l_date2 DATE := to_date('2011/05/26 16:58:00', 'YYYY/MM/DD HH24:MI:SS');  
  4.   l_val   nls_session_parameters.VALUE%TYPE;  
  5. BEGIN  
  6.   SELECT VALUE  
  7.   INTO   l_val  
  8.   FROM   nls_session_parameters  
  9.   WHERE  parameter = upper('nls_date_format');  
  10.   dbms_output.put_line('Original: nls_date_format=' || l_val);  
  11.   
  12.   dbms_output.put_line('===test if two dates equal with confused info===');  
  13.   EXECUTE IMMEDIATE 'ALTER SESSION SET nls_date_format = ''DD-MON-RR''';  
  14.   IF (l_date1 != l_date2)  
  15.   THEN  
  16.     dbms_output.put_line('l_date1 != l_date2');  
  17.     dbms_output.put_line('l_date1=' || l_date1 || ', l_date2=' || l_date2);  
  18.   END IF;  
  19.   
  20.   dbms_output.put_line('===test if two dates equal with clear info===');  
  21.   EXECUTE IMMEDIATE 'ALTER SESSION SET nls_date_format = ''DD-MON-YYYY HH24:MI:SS''';  
  22.   IF (l_date1 != l_date2)  
  23.   THEN  
  24.     dbms_output.put_line('l_date1 != l_date2');  
  25.     dbms_output.put_line('l_date1=' || l_date1 || ', l_date2=' || l_date2);  
  26.   END IF;  
  27.   
  28.   --revert  
  29.   EXECUTE IMMEDIATE 'ALTER SESSION SET nls_date_format = ''DD-MON-RR''';  
  30. END;  

输出：

  1. Original: nls_date_format=DD-MON-RR  
  2. ===test if two dates equal with confused info===  
  3. l_date1 != l_date2  
  4. l_date1=26-MAY-11, l_date2=26-MAY-11  
  5. ===test if two dates equal with clear info===  
  6. l_date1 != l_date2  
  7. l_date1=26-MAY-2011 00:00:00, l_date2=26-MAY-2011 16:58:00  

相关内容