如果通过循环来处理结果集,并且对每一行都执行了另一个查询,则可以通过移除循
环中的第二个查询,并使之成为第一个查询的子查询。普通的子查询对每个表进行评估,
相关子查询对每一行进行评估。
GBase 8s PL/SQL手册
南大通用数据技术股份有限公司
- 109 -
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;
--Result:
--Department = IT, staff = 5
--Department = Finance, staff = 6
--Department = Purchasing, staff = 6
--Department = Sales, staff = 34
--Department = Shipping, staff = 45
相关子查询
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;
--Result:
--Making above-average salary = Hartstein
GBase 8s PL/SQL手册
南大通用数据技术股份有限公司
- 110 -
--Making above-average salary = Raphaely
--Making above-average salary = Bell
--...
--Making above-average salary = Higgins