jljl

CREATE OR REPLACE PROCEDURE P3_8_OVERZICHT_KLANTEN_ORDERS AS
  CURSOR C_CUSTOMER IS
    SELECT ID, NAME, REGION_ID
    FROM S_CUSTOMER
    ORDER BY ID;
 
  CURSOR C_ORDERS(P_CUSTOMER IN S_CUSTOMER.ID%TYPE) IS
    SELECT ID, DATE_ORDERED, TOTAL 
    FROM S_ORD
    WHERE CUSTOMER_ID = P_CUSTOMER
    ORDER BY ID;
BEGIN
 
  UPDATE S_CUSTOMER
  SET CREDIT_RATING = 'POOR'
  WHERE REGION_ID = 2;
 
  FOR R_CUST IN C_CUSTOMER LOOP
    DBMS_OUTPUT.PUT_LINE(' ');
    DBMS_OUTPUT.PUT_LINE(R_CUST.ID || ' ' || R_CUST.NAME);
    DBMS_OUTPUT.PUT_LINE('------------------------------');
    FOR R_ORD IN C_ORDERS(R_CUST.ID) LOOP
      DBMS_OUTPUT.PUT_LINE(R_ORD.ID || ' ' || R_ORD.DATE_ORDERED || ' ' || R_ORD.TOTAL);
      IF R_CUST.REGION_ID = 2 THEN
        UPDATE S_ORD
        SET PAYMENT_TYPE = 'CASH'
        WHERE ID = R_ORD.ID;
      END IF;
    END LOOP;
  END LOOP;
END P3_8_OVERZICHT_KLANTEN_ORDERS;