CODEWITHSUNDEEP
functionplsqloracle11gprocedure
Maxime de Lange
Maxime de Lange

Reputation: 297

How to get this function to work in Oracle 11G - PL/SQL

I'm new with PL/SQL I have a assignment where I need to make a function. The assignment is as follows:

> "Create a function 'afdeling_van:'
> - this function accepts a medewerkernummer(employee number) as argument
> - give the afdelingnummer(department number) from the medewerkernummer(employee number) back"

So I need to create a function with a parameter that returns a number. After that I probably need to add some code to make it return a medewerker(employee) number back.

I got pretty stuck with this one as I am really new to PL/SQL. What I do have at the moment is this:

declare
 procedure afdeling_van(p_persoon in medewerkers.mnr%type)--table name with column name
 is
 begin
  select med.mnr
  from medewerkers med;
  where mnr = p_persoon;
 end afdeling_van;
begin
afdeling_van(10);
end;

It's not working for me. I have tried different solutions. But as I lack experience and I cannot find the solution or information that I need on the web. I am trying it to ask here

one other thing. I think it's similair to my problem. In the previous assignment I made a procedure instead of a function. The procedure is as follows:

declare
v_medewerker varchar2(50) := ontsla_med();
 procedure ontsla_med(p_medewerkers in medewerkers.naam%type)
 is 
 begin
  delete from medewerkers
  where naam = p_medewerkers;
 end ontsla_med;
begin
ontsla_med('');
dbms_output.put_line('Medewerker: ' || v_medewerker || 'verwijdert uit medewerker, inschrijven en uitvoeringen bestand.' );
 exception
  when no_data_found then
  dbms_output.put_line('Medewerker bestaat niet/ is al verwijderd.');
end;
/

this works except for the last dbms_output.put_line. If I remove the output line, then it will work and with the output line, it won't.

I hope my question is not too vague.

Thanks in advance.

Upvotes: 0

Views: 63

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50067

You need to create a function instead of a procedure, and you've got a semi-colon in the wrong place. Try something like:

declare
  nReturned_value  MEDEWERKERS.AFDELINGNUMMER%TYPE;

  FUNCTION afdeling_van(p_persoon in medewerkers.mnr%type) --table name with column name
      RETURN MEDEWERKERS.AFDELINGNUMMER%TYPE
  is
    nAFDELINGNUMMER  MEDEWERKERS.AFDELINGNUMMER%TYPE;
  begin
    select med.AFDELINGNUMMER
      INTO nAFDELINGNUMMER  
      from medewerkers med
      where mnr = p_persoon;

    RETURN nAFDELINGNUMMER  ;
  end afdeling_van;

begin
  nReturned_value := afdeling_van(10);
  DBMS_OUTPUT.PUT_LINE('nReturned_value = ' || nReturned_value);
end;

Edit

In your second example, I don't believe that the line v_medewerker varchar2(50) := ontsla_med(); will compile. ontsla_med is a procedure rather than a function, and because procedures don't return anything they can't be used in an assignment statement.

However, v_medewerker is only used in the DBMS_OUTPUT line which you say causes a problem - thus, it may be that the compiler is eliminating the variable because it's not used if the DBMS_OUTPUT line is removed, thus eliminating the problem. Try changing the declaration to v_medwerker varchar2(50) := 'Hello'; and see if that helps.

Upvotes: 1

Related Questions