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 <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.
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;
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.
714 views last month, 3 views today