Q&A

Q Does Oracle allow you to create your own functions?
A Yes! This is what makes Oracle so powerful. It allows you to reduce the amount of PL/SQL coding.
Q Can you call functions from any PL/SQL code?
A Yes, but this is only a recent development to include stored functions as part of the Oracle product.
Q Is there any reason to use the NULL statement?
A The NULL statement acts as a placeholder to make your code more readable. It is also used when no action is required.
Q What is the difference between nested IFs and IF...ELSIF?
A Nested IFs allow you to do logical AND checking. In addition, you do not always have to execute nested IF statements further in the block if the first IF statement evaluates to false. IF...ELSIF blocks allow you to check through a series of mutually exclusive choices.
Q What is the order of execution of nested loops?
A The outer loop executes first. The inner loop then executes in full from low to high values before it returns control to the outer loop. All looping ends when the outer loop has been completed or an EXIT statement has occurred.

Workshop

You can now review your knowledge of functions and conditional branching starting with a quick quiz, followed by some challenging exercises. The answers to both can be found in Appendix A, "Answers."

Quiz

1. What parts of the function are required for coding?
2. If a function takes parameters, is it always necessary to pass these parameters from the calling statement?
3. If an error occurs and you haven't coded an EXCEPTION statement, what is returned from the function?
4. Is there a way to return more than one value from a function?

5. If you code an IF...ELSE statement, and you do not have any conditions to execute if the statement is false, how would you code the ELSE statement?
6. What are some of the common pitfalls in coding IF statements?
7. How can I determine what is wrong with my code when it compiles?
8. When coding a loop in reverse, how must the beginning and ending values be coded?

Exercises

1. Rewrite the Grade example from Listing 5.11 as a stored function that passes the parameter of the score and returns a value of a grade letter.
2. Rewrite the Grade example from Listing 5.11 and use between for the ranges. Make sure that there is no overlapping of ranges.
3. Write a loop that increments by a value of 3 and then multiplies a counter by the returned value of the function mypi. The range should be from 1 to 9. Output the values with DBMS_OUTPUT. Make sure that you have entered SET SERVEROUTPUT ON to see the output.
4. Write a loop to calculate a factorial. For example, 6! is 6 * 5 * 4 * 3 * 2 * 1. Allow the high boundary to be a variable that can change. Use an initial value of 3 for testing.