Oefening4Package

CREATE OR REPLACE
PACKAGE OEFENING4PACKAGE AS
  TYPE order_product_rec_type IS RECORD
  -- %%%%%%%%%%%%DATATYPES%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  -- Step 2 of exercise
  (
    product_id        OE.product_information.product_id%TYPE,
    product_name      OE.product_information.product_name%TYPE,
    category_id       OE.product_information.category_id%TYPE,
    product_status    OE.product_information.product_status%TYPE,
    list_price        OE.product_information.list_price%TYPE,
    min_price         OE.product_information.min_price%TYPE
  );
  -- Step 3 of exercise
  TYPE order_product_tab_type IS TABLE OF order_product_rec_type;
  -- %%%%%%%%%%%%%FUNCTIONS%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  -- Step 4 of exercise
  FUNCTION checkProduct(pid OE.product_information.product_id%TYPE) RETURN BOOLEAN;
  -- Step 5 of exercise
  FUNCTION checkCategory(cid OE.product_information.category_id%TYPE) RETURN BOOLEAN;
  -- Step 6 of exercise
  FUNCTION checkProductPrice
    (
      cid OE.product_information.category_id%TYPE,
      lpri OE.product_information.list_price%TYPE,
      mpri OE.product_information.min_price%TYPE
    )
    RETURN BOOLEAN;
  -- %%%%%%%%%%PROCEDURES%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  PROCEDURE addProduct(prod IN order_product_tab_type);
  PROCEDURE printrecord(rec IN order_product_tab_type);
  -- %%%%%%%%%%PROCEDURES%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  no_category_found EXCEPTION;
  product_bestaat EXCEPTION;
  categorie_bestaat_niet EXCEPTION;
  prijs_te_hoog EXCEPTION;
END OEFENING4PACKAGE;
/
CREATE OR REPLACE
PACKAGE BODY OEFENING4PACKAGE AS
 FUNCTION checkProduct(pid OE.product_information.product_id%TYPE) RETURN BOOLEAN AS
  occurences NUMBER;
 BEGIN
  SELECT COUNT(*) INTO occurences FROM OE.product_information
  WHERE product_id = pid;

  IF occurences>0 THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
 END;
  -- Step 5 of exercise
FUNCTION checkCategory(cid OE.product_information.category_id%TYPE) RETURN BOOLEAN AS
  occurences NUMBER;
 BEGIN
  SELECT COUNT(*) INTO occurences FROM OE.categories
  WHERE category_id = cid;

  IF occurences>0 THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;
 END;
  -- Step 6 of exercise
FUNCTION checkProductPrice
    (
      cid OE.product_information.category_id%TYPE,
      lpri OE.product_information.list_price%TYPE,
      mpri OE.product_information.min_price%TYPE
    )
RETURN BOOLEAN AS
  clearflag BOOLEAN;
  avg_listprice NUMBER;
  avg_minprice NUMBER;

  perc1 NUMBER;
  perc2 NUMBER;
BEGIN
  clearflag := checkCategory(cid);
  IF clearflag THEN
    SELECT AVG(list_price), AVG(min_price)
    INTO avg_listprice, avg_minprice FROM OE.product_information
    WHERE category_id = cid;

    perc1 := (lpri-avg_listprice)/avg_listprice;
    perc2 := (mpri-avg_minprice)/avg_minprice;

    IF perc1 <= 0.2 AND perc2 <= 0.2 THEN
      RETURN TRUE;
    ELSE
      RETURN FALSE;
    END IF;
  ELSE
    RAISE no_category_found;
  END IF;
END;
  -- %%%%%%%%%%PROCEDURES%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
PROCEDURE addProduct(prod IN order_product_tab_type) IS
 i INTEGER;
 product order_product_rec_type;
BEGIN
  FOR i IN 1 .. prod.COUNT
  LOOP
    product := prod(i);
    IF(checkProduct(product.product_id))
    THEN
      DBMS_OUTPUT.put_line('Bij product met id '||product.product_id);
      RAISE product_bestaat;
    ELSIF(checkCategory(product.category_id)!=TRUE)
    THEN
      DBMS_OUTPUT.put_line('Bij product met id '||product.product_id);
      RAISE categorie_bestaat_niet;
    ELSIF(checkProductPrice(product.category_id, product.list_price, product.min_price)!=TRUE)
    THEN
      DBMS_OUTPUT.put_line('Bij product met id '||product.product_id);
      RAISE prijs_te_hoog;
    ELSE
      INSERT INTO OE.product_information(product_id, product_name, category_id,product_status, list_price, min_price)
      VALUES(product.product_id, product.product_name, product.category_id,product.product_status,
      product.list_price, product.min_price);
      COMMIT;
    END IF;
  END LOOP;
END;
PROCEDURE printrecord(rec IN order_product_tab_type) IS
  i INTEGER;
BEGIN
  FOR i IN 1 .. rec.COUNT
  LOOP
    DBMS_OUTPUT.put_line
    (
      '(' ||
          rec(i).product_id || ', ' ||
          rec(i).product_name || ', ' ||
          rec(i).category_id || ', ' ||
          rec(i).product_status || ', ' ||
          rec(i).list_price || ', ' ||
          rec(i).min_price  ||
      ')'
    );
  END LOOP;
END;
END;
/
/********Test application **********************/
DECLARE
  producten oefening4package.order_product_tab_type;
  entry1 oefening4package.order_product_rec_type;
  entry2 oefening4package.order_product_rec_type;
BEGIN
  entry1.product_id := 4203;
  entry1.product_name := 'test_product_1';
  entry1.category_id := 11;
  entry1.product_status := 'planned';
  entry1.list_price := 1510;
  entry1.min_price := 480;

  entry2.product_id := 4201;
  entry2.product_name := 'test_product_2';
  entry2.category_id := 11;
  entry2.product_status := 'obsolete';
  entry2.list_price := 520;
  entry2.min_price := 470;

  producten := NEW oefening4package.order_product_tab_type();
  producten.extend;
  producten(1) := entry1;
  producten.extend;
  producten(2) := entry2;

  oefening4package.printrecord(producten);
  BEGIN
  oefening4package.addproduct(producten);
  EXCEPTION
    WHEN oefening4package.no_category_found THEN DBMS_OUTPUT.put_line('Opgegeven categorie bestaat niet');
    WHEN oefening4package.product_bestaat THEN DBMS_OUTPUT.put_line('Opgegeven product bestaat al');
    WHEN oefening4package.prijs_te_hoog THEN DBMS_OUTPUT.put_line('Opgegeven min en verkoopsprijs verschillen te veel');
  END;
END;