英文:
Understanding stored procedure logic
问题
我对PL/SQL不太熟悉,但我可以帮助你理解下面的存储过程。我看到数据中存在一些情况,根据下面的代码数据没有被更新,我想了解可能发生这种情况的原因。
这个存储过程包被另一个程序调用,看起来是根据下面的代码来更新 PARTY_ORIGINAL_SYSTEM_REF
和 LOCATION_ORIGINAL_SYSTEM_REF
,但是我找到了一些示例,即使看似符合条件,数据也没有使用下面的 'INT018<l_seq_id>' 进行更新。
我的另一个问题是,对象 rec_pmt
是否引用与其下面的UPDATE语句相同的查询?
存储过程如下:
PROCEDURE TRANSFORM_PMT_REQ_DATA (p_instance_id IN NUMBER,
p_metadata_id IN NUMBER,
p_sourcefile IN VARCHAR2,
p_status OUT VARCHAR2,
p_err_msg OUT VARCHAR2
)
AS
l_seq_id NUMBER;
l_intg_code VARCHAR2(240) := 'INT018';
l_counter NUMBER := 0;
BEGIN
FOR rec_pmt IN (SELECT DISTINCT PARTY_NAME,ADDRESS_LINE_1,ADDRESS_LINE_2,
CITY,STATE,POSTAL_CODE,POSTAL_PLUS_4_CODE
FROM TGC_INT018_PMT_REQ_FBDI
WHERE INSTANCE_ID = p_instance_id
AND METADATA_ID = p_metadata_id
AND SOURCE_FILE_NAME = p_sourcefile
AND INTF_STATUS = 'NEW'
)
LOOP
SELECT TGC_INT018_PMT_REQ_SEQ.NEXTVAL INTO l_seq_id FROM DUAL;
UPDATE TGC_INT018_PMT_REQ_FBDI
SET PARTY_ORIGINAL_SYSTEM_REF = l_intg_code||l_seq_id
,LOCATION_ORIGINAL_SYSTEM_REF = l_intg_code||l_seq_id
WHERE PARTY_NAME = rec_pmt.PARTY_NAME
AND ADDRESS_LINE_1 = rec_pmt.ADDRESS_LINE_1
AND NVL(ADDRESS_LINE_2,'1') = NVL(rec_pmt.ADDRESS_LINE_2,'1')
AND CITY = rec_pmt.CITY
AND STATE = rec_pmt.STATE
AND POSTAL_CODE = rec_pmt.POSTAL_CODE
AND NVL(POSTAL_PLUS_4_CODE,'1') = NVL(rec_pmt.POSTAL_PLUS_4_CODE,'1')
AND INSTANCE_ID = p_instance_id
AND METADATA_ID = p_metadata_id
AND SOURCE_FILE_NAME = p_sourcefile
AND INTF_STATUS = 'NEW';
COMMIT;
END LOOP;
END TRANSFORM_PMT_REQ_DATA;
希望这可以帮助你理解这个存储过程的功能。
英文:
I am not as familiar with PL/SQL as I am with standard SQL and need a little help understanding the below procedure, as I am seeing in the data cases where the data is NOT being updated based on the below, and I'd like to understand why this might be happening.
This procedure package gets called from another program and looks to be updating PARTY_ORIGINAL_SYSTEM_REF
and LOCATION_ORIGINAL_SYSTEM_REF
per the below, however I have found examples where the data is not updated with the below 'INT018<l_seq_id>' even though it seems it meets the criteria.
My other question is where the object rec_pmt
is referring to the same query as the UPDATE statement below it?
PROCEDURE TRANSFORM_PMT_REQ_DATA (p_instance_id IN NUMBER,
p_metadata_id IN NUMBER,
p_sourcefile IN VARCHAR2,
p_status OUT VARCHAR2,
p_err_msg OUT VARCHAR2
)
AS
l_seq_id NUMBER;
l_intg_code VARCHAR2(240) :='INT018';
l_counter NUMBER:=0;
BEGIN
FOR rec_pmt IN (SELECT DISTINCT PARTY_NAME,ADDRESS_LINE_1,ADDRESS_LINE_2,
CITY,STATE,POSTAL_CODE,POSTAL_PLUS_4_CODE
FROM TGC_INT018_PMT_REQ_FBDI
WHERE INSTANCE_ID = p_instance_id
AND METADATA_ID = p_metadata_id
AND SOURCE_FILE_NAME = p_sourcefile
AND INTF_STATUS = 'NEW'
)
LOOP
SELECT TGC_INT018_PMT_REQ_SEQ.NEXTVAL INTO l_seq_id FROM DUAL;
UPDATE TGC_INT018_PMT_REQ_FBDI
SET PARTY_ORIGINAL_SYSTEM_REF = l_intg_code||l_seq_id
,LOCATION_ORIGINAL_SYSTEM_REF = l_intg_code||l_seq_id
WHERE PARTY_NAME = rec_pmt.PARTY_NAME
AND ADDRESS_LINE_1 = rec_pmt.ADDRESS_LINE_1
AND NVL(ADDRESS_LINE_2,'1') = NVL(rec_pmt.ADDRESS_LINE_2,'1')
AND CITY = rec_pmt.CITY
AND STATE = rec_pmt.STATE
AND POSTAL_CODE = rec_pmt.POSTAL_CODE
AND NVL(POSTAL_PLUS_4_CODE,'1') = NVL(rec_pmt.POSTAL_PLUS_4_CODE,'1')
AND INSTANCE_ID = p_instance_id
AND METADATA_ID = p_metadata_id
AND SOURCE_FILE_NAME = p_sourcefile
AND INTF_STATUS = 'NEW' ;
COMMIT;
END LOOP;
END TRANSFORM_PMT_REQ_DATA
答案1
得分: 1
有两种可能性会阻止行被更新:
- 传递给过程的参数导致游标的
select
语句未返回任何行,因此根本不执行update
。 - 如果游标返回了某些内容,那么是
update
的where
子句阻止了任何行被更新。
你说过你
找到了一些示例,即使使用下面的'INT018 <l_seq_id>',数据似乎符合标准,但没有更新
由于我们没有您的表格或数据,看不到传递给过程的参数... 没有什么。因此,您应该进行调试,并确保标准确实得到满足。
英文:
There are two possibilities that prevent row(s) to be updated:
- parameters passed to procedure caused cursor's
select
statement NOT to return any rows, soupdate
isn't executed at all - if cursor returned something, then it is
update
'swhere
clause that prevented any row(s) to be updated
You said that you
> have found examples where the data is not updated with the below 'INT018<l_seq_id>' even though it seems it meets the criteria
We can't help much as we don't have your table nor data, don't see parameters being passed to the procedure ... nothing. Therefore, you should debug it and make sure that criteria is really met.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论