Scribd
Upload
3 views3 pages

PL SQL

The document contains two PL/SQL procedures: one named 'Check_Customer_Order' that evaluates a customer's total order amount and categorizes them as high, medium, or low value, and another named 'Get_Total_Quantity' that calculates the total quantity of products ordered by a customer in a specific category using a cursor. Both procedures utilize loops for processing data from the 'Orders' and 'Order_Details' tables. The document also includes an example call to the 'Get_Total_Quantity' function.

Uploaded by

khalidmuhammad8092
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3 views3 pages

PL SQL

The document contains two PL/SQL procedures: one named 'Check_Customer_Order' that evaluates a customer's total order amount and categorizes them as high, medium, or low value, and another named 'Get_Total_Quantity' that calculates the total quantity of products ordered by a customer in a specific category using a cursor. Both procedures utilize loops for processing data from the 'Orders' and 'Order_Details' tables. The document also includes an example call to the 'Get_Total_Quantity' function.

Uploaded by

khalidmuhammad8092
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

1.

PROCEDURE USING IF-ELSEIF-ELSE + LOOP

CREATE OR REPLACE PROCEDURE Check_Customer_Order (


p_customer_id IN NUMBER
)
IS
total_amount NUMBER := 0;
BEGIN
FOR rec IN (
SELECT Total_Amount
FROM Orders
WHERE Customer_ID = p_customer_id
)

LOOP
total_amount := total_amount + rec.Total_Amount;
END LOOP;

IF total_amount > 20000 THEN


DBMS_OUTPUT.PUT_LINE('High Value Customer');

ELSIF total_amount > 5000 THEN


DBMS_OUTPUT.PUT_LINE('Medium Value Customer');
ELSE
DBMS_OUTPUT.PUT_LINE('Low Value Customer');
END IF;
END;
/
BEGIN
Check_Customer_Order(1);
END;
/

2. FUNCTION USING 2 PARAMETERS + LOOP + CURSOR

CREATE OR REPLACE FUNCTION Get_Total_Quantity (


p_customer_id IN NUMBER,
p_category_id IN NUMBER
)
RETURN NUMBER
IS
total_qty NUMBER := 0;

CURSOR c1 IS

SELECT [Link]
FROM Orders o
JOIN Order_Details od
ON o.Order_ID = od.Order_ID
JOIN Product p
ON od.Product_ID = p.Product_ID
WHERE o.Customer_ID = p_customer_id
AND p.Category_ID = p_category_id;
rec c1%ROWTYPE;

BEGIN
OPEN c1;

LOOP
FETCH c1 INTO rec;
EXIT WHEN c1%NOTFOUND;
total_qty := total_qty + [Link];
END LOOP;

CLOSE c1;
RETURN total_qty;

END;
/

SELECT Get_Total_Quantity(1,1)
FROM dual;

You might also like