oracle变量不能用作查询,标记变量并在查询oracle中使用它们

variable

不支持日期或时间戳。最后的查询也应该使用

:my_date

my_date

如果目标是使用在上一步中填充的变量来填充表,那么可以使用PL/SQL来完成整个任务,例如。

declare

myDate date;

begin

select some_col into myDate from wherever;

insert into target_table (col1, col2, col2)

select x, y, z

from source_table

where business_date = myDate;

end;

declare

my_date timestamp;

results sys_refcursor;

begin

select systimestamp into my_date

from dual;

open results for

select my_date as my_date from dual;

dbms_sql.return_result(results);

end;

/

PL/SQL procedure successfully completed.

ResultSet #1

MY_DATE

---------------------------------------------------------------------------

20-JUN-20 10.02.29.130000000

1 row selected.

对于早期的Oracle版本,您仍然需要在SQL*Plus中将ref cursor声明为主机变量:

set autoprint on

var results refcursor

declare

my_date timestamp;

begin

select systimestamp into my_date

from dual;

open :results for

select my_date as my_date from dual;

end;

/

(或

set autoprint off

然后

print results

明确地说。)

但是我不懂Java,所以我不知道上面的任何一种方法在您的环境中是否有效。