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.
A stored function is a named PL/SQL block just as a procedure is. A function can be invoked and passed paramenters. A function must return a value to the calling block, therefore it is used to compute a value frequently.
This show matches the handout. More information on functions is in the more on presentation...
This slide shows both the function and the anonymous block that causes the function to be run.
Note that the assignment statement assigns the number that is returned by the function to v_new_goal.
The function is called and the identification number is sent. The SELECT in the function retrieves the contribution amount for that donation. The IF statement than calculates the v_return_donation depending on v_contamt. The result of the calculation is returned to the anonymous block which can then use the amount as needed.
Note that the select is getting a single record. This works because the donation table that is being used has only one donation per id.
The number to be returned is v_return_donation.
Remember the anonymous block displays the value assigned to v_new_goal by the function.
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; ENDIF;
Note that 12121 had a contamt of 75 so v_return_donation is twice that or 150.
With 22222 the contamt was 10 so the ELSE is taken so v_return_donation is 1.5 times or 15.