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.
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.