Problem with UPDATE statement in stored-procedure in Oracle Database
Posted
by MKP
on Stack Overflow
See other posts from Stack Overflow
or by MKP
Published on 2010-05-20T20:24:17Z
Indexed on
2010/05/20
21:00 UTC
Read the original article
Hit count: 281
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.
© Stack Overflow or respective owner