Oracle Cursors : Invalid Identifier

Question

I need to write code that increases the salary of an employee who is over 40 years old.

Here is my code:

DECLARE  
CURSOR kurs IS SELECT  ID_PRACOWNIKA , pensja_BR, wiek FROM PRACOWNICY p , OSOBY o ;
ID_PRACOWNIKA decimal(2):=0;
pensja DECIMAL(8,2);
wiek DECIMAL(2);

BEGIN
    OPEN kurs;
LOOP

   IF wiek > 40 
   THEN
      UPDATE PRACOWNICY 
       SET pensja = PENSJA_BR * 1.02
      WHERE ID_PRACOWNIKA = ID_PRACOWNIKA;

      dbms_OUTPUT.put_line( ID_PRACOWNIKA|| '-'||pensja);
     END IF;
 ID_PRACOWNIKA := ID_PRACOWNIKA+1;
 EXIT WHEN ID_PRACOWNIKA=6;
  END LOOP;
   CLOSE kurs;
   END;

Unfortunately I have SQL error

SQL Error [6550] [65000]: ORA-06550: line 14, column 12:
PL/SQL: ORA-00904: "PENSJA": invalid identifier
ORA-06550: line 13, column 7:
PL/SQL: SQL Statement ignored

Osoby table strucuture:

Id_osoby NUMBER CONSTRAINT osoby_pk PRIMARY KEY,
Imie VARCHAR2(15) NOT NULL,
Nazwisko VARCHAR2(30) NOT NULL,
Wiek NUMBER NOT NULL CONSTRAINT ch_wiek CHECK((Wiek>=0) AND (Wiek<=125)),
Stan_cywilny VARCHAR2(12) NOT NULL,
Telefon VARCHAR2(20),
Pesel CHAR(11) NOT NULL CONSTRAINT osoba_uni UNIQUE,
Id_adresu NUMBER NOT NULL,
CONSTRAINT os_ad_fk FOREIGN KEY (Id_adresu) REFERENCES Adresy(Id_adresu)

Pracownicy table structure:

Id_pracownika NUMBER CONSTRAINT pracownik_pk PRIMARY KEY,
Id_osoby NUMBER NOT NULL CONSTRAINT pr_unique UNIQUE,
Id_stanowiska NUMBER NOT NULL,
Staz NUMBER NOT NULL CONSTRAINT ch_staz CHECK((Staz>=0) AND (Staz<=45)),
Pensja_br NUMBER NOT NULL CONSTRAINT pen_staz CHECK(Pensja_br>=1226),
CONSTRAINT pr_os_fk FOREIGN KEY (Id_osoby) REFERENCES Osoby(Id_osoby),
CONSTRAINT pr_st_fk FOREIGN KEY (Id_stanowiska) REFERENCES Stanowiska(Id_stanowiska)

Answer 1

I think you can use the single update statement but as you want to print the details also. You can go with FOR loop as follows:

BEGIN 
FOR I IN (
    SELECT P.ID_PRACOWNIKA,
           P.PENSJA_BR * 1.02 AS PENSJA_BR
      FROM PRACOWNICY   P
      JOIN OSOBY        O
    ON P.ID_OSOBY = O.ID_OSOBY
     WHERE O.WIEK > 40
) LOOP
        UPDATE PRACOWNICY P
           SET P.PENSJA_BR = I.PENSJA_BR
         WHERE P.ID_PRACOWNIKA = I.ID_PRACOWNIKA;

        DBMS_OUTPUT.PUT_LINE(I.ID_PRACOWNIKA || '-' || I.PENSJA_BR);
  END LOOP;
END;

If you just want to update the data with a single query then you can use the following update SQL:

UPDATE PRACOWNICY P
   SET P.PENSJA_BR = P.PENSJA_BR * 1.02
 WHERE EXISTS (
    SELECT 1
      FROM OSOBY O
     WHERE P.ID_OSOBY = O.ID_OSOBY
       AND O.WIEK > 40
);