英文:
Oracle PLSQL long run issue
问题
有以下代码长期问题，请求协助纠正。提前致以感谢。
Declare
l_status varchar2(10);
Begin
Insert into @table_name (ENTRP_CUST_ID_NUM, CUST_SROGT_ID, TRD_REF_NUM, CNTRY_CD)
Select ENTRP_CUST_ID_NUM, CUST_SROGT_ID, TRD_REF_NUM, CNTRY_CD
from {{sourceTableLatest('Aggregation[table]')}} partition ({{getSourcePartitionNameLatest('Aggregation[table]')}});
Commit;
For i in ( Select mfu_source.Customer_Surrogate_ID CUST_SROGT_ID, Agg.TRD_REF_NUM TRD_REF_NUM, mfu_source.Enterprise_Customer_ID ENTRP_CUST_ID_NUM
from {{sourceTableLatest('Aggregation[table]')}} partition ({{getSourcePartitionNameLatest('Aggregation[table]')}}) AGG,
{{sourceTableLatest('mfu_table')}} partition ({{getSourcePartitionNameLatest('mfu_table')}}) mfu_source
Where (mfu_source.TA_LINK_ID=AGG.TRD_REF_NUM or mfu_source.TRANSACTION_ID=AGG.TRD_REF_NUM)
and trim(AGG.ENTRP_CUST_ID_NUM) is null)
Loop
Update @table_name set ENTRP_CUST_ID_NUM= i.ENTRP_CUST_ID_NUM where TRD_REF_NUM=i.TRD_REF_NUM;
Commit;
End Loop;
END;
上述代码存在长期问题，需要简单和容易地进行修正。
<details>
<summary>英文:</summary>
there is long run issue with below code kindly help to rectify this issue. Thanks in advance.
Declare
l_status varchar2(10);
Begin
Insert into @table_name (ENTRP_CUST_ID_NUM, CUST_SROGT_ID, TRD_REF_NUM, CNTRY_CD)
Select ENTRP_CUST_ID_NUM, CUST_SROGT_ID, TRD_REF_NUM, CNTRY_CD
from {{sourceTableLatest('Aggregation[table]')}} partition ({{getSourcePartitionNameLatest('Aggregation[table]')}});
Commit;
For i in ( Select mfu_source.Customer_Surrogate_ID CUST_SROGT_ID, Agg.TRD_REF_NUM TRD_REF_NUM, mfu_source.Enterprise_Customer_ID ENTRP_CUST_ID_NUM
from {{sourceTableLatest('Aggregation[table]')}} partition ({{getSourcePartitionNameLatest('Aggregation[table]')}}) AGG,
{{sourceTableLatest('mfu_table')}} partition ({{getSourcePartitionNameLatest('mfu_table')}}) mfu_source
Where (mfu_source.TA_LINK_ID=AGG.TRD_REF_NUM or mfu_source.TRANSACTION_ID=AGG.TRD_REF_NUM)
and trim(AGG.ENTRP_CUST_ID_NUM) is null)
Loop
Update @table_name set ENTRP_CUST_ID_NUM= i.ENTRP_CUST_ID_NUM where TRD_REF_NUM=i.TRD_REF_NUM;
Commit;
End Loop;
END;
the above code take long run issue need to rectify the issue with simple and easy.
</details>
# 答案1
**得分**: 2
你不需要使用循环，可以使用单个UPDATE语句来运行它。除此之外，你应该切换到现代（已经存在超过20年）的ANSI连接语法。
```sql
UPDATE @table_name SET ENTRP_CUST_ID_NUM = (
SELECT mfu_source.Enterprise_Customer_ID
FROM {{sourceTableLatest('Aggregation[table]')}} PARTITION ({{getSourcePartitionNameLatest('Aggregation[table]')}}) AGG
JOIN {{sourceTableLatest('mfu_table')}} PARTITION ({{getSourcePartitionNameLatest('mfu_table')}}) mfu_source ON mfu_source.TA_LINK_ID = AGG.TRD_REF_NUM OR mfu_source.TRANSACTION_ID = AGG.TRD_REF_NUM
WHERE TRIM(AGG.ENTRP_CUST_ID_NUM) IS NULL
AND TRD_REF_NUM = AGG.TRD_REF_NUM)
WHERE EXISTS
(SELECT 'x'
FROM {{sourceTableLatest('Aggregation[table]')}} PARTITION ({{getSourcePartitionNameLatest('Aggregation[table]')}}) AGG
JOIN {{sourceTableLatest('mfu_table')}} PARTITION ({{getSourcePartitionNameLatest('mfu_table')}}) mfu_source ON mfu_source.TA_LINK_ID = AGG.TRD_REF_NUM OR mfu_source.TRANSACTION_ID = AGG.TRD_REF_NUM
WHERE TRIM(AGG.ENTRP_CUST_ID_NUM) IS NULL
AND TRD_REF_NUM = AGG.TRD_REF_NUM)
英文:
You don't need a loop, you can run it with a single UPDATE statement. Apart from that, you should switch to modern (well, it exists for more than 20 years) ANSI join syntax.
UPDATE @table_name SET ENTRP_CUST_ID_NUM = (
SELECT mfu_source.Enterprise_Customer_ID
FROM {{sourceTableLatest('Aggregation[table]')}} PARTITION ({{getSourcePartitionNameLatest('Aggregation[table]')}}) AGG
JOIN {{sourceTableLatest('mfu_table')}} PARTITION ({{getSourcePartitionNameLatest('mfu_table')}}) mfu_source ON mfu_source.TA_LINK_ID = AGG.TRD_REF_NUM OR mfu_source.TRANSACTION_ID = AGG.TRD_REF_NUM
WHERE TRIM(AGG.ENTRP_CUST_ID_NUM) IS NULL
AND TRD_REF_NUM = Agg.TRD_REF_NUM)
WHERE EXISTS
(SELECT 'x'
FROM {{sourceTableLatest('Aggregation[table]')}} PARTITION ({{getSourcePartitionNameLatest('Aggregation[table]')}}) AGG
JOIN {{sourceTableLatest('mfu_table')}} PARTITION ({{getSourcePartitionNameLatest('mfu_table')}}) mfu_source ON mfu_source.TA_LINK_ID = AGG.TRD_REF_NUM OR mfu_source.TRANSACTION_ID = AGG.TRD_REF_NUM
WHERE TRIM(AGG.ENTRP_CUST_ID_NUM) IS NULL
AND TRD_REF_NUM = Agg.TRD_REF_NUM)
答案2
得分: 1
你可以使用单个 MERGE 语句（而不是循环中的多个语句）：
申明
l_status varchar2(10);
开头
插入到 @table_name (ENTRP_CUST_ID_NUM, CUST_SROGT_ID, TRD_REF_NUM, CNTRY_CD)
选择 ENTRP_CUST_ID_NUM, CUST_SROGT_ID, TRD_REF_NUM, CNTRY_CD
从 {{sourceTableLatest('Aggregation[table]')}} 分区 ({{getSourcePartitionNameLatest('Aggregation[table]')}});
合并到 @table_name dst
使用 (
选择 mfu_source.Customer_Surrogate_ID CUST_SROGT_ID,
Agg.TRD_REF_NUM TRD_REF_NUM,
mfu_source.Enterprise_Customer_ID ENTRP_CUST_ID_NUM
从 {{sourceTableLatest('Aggregation[table]')}} 分区 ({{getSourcePartitionNameLatest('Aggregation[table]')}}) AGG
内连接 {{sourceTableLatest('mfu_table')}} 分区 ({{getSourcePartitionNameLatest('mfu_table')}}) mfu_source
在 mfu_source.TA_LINK_ID = AGG.TRD_REF_NUM
或 mfu_source.TRANSACTION_ID = AGG.TRD_REF_NUM
其中 去除空格(AGG.ENTRP_CUST_ID_NUM) 是 空
) src
在 dst.TRD_REF_NUM = src.TRD_REF_NUM
当 匹配时 然后
更新
设置 dst.ENTRP_CUST_ID_NUM = src.ENTRP_CUST_ID_NUM;
提交;
结尾;
/
英文:
You can use a single MERGE statement (rather than multiple statements in a loop):
Declare
l_status varchar2(10);
Begin
Insert into @table_name (ENTRP_CUST_ID_NUM, CUST_SROGT_ID, TRD_REF_NUM, CNTRY_CD)
Select ENTRP_CUST_ID_NUM, CUST_SROGT_ID, TRD_REF_NUM, CNTRY_CD
from {{sourceTableLatest('Aggregation[table]')}} partition ({{getSourcePartitionNameLatest('Aggregation[table]')}});
MERGE INTO @table_name dst
USING (
Select mfu_source.Customer_Surrogate_ID CUST_SROGT_ID,
Agg.TRD_REF_NUM TRD_REF_NUM,
mfu_source.Enterprise_Customer_ID ENTRP_CUST_ID_NUM
from {{sourceTableLatest('Aggregation[table]')}} partition ({{getSourcePartitionNameLatest('Aggregation[table]')}}) AGG
INNER JOIN {{sourceTableLatest('mfu_table')}} partition ({{getSourcePartitionNameLatest('mfu_table')}}) mfu_source
ON mfu_source.TA_LINK_ID = AGG.TRD_REF_NUM
or mfu_source.TRANSACTION_ID = AGG.TRD_REF_NUM
Where trim(AGG.ENTRP_CUST_ID_NUM) is null
) src
ON dst.TRD_REF_NUM = src.TRD_REF_NUM
WHEN MATCHED THEN
UPDATE
SET dst.ENTRP_CUST_ID_NUM = src.ENTRP_CUST_ID_NUM;
Commit;
END;
/
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论