ORACLE) update query Why doesn't it work?

Question

this is procedure in oracle pakage update query Why doesn't it work?

create or replace PACKAGE BODY SAP_MATERIALMASTER_PKG IS

 PROCEDURE SAP_MATERIALMASTER_INS (
    TRANS_REF_NO   IN NUMBER,
    MATERIAL   IN VARCHAR2,
    UNIT_OF_MEASURE   IN CHAR )
      IS
      cnt NUMBER := 0;   
      BEGIN

        --insert or update 구분을 위한 동작
          select COUNT(*) into cnt from SAP_MATERIAL
          where
          material = MATERIAL;

        if cnt = 0 then 

          INSERT INTO SAP_MATERIAL(
          TRANS_REF_NO,MATERIAL,UNIT_OF_MEASURE
          ) 
          VALUES(TRANS_REF_NO,MATERIAL,UNIT_OF_MEASURE); commit;

        else

          UPDATE SAP_MATERIAL
          SET
          TRANS_REF_NO = TRANS_REF_NO, 
          MATERIAL = MATERIAL,
          UNIT_OF_MEASURE = UNIT_OF_MEASURE
          WHERE MATERIAL = MATERIAL;
          commit;
        end if;

      END SAP_MATERIALMASTER_INS;

 END  SAP_MATERIALMASTER_PKG;

... plz change TRANS_REF_NO IN NUMBER, to p_trans_ref_no IN NUMBER, and so on. — Pham X. Bach
– Pham X. Bach, Commented Aug 29, 2019 at 1:34

Gordon Linoff · Accepted Answer · 2019-08-29 01:35:25Z

1

Fix your parameters! They need to differ from the column names. I recommend a prefix, in this case in_ to indicate that these are "in" parameters:

PROCEDURE SAP_MATERIALMASTER_INS (
    in_TRANS_REF_NO   IN NUMBER,
    in_MATERIAL   IN VARCHAR2,
    in_UNIT_OF_MEASURE   IN CHAR
) as
cnt NUMBER := 0;   
BEGIN
    . . .
    UPDATE SAP_MATERIAL
         SET TRANS_REF_NO = in_TRANS_REF_NO, 
              UNIT_OF_MEASURE = in_UNIT_OF_MEASURE
          WHERE MATERIAL = in_MATERIAL;
     . . .

answered Aug 29, 2019 at 1:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bob Jarvis - Слава Україні · Accepted Answer · 2019-08-29 03:30:53Z

Along with @GordonLinoff's solution, might I suggest that you learn to use a MERGE statement? It's designed to do precisely what you're doing with less code:

PROCEDURE SAP_MATERIALMASTER_INS (pin_TRANS_REF_NO    IN NUMBER,
                                  pin_MATERIAL        IN VARCHAR2,
                                  pin_UNIT_OF_MEASURE IN CHAR )
  IS
  BEGIN
    MERGE INTO SAP_MATERIAL sm
      USING (SELECT pin_TRANS_REF_NO    AS P_TRANS_REF_NO,
                    pin_MATERIAL        AS P_MATERIAL,
                    pin_UNIT_OF_MEASURE AS P_UNIT_OF_MEASURE
               FROM DUAL) d
        ON (sm.MATERIAL = d.P_MATERIAL)
      WHEN NOT MATCHED THEN
        INSERT (TRANS_REF_NO,     MATERIAL,     UNIT_OF_MEASURE)
        VALUES (d.P_TRANS_REF_NO, d.P_MATERIAL, d.P_UNIT_OF_MEASURE)
      WHEN MATCHED THEN
        UPDATE
          SET sm.TRANS_REF_NO = d.P_TRANS_REF_NO,
              sm.UNIT_OF_MEASURE = d.P_UNIT_OF_MEASURE;

    COMMIT;
  END SAP_MATERIALMASTER_INS;

Plus, SELECT COUNT(*)... can be very slow.

Collectives™ on Stack Overflow

ORACLE) update query Why doesn't it work?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related