-
-
Save balidani/51809708269b28fc5109 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment