Instantly share code, notes, and snippets.

Embed
What would you like to do?
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