PL provides easier-to-use FOR loop statements to automatically execute the OPEN, FETCH, and CLOSE statements and loop statements of cursors.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
When the program enters a loop, the FOR loop statement of the cursor automatically opens the cursor and fetches the first row of cursor data. When the program enters the next loop after processing the fetched data, the FOR loop statement of the cursor automatically fetches the next row of data for processing by the program. After all data rows are fetched from the result set, the loop is ended and the cursor is automatically closed.
The syntax is as follows:
FOR index_variable IN cursor_name[value[, value]...] LOOP
-- Cursor data processing code
END LOOP;
index_variable is the index variable implicitly declared by the FOR loop statement of the cursor. This index variable does not need to be declared in DECLARE. It is of the RECORD type. Its structure is the same as that of the result set returned by the cursor query statement.
The program can reference elements in this index variable to read the fetched cursor data. The names of elements in index_variable are the same as the column names in the select list of the cursor query statement. If calculation columns exist in the select list of the cursor query statement, you must specify aliases for these calculation columns so that the column data can be accessed 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
When you use the FOR loop statement to traverse cursor data, cursor parameters can also be passed. 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 also allows you to use subqueries in a FOR loop statement 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