在Oracle 11g中的哪里更改NLS_DATE_FORMAT? [英] Where to change the NLS_DATE_FORMAT in oracle 11g?

问题描述

我需要在存储过程中更改NLS_DATE_FORMAT.好像它是一个会话变量，我应该在哪里更改变量掩码.

i need to change the NLS_DATE_FORMAT in my Stored procedure.seems like its a session variable and where should i alter the variable mask.

应该在全局声明中还是在其他地方(我的意思是从前端开始)完成.

should it be done in global declaration or elsewhere(i mean from frontend).

推荐答案

如果要在过程中更改NLS参数，则可以使用

If you want to change a NLS parameter in a procedure then you can use DBMS_SESSION.SET_NLS. Given an environment that looks like this:

SQL> select value
  2    from nls_session_parameters
  3   where parameter = 'NLS_DATE_FORMAT';

VALUE
------------------------------------------

DD-MON-RR

SQL> select sysdate from dual;

SYSDATE
---------
25-NOV-14

执行该过程会更改格式(请注意三重单引号):

executing the procedure changes the format (note the triple single quotes):

SQL> begin
  2     dbms_session.set_nls('nls_date_format', '''yyyy-mm-dd''');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select sysdate from dual;

SYSDATE
----------
2014-11-25

或者，您也可以使用动态SQL， ，例如:

Alternatively you can use dynamic SQL, EXECUTE IMMEDIATE, for instance:

SQL> begin
  2     execute immediate 'alter session set nls_date_format = ''yyyy-mm''';
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select sysdate from dual;

SYSDATE
-------
2014-11

但是，如 TO_DATE()

However, as Justin Cave notes this is highly unusual. If you're converting between datatypes you should always do this explicitly with an explicit format. In the case of dates the correct function to use is TO_DATE()

这篇关于在Oracle 11g中的哪里更改NLS_DATE_FORMAT?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！