Day 15

Week
2
In Review

You have finished your second week of learning how to program in PL/SQL. The week started with you learning about stored procedures and packages on Day 8. These offer the programmer the advantages of encapsulation and grouping similar procedures and functions together in packages. You also learned how to plan for and react to certain runtime errors that can arise in your PL/SQL code. This includes how to write exception-handling routines to handle internal and user-defined PL/SQL processing errors.

The second week continued on Day 9 with you learning how to create and use two composite PL/SQL structures—the PL/SQL table and record. You also learned how to use INSERT and SELECT statements.

Next, on Day 10, you learned how to manipulate data with PL/SQL cursors. Cursors are wonderful constructs in that they enable you to process a multiple-row query result set, one row at a time. You were also presented with material that showed you how to pass arguments into cursors and how to use cursors as variables on Day 12.

On Day 11, you learned how to use triggers. These triggers are automatically executed based upon your predefined firing criteria. You were also introduced to the new objects contained in the Oracle8 release on Day 13.

Finally, the week ended with you learning how to prepare for errors and write error-handling routines to help prevent unwanted termination of the execution of your PL/SQL programs.

Week
3
At a Glance

At this point, you should have mastered the basics of Oracle, from functions to procedures to SQL*Plus. With this knowledge, you can now master the packages supplied by Oracle, which offer some additional advanced features. Each chapter guides you through a package or concept and demonstrates its topic through an actual example that you can try.

Where You Are Going

Day 15 covers advanced topics including features new to Oracle8, such as the DBMS_LOB package. You also learn how to implement recursion. Day 16 covers how to work with multiple users across multiple platforms by managing transactions and locks. On Day 17, you learn

about the DBMS_SQL package. Continuing with packages, Day 18 covers the UTL_FILE and DBMS_OUTPUT packages for various methods of output. On Day 19 you are exposed to the DBMS_JOB package, which manages database jobs. You also see how sessions communicate with the DBMS_PIPE package. On Day 20, you learn how to manage alerts with the DBMS_ALERT package. Finally, the week ends with a discussion on how to interface Oracle to the Web using J/SQL on Day 21.

Week 3

Day 15

Exploring Advanced
Topics

by Timothy Atwood

Many new advanced features were added with the release of Oracle8. This chapter covers several of these features, including locators, large objects, and the DBMS_LOB package. In addition, you will learn how to create and implement recursive functions. Today's lesson focuses on the following topics:

• Writing recursive functions
• Defining large objects
• Using the DBMS_LOB package with external files
• Understanding locators
• Using the DBMS_LOB package with internal files

What Is Recursion?

Earlier in this book, you learned how to use Oracle's built-in functions, as well as how to create your own functions. Functions allow you to create reusable code, which is also easier to test and debug when broken into smaller components. In PL/SQL, functions can easily call other functions. A recursive function is one that calls itself until some exit condition occurs. One problem with coding recursion is making sure that the exit condition is met!

Practicing Recursion

As with any third-generation programming language, you have to code the functions as recursive because recursion is not already built into PL/SQL. The classic example of using recursion is to calculate the factorial of a number. To compute the factorial of the number, you would multiply the number by the number -1 (n*(n-1)) until n equals the value of 1. Table 15.1 shows factorial calculations for numbers 1 through 6. Factorial in math textbooks uses the punctuation !, so 3! means three factorial, which is 3 * 2 * 1 = 6.

Table 15.1. Factorial calculations.

Now try using recursion to calculate the factorial of an integer. First, you will need to create the function FACTORIAL by executing the code in Listing 15.1.

INPUT
Listing 15.1. Creating the FACTORIAL recursive function.

CREATE OR REPLACE FUNCTION Factorial(p_MyNum INTEGER)

/* Creates a recursive function that simply calculates
   the factorial of a number.  The function starts with
   the number and then calls the function with n-1,
   until n = 1 which returns a value of 1.  Without this
   statement, the function would never end.         */

     RETURN NUMBER AS
BEGIN -- Start of Factorial Function
     IF p_MyNum = 1 THEN -- Checking for last value to process of n-1
          RETURN 1;
     ELSE
          RETURN(p_MyNum * Factorial(p_MyNum-1)); -- Recursive
     END IF;
END; -- End of Factorial Function

After you execute the code in Listing 15.1, you should see the following output to the screen:

function   created

This function FACTORIAL will continue to call itself until n-1, where n is the factorial processed, equals a value of one. After this value is reached, the return value is the running factorial multiplied by the value of calling the same function submitting the running factorial number less one. As you can see, this is a short function that is easy to read, but a little difficult to follow.

To see a demonstration of all factorial values from one to 10, first make sure that you have typed SET SERVEROUTPUT ON at the SQL*Plus prompt. Then execute the code in Listing 15.2.

INPUT
Listing 15.2. Testing the recursive function with an anonymous PL/SQL block.

DECLARE
     v_test NUMBER := 10;
     v_Counter INTEGER ; -- Counter for For Loop
BEGIN
     FOR v_Counter IN 1..v_test LOOP
          DBMS_OUTPUT.PUT_LINE(`The factorial of ` ||
               v_Counter || ` is ` || factorial(v_Counter));
     END LOOP;
END;

Your output should look the same as the following:

OUTPUT

The factorial of 1 is 1
The factorial of 2 is 2
The factorial of 3 is 6
The factorial of 4 is 24
The factorial of 5 is 120
The factorial of 6 is 720
The factorial of 7 is 5040
The factorial of 8 is 40320
The factorial of 9 is 362880
The factorial of 10 is 3628800