We begin to explain PL/SQL cursors. We’ll look at explicit cursors, their attributes, and coupling variables.
In these we have seen the fundamentals of the PL/SQL language, well, from now on we will study the use of this language to work with the Oracle manager. We will start with the use of cursors.
So far we have used , when we returned the result of a select via the into clause to a variable. But this is a problem when the result of a subquery returned several rows, because this would give us an error when executing the query.
So that we do not get an error in these cases we must use explicit cursors.
explicit cursors
Explicit cursors are used when we have queries that return more than one row.
We have 4 basic operations to work with an explicit cursor.
- Cursor declaration: we have to declare it in the declaration area, with the following format:
CURSORIS - Cursor opening: It must be placed in the instructions area, with the following format:
OPEN
;
Doing so automatically executes the select statement and its results are stored in internal memory structures handled by the cursor.
- Information collection: To recover the information previously stored in the internal memory structures we have to use the following format:
FETCH
INTO { | };
If we have a single variable that collects the data from all the columns, the format of the variable would be as follows:
%ROWTYPE;
If we have a list of variables, each one will pick up the corresponding column from the select clause, so they will be of the same type as the columns.
- – Cursor closure:
CLOSE
;
Now, let’s see an example of using explicit cursors:
DECLARE
CURSOR C1 IS SELECT first name, last name FROM arbitrator;
Vname VARCHAR2(12);
Vape VARCHAR2(20);
BEGIN
OPENC1;
LOOP
FETCH C1 INTO Vnom, Vape;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(Vnom || ” || Vapen);
END LOOP;
CLOSE C1;
END;
If we look closely, in the declaration of the explicit cursors we do not use the INTO clause, which was used in the implicit cursors.
You can also see that after the FETCH we have checked if it returns values with the EXIT line. It is something important because if it does not return anything, the LOOP will be interrupted.
cursor attributes
To know details of the cursor situation we have 4 attributes:
- %FOUND: returns true if the last FETCH has retrieved some value; otherwise it returns false; If the cursor is not open, it returns an error.
- %NOTFOUND: does just the opposite of the previous one.
- %ROWCOUNT: returns the number of rows retrieved so far.
- %ISOPEN: returns true if the cursor is open.
Now let’s see an example of using %ROWCOUNT:
DECLARE
CURSOR C1 IS SELECT name from footballer WHERE Cod=’e1′;
Vname VARCHAR2(15);
BEGIN
OPENC1;
LOOP
FETCH C1 INTO Vnom;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (C1%ROWCOUNT || Vnom);
END LOOP;
CLOSE C1;
END;
Coupling variables in cursor handling
In the following example we can see that the WHERE clause includes a variable that should have been declared previously. These types of variables are called coupling variables. The program will replace it with its value at the moment the cursor is opened, and the rows will be selected according to that value. Even if that value changes during data retrieval with FETCH, the rowset containing the cursor will not change.
The example shows us the footballers of any team.
CREATE OR REPLACE PROCEDURE see_footballers_by_teams(codeq VARCHAR2)
ES
Vequi VARCHAR2(3);
CURSOR C1 IS SELECT name from footballer where codeq=Vequi;
Vname VARCHAR(15);
BEGIN
vequi:=codeq;
OPENC1;
FETCH C1 INTO vnom;
WHILE C1%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(Vnom);
FETCH C1 INTO Vnom;
END LOOP;
CLOSE C1;
END;
Facebook Comments
Disqus