aezrae

CREATE OR REPLACE
PROCEDURE P2_4_VERLAAGMINSTVERKOCHTE AS 
  TYPE TYPE_REC_SKI IS  RECORD (
      PRODUCTID S_INVENTORY.PRODUCT_ID%TYPE,
      TOTAL_QUANTITY NUMBER );
 
  TYPE type_col_ski IS
  TABLE OF type_rec_ski INDEX BY PLS_INTEGER;
  t_ski type_col_ski;
BEGIN
  SELECT i.product_id,  SUM(i.quantity) quantity
  BULK COLLECT INTO t_ski
  FROM s_item i, s_product p
  WHERE i.product_id = p.id AND p.name LIKE '%Ski%'
  GROUP BY i.product_id
  ORDER BY quantity ASC;
 
  FOR i IN 1..t_ski.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE (t_ski(i).productid || ' ' || t_ski(i).total_quantity);
    UPDATE S_INVENTORY
    SET reorder_point = reorder_point * 0.8, max_in_stock    = max_in_stock  * 0.8
    WHERE product_id  = t_ski(i).productid;
    EXIT WHEN i = 10;
  END LOOP;
END P2_4_VERLAAGMINSTVERKOCHTE;