PL/SQL For Practitioners – #2 Implicit Cursor FOR LOOP Statement

Hi,

Other structures when programming in PL/SQL are the FOR LOOP and CURSOR. In this post, I’m going to show how to use both of them together.

Well, there are plenty of ways to create a loop in PL/SQL, one of the most common using an implicit cursor. This is encouraged when the query is not to much complex. I like it because it is very simple, I don’t need to worry to open, fetch and close each cursor. Everything is controlled by the database engine.

The basic structure

When using an implicit cursor, you don’t have to OPEN CURSOR, FETCH CURSOR and CLOSE CURSOR. When the loop is finished, the current cursor is over automatically.

FOR <Loop_index> IN (<Select_statement>)

LOOP

   <Executable_statements>;

END LOOP;

Example

Please, use the file create_and_populate_tables_city_and_country file to create and populate the tables used in this article

BEGIN

FOR C1 IN (select ci.name as name
           from city ci
              , country co
          where ci.country_id = co.id
            and co.iso2 = 'BR')
LOOP

   dbms_output.put_line(C1.name);

END LOOP;

END;

Result

Dynamic values

It is also possible to add dynamic values to filter your data in the query inside of the cursor. Check out the following example.

DECLARE
  ISO VARCHAR2(3);
BEGIN ISO := 'BO'; 
   FOR C1 IN (select ci.name as name 
               from city ci ,
                   country co 
              where ci.country_id = co.id and co.iso2 = ISO) 
   LOOP 
      dbms_output.put_line(C1.name); 
   END LOOP; 
END;

Result

Well, this is pretty much enough to you write your own implicit cursors. Many more could be written, but I’m trying to keep these posts as simple as possible to be practical.

This post is part of a series about the most common structures in PL/SQL. You can check the rest of them in the link below.

PL/SQL for real world – 8 features that you have to know

Please write down in the comments any further questions you may have, or get in touch via e-mail and LinkedIn. I’ll be happy to help you.