shop_private_pkg.sql

PACKAGE BODY shop_private_pkg
AS

  
  
  
  FUNCTION GET_CATEGORY_PRODUCTS 
  ( 
    P_CATEGORY_NAME     PRODUCT_CATEGORIES.NAME%TYPE
  )
  RETURN SYS_REFCURSOR
  AS
    C_NAMES             SYS_REFCURSOR; 
  BEGIN
    OPEN C_NAMES FOR SELECT P.NAME
                       FROM SECRET_PRODUCTS    P
                          , PRODUCT_CATEGORIES PC
                      WHERE 1 = 1
                        AND P.CATEGORY_ID = PC.ID
                        AND PC.NAME = P_CATEGORY_NAME
                      ORDER BY P.NAME;
    RETURN C_NAMES;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(EX, 'Products for category ' || P_CATEGORY_NAME || ' are not found :(');
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(EX, SQLERRM);
  END GET_CATEGORY_PRODUCTS;
  
  
  
  
  FUNCTION GET_PRODUCT_QUANTITY 
  ( 
    P_PRODUCT_NAME      SECRET_PRODUCTS.NAME%TYPE
  )
  RETURN NUMBER
  AS
    L_QUANTITY          NUMBER;
    L_QUERY             VARCHAR2(32767);
  BEGIN
    L_QUERY := ' select p.quantity
                   from secret_products p
                  where 1 = 1
                   and p.name = ''' || P_PRODUCT_NAME|| '''';
    EXECUTE IMMEDIATE L_QUERY INTO L_QUANTITY;
    RETURN L_QUANTITY;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Product ' || P_PRODUCT_NAME || ' is not found :(');
      RETURN 0;
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(EX, SQLERRM);
  END GET_PRODUCT_QUANTITY;
  
  
  
  
  FUNCTION GET_PRODUCT_CATEGORY
  ( 
    P_PRODUCT_ID        SECRET_PRODUCTS.ID%TYPE               
  )
  RETURN VARCHAR2
  AS
    L_CATEGORY          PRODUCT_CATEGORIES.NAME%TYPE;
  BEGIN
    SELECT C.NAME
      INTO L_CATEGORY
      FROM PRODUCT_CATEGORIES    C
         , SECRET_PRODUCTS       P
     WHERE 1 = 1
       AND C.ID = P.CATEGORY_ID
       AND P.ID = P_PRODUCT_ID
     ORDER BY C.NAME;
    RETURN L_CATEGORY;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Product with id ' || P_PRODUCT_ID || ' is not found :(');
      RETURN NULL;
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(EX, SQLERRM);
  END GET_PRODUCT_CATEGORY;
  
  
  
  
  FUNCTION GET_PRODUCT_SUBCATEGORY
  ( 
    P_PRODUCT_ID        SECRET_PRODUCTS.ID%TYPE              
  )
  RETURN VARCHAR2
  AS
    L_SUBCATEGORY       PRODUCT_SUBCATEGORIES.NAME%TYPE;
  BEGIN
    SELECT SC.NAME
      INTO L_SUBCATEGORY
      FROM PRODUCT_SUBCATEGORIES SC
         , SECRET_PRODUCTS       P
     WHERE 1 = 1
       AND SC.ID = P.SUBCATEGORY_ID
       AND P.ID = P_PRODUCT_ID
     ORDER BY SC.NAME;
    RETURN L_SUBCATEGORY;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Product with id ' || P_PRODUCT_ID || ' is not found :(');
      RETURN NULL;
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(EX, SQLERRM);
  END GET_PRODUCT_SUBCATEGORY;
  
  
  
  
  FUNCTION GET_PRODUCT_NAME 
  ( 
    P_PRODUCT_ID        SECRET_PRODUCTS.ID%TYPE
  )
  RETURN VARCHAR2
  AS
    L_NAME              SECRET_PRODUCTS.NAME%TYPE;
  BEGIN
    SELECT P.NAME
      INTO L_NAME
      FROM SECRET_PRODUCTS P 
     WHERE 1 = 1
       AND P.ID = P_PRODUCT_ID
     ORDER BY P.NAME;
    RETURN L_NAME;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Product with id ' || P_PRODUCT_ID || ' is not found :(');
      RETURN NULL;
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(EX, SQLERRM);
  END GET_PRODUCT_NAME;
  
  
  
  
  FUNCTION GET_FULL_PRODUCT_NAME 
  ( 
    P_PRODUCT_ID        SECRET_PRODUCTS.ID%TYPE
  )
  RETURN VARCHAR2
  AS
  BEGIN
    RETURN GET_PRODUCT_CATEGORY(P_PRODUCT_ID) || ' ' || GET_PRODUCT_SUBCATEGORY(P_PRODUCT_ID) || ' '  || GET_PRODUCT_NAME(P_PRODUCT_ID);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Product with id ' || P_PRODUCT_ID || ' is not found :(');
      RETURN NULL;
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(EX, SQLERRM);
  END GET_FULL_PRODUCT_NAME;

END SHOP_PRIVATE_PKG;