More and still more on procedures and functions

Speaker Notes

Slide 1:

This is only an introduction to Procedure and Functions. Details would be appropriate for a more advanced course.

Slide 2:

This slide will start to look at PL/SQL code that has both an internal procedure and an internal function to try and clarify the differences.

The examples are from the notes!

This shows the DECLARE part of the PL/SQL code. The next slide will show the BEGIN.

Note that the name in the END statement is not required - it can help to clarify when there are multiple procedures and functions.

Slide 3:

The processing starts with the BEGIN on this page. Depending on the contribution amount that is inputed by the user, a code is set and the function is passed the code and the inputed amount. The results of the calculation in the function will be returned as f_contamt and stored in v_newcontamt.

After the function has returned the result. The AddDonProc is called and is passed the information needed to write a record. The information passed includes the calculated amount from the function.

The procedure then INSERTS a new record and the processing is complete.

Slide 4:

Note that the processing begins with the BEGIN of call_adddonproc6. This anonymous block has the code to get the calculation result from the function and call the procedure to do the INSERT.

Notice that the Insert made 11 rows - originally there were 10.

Slide 5:

The inserted row brings the total from 11 to 12.

Slide 6:

Notice that there is a testprfn and a testprfn2 which have the same layout and data.

Slide 7:

The begin of the main anonymous block starts the processing. It opens the cursor which fills it according to the SELECT. It then Fetches the first record and starts the loop. The function is executed and the result of the calculation is returned. Then the procedure is called and passed the result of the function and another piece of data. After the function, the file is updated. Notice it is updated even if no change has been made (not very efficient) - a code could have been passed back from the function to determine if the update should take place. The results are displayed and the next record is fetched and the loop is continued until all records in the cursor have been processed. For a more detailed analysis, please see the notes.

Slide 8:

For details on how results, see the next slide.

Slide 9:

This shows the results of the processing that is done in the function and in the procedure. The function does the calculation and the procedure does the checking.