Posted By

wh0emPah on 03/31/10


Tagged


Versions (?)

zerze


 / Published in: PL/SQL
 

  1. CREATE OR REPLACE PROCEDURE P2_1_OVERZICHT_PROD_WAREHOUSE AS
  2.  
  3. TYPE TYPE_REC_PRODUCTS IS RECORD(
  4. PID S_PRODUCT.ID%TYPE,
  5. PNAME S_PRODUCT.NAME%TYPE,
  6. WHID S_INVENTORY.WAREHOUSE_ID%TYPE,
  7. WHCITY S_WAREHOUSE.CITY%TYPE
  8. );
  9.  
  10. TYPE TYPE_COLL_PRODUCTS
  11. IS TABLE OF TYPE_REC_PRODUCTS;
  12. T_PRODUCTS TYPE_COLL_PRODUCTS;
  13. V_OLDWHID S_WAREHOUSE.ID%TYPE DEFAULT -1;
  14.  
  15. SELECT PRODUCT_ID,
  16. (SELECT NAME FROM S_PRODUCT WHERE ID= PRODUCT_ID) NAME, WAREHOUSE_ID,
  17. (SELECT CITY FROM S_WAREHOUSE WHERE ID=WAREHOUSE_ID)
  18. BULK COLLECT INTO T_PRODUCTS
  19. FROM S_INVENTORY
  20. ORDER BY WAREHOUSE_ID, NAME;
  21.  
  22. FOR i IN 1..T_PRODUCTS.COUNT
  23. IF V_OLDWHID != T_PRODUCTS(I).WHID THEN
  24. DBMS_OUTPUT.PUT_LINE(' ');
  25. DBMS_OUTPUT.PUT_LINE(T_PRODUCTS(I).WHID || ' ' || T_PRODUCTS(I).WHCITY);
  26. DBMS_OUTPUT.PUT_LINE('-----------');
  27. DBMS_OUTPUT.PUT_LINE(T_PRODUCTS(I).PID || ' ' || T_PRODUCTS(I).PNAME);
  28. V_OLDWHID := T_PRODUCTS(I).WHID;
  29. END P2_1_OVERZICHT_PROD_WAREHOUSE;

Report this snippet  

You need to login to post a comment.