Introduction to Functions:

A function like a procedure receives arguments from the calling program. The difference is that a function is part of an expression and returns a single value to the calling program for its use.

In the example below, I have a function that takes an idno from the calling program and then does a select of that record from the table new_donation_one (notice that new_donation_one is a modified version of the donation file that only has 1 record for each idno). The select puts the contamt from the table into the variable v_contamt and then checks to see if it is > 10. Depending on the answer there is a different calculation to determine the variable v_return_donation. At the end of the IF and the ELSE, the RETURN verb is used to return v_return_donation. I could also have removed the RETURN and done it once after the END IF. When the function is complete, v_return_donation goes back to the PL/SQL block that called the function.

Now look at the calling block calc_func_calc. It calls the function with the assignment statement that assignes the results of the function to the variable v_new_goal. The function call passes the identification for use in the function. The function returns a value that is essentially inserted on the right side of the assignment sign and thereby assigned to v_new_goal. All I did was display v_new_goal, but I could have used it to update the table, create a new record in another table etc. I executed the PL/SQL calc_func_calc three times and the output is shown below. Note: As with procedures, I need to execute the function code first and have the function created before I execute the code that calls the function.

SQL CODE:

SQL> edit func_calc1

PL/SQL CODE:

CREATE OR REPLACE FUNCTION func_calc1
   (v_idno             new_donation_one.idno%TYPE)
  RETURN NUMBER IS 
   v_contamt          new_donation_one.contamt%TYPE;
   v_return_donation  new_donation_one.contamt%TYPE;
BEGIN
  SELECT contamt INTO v_contamt
    FROM new_donation_one
    WHERE idno = v_idno;
  IF v_contamt > 10 THEN
     v_return_donation := v_contamt * 2;
     RETURN v_return_donation;
  ELSE
     v_return_donation := v_contamt * 1.5;
     RETURN v_return_donation;
  END IF;
END func_calc1;
/

SQL CODE:

SQL> edit calc_func_calc

PL/SQL CODE:

SET SERVEROUTPUT ON
DECLARE
  v_idno             new_donation_one.idno%TYPE :='&input_idno';
  v_new_goal          new_donation_one.contamt%TYPE;

BEGIN
  v_new_goal := func_calc1(v_idno);
  dbms_output.put_line('New amount: '||TO_CHAR(v_new_goal));
END;
/
SET SERVEROUTPUT OFF

SQL CODE:

SQL> select * from new_donation_one order by idno;

IDNO  DRI CONTDATE    CONTAMT
----- --- --------- ---------
11111 100 07-JAN-99        25
12121 300 10-JUN-99        75
22222 100 14-MAR-99        10
23456 300 14-JUN-99        10
33333 300 10-MAR-99        10


SQL> @ func_calc1
Input truncated to 1 characters

Function created.

SQL> @ calc_func_calc
Enter value for input_idno: 12121
old   2:   v_idno             new_donation_one.idno%TYPE :='&input_idno';
new   2:   v_idno             new_donation_one.idno%TYPE :='12121';
New amount: 150

PL/SQL procedure successfully completed.

SQL> @ calc_func_calc
Enter value for input_idno: 22222
old   2:   v_idno             new_donation_one.idno%TYPE :='&input_idno';
new   2:   v_idno             new_donation_one.idno%TYPE :='22222';
New amount: 15

PL/SQL procedure successfully completed.

SQL> edit calc_func_calc

SQL> @ calc_func_calc
Enter value for input_idno: 11111
old   2:   v_idno             new_donation_one.idno%TYPE :='&input_idno';
new   2:   v_idno             new_donation_one.idno%TYPE :='11111';
New amount: 50

This is the example where the RETURN was moved to after the IF so that it only appeared once in the code. Note that I had to make the change to call a different function in calc_func_calc. Note also that I have to run the function to create it before I can run the code that calls the function.

SQL CODE:

SQL> edit func_calc

PL/SQL CODE:

CREATE OR REPLACE FUNCTION func_calc
   (v_idno             new_donation_one.idno%TYPE)
  RETURN NUMBER IS 
   v_contamt          new_donation_one.contamt%TYPE;
   v_return_donation  new_donation_one.contamt%TYPE;
BEGIN
  SELECT contamt INTO v_contamt
    FROM new_donation_one
    WHERE idno = v_idno;
  IF v_contamt > 10 THEN
     v_return_donation := v_contamt * 2;
  ELSE
     v_return_donation := v_contamt * 1.5;
  END IF;
  RETURN v_return_donation;
END func_calc;
/

SQL CODE:

SQL> edit calc_func_calc

PL/SQL CODE:

SET SERVEROUTPUT ON
DECLARE
  v_idno             new_donation_one.idno%TYPE :='&input_idno';
  v_new_goal         new_donation_one.contamt%TYPE;
BEGIN
  v_new_goal := func_calc(v_idno);
  dbms_output.put_line('New amount: '||TO_CHAR(v_new_goal));
END;
/
SET SERVEROUTPUT OFF

SQL CODE:

SQL> @ func_calc
Input truncated to 1 characters

Function created.

SQL> @ calc_func_calc
Enter value for input_idno: 23456
old   2:   v_idno             new_donation_one.idno%TYPE :='&input_idno';
new   2:   v_idno             new_donation_one.idno%TYPE :='23456';
New amount: 15

In the procedure examples and in the function example above I did not use exception routines. In the function example, if an invalid idno is entered, the program will terminate with an error message. Exception coding could be included to handle the errors according to programmer specifications.