More on Procedures (Internal/Local Procedures)

Speaker Notes

Slide 1:

This presentation includes information from the notes and additional examples.

An internal or local procedure (there can be internal or local functions as well) is defined in the PL/SQL block in the DECLARE. Note that the PL/SQL block can be an anonymous block or a named block.

Please contrast these examples with the examples of stored procedures discussed in the presentation Introduction to Procedures.

Slide 2:

Note the location here! This is an internal procedure that has been embedded in the DECLARE portion of the PL/SQL block. The internal or local procedure must be the last thing in the DECLARE. When the call is made from the block BEGIN, the declared procedure is executed. Notice the way the execution works. The block BEGIN is executed. If the user entry stored in v_contamt is greater than 20, then the PROCEDURE is executed. When the procedure is complete control returns to the command after the call, which ends the if and ends the anonymous block.

Note that in the anonymous block I named the variables with a v in front of the name. These are the names that are passed to the internal procedure. They are stored in the procedure variables, which have a p in front of the name. When the INSERT is done in the internal procedure, the names used are the names defined in the internal procedure.

Slide 3:

See the previous slide for the complete code in the anonymous block.

Slide 4:

The calculation is being done in the procedure but it is using a variable defined in the main block. Data defined in the main block is available to an internal procedure.

Slide 5:

The three inputted values are passed to the internal procedure. One of them is used to calculate a new value that is stored in the external procedure.

The output takes two of the passed value and the calculation stored in the output procedure and Inserts them into the table.

Slide 6:

In fact, passing something to this subroutine was not necessary. You could have done the processing using the data in the anonymous block.

Note that this example is not in the notes.

Slide 7:

Note some of the records are slightly different than in previous examples - tried too many things!. The last record added here is the one we are interested in.

In this example nothing was passed to the internal procedure - it was simply called. It used the variable names defined within the declare to calculate a new contribution amount and write the idno, driveno and contribution amount to the table.

Note the variety of other approaches covered in the notes.

Slide 8:

In the anonymous block, I have declared a group of variables and an internal or local named procedure. The processing starts with the block begin which selects a record according to user entered identification number. The drive number for that record is then passed to the procedure and within the procedure the drivename is retrieved. When the procedure is complete, processing returns to the main block and the information from the variables (including the retrieved drivename) is inserted in a new table.

Slide 9:

The SELECT in the begin uses the user entered idno to select a record, putting information from the record into the variables.

The call to get_drive_name passes the driveno

The SELECT within the procedure gets the drivename from the drive file and stores it in the variables.

When the procedure is complete control returns to the line after the call and the INSERT is executed which writes a record to the new file containing the information in the variables.

Slide 10:

This anonymous block with a named procedure accomplishes the same thing that the anonymous block with the internal/local procedure on the previous slide accomplished.

Slide 11:

Again please note that if you want records in a particular order in the table, use the order by clause.

Hopefully these examples help to clarify the difference between a named procedure and an internal/local procedure.

Slide 12:

This slide uses a cursor to process all the records individually and write each one into a new table. Clearly individual processing based on IF statements etc. could be included.

Note that the routine named_get_drive_name has not changed from the routine used in the previous slide.

Note that as each record is processed, the procedure named-get_drive_name is called and the drive number is passed to it and the drive name is passed back.

Slide 13:

Again note that all records are processed.

Slide 14:

This uses a cursor and an internal/local procedure to process the each record in the donation_unique file individually.

The results are written to the donordrive.

Slide 15:

This is the output that is produced from the code on the previous slide.

Note that because the procedure is internal it does not have to be created.