PL/SQL For Practitioners – #3 Explicit Cursor FOR LOOP Statement

Hi there,

Well, cursors are widely used when programming in PL/SQL, so it is better to master it. In this article, I’m going to cover the basic structure for explicit cursors in FOR LOOP statement.

Basically, these cursors have to be declared with a SELECT statement in the declaration section of a PL/SQL unit before using it. With the declaration, you will be able to access this cursor as a regular variable.

Cursor Declaration

CURSOR <Cursor_name>
IS
  <Select_statement>;

How to use in a LOOP

FOR <Loop_index> IN (<Cursor_name>)
LOOP
   <Executable_statements>;
END LOOP;

When using this syntax, you don’t need to worry about open, fetch and close each cursor. Once the LOOP is ended, the cursor is gone.

Example 1

To go to the following examples, you can use this script to create and populate the used tables create_and_populate_tables_city_and_country

DECLARE 
   CURSOR city_names is 
   select ci.name as name
             from city ci
                , country co 
            where ci.country_id = co.id
              and co.iso2 = 'CA';     
BEGIN
  FOR C1 IN city_names
  LOOP
  
     dbms_output.put_line(C1.name);
  
  END LOOP;
END;

Result

Example 2 – With parameters

The following example contains a parameter in the cursor city_names. 

DECLARE 
   ISO VARCHAR2(3);
   CURSOR city_names(p_iso2 varchar2) is 
   select ci.name as name
             from city ci
                , country co 
            where ci.country_id = co.id
              and co.iso2 = p_iso2;
              
BEGIN

  ISO := 'BO';
  
  FOR C1 IN city_names(ISO)
  LOOP
  
     dbms_output.put_line(C1.name);
  
  END LOOP;
END;

Example 3 – With OPEN/FETCH/CLOSE CURSOR

The following example is exactly the same as the previous one. However, in this example, I’m using the commands OPEN to initiate the cursor, FETCH to retrieve the data finally CLOSE to finishes the cursor.

DECLARE 
   V_ISO VARCHAR2(3);
   V_CITY_NAME VARCHAR2(255);

   CURSOR city_names(P_ISO2 VARCHAR2) is 
      select ci.name as name
        from city ci
           , country co 
       where ci.country_id = co.id
         and co.iso2 = p_iso2;
BEGIN

   V_ISO := 'BO';
   OPEN city_names(V_ISO);

   IF city_names%isopen THEN
      LOOP 
         FETCH city_names INTO V_CITY_NAME;
         EXIT WHEN city_names%notfound;
         dbms_output.put_line(V_CITY_NAME); 
      END LOOP;
      CLOSE city_names;
   END IF;
END;

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.