在准备好的语句中重用一个匿名参数 [英] Reusing an anonymous parameter in a prepared statement
问题描述
我正在自定义由hibernate生成的插入SQL,并且遇到了问题。当Hibernate自己生成查询时,它会将数据插入表的前两列,但这会导致数据库错误,因为该表的所有四列都是不可空的。为了正确执行插入操作,它必须将相同的数据插入到新记录的两列中。这意味着我需要Hibernate将相同的数据绑定到我正在编写的查询中的两个不同参数中。
引用绑定到准备好的语句的匿名参数,其顺序与绑定的顺序不同?详细信息
REF_USER_PAGE_XREF
---------------------------- ------------
PK FK1 | NETWORK_ID | VARCHAR2(100)
PK FK1 | PAGE_PATH | VARCHAR2(1000)
| USER_LAST_UPDT | VARCHAR2(100)
| TMSP_LAST_UPDT | DATE
插入
REF_USER_PAGE_XREF(
NETWORK_ID,
PAGE_PATH,
TMSP_LAST_UPDT,
USER_LAST_UPDT)
值($ b $在这里插入相同的数据* /
?,
?,/ *和这里* /
(选择
to_char(sysdate,'DD-MON -YY')
from
dual)
我想插入相同的数据转化为第一个和第三个匿名参数。
$ b
最终答案 (基于@mdma的答案)插入
REF_USER_PAGE_XREF(
NETWORK_ID,
PAGE_PATH,
USER_LAST_UPDT,
$ b
$ b TMSP_LAST_UPDT)
select
Param1,
Param2,
Param1,
(选择
to_char(sysdate,'DD-MON-YY')
from
dual)
from
(选择
?作为Param1,
?as Param 2
from
dual)params
您可以编写
INSERT INTO REF_USER_ROLE_XRE
SELECT参数1,参数2,参数1,
(选择to_char (sysdate,'DD-MON-YY')from dual)
FROM
(SELECT? AS参数1,? AS Param2)params
I am customizing the insert SQL generated by hibernate and have hit an issue. When Hibernate generates the query by itself, it inserts data into the first two columns of the table, but this causes a database error since all four columns of the table are non-nullable. For the insert to be performed properly, it must insert the same data into two columns of the new record. This means that I need Hibernate to bind the same data to two different parameters in the query (prepared statement) that I am writing.
Is there some SQL syntax that allows me to refer to anonymous parameters bound to a prepared statement in an order different from which they are bound?
Details
REF_USER_PAGE_XREF
----------------------------------------
PK FK1 | NETWORK_ID | VARCHAR2(100)
PK FK1 | PAGE_PATH | VARCHAR2(1000)
| USER_LAST_UPDT | VARCHAR2(100)
| TMSP_LAST_UPDT | DATE
insert into
REF_USER_PAGE_XREF(
NETWORK_ID,
PAGE_PATH,
TMSP_LAST_UPDT,
USER_LAST_UPDT)
values (
?, /* want to insert the same data here */
?,
?, /* and here */
(select
to_char(sysdate, 'DD-MON-YY')
from
dual)
I want to insert the same data into the first and third anonymous parameters.
Final Answer (based on answer from @mdma)
insert into
REF_USER_PAGE_XREF(
NETWORK_ID,
PAGE_PATH,
USER_LAST_UPDT,
TMSP_LAST_UPDT)
select
Param1,
Param2,
Param1,
(select
to_char(sysdate, 'DD-MON-YY')
from
dual)
from
(select
? as Param1,
? as Param2
from
dual) params
You can write
INSERT INTO REF_USER_ROLE_XRE
SELECT Param1, Param2, Param1,
(select to_char(sysdate, 'DD-MON-YY') from dual)
FROM
(SELECT ? AS Param1, ? AS Param2) params
这篇关于在准备好的语句中重用一个匿名参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!