CODEWITHSUNDEEP
oracle-databaseplsql
Tom Scott
Tom Scott

Reputation: 49

Syntax when procedure calling two functions

So I im trying to add a row in the table "gymbesök", nothing complex about that. But before adding a row im supposed to call two functions that ive made. But im unsure how syntax is built up when calling two. If the two functions doesnt meet the REQ's shall the transaction stop.

Here's my code

Create or replace procedure do_gymbesök(
P_rfid in gymbesök.rfid%type,
P_gymid in number)
As
Begin
Insert into gymbesök (radnr,rfid,gymid,datum)
Values (radnr_seq.nextval, p_rfid, p_gymid, sysdate);
if (get_behörighet (p_gymid, p_rfid) = 0) then
raise_application_error (-20001, 'Öbehörig');
or
 if  (get_träningspass(p_antal) = 0) then
raise_application_error (-20001, 'Inga träningspass kvar');

end if;
end;
/

Thank you

Upvotes: 0

Views: 124

Answers (2)

APC
APC

Reputation: 146239

Do the validation as two separate calls. Also, it would be better to validate the parameters first, and only insert the row if the validation passes.

Create or replace procedure do_gymbesök(
P_rfid in gymbesök.rfid%type,
P_gymid in number)
As
Begin
 if  (get_träningspass(p_antal) = 0) then
      raise_application_error (-20001, 'Inga träningspass kvar');
  end if;
  if (get_behörighet (p_gymid, p_rfid) = 0) then
      raise_application_error (-20001, 'Öbehörig');
   end if;
  Insert into gymbesök (radnr,rfid,gymid,datum)
  Values (radnr.seq.nextval, p_rfid, p_gymid, sysdate);


end;
/

Both your calls to RAISE_APPLICATION_ERROR use the same error number. Generally speaking it is a good idea to use different error numbers for different errors, not just different messages. Oracle provides a generous thousand numbers between -20999 and -20000, so there should be enough to go round.

You have a test for p_antal which does not appear in the posted code. I presume this is a transcription error.

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142713

Something like this, perhaps?

  • If the first function fails, it'll raise 20000.
  • Else, check what the second one returns; if it fails, raise 20001.
  • If none of them fail, do the INSERT

I've modified error codes you return so that you could distinguish these two. It is not an error to use 20001 for both cases, but - why would you?

create or replace procedure do_gymbesök(
  p_rfid in gymbesök.rfid%type,
  p_gymid in number)
as
begin
  if get_behörighet (p_gymid, p_rfid) = 0 then
     raise_application_error (-20000, 'Öbehörig');
  elsif get_träningspass(p_antal) = 0 then
     raise_application_error (-20001, 'Inga träningspass kvar');
  end if;

  insert into gymbesök (radnr,rfid,gymid,datum)
  values (radnr_seq.nextval, p_rfid, p_gymid, sysdate);
end;
/

Alternatively, use one IF condition with OR:

create or replace procedure do_gymbesök(
  p_rfid in gymbesök.rfid%type,
  p_gymid in number)
as
begin
  if get_behörighet (p_gymid, p_rfid) = 0 or
     get_träningspass(p_antal) = 0
  then
     raise_application_error (-20001, 'Öbehörig / Inga träningspass kvar');
  else  
     insert into gymbesök (radnr,rfid,gymid,datum)
     values (radnr_seq.nextval, p_rfid, p_gymid, sysdate);
  end if;
end;
/

Upvotes: 2

Related Questions