具有时间值的动态PL / SQL日期参数保留 [英] Dynamic PL/SQL date parameter with time value retained

问题描述

这可能是一个愚蠢的问题，但是我无法找到一个动态调用PL / SQL proc中传递的DATE类型的解决方案。我需要的是将日期和时间部分传递给所谓的proc：

It might be a silly problem but I cant find a solution with "DATE" type passed in a PL/SQL proc which is called dynamically. What I need is to pass both date and time parts in the called proc:

create or replace 
PROCEDURE DATE_TIME_TEST (  dte_Date_IN  IN DATE ) 
IS
    vch_SQL_Stmnt VARCHAR2(2000);
BEGIN
    DBMS_OUTPUT.PUT_LINE('Date is :'||TO_CHAR(dte_Date_IN, 'DD-Mon-YYYY HH24:MI:SS'));

END;
/

declare
    v_sql varchar2(2000);
begin
    v_sql := 'begin DATE_TIME_TEST( dte_Date_IN => '''|| 
              sysdate || ''''|| '); end;';
    execute immediate v_sql;
end; 
/

此处的输出是 - 日期是：2013年8月27日00:00 ：00。

The output here is - Date is :27-Aug-2013 00:00:00.

我希望它是 - 日期是：2013年8月27日13:01:09

I want it to be - Date is :27-Aug-2013 13:01:09

推荐答案

使用绑定变量

SQL> create or replace procedure proc( p_dt in date )
  2  as
  3  begin
  4    dbms_output.put_line( to_char( p_dt, 'yyyy-mm-dd hh24:mi:ss' ));
  5  end;
  6  /

Procedure created.

SQL> declare
  2    l_sql varchar2(1000);
  3  begin
  4    l_sql := 'begin proc(:dt); end;';
  5    execute immediate l_sql using sysdate;
  6  end;
  7  /
2013-08-26 22:14:26

PL/SQL procedure successfully completed.

您的代码的问题是，为了建立您的字符串，Oracle必须转换 DATE 到 VARCHAR2 。它使用你的会话的 NLS_DATE_FORMAT 。但是，您的会话的 NLS_DATE_FORMAT 可能不包括时间组件，因此在实际调用过程时，会丢失时间。使用绑定变量意味着你不必处理这种隐式转换（它也更有效率和更安全）。

The problem with your code is that in order to build up your string, Oracle has to convert the DATE to a VARCHAR2. It does that using your session's NLS_DATE_FORMAT. But your session's NLS_DATE_FORMAT probably doesn't include the time component so the time is lost when your procedure is actually called. Using bind variables means that you don't have to deal with that sort of implicit conversion (it is also more efficient and more secure).

如果你真的想避免使用绑定变量，您可以使用 to_char 将 sysdate 显式转换为字符串，然后将$ code > to_date 在动态过程调用中。但是这是很多额外的代码和一些不必要的转换。

If you really wanted to avoid using bind variables, you could explicitly cast sysdate to a string using a to_char and then put a to_date in the dynamic procedure call. But that's a lot of extra code and a number of unnecessary conversions.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_sql varchar2(1000);
  3  begin
  4    l_sql := q'{begin proc(to_date('}' ||
  5               to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') ||
  6               q'{', 'yyyy-mm-dd hh24:mi:ss')); end;}';
  7    execute immediate l_sql;
  8* end;
SQL> /
2013-08-26 22:19:52

PL/SQL procedure successfully completed.

这篇关于具有时间值的动态PL / SQL日期参数保留的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！