PL/SQL provides the FOR loop statement, which automatically executes the OPEN, FETCH, and CLOSE statements for a cursor.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
When the loop starts, the FOR loop statement automatically opens the cursor and fetches the first row of data. When the program processes the current row of data and moves to the next iteration, the FOR loop statement automatically fetches the next row of data. After all rows of data in the result set are fetched, the loop ends, and the cursor is automatically closed.
The syntax is as follows:
FOR index_variable IN cursor_name[value[, value]...] LOOP
-- Process cursor data
END LOOP;
index_variable is an implicitly declared index variable in the FOR loop statement. You do not need to declare it in the DECLARE section. It is of the RECORD type and has the same structure as the result set returned by the cursor query statement.
You can read the fetched cursor data by referencing the elements of the index record variable. The names of the elements in index_variable are the same as the column names in the select list of the cursor query statement. If the select list contains computed columns, you must specify aliases for these columns before you can access their data using the index variable in the FOR loop statement.
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 check 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
You can also pass cursor parameters when you use FOR to iterate over a 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