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 mode.
When you enter the loop, the FOR loop statement for the cursor automatically opens the cursor and fetches 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 fetches the next row of data for the program to process. The loop ends after all rows of data in the result set are fetched 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 implicitly declared index variable for the FOR loop statement of 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.
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 those of the columns specified 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 column data by using the index variable in the FOR loop statement of 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
You can also pass cursor parameters when you use a FOR loop to traverse 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