Example 6-19 Using a Subquery in a Cursor

DECLARE
  CURSOR c1 IS
-- main query returns only rows where the salary is greater than the average
    SELECT employee_id, last_name FROM employees 
      WHERE salary > (SELECT AVG(salary) FROM employees);
  CURSOR c2 IS
-- subquery returns all the rows in descending order of salary
-- main query returns just the top 10 highest-paid employees
   SELECT * FROM
     (SELECT last_name, salary FROM employees ORDER BY salary DESC, last_name)
     WHERE ROWNUM < 11;
BEGIN
  FOR person IN c1
  LOOP
    DBMS_OUTPUT.PUT_LINE('Above-average salary: ' || person.last_name);
  END LOOP;
  FOR person IN c2
  LOOP
    DBMS_OUTPUT.PUT_LINE('Highest paid: ' || person.last_name || 
                         ' $' || person.salary);
  END LOOP;
-- subquery identifies a set of rows to use with CREATE TABLE or INSERT
END;
/

Example 6-19 Using a Subquery in a Cursor

DECLARE
  CURSOR c1 IS
-- main query returns only rows where the salary is greater than the average
    SELECT employee_id, last_name FROM employees 
      WHERE salary > (SELECT AVG(salary) FROM employees);
  CURSOR c2 IS
-- subquery returns all the rows in descending order of salary
-- main query returns just the top 10 highest-paid employees
   SELECT * FROM
     (SELECT last_name, salary FROM employees ORDER BY salary DESC, last_name)
     WHERE ROWNUM < 11;
BEGIN
  FOR person IN c1
  LOOP
    DBMS_OUTPUT.PUT_LINE('Above-average salary: ' || person.last_name);
  END LOOP;
  FOR person IN c2
  LOOP
    DBMS_OUTPUT.PUT_LINE('Highest paid: ' || person.last_name || 
                         ' $' || person.salary);
  END LOOP;
-- subquery identifies a set of rows to use with CREATE TABLE or INSERT
END;
/