How can i show information from a foreign key when creating a SQL Function?

Question

I am currently working on a school project and keep hitting a roadblock.. I would like to get the names linked to the foreign keys('geleendvan' and 'geleendaan'). these two are currently numbers that match with a ID from a person table. Before i go deeper into my question let me show you the queries:

'''

create or replace trigger lening_bri
BEFORE
insert on lening
for each row
DECLARE
 NieuweSleutel INTEGER;
 Resultaat VARCHAR2(255);
 Fout EXCEPTION;
BEGIN
 -- eerst integriteits check uitvoeren
 Resultaat := sf_check_lener_onmogelijk(:new.geleendvan,:new.geleendaan);
 IF Resultaat <> 'Normaal' THEN
  -- resultaat niet goed, fout forceren
  RAISE fout;
 END IF;
 --
 SELECT nvl(max(l.nummer),0)
 INTO NieuweSleutel
 FROM lening l;
 --
 NieuweSleutel := NieuweSleutel + 1;
 :new.nummer := NieuweSleutel;
EXCEPTION
  -- foutafhandeling
  WHEN fout THEN
    apex_error.add_error (p_message => resultaat,
                          p_additional_info => null, 
                          p_display_location => apex_error.c_inline_in_notification );
    raise;
END; 

'''

The above query is a trigger when a loan(lening) is created, the newkey(NieuweSleutel) part is quite irrelevant but as you can see at line 10 there is a variable Result(Resultaat) here i use the following SQLFunction:

'''

create or replace FUNCTION sf_check_lener_onmogelijk
(in_geleendvan IN VARCHAR2,
in_geleendaan IN VARCHAR2)
RETURN VARCHAR2
is
 resultaat VARCHAR2(255);

BEGIN
 IF in_geleendvan == in_geleendaan THEN
  resultaat := 'Toevoegen afgebroken. Persoon ['||in_geleendvan||' '||in_geleendaan||'] mogen niet het zelfde zijn :( .';
 ELSE
  resultaat := 'Normaal';
 END IF;
  RETURN resultaat;
END; 

'''

As you can see the Function checks if the person that is loaning isnt the same as the person who is lending(in_geleendvan and in_geleendaan).

But here is the problem: The function uses the ID's to check if they are the same, which isnt a problem on itself but i would like to return the names that match with the number. Does this mean i have to alter the function or change something in my trigger? If so do you guys have any suggestion?

Thanks in advance! :)

Answer 1

If I understood you correctly, you'd change the function. Something like this (I don't have your tables so I'm just guessing column/table names):

CREATE OR REPLACE FUNCTION sf_check_lener_onmogelijk (
   in_geleendvan  IN VARCHAR2,
   in_geleendaan  IN VARCHAR2)
   RETURN VARCHAR2
IS
   resultaat          VARCHAR2 (255);
   l_name_geleendvan  some_table.name%TYPE;
   l_name_geleendaan  some_table.name%TYPE;
BEGIN
   IF in_geleendvan = in_geleendaan
   THEN
      SELECT name
        INTO l_name_geleendvan
        FROM some_table
       WHERE id = in_geleendvan;

      SELECT name
        INTO l_name_geleendaan
        FROM some_table
       WHERE id = in_geleendaan;

      resultaat :=
            'Toevoegen afgebroken. Persoon ['
         || l_name_geleendvan
         || ' '
         || l_name_geleendaan
         || '] mogen niet het zelfde zijn :( .';
   ELSE
      resultaat := 'Normaal';
   END IF;

   RETURN resultaat;
END;