Example 6-18 Implicit Cursor FOR LOOP Statement
BEGIN FOR item IN ( SELECT last_name, job_id FROM employees WHERE job_id LIKE '%CLERK%' AND manager_id > 120 ORDER BY last_name ) LOOP DBMS_OUTPUT.PUT_LINE ('Name = ' || item.last_name || ', Job = ' || item.job_id); END LOOP; END; /
Result:
Name = Atkinson, Job = ST_CLERK Name = Bell, Job = SH_CLERK Name = Bissot, Job = ST_CLERK ... Name = Walsh, Job = SH_CLERK
Example 6-19 is like Example 6-18, except that it uses an explicit cursor FOR
LOOP
statement.
Example 6-19 Explicit Cursor FOR LOOP Statement
DECLARE CURSOR c1 IS SELECT last_name, job_id FROM employees WHERE job_id LIKE '%CLERK%' AND manager_id > 120 ORDER BY last_name; BEGIN FOR item IN c1 LOOP DBMS_OUTPUT.PUT_LINE ('Name = ' || item.last_name || ', Job = ' || item.job_id); END LOOP; END; /
Result:
Name = Atkinson, Job = ST_CLERK Name = Bell, Job = SH_CLERK Name = Bissot, Job = ST_CLERK ... Name = Walsh, Job = SH_CLERK
Example 6-20 declares and defines an explicit cursor that accepts two parameters, and then uses it in an explicit cursor FOR
LOOP
statement to display the wages paid to employees who earn more than a specified wage in a specified department.
Example 6-20 Passing Parameters to Explicit Cursor FOR LOOP Statement
DECLARE CURSOR c1 (job VARCHAR2, max_wage NUMBER) IS SELECT * FROM employees WHERE job_id = job AND salary > max_wage; BEGIN FOR person IN c1('ST_CLERK', 3000) LOOP -- process data record DBMS_OUTPUT.PUT_LINE ( 'Name = ' || person.last_name || ', salary = ' || person.salary || ', Job Id = ' || person.job_id ); END LOOP; END; /
Result:
Name = Nayer, salary = 4065.6, Job Id = ST_CLERK Name = Mikkilineni, salary = 3430.35, Job Id = ST_CLERK Name = Landry, salary = 3049.2, Job Id = ST_CLERK ... Name = Vargas, salary = 3176.25, Job Id = ST_CLERK
In Example 6-21, the implicit cursor FOR
LOOP
references virtual columns by their aliases, full_name
and dream_salary
.
Example 6-21 Cursor FOR Loop References Virtual Columns
BEGIN FOR item IN ( SELECT first_name || ' ' || last_name AS full_name, salary * 10 AS dream_salary FROM employees WHERE ROWNUM <= 5 ORDER BY dream_salary DESC, last_name ASC ) LOOP DBMS_OUTPUT.PUT_LINE (item.full_name || ' dreams of making ' || item.dream_salary); END LOOP; END; /
Result:
Michael Hartstein dreams of making 143325 Pat Fay dreams of making 66150 Jennifer Whalen dreams of making 48510 Douglas Grant dreams of making 31531.5 Donald OConnell dreams of making 31531.5