PL/SQL provides the FOR loop statement, which automatically executes the OPEN, FETCH, and CLOSE statements for a cursor and the loop statement.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL-compatible mode.
When the loop starts, the FOR loop statement for the cursor automatically opens the cursor and retrieves the first row of data from the cursor. When the program processes the current row of data and enters the next iteration of the loop, the FOR loop statement for the cursor automatically retrieves the next row of data for the program to process. The loop ends when all rows of data in the result set are retrieved and the cursor is automatically closed.
The syntax is as follows:
FOR index_variable IN cursor_name[value[, value]...] LOOP
-- Process the cursor data.
END LOOP;
index_variable is an index variable implicitly declared by the FOR loop statement for the cursor. You do not need to declare it in the DECLARE section. The variable is of the RECORD type, and its structure is the same as that of the set of structures returned by the cursor query statement.
In the program, you can read the retrieved cursor data by referencing the elements of the index record variable. The names of the elements in index_variable are the same as those of the columns in the select list of the cursor query statement. If the select list of the cursor query statement contains computed columns, you must specify aliases for these columns before you can access the data of these columns by using the index variable in the FOR loop statement for the cursor.
Here is an example:
obclient> CREATE TABLE emp(
empno NUMBER(4,0),
empname VARCHAR(10),
job VARCHAR(10),
deptno NUMBER(2,0),
salary NUMERIC
);
Query OK, 0 rows affected
obclient> INSERT INTO emp VALUES (200,'Jennifer','AD_ASST',1,15000),(202,'Pat','MK_REP',2,12000),
(119,'Karen','PU_CLERK', 4,10000),(118,'Guy','PU_CLERK', 4,10000),
(201,'Michael','MK_MAN',3,9000);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
obclient> DECLARE
CURSOR c_emp IS SELECT empname, salary
FROM emp where rownum<5;
BEGIN
-- Implicitly open the cursor.
FOR r_emp IN c_emp LOOP
-- Implicitly execute the FETCH statement.
DBMS_OUTPUT.PUT_LINE(r_emp.empname||'''s salary is '||r_emp.salary );
-- Implicitly detect c_emp%NOTFOUND.
END LOOP;
END;
/
Query OK, 0 rows affected
Jennifer's salary is 15000
Pat's salary is 12000
Karen's salary is 10000
Guy's salary is 10000
When you use the FOR loop to traverse a cursor, you can also pass parameters to the cursor. Here is an example:
obclient> DECLARE
CURSOR c_emp (in_job varchar default 'AC_MGR' ) IS
SELECT empname, salary
FROM emp where job = in_job;
v_job emp.job%TYPE;
BEGIN
v_job := 'PU_CLERK';
FOR r_emp IN c_emp(v_job) LOOP
DBMS_OUTPUT.PUT_LINE('DEPT '||v_job||': '||r_emp.empname||'''s salary is '||r_emp.salary );
END LOOP;
END;
/
Query OK, 0 rows affected
DEPT PU_CLERK: Karen's salary is 10000
DEPT PU_CLERK: Guy's salary is 10000
PL/SQL also allows you to use subqueries in FOR loops to directly define cursors. Here is an example:
obclient> BEGIN
FOR r_emp IN (SELECT empname, salary FROM emp) LOOP
DBMS_OUTPUT.PUT_LINE(r_emp.empname||'''s salary is '||r_emp.salary );
END LOOP;
END;
/
Query OK, 0 rows affected
Jennifer's salary is 15000
Pat's salary is 12000
Karen's salary is 10000
Guy's salary is 10000
Michael's salary is 9000