PLSQL Language Referenc-PL/SQL靜態SQL-帶有子查詢的查詢結果集處理

LuiseDalian發表於2014-03-16
SQL

帶有子查詢的查詢結果集處理

如果通過迴圈來處理結果集，並且對於每一行都執行另一個查詢，則可以通過移除迴圈中的第2個查詢，並使第2個查詢成為第1個查詢的子查詢。

--子查詢位於父查詢的from子句中

DECLARE

    CURSOR c1 IS

    SELECT t1.department_id, department_name, staff

    FROM departments t1,

         ( SELECT department_id, COUNT(*) AS staff

           FROM employees

           GROUP BY department_id

         ) t2

    WHERE (t1.department_id = t2.department_id) AND staff >= 5

    ORDER BY staff;

 

BEGIN

   FOR dept IN c1

   LOOP

        DBMS_OUTPUT.PUT_LINE ('Department = '

        || dept.department_name || ', staff = ' || dept.staff);

   END LOOP;

END;

 

普通的子查詢對每個表進行計算，相關子查詢對每一行進行計算。

DECLARE

    CURSOR c1 IS

    SELECT department_id, last_name, salary

    FROM employees t

    WHERE salary > ( SELECT AVG(salary)

                     FROM employees

                     WHERE t.department_id = department_id

                   )

    ORDER BY department_id, last_name;

BEGIN

    FOR person IN c1

    LOOP

        DBMS_OUTPUT.PUT_LINE('Making above-average salary = ' || person.last_name);

    END LOOP;

END;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17013648/viewspace-1122764/，如需轉載，請註明出處，否則將追究法律責任。

相關文章