理解存储过程逻辑。

huangapple go评论61阅读模式
英文:

Understanding stored procedure logic

问题

我对PL/SQL不太熟悉,但我可以帮助你理解下面的存储过程。我看到数据中存在一些情况,根据下面的代码数据没有被更新,我想了解可能发生这种情况的原因。

这个存储过程包被另一个程序调用,看起来是根据下面的代码来更新 PARTY_ORIGINAL_SYSTEM_REFLOCATION_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) :=&#39;INT018&#39;;
	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 = &#39;NEW&#39;
				   ) 
    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,&#39;1&#39;) = NVL(rec_pmt.ADDRESS_LINE_2,&#39;1&#39;)
		 AND CITY = rec_pmt.CITY
		 AND STATE = rec_pmt.STATE
		 AND POSTAL_CODE = rec_pmt.POSTAL_CODE
		 AND NVL(POSTAL_PLUS_4_CODE,&#39;1&#39;) = NVL(rec_pmt.POSTAL_PLUS_4_CODE,&#39;1&#39;)
		 AND INSTANCE_ID = p_instance_id
		 AND METADATA_ID = p_metadata_id
		 AND SOURCE_FILE_NAME = p_sourcefile
		 AND INTF_STATUS = &#39;NEW&#39; ;
		 COMMIT;
	END LOOP;
END TRANSFORM_PMT_REQ_DATA

答案1

得分: 1

有两种可能性会阻止行被更新:

  1. 传递给过程的参数导致游标的select语句未返回任何行,因此根本不执行update
  2. 如果游标返回了某些内容,那么是updatewhere子句阻止了任何行被更新。

你说过你

找到了一些示例,即使使用下面的'INT018 <l_seq_id>',数据似乎符合标准,但没有更新

由于我们没有您的表格或数据,看不到传递给过程的参数... 没有什么。因此,您应该进行调试,并确保标准确实得到满足。

英文:

There are two possibilities that prevent row(s) to be updated:

  1. parameters passed to procedure caused cursor's select statement NOT to return any rows, so update isn't executed at all
  2. if cursor returned something, then it is update's where 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.

huangapple
  • 本文由 发表于 2023年7月11日 04:38:49
  • 转载请务必保留本文链接:https://go.coder-hub.com/76657188.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定