我正在使用 Perl DBI 和 DB2。
當我運行此代碼時：
sub MergePolygonNameTable()
{
my $table = "THESCHEMA.NAME";
print "Merging into ${table} table. ", scalar localtime, "\n";
eval
{
$DbHandle->do("
declare global temporary table session.TEMP_NAME
(POLICY_MASTER_ID INT
)
;
");
$DbHandle->do("
CREATE UNIQUE INDEX session.TEMP_NAME_IDX1 ON session.TEMP_NAME
(POLICY_MASTER_ID ASC
)");
$DbHandle->do("
insert into session.TEMP_NAME
(POLICY_MASTER_ID
)
SELECT pm.ID as POLICY_MASTER_ID
FROM THESCHEMA.POLICY_MASTER pm
");
$DbHandle->do("
MERGE INTO THESCHEMA.NAME as t
USING session.TEMP_NAME as s
ON t.POLICY_MASTER_ID = s.POLICY_MASTER_ID
WHEN MATCHED
) THEN
UPDATE SET t.UPDATED_DATETIME = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
INSERT
(POLICY_MASTER_ID
) VALUES
(s.POLICY_MASTER_ID
)
;
");
};
if ($@)
{
print STDERR "ERROR: $ExeName: Cannot merge into ${table} table.\n$@\n";
ExitProc(1);
}
}
問題是 THESCHEMA.NAME 在運行后為空。
我懷疑 DBI 在 do() 之后沒有保留臨時表的內容。但是 DBI 不允許我在 do() 中放置多個陳述句。
如何讓臨時表在 DBI 中作業？
uj5u.com熱心網友回復：
這ON COMMIT DELETE ROWS是DECLARE GLOBAL TEMPORARY TABLE 陳述句的默認選項。
使用ON COMMIT PRESERVE ROWS選項在顯式或隱式時保留行COMMIT。
像這樣：
declare global temporary table session.TEMP_NAME
(POLICY_MASTER_ID INT
)
ON COMMIT PRESERVE ROWS
;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/488113.html