Using the Cursor FOR Loop in PL/SQL for Oracle Database 12c

By: Ian Abramson, Michael Abbey, Michelle Malcher, Michael Corey


You get a better sense of the power of the cursor in Oracle by combining it with a loop. The cursor FOR loop is the result of combining the select cursor with a FOR loop (we go into additional detail about this loop in the next section). This allows you to retrieve multiple rows from the database if your result set should do this. It also is simpler to program, and you don’t have to worry about opening or closing your cursor; Oracle handles all that within the loop. Let’s look at an example of the cursor FOR loop. The important lines have been set in boldface for you:

p175-01

NOTE

To reference columns during a FOR loop, use the name of the loop and concatenate it with the name of the column as defined within the cursor declaration. Thus, your result will be a variable named cursor_name.fieldname (in our example, we did this using the variable i.prod_name).

The cursor FOR loop is truly PL/SQL power in action. It provides you with the ability to easily move through the result set of a SELECT statement and perform the logic and manipulations you need to be successful.

We have just touched the surface of getting information from PL/SQL. You will need to get comfortable with debugging PL/SQL programs, which can be a very complex task. Experience has taught us that finding your errors can never be taken lightly or ignored. Using a simple facility such as DBMS_OUTPUT, you have a way of tracking the progress of your program.

NOTE

Oracle provides a couple of packages to analyze your PL/SQL programs. The first is DBMS_PROFILER. This package analyzes how your program is running and collects statistics on how long each line takes to execute. This helps you find code that runs slowly or inefficiently. When you need to access more advanced statistics about your programs, take the time to investigate this package and how to integrate it into your PL/SQL code. Because this is a beginner’s guide, we’ll only direct you to an important feature when you need its functionality. With the release of Oracle 12c, Oracle also introduced another function, UTL_CALL_STACK. This package provides an interface for PL/SQL programmers to obtain information about currently executing programs, including the subprogram name from dynamic and lexical stacks and the depths of those stacks. Individual functions return subprogram names, unit names, owner names, edition names, and line numbers for given dynamic depths. More functions return error stack information. Such information can be used to create more revealing error logs and application execution traces.

Having seen how to write and debug programs, you can now make those programs more complex.

Leave a Reply