ตอนที่ 14 : รู้จัก Oracle Package คืออะไร การใช้งาน Package (Oracle : Stored Procedure)

CREATE OR REPLACE PACKAGE CUSTOMER_INFO AS 

  PROCEDURE GET_BY_COUNTRY_CODE(pCountryCode IN VARCHAR2, pCustomer OUT SYS_REFCURSOR); 
  
  PROCEDURE GET_BY_USED(pAmount IN DECIMAL, pCustomer OUT SYS_REFCURSOR);
  
  FUNCTION GET_SUM_USED(pCountryCode IN VARCHAR) RETURN DECIMAL;

END CUSTOMER_INFO;

CREATE OR REPLACE PACKAGE BODY CUSTOMER_INFO AS 
  
  -- Procedure GET_BY_COUNTRY_CODE
  PROCEDURE GET_BY_COUNTRY_CODE(pCountryCode IN VARCHAR2, pCustomer OUT SYS_REFCURSOR)
  AS
  BEGIN
    OPEN pCustomer FOR
         SELECT *
           FROM CUSTOMER WHERE COUNTRY_CODE = pCountryCode;  
  END;
  
  -- Procedure GET_BY_USED
  PROCEDURE GET_BY_USED(pAmount IN DECIMAL, pCustomer OUT SYS_REFCURSOR)
  AS
  BEGIN
    OPEN pCustomer FOR
         SELECT *
           FROM CUSTOMER WHERE USED <= pAmount;  
  END;
  
  -- Function GET_SUM_USED
  FUNCTION GET_SUM_USED(pCountryCode IN VARCHAR) RETURN DECIMAL
  AS
    sumUsed DECIMAL(18,2) DEFAULT '0';
  BEGIN
         SELECT SUM(USED) INTO sumUsed FROM CUSTOMER 
            WHERE COUNTRY_CODE = pCountryCode;  
         RETURN sumUsed;
  END; 

END CUSTOMER_INFO;

VAR  pCustomer REFCURSOR;
EXEC CUSTOMER_INFO.GET_BY_COUNTRY_CODE('US',:pCustomer)
PRINT pCustomer;

VAR  pCustomer REFCURSOR;
EXEC CUSTOMER_INFO.GET_BY_USED('600000',:pCustomer)
PRINT pCustomer;

SELECT CUSTOMER_INFO.GET_SUM_USED('US') FROM DUAL;