----1----
DECLARE
TYPE t_tab IS TABLE OF NUMBER(2) INDEX BY PLS_INTEGER;
tab t_tab;
dep_id NUMBER(2) := 40;
dep_name VARCHAR2(50);
CURSOR c(c_dep dep_id%TYPE) IS SELECT o.order_id,SUM(oi.quantity*oi.unit_price) AS VALUE
FROM orders o, order_items oi, employees e WHERE o.order_id=oi.order_id AND o.sales_rep_id=e.employee_id AND e.department_id=c_dep
GROUP BY o.order_id ORDER BY VALUE DESC;
BEGIN
FOR i IN 0..4 LOOP
tab(i):=dep_id+i*10;
SELECT department_name INTO dep_name FROM departments WHERE department_id=tab(i);
DBMS_OUTPUT.put_line(tab(i)||' '||dep_name);
FOR r IN c(tab(i)) LOOP
IF c%ROWCOUNT > 0 THEN
DBMS_OUTPUT.put_line(r.order_id||' '||r.VALUE);
ELSE
DBMS_OUTPUT.put_line('Nothing');
END IF;
END LOOP;
END LOOP;
END;
/


----2----
DECLARE
TYPE t_table IS TABLE OF NUMBER(4) INDEX BY PLS_INTEGER;
tab t_table;
YEAR NUMBER(4) := 1999;
CURSOR c(c_year year%TYPE) IS SELECT o.order_id,SUM(oi.quantity*oi.unit_price) AS VALUE
FROM orders o, order_items oi WHERE o.order_id=oi.order_id
AND EXTRACT(YEAR FROM o.order_date)=c_year
GROUP BY o.order_id
ORDER BY VALUE DESC;
BEGIN
FOR i IN 0..4 LOOP
tab(i):=YEAR+i;
DBMS_OUTPUT.put_line('Year '||tab(i));
FOR n IN c(tab(i)) LOOP
IF c%rowcount > 0 THEN
DBMS_OUTPUT.put_line(n.order_id||' '||n.VALUE);
ELSE
DBMS_OUTPUT.put_line('Nothing');
END IF;
END LOOP;
END LOOP;
END;
/