是否从Oracle函数返回数据？

Question

您好，我在Oracle中创建了以下函数。我必须将date-1和date-2参数传递给函数，函数应该向我返回另一个日期。

请看下面的代码。

create or replace function GETD(p_d1   in date, 
                                p_d2   in date ) return DATE 
   as 
       l_result    DATE; 
   begin 
    SELECT EDIT_Date into l_result FROM qa.employees  WHERE qa.employee_join_date BETWEEN TO_DATE(p_d1, 'MM/DD/YYYY') AND TO_DATE(p_d2, 'MM/DD/YYYY') AND ROWNUM <= 1
    ;
       return l_result; 
  end; 

我按如下所示执行函数

SELECT GETD('27-JUN-12','28-JUN-12') FROM DUAL

函数被编译，当im传递参数来执行函数时，我得到以下错误：“不是一个有效的月份”。有没有人能告诉我哪里出错了？

谢谢，贾斯汀

Answer 1

首先，您不应该在日期变量上调用TO_DATE。如果这样做，则强制Oracle首先使用会话的NLS_DATE_FORMAT将日期转换为字符串，然后使用指定的格式掩码将字符串转换回日期。如果您的NLS_DATE_FORMAT碰巧与指定的格式掩码不匹配(而且，由于NLS_DATE_FORMAT由客户端控制，一些用户和会话将不可避免地具有不同的日期格式)，您将收到一个错误。

假设您的意图仅仅是忽略任何时间部分，那么您应该使用trunc函数。

create or replace function GETD(p_d1   in date, 
                                p_d2   in date ) 
  return DATE 
as 
  l_result    DATE; 
begin 
  SELECT EDIT_Date 
    into l_result 
    FROM qa.employees  
   WHERE qa.employee_join_date BETWEEN trunc(p_d1) AND trunc(p_d2) 
     AND ROWNUM <= 1;

  return l_result; 
end; 

rownum <= 1条件的存在似乎有点奇怪--您似乎不太可能真的想从表中提取匹配employee_join_date条件的任意行。在不知道需求的情况下，您可能想要做一些确定性的操作，以获取具有最小(或最大) edit_date的行。

其次，在调用函数时，应该传入DATE参数，而不是传入字符串，然后让Oracle使用会话的NLS_DATE_FORMAT将字符串隐式地转换为日期。如果您使用日期文字

SELECT GETD(date '2012-06-27', date '2012-06-28') 
  FROM DUAL

或者使用TO_DATE显式地将字符串转换为日期

SELECT GETD(to_date('27-JUN-12', 'DD-MON-RR'), to_date('28-JUN-12', 'DD-MON-RR')) 
  FROM DUAL

那么，无论客户端的NLS_DATE_FORMAT如何，您的调用都将正常工作。