Problem with UPDATE statement in stored-procedure in Oracle Database

Question

Hello, I have stored-procedure in Oracle database like this:

create or replace
PROCEDURE EDYTUJ_PRACOWNIKA
  (PR_IMIE IN VARCHAR2, PR_NAZWISKO IN VARCHAR2, PR_PENSJA IN FLOAT,
  PR_PRZELOZONY IN NUMBER, PR_ODDZIAL IN NUMBER, PRAC_ID IN NUMBER)
AS
tmpPensja FLOAT := 0;
tmpPrzel NUMBER := 0;
BEGIN
  select przelozony into tmpPrzel from pracownik where id = PRAC_ID;
  IF(tmpPrzel IS NOT NULL) THEN
    select pensja into tmpPensja from pracownik where id = tmpPrzel;
    IF(tmpPensja < 1150) THEN
      UPDATE PRACOWNIK SET pensja = 1000 WHERE id = tmpPrzel;
    ELSE
      UPDATE PRACOWNIK SET pensja = pensja - 150 WHERE id = tmpPrzel; (4)
    END IF;
  END IF;

  IF(PR_PRZELOZONY > 0) THEN 
    UPDATE PRACOWNIK SET imie = PR_IMIE, nazwisko = PR_NAZWISKO, pensja = PR_PENSJA, przelozony = PR_PRZELOZONY,
      oddzial = PR_ODDZIAL WHERE id = PRAC_ID; (2)
    select pensja into tmpPensja from pracownik where id = PR_PRZELOZONY;

    IF(tmpPensja > 4850) THEN
      UPDATE PRACOWNIK SET pensja = 5000 WHERE id = PR_PRZELOZONY;
    ELSE
      UPDATE PRACOWNIK SET pensja = pensja + 150 WHERE id = PR_PRZELOZONY; (1)
    END IF;
  ELSE
    UPDATE PRACOWNIK SET imie = PR_IMIE, nazwisko = PR_NAZWISKO, pensja = PR_PENSJA, przelozony = NULL,
      oddzial = PR_ODDZIAL WHERE ID = PRAC_ID; (3)
  END IF;
END;

where przelozony and pensja are columns in pracownik table.

And I have problem that when running procedure with parameters that provide that line marked with "(1)" (there is the same problem with line marked with "(4)") should be executed that update statement don't have any effect. What's more statements in lines marked with "(2)" and "(3)" works fine.

I have no ideas how to fix it. Thank you in advance for your help.

Answer 1

It's difficult to read code with foreign table and column names, so I hope I got it right (no offense) - make sure to review carefully though.

As far as I understand your code, you should be able to remove your temporary variables and do everything in three subsequent update statements (updating different rows). I don't know what exactly does not work, but if it still does not work after, try to execute the single SQL statements manually and check the results.

---

Update pracownik, reduce pensja by 150 but not below 1000 where id = przelozony (prac_id)

UPDATE pracownik
SET pensja = LEAST( pensja-150, 1000 )
WHERE id = ( SELECT przelozony FROM pracownik where id = PRAC_ID );

Update pracownik, set some values, and przelozony.

UPDATE pracownik
SET imie = PR_IMIE,
    nazwisko = PR_NAZWISKO,
    pensja = PR_PENSJA,
    przelozony = CASE WHEN PR_PRZELOZONY > 0 THEN PR_PRZELOZONY ELSE NULL END,
    oddzial = PR_ODDZIAL
WHERE id = PRAC_ID;

Update pracownik if PR_PRZELOZONY > 0, increase pensja by 15, but not above 5000.

IF(PR_PRZELOZONY > 0) THEN 
    UPDATE pracownik
    SET pensja = GREATEST( pensja + 150, 5000 )
    WHERE id = pr_przelozony;
END IF;

Answer 2

Almost certainly the values you think you have are not the values you actually have. For instance, if this statement returns a NULL

select przelozony into tmpPrzel from pracownik where id = PRAC_ID;

statement (4) will never be executed. Likewise if this returns a null

select pensja into tmpPensja from pracownik where id = PR_PRZELOZONY;

statement (1) will never be executed. To check this you need to put some trace statements in your code, or run it through a debugger.

The quickest way of putting trace into a program is to use DBMS_OUTPUT.PUT_LINE and run the stored procedure in a client like SQL*Plus (or use an IDE).

select przelozony into tmpPrzel from pracownik where id = PRAC_ID;
dbms_output.put_line('PRAC_ID ='|| PRAC_ID ||':: tmpPrze='|| tmpPrze );
IF(tmpPrzel IS NOT NULL) THEN
    select pensja into tmpPensja from pracownik where id = tmpPrzel;
    dbms_output.put_line('tmpPrzel IS NOT NULL:: tmpPensja='|| tmpPensja );
    ...

etc.

All the most popular PL/SQL IDEs - Ouest TOAD, Allround Automation PL/SQL Developer and Oracle SQL Developer offer debugging. You can find instructions for debugging in SQL Developer here on OTN.