Introduction to Procedures

Speaker Notes

Slide 1:

We have been looking at PL/SQL anonymous blocks up until now. An anonymous block is compiled and run each time the code is loaded. An anonymous block is stand-alone code that cannot be called by other blocks. The alternative to writing anonymous blocks is to write a procedure, a function, a package or a trigger which can be called from other blocks. Procedures and functions can be grouped together as subprograms.

Slide 2:

Again, contrast the structure of a named block or subprogram such as a procedure or function with the anonymous blocks that we have been coding.

Please check a text or Web site for additional information.

In this example, adddonation is the name of the procedure.

Slide 3:

This anonymous block calls AddDonation and passes it the variables v_idno, v_driveno, v_contdate, v_contamt.

Note that in AddDonation they come in as p_idno, p_driveno, p_contdate, p_contamt. The names can be the same in the two locations or they can be different.

The procedure is called and the record is added to the table from the procedure.

Slide 4:

Parameter modes:

IN sends the parameters from the calling program to the procedure. This is the default mode illustrated on the previous two slides.

OUT sends the parameters from the procedure back to the calling program.

IN OUT passes to and receives from the procedure.

In this procedure we see p_in which will receive input from the call procedure, p_out which will hold information to be sent back to the call procedure and p_inout which will receive information and return information.

Slide 5:

The three numbers that are inputted are 15, 72, 99. Notice that entering something for v_out which is being passed to the procedure that has defined the receiving field as OUT does no good. The data cannot be read because p_out in the procedure is defined as OUT.

Note that in the notes there is no INSERT in this anonymous block, therefore only one line appears after execution.

Slide 6:

15, 72, and 99 are received by the anonymous block. v_in, v_out and v_inout are passed. The 15 and 99 will be received but the 72 will not be received since the p_out is defined as OUT. Note that v_out has to be part of the call because we want to receive information back in v_out.

Slide 7:

I have now modified the p_inout to have a different value.

Slide 8:

First I will run through the exercises in the notes.

See the next slide for notes on the processing.

Slide 9:

Notice that the INSERT in the named procedure is done before the INSERT in the anonymous block.

15, 72 and 99 are passed to the procedure. In fact only 15 and 99 are taken in because p_out cannot be read - it is an output parameter.

In the procedure, p_out is calculated using p_in. V_out is in fact set so that it can receive data - if there is data in it the v_out data is ignored.

Needless to say, in this example, I did not need to take in the 72 for v_out unless I wanted it in the anonymous block. If I had done the INSERT before the call to the procedure, the 72 would have shown up in the output record/row.

Slide 10:

Note I changed the calculation of p_out to include p_out as part of the calculation. This is assuming that data is received in p_out and that it can be used in a calculation. However because p_out was defined as OUT it cannot receive data or be used in the calculating part of the assign. It can only be used to receive an answer. The resulting error is shown.

Also remember that I had to use @ proc_calc to have the procedure created with the code change. The warning came back. To see the errors I used show errors.

Slide 11:

This procedure was created in ORACLE at the SQL prompt. I usually create them in the editor because I am prone to typos, but I wanted to show that it could be done. This is also a good technique for testing a procedure independently.

Note that after END, I added the procedure name - this is entirely optional but is frequently used for documentation and clarity.

This procedure can be saved under the name in_yrgoal and you can use the editor to make changes.

Slide 12:

Again remember that show errors can be used to see the specific errors involved in the compilation of the procedure.

The fix and execution are shown on the next slides.

Slide 13:

Note that the procedure can be saved under a name. In this code I am simply editing the last code at the SQL prompt.

While I was in the editor, I saved the procedure under the name out_yrgoaletc under the directory/folder Orawin95 and the subdirectory/folder bin. This is the default save area.

Slide 14:

The information in this slide is really beyond the scope of this course. I am presenting it because it clearly shows the difference between something defined as IN and OUT.