Hi I have problem with variable:
Here is my code
CREATE OR REPLACE Procedure WstawPotomka( wPesel IN Osoba.Pesel%TYPE, wImie IN Osoba.Imie%Type ,
wNazwisko IN Osoba.Nazwisko%TYPE, wDataUr IN Osoba.Data_Urodzenia%Type, wMiejsceUr IN Osoba.Miejsce_Urodzenia%Type, PeselMatka IN Osoba.Matka%Type, PeselOjciec IN Osoba.Ojciec%Type)
AS Cursor Potomek
IS SELECT
o.pesel as Col1, b.pesel AS Col2, c.pesel AS Col3 FROM OSOBA o
CROSS JOIN Osoba b
CROSS JOIN Osoba c;
poto Potomek%ROWTYPE;
BEGIN
OPEN Potomek;
LOOP
FETCH Potomek INTO poto;
EXIT WHEN Potomek%NotFound;
IF Col1.poto != wPesel THEN
IF COL2.poto = PeselMatka and COL3.poto = PeselOjciec THEN
INSERT INTO Osoba Values (wPesel, wImie, wNazwisko, wDataUr, wMiejsceUr, null, PeselMatka, PeselOjciec, null);
END IF;
END IF;
END LOOP;
Close Potomek;
END;
The Osoba table:
It's my first post so don't be so angry for formatting.
This can be done with a single efficient SQL statement.
I highly doubt that the SELET statement and the general logic in your code is correct. CROSS JOINs create a cartesian product and are always suspect. But even worse: The inserted rows only take data from the input parameters and thus are all the same. The SELECT statement only determines the number of rows.
CREATE OR REPLACE PROCEDURE WstawPotomka(
wPesel IN Osoba.Pesel%TYPE,
wImie IN Osoba.Imie%TYPE ,
wNazwisko IN Osoba.Nazwisko%TYPE,
wDataUr IN Osoba.Data_Urodzenia%TYPE,
wMiejsceUr IN Osoba.Miejsce_Urodzenia%TYPE,
PeselMatka IN Osoba.Matka%TYPE,
PeselOjciec IN Osoba.Ojciec%TYPE
)
AS
BEGIN
INSERT INTO Osoba
SELECT wPesel, wImie, wNazwisko, wDataUr, wMiejsceUr, NULL, PeselMatka, PeselOjciec, NULL
FROM OSOBA o
CROSS JOIN Osoba b
CROSS JOIN Osoba c
WHERE o.pesel <> wPesel
AND b.pesel = PeselMatka
AND c.pesel = PeselOjciec;
END;