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

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! :)

CodePudding user response：

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;

CodePudding user response：

Your function is invalid as you want = instead of ==. Then you can select the name from the appropriate table:

create or replace FUNCTION sf_check_lener_onmogelijk(
  in_geleendvan IN lening.geleendvan%TYPE,
  in_geleendaan IN lening.geleendaan%TYPE
)
RETURN VARCHAR2
IS
  v_name_gv people.name%TYPE;
  v_name_ga people.name%TYPE;
BEGIN
  IF in_geleendvan = in_geleendaan THEN
    SELECT name
    INTO   v_name_gv
    FROM   people
    WHERE  id = in_geleendvan;

    SELECT name
    INTO   v_name_ga
    FROM   people
    WHERE  id = in_geleendaan;

    RETURN 'Toevoegen afgebroken. Persoon ['||v_name_gv||' '||v_name_ga||'] mogen niet het zelfde zijn :( .';
  ELSE
    RETURN 'Normaal';
  END IF;
END;
/

Then you should not find the maximum nummer and add one; use a sequence or an IDENTITY column (and ignore any gaps in the sequence):

CREATE TABLE lening (
  nummer     INTEGER
             GENERATED ALWAYS AS IDENTITY
             CONSTRAINT lenng__nummer__pk PRIMARY KEY,
  geleendvan VARCHAR2(20),
  geleendaan VARCHAR2(20)
);

Then your trigger can be:

create or replace trigger lening_bri
BEFORE insert on lening
for each row
DECLARE
 Resultaat VARCHAR2(255);
 Fout EXCEPTION;
BEGIN
  -- eerst integriteits check uitvoeren
  Resultaat := sf_check_lener_onmogelijk(:new.geleendvan,:new.geleendaan); 
  IF Resultaat <> 'Normaal' THEN
    RAISE fout;
  END IF;
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; 
/

db<>fiddle here