oracle 在oracle中创建一个返回当前日期和时间的函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2001799/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create a function to return current date and time in oracle
提问by Adnan
I am new to oracle, I have to create a function that returns current date and time.
我是 oracle 的新手，我必须创建一个返回当前日期和时间的函数。
I am using this to return date;
我用它来返回日期；
SELECT CURRENT_DATE FROM dual;
Thanx
谢谢
采纳答案by Doug Porter
As an actual function you could do:
作为实际功能，您可以执行以下操作：
create or replace function getSysdate
return date is
l_sysdate date;
begin
select sysdate
into l_sysdate
from dual;
return l_sysdate;
end;
/
Which you could test in SQLPlus as:
您可以在 SQLPlus 中测试为：
TEST>select getSysdate() from dual;
GETSYSDATE
----------
2010-01-04
Not sure why you would want this versus just having sysdatein your code. The Oracle date datatype includes the time portion.
不知道为什么你会想要这个而不是仅仅sysdate在你的代码中。Oracle 日期数据类型包括时间部分。
回答by APC
CURRENT_DATEreturns the date and time of the session. SYSDATEreturns the date and time of the database. These values may be different because we can change our session's timezone using ALTER SESSION. You probably ought to be using SYSDATEbecause it returns a consistent value, although without knowing your business context it is hard to be certain.
CURRENT_DATE返回会话的日期和时间。 SYSDATE返回数据库的日期和时间。这些值可能不同，因为我们可以使用ALTER SESSION. 您可能应该使用SYSDATE它，因为它返回一个一致的值，尽管在不了解您的业务背景的情况下很难确定。
From your question I suspect you don't realise that Oracle date pseudo-columns include a time element. Try this:
根据您的问题，我怀疑您没有意识到 Oracle 日期伪列包含时间元素。尝试这个：
alter session set nls_date_format='dd-mon-yyyy hh24Lmi:ss'
/
select current_date from dual
/
select sysdate from dual
/
There is not a lot of point in wrapping one of those pseudo-columns in your own user-defined function. The one time I seriously considered it was to make it easier to inject times into some automated unit tests. But I never convinced myself that this facility would justify not using the standard approach.
将这些伪列之一包装在您自己的用户定义函数中并没有多大意义。有一次我认真考虑它是为了更容易地将时间注入一些自动化单元测试。但我从来没有说服自己，这个设施可以证明不使用标准方法是合理的。
edit
编辑
The solution in the accepted answer works but has a lot of unnecessary baggage. All that additional PL/SQL runs 2-3 times slower than a straight select sysdate from dual;. It is true that these are very small differences in absolute terms - milliseconds, if that. But in a busy system with lots of calls to getSysdate()all those milliseconds could add up to a big chunk of time. A better solution would be to replace all that code with a plain return sysdate; This is slightly slower than calling sysdatedirectly but only by a little.
接受的答案中的解决方案有效，但有很多不必要的包袱。所有这些额外的 PL/SQL 运行速度比直接select sysdate from dual;. 确实，这些绝对值差异非常小 - 如果是毫秒，则是毫秒。但是在一个繁忙的系统中，有很多对getSysdate()所有这些毫秒的调用可能会占用大量时间。更好的解决方案是将所有代码替换为普通的return sysdate; 这比sysdate直接调用稍慢，但只慢了一点。
Expanding on dpbradley's comment, I have knocked up a function which allows us to substitute a different clocktime from the database, for the purposes of testing. I am storing my alternate datetime in the CLIENT_INFO namespace in the default context; if I were implementing this in a production system I would build a dedicated user defined context for it.
扩展 dpbradley 的评论，我提出了一个函数，它允许我们从数据库中替换不同的时钟时间，以进行测试。我将备用日期时间存储在默认上下文中的 CLIENT_INFO 命名空间中；如果我在生产系统中实现它，我会为它构建一个专用的用户定义上下文。
So here is my take on the getSysdate()function...
所以这是我对这个getSysdate()功能的看法......
SQL> create or replace function myGetSysdate
2 ( p_alt_date in varchar2 := null )
3 return date is
4 begin
5 if p_alt_date is null then
6 return sysdate;
7 else
8 return to_date(sys_context('userenv', p_alt_date)
9 , 'dd-mon-yyyy hh24:mi:ss');
10 end if;
11 end;
12 /
Function created.
SQL>
Here is how we set the alternate datetime...
这是我们如何设置备用日期时间...
SQL> exec dbms_application_info.set_client_info('01-DEC-2010 12:12:12')
PL/SQL procedure successfully completed.
If no parameter is passed it returns sysdate(the default and preferred option).
如果没有传递参数，则返回sysdate（默认和首选选项）。
SQL> select getsysdate from dual
2 /
GETSYSDATE
-----------------
05-JAN-2010 16:25
SQL>
If we pass a context namespace when we call the function we get the alternate datetime....
如果我们在调用函数时传递上下文命名空间，我们将获得备用日期时间....
SQL> select mygetsysdate('CLIENT_INFO') from dual
2 /
MYGETSYSDATE('CLI
-----------------
01-DEC-2010 12:12
SQL>
回答by davek
try:
尝试：
SELECT TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY HH:MI:SS') FROM dual;
if you need it in a certain format. More options here:
如果您需要某种格式的文件。更多选项在这里：
回答by sphinks
The most simple, I suppose:
最简单的，我想：
SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;
