Posted By

wh0emPah on 04/01/10


Tagged


Versions (?)

jljl


 / Published in: PL/SQL
 

  1. CREATE OR REPLACE PROCEDURE P3_8_OVERZICHT_KLANTEN_ORDERS AS
  2. CURSOR C_CUSTOMER IS
  3. SELECT ID, NAME, REGION_ID
  4. FROM S_CUSTOMER
  5. ORDER BY ID;
  6.  
  7. CURSOR C_ORDERS(P_CUSTOMER IN S_CUSTOMER.ID%TYPE) IS
  8. SELECT ID, DATE_ORDERED, TOTAL
  9. FROM S_ORD
  10. WHERE CUSTOMER_ID = P_CUSTOMER
  11. ORDER BY ID;
  12.  
  13. UPDATE S_CUSTOMER
  14. SET CREDIT_RATING = 'POOR'
  15. WHERE REGION_ID = 2;
  16.  
  17. FOR R_CUST IN C_CUSTOMER LOOP
  18. DBMS_OUTPUT.PUT_LINE(' ');
  19. DBMS_OUTPUT.PUT_LINE(R_CUST.ID || ' ' || R_CUST.NAME);
  20. DBMS_OUTPUT.PUT_LINE('------------------------------');
  21. FOR R_ORD IN C_ORDERS(R_CUST.ID) LOOP
  22. DBMS_OUTPUT.PUT_LINE(R_ORD.ID || ' ' || R_ORD.DATE_ORDERED || ' ' || R_ORD.TOTAL);
  23. IF R_CUST.REGION_ID = 2 THEN
  24. UPDATE S_ORD
  25. SET PAYMENT_TYPE = 'CASH'
  26. WHERE ID = R_ORD.ID;
  27. END P3_8_OVERZICHT_KLANTEN_ORDERS;

Report this snippet  

You need to login to post a comment.