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
| São Paulo Rio de Janeiro Belo Horizonte Porto Alegre Brasília Recife Fortaleza Salvador Curitiba Campinas Belém Goiânia Manaus Santos Vitória Niterói Vila Velha Vila Velha Maceió Natal |
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
| Santa Cruz La Paz Sucre Cochabamba Oruro Santa Ana de Yacuma Quillacollo Potosí Tarija Trinidad Cobija Santa Cruz de la Sierra Montero Riberalta Villazón Bermejo Guayaramerín |
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.
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.