procedure.sql

CREATE OR REPLACE PROCEDURE CUSTOMER_SPENT(K IN INT, CUR OUT SYS_REFCURSOR)
AS
BEGIN
	OPEN CUR FOR SELECT CUSTOMER_ID
		FROM
			(SELECT S.CUSTOMER_ID, (1+ (SELECT COUNT(*) FROM CUST_RANK T WHERE T.TOTAL>S.TOTAL)) AS RANK
			FROM CUST_RANK S)
		WHERE RANK <= K;
END;
/


CREATE OR REPLACE PROCEDURE STORE_EARNING_RANK(K IN INT, CUR OUT SYS_REFCURSOR)
AS
BEGIN
	OPEN CUR FOR SELECT StoreID
		FROM(
			SELECT S.StoreID, (1 + (SELECT COUNT(*) FROM SALES E WHERE E.Total > S.Total)) AS Rank
			FROM SALES S)
		WHERE RANK <= K;
END;
/

CREATE OR REPLACE PROCEDURE FIND_COFFEE_BY_DESC(DESCRIPTION_1 IN varchar2, DESCRIPTION_2 IN varchar2, CUR OUT SYS_REFCURSOR)
AS
BEGIN
	OPEN CUR FOR SELECT Coffee_ID
		from   Coffee C
		where  UPPER(C.DESCRIPTION) LIKE '%'||UPPER(DESCRIPTION_1)||'%' AND UPPER(C.DESCRIPTION) LIKE '%'||UPPER(DESCRIPTION_2)||'%' ;
END;
/