MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Sample PL/SQL Cursor Loops

with 2 comments

A few of my students wanted me to post sample cursor loops, so here are examples of simple, FOR, and WHILE loops. There are a couple variations on approaches that demonstrate %TYPE and %ROWTYPE anchoring to data dictionary table definitions and local cursors.

Part of the idea behind these examples is to show the basic structure while mimicking the \G option of MySQL. The \G (Go) displays results as a list of column names and values by row. Ever since I discovered that in MySQL, I’ve hoped Oracle would incorporate something similar in their product. While discussing my wish list, I’d also like Oracle to make the FROM dual optional (like MySQL does) when selecting a string or numeric literal. You can find an implementation here, that leverages an example from Tom Kyte.

You can click any of the titles to view the code, which isn’t needed when you don’t have JavaScript enabled or the RSS expands them for you.

I’m sure this will help my students and hope it helps somebody else.

Written by maclochlainn

January 21st, 2010 at 10:53 pm

2 Responses to 'Sample PL/SQL Cursor Loops'

Subscribe to comments with RSS or TrackBack to 'Sample PL/SQL Cursor Loops'.

  1. There is a mistake on this page for Sample PL/SQL Cursor Loops.
    In the code a comment says:

        -- Exit when there aren't any more records in the cursor, with this you loop infinitely.
        EXIT WHEN c%NOTFOUND;

    Shouldn’t the comment say [without] not [with]?

    michael stokes

    5 Jun 10 at 1:01 pm

  2. Yes, that’s true. It’s fixed now. Thanks!

    maclochlainn

    6 Jun 10 at 10:39 am

Leave a Reply