Oefening6Package

CREATE OR REPLACE
PACKAGE OEFENING6PACKAGE AS
  -- %%%%%%%%%%%%%%%Types%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  -- Step 6.1
  TYPE product_rec_type IS RECORD (
    product_id      OE.product_information.product_id%TYPE,
    product_name    OE.product_information.product_name%TYPE,
    new_price       OE.product_information.list_price%TYPE
  );
  -- Step 6.2
  TYPE product_tab_type IS TABLE OF product_rec_type;
  -- %%%%%%%%%%%%%%%Procedures%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  PROCEDURE productenUitDeInventaris;
  PROCEDURE updatePrijzen(p_category_id IN OE.categories.category_id%TYPE, p_number IN NUMBER);
  PROCEDURE printproductabtype(tab IN product_tab_type);
  -- %%%%%%%%%%%%%%%Functies%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  FUNCTION checkCategory(category_p_id OE.categories.category_id%TYPE) RETURN BOOLEAN;
  FUNCTION checkEnough(
      category_p_id   OE.categories.category_id%TYPE,
      p_number      NUMBER
  ) RETURN BOOLEAN;

  FUNCTION getProductDiscounts
  (
      category_p_id   OE.categories.category_id%TYPE,
      p_number      NUMBER
  ) RETURN product_tab_type;
  -- %%%%%%%%%%%%%%%Exceptions%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  category_not_found EXCEPTION;
  not_enough_products EXCEPTION;

END OEFENING6PACKAGE;
/
CREATE OR REPLACE
PACKAGE BODY OEFENING6PACKAGE AS

  PROCEDURE productenUitDeInventaris AS
      CURSOR warehouse_cursor IS SELECT * FROM warehouses;

      CURSOR product_pw_cursor(wrh warehouse_cursor%ROWTYPE) IS
      SELECT pi.product_id, pi.product_name, inv.quantity_on_hand FROM inventories inv
      inner join product_information pi ON pi.product_id = inv.product_id
      WHERE inv.warehouse_id = wrh.warehouse_id;
  BEGIN
      -- Loop through every warehouse
      FOR warehouse IN warehouse_cursor LOOP
        DBMS_OUTPUT.put_line('Warehouse '||warehouse.warehouse_id||', '||warehouse.warehouse_name);
        -- Loop through every product in a warehouse
        FOR product IN product_pw_cursor(warehouse) LOOP
          DBMS_OUTPUT.put_line(product.product_id||' '||product.product_name||' Stockaantal = '||product.quantity_on_hand);
        END LOOP;
      END LOOP;
  END productenUitDeInventaris;

  PROCEDURE updatePrijzen(p_category_id IN OE.categories.category_id%TYPE, p_number IN NUMBER) AS
      producten product_tab_type;
  BEGIN
      producten := getproductdiscounts(p_category_id, p_number);
      FORALL indx IN 1..producten.COUNT
        UPDATE product_information i SET i.list_price = producten(indx).new_price
        WHERE i.product_id = producten(indx).product_id;
      EXCEPTION
        WHEN category_not_found THEN
          DBMS_OUTPUT.put_line('Category with id ' || p_category_id || ' not found');
        WHEN not_enough_products THEN
          DBMS_OUTPUT.put_line('Category with id ' || p_category_id || ' not enough products');
  END updatePrijzen;

  PROCEDURE printproductabtype(tab IN product_tab_type) AS
    counter INTEGER:=0;
    rowt product_rec_type;
  BEGIN
    DBMS_OUTPUT.put_line('Lijst van de '|| tab.COUNT || ' duurste producten');
    FOR i IN 1..tab.COUNT LOOP
      rowt := tab(i);
      counter := counter +1;
      DBMS_OUTPUT.put_line(counter||'    '||rowt.product_id||'    '||rowt.product_name||'    '||rowt.new_price);
    END LOOP;
  END;

  FUNCTION checkCategory(category_p_id OE.categories.category_id%TYPE) RETURN BOOLEAN AS
  occurences NUMBER;
  BEGIN
    SELECT COUNT(*) INTO occurences FROM categories WHERE category_id = category_p_id;
    IF occurences = 0 THEN
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
  END checkCategory;

  FUNCTION checkEnough(
                        category_p_id   OE.categories.category_id%TYPE,
                        p_number      NUMBER
                      )
  RETURN BOOLEAN AS
  occurences NUMBER;
  BEGIN
    SELECT COUNT(*) INTO occurences FROM product_information pi
    inner join categories cat ON cat.category_id = pi.category_id
    WHERE pi.category_id = category_p_id;

    IF occurences >= p_number THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;
  END checkEnough;

  FUNCTION getProductDiscounts
  (
      category_p_id   OE.categories.category_id%TYPE,
      p_number      NUMBER
  )
  RETURN product_tab_type AS
    -- %%%%%Extra vars %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

    v_product_tab product_tab_type;
    counter INTEGER:=0;
     -- %%%%%Extra cursor %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
    CURSOR pduurste
          (
            p_category_id OE.categories.category_id%TYPE,
            p_number NUMBER
          )
  IS
    SELECT product_id, product_name, (list_price * 0.9) AS NEW_PRICE FROM OE.product_information
    WHERE (ROWNUM <= p_number) AND (category_id = p_category_id)
    ORDER BY list_price DESC;
     -- %%%%%Implementation %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
    BEGIN
    IF checkcategory(category_p_id) != TRUE THEN
      RAISE category_not_found;
    END IF;
    IF checkenough(category_p_id, p_number) != TRUE THEN
      RAISE not_enough_products;
    END IF;
    v_product_tab := product_tab_type();
    FOR n IN pduurste(category_p_id, p_number) LOOP
      counter := counter + 1;
      v_product_tab.extend;
      v_product_tab(counter).product_id := n.product_id;
      v_product_tab(counter).product_name := n.product_name;
      v_product_tab(counter).new_price := n.new_price;
    END LOOP;
    RETURN v_product_tab;
  END getProductDiscounts;

END OEFENING6PACKAGE;
/
/***Testapplication ****/
BEGIN
--products per warehouse
-- OE.oefening6package.productenuitdeinventaris();
--geef 5 duurste met id 11
OE.oefening6package.printproductabtype(oefening6package.getproductdiscounts(11,5));
-- Update them
OE.oefening6package.updateprijzen(11,5);
OE.oefening6package.printproductabtype(oefening6package.getproductdiscounts(11,5));
END;