Cursors in PL/SQL & Cursor Example & Continuation of first cursor example

Speaker Notes

Slide 1:

Oracle supports the implicit cursor which is created and managed by PL/SQL when the block processes a SQL statement and the explicit cursor which is created and managed by the programmer/developer.

This presentation includes information from three handouts:

Cursors in PL/SQL

Cursor Example

Continuation of first cursor example.

Slide 2:

An explicit cursor is the key to managing multiple records one record at a time in your PL/SQL code.

Slide 3:

The cursor structure allows you to process multiple records, one record at a time within PL/SQL.

On the next slide will look at the data and on the following slide we will look at the components of the block.

Slide 4:

As you can see, information from the donor table is now in the donor_part table.

When the FETCH after the INSERT (the last command in the loop) does not retrieve data the LOOP will end.

Slide 5:

Note that the variable names use TYPE so that they have the columns on the donor table that is being used as input to the block code.

Notice that there is an initial FETCH that fetches the first record. Then the processing loop is entered and the record gets processed. There is another FETCH at the bottom of the processing loop which will get another record.

Slide 6:

Two of the records on donor have a year goal of 50 and one has a null year goal. These three records were not inserted because of the IF statement.

Slide 7:

Because of the WHERE clause only records with a yearly goal greater than 50 are in the cursor. Therefore only those records get processed to the new table.

Slide 8:

Please be sure to check the notes for cursor attributes. They are discussed in the notes but not in this presentation

Slide 9:

As stated, I want to only process 4 records but in case there are not 4 records, good programming means I also test for essentially end of file - in this case, no more records in the cursor. So if row count is > 4 OR there are no more records, processing will terminate.

When I put these conditions in a WHILE loop, I have to phrase them as the conditions to keep processing. Therefore, I say keep processing while rowcount is < 5 AND there are still records.

Slide 10:

This shows the logic discussed on the previous slide. Notice that the same objectives are accomplished but one expresses the logic with the OR logic that decides when to stop and the other expresses the logic with the AND logic that decides when to process.

Start of Cursor Example:

Slide 11:

This slide starts looking at the handout cursor example.

The code that I have written will take the first drive from the drive table, go to the donation table and accumulate all of the contributions to that drive and write the results as a row/record on the cont_info table.

It will then go back and read the next row/record from the drive table and go to the donation table and accumulate all of the contributions for that drive and write to the cont_info table.

Processing will stop when all of the drives from the drive table have been processed.

The check for donation_cursor%ISOPEN is not needed for the logic. It is used to demonstrate the command.

Slide 12:

The two tables that are used in the next cursor example are drive and donor. This slide shows the data.

Slide 13:

Note, in this demonstration I am not showing the line that I displayed from the inner loop as a tool to help with understanding.

The drive_cursor has 100 Kids Shelter and the donation_cursor has records with a drive number of 100.

Slide 14:

There are two donations to drive 200. They are for 40 and 35 so the total of 75 gets written to the new table.

Slide 15:

There are two donations to drive 300, each of them is for 10.

Slide 16:

Since there are no donations to drive 400, the code is exited after the first attempt at the FETCH.

Slide 17:

This starts continuation of first cursor example (but this example does not appear on the notes).

This example does not take full advantage of the power of passing a parameter. In fact processing could be done prior to the opening of the donation_cursor and a wide variety of information could be passed.

Note the output is the same as in previous examples that did not use the parameter.

Slide 18:

This slide starts with continuation of first cursor example (this one refers to the first example in the notes). The information being covered is a cursor with parameters. The open statement passes parameter values to the cursor that will be used in the query when it is executed. For each parameter you define in the cursor, there must be an actual parameter in the OPEN statement to correspond to it.

The select statement used with a parameter does not have the into clause.

Note the output is the same as previous examples with the dbms lines and the rows inserted into the table.

Slide 19:

Note that two parameters are passed to the donation_cursor when it is opened and the WHERE clause tests both of the parameters. Therefore only rows that meet the criteria are put in the cursor.

Slide 20:

The output from this processing is shown on the next slide.

Slide 21:

This shows the processing and output from cursor9.

Slide 22:

This slide shows the same logic but it uses the WHILE loop instead of the simple loop.

The output is the same as the output from the other loops where all records are processed.