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;

blank

 

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.

 401 views last month,  2 views today

About Joel Medeiros

blank
I am an experienced Business and Data Analyst, ETL and PL/SQL with over 13 years of experience. My technical skills are focus on Oracle mainly in developing with tools such as PL/SQL, ODI and RIB. You also can check all my post right here https://ittutorial.org/author/joel-medeiros/ Please, feel free to keep in touch by e-mail or Linkedin at: joel.medeiros01@gmail.com | https://www.linkedin.com/in/joel-medeiros/

Leave a Reply