Posted By

wh0emPah on 04/01/10


Tagged


Versions (?)

jljl


 / Published in: PL/SQL
 

  1. CREATE OR REPLACE PROCEDURE P3_9_VOORRAAD_PER_PRODUCT AS
  2. CURSOR C_PRODUCTS IS
  3. SELECT ID, NAME, SUGGESTED_WHLSL_PRICE
  4. FROM S_PRODUCT
  5. WHERE SUGGESTED_WHLSL_PRICE > 100;
  6.  
  7. CURSOR C_WAREHOUSES(P_PRODUCT_ID IN S_PRODUCT.ID%TYPE) IS
  8. SELECT WAREHOUSE_ID, AMOUNT_IN_STOCK, (SELECT CITY
  9. FROM S_WAREHOUSE
  10. WHERE ID=WAREHOUSE_ID) CITY
  11. FROM S_INVENTORY
  12. WHERE PRODUCT_ID = P_PRODUCT_ID;
  13. FOR R_PRODUCT IN C_PRODUCTS LOOP
  14. DBMS_OUTPUT.PUT_LINE(' ');
  15. DBMS_OUTPUT.PUT_LINE(R_PRODUCT.ID || ' ' || R_PRODUCT.NAME ||' ' || R_PRODUCT.SUGGESTED_WHLSL_PRICE);
  16. DBMS_OUTPUT.PUT_LINE('--------------------------');
  17. FOR R_WAREHOUSE IN C_WAREHOUSES(R_PRODUCT.ID) LOOP
  18. DBMS_OUTPUT.PUT_LINE(R_WAREHOUSE. WAREHOUSE_ID || ' ' || R_WAREHOUSE.CITY || ' ' || R_WAREHOUSE.AMOUNT_IN_STOCK);
  19. END P3_9_VOORRAAD_PER_PRODUCT;

Report this snippet  

You need to login to post a comment.