-- Create object type
create or replace TYPE TYP_SIMPLE_ORDER AS OBJECT (
ANIMAL_ID NUMBER,
SUPPLIER_ID NUMBER,
ANIMAL_TYPE VARCHAR2(20),
BUYING_PRICE NUMBER,
STATUS VARCHAR2(20)
);
-- Create table type
CREATE TYPE MY_SIMPLE_ORDER_TABLE AS
TABLE OF TYP_SIMPLE_ORDER;
-- Create FUNCTION
CREATE OR REPLACE FUNCTION SIMPLIFIED_ORDERS (
P_SUPPLIER_ID NUMBER,
P_ANIMAL_TYPE VARCHAR2 DEFAULT NULL
) RETURN MY_SIMPLE_ORDER_TABLE PIPELINED
IS
BEGIN
FOR X IN (
SELECT ANIMAL_ID, SUPPLIER_ID, ANIMAL_TYPE, BUYING_PRICE, STATUS
FROM ANIMALS
WHERE SUPPLIER_ID = P_SUPPLIER_ID
AND (P_ANIMAL_TYPE IS NULL OR ANIMAL_TYPE = P_ANIMAL_TYPE)
)
LOOP
IF X.STATUS IN ('I', 'R') THEN
PIPE ROW(TYP_SIMPLE_ORDER(
X.ANIMAL_ID,
X.SUPPLIER_ID,
X.ANIMAL_TYPE,
X.BUYING_PRICE,
CASE
WHEN X.STATUS = 'I' THEN 'In Stock'
WHEN X.STATUS = 'R' THEN 'Returned'
ELSE NULL
END
));
END IF;
END LOOP;
RETURN;
END;
--- Calling Function
SELECT *
FROM TABLE(SIMPLIFIED_ORDERS(P_SUPPLIER_ID => 105,
P_ANIMAL_TYPE => 'BULL'));
Result:
SELECT *
FROM TABLE(SIMPLIFIED_ORDERS(P_SUPPLIER_ID => 105 ));
Result:
| ANIMAL_ID | SUPPLIER_ID | ANIMAL_TYPE | BUYING_PRICE | STATUS |
|---|---|---|---|---|
| 1051 | 105 | GOAT | 23000 | In Stock |
| 1052 | 105 | COW | 50000 | In Stock |
| 1053 | 105 | BULL | 45000 | In Stock |
| 1054 | 105 | COW | 500000 | In Stock |
| 1035 | 105 | BULL | 20000 | Returned |
| 1037 | 105 | SHEEP | 19000 | In Stock |
| 1038 | 105 | BULL | 25000 | In Stock |
| 1039 | 105 | GOAT | 13000 | Returned |
| 8 rows returned in 0.01 seconds | Download |