ตอนที่ 11 : การใช้ IF....ELSE...ELSEIF... สร้างเงื่อนไข (Oracle : Stored Procedure)

IF condition THEN
   {...statements to execute when condition is TRUE...}
END IF;

IF condition THEN
   {...statements to execute when condition is TRUE...}
ELSE
   {...statements to execute when condition is FALSE...}
END IF;

IF condition1 THEN
   {...statements to execute when condition1 is TRUE...}
ELSIF condition2 THEN
   {...statements to execute when condition2 is TRUE...}
END IF;

IF condition1 THEN
   {...statements to execute when condition1 is TRUE...}
ELSIF condition2 THEN
   {...statements to execute when condition2 is TRUE...}
ELSE
   {...statements to execute when both condition1 and condition2 are FALSE...}
END IF;

CREATE OR REPLACE PROCEDURE GET_CUSTOMER(pType IN VARCHAR2)
AS
BEGIN

  IF pType = '1' THEN
    FOR CusInfo IN (SELECT * FROM CUSTOMER WHERE COUNTRY_CODE = 'US')
    LOOP
        DBMS_OUTPUT.PUT_LINE(CusInfo.CUSTOMER_ID || ' ' || CusInfo.NAME || ' ' 
          || CusInfo.EMAIL || ' ' || CusInfo.COUNTRY_CODE || ' ' || CusInfo.BUDGET || ' ' || CusInfo.USED);
    END LOOP;
  ELSE
    FOR CusInfo IN (SELECT * FROM CUSTOMER WHERE COUNTRY_CODE != 'US')
    LOOP
        DBMS_OUTPUT.PUT_LINE(CusInfo.CUSTOMER_ID || ' ' || CusInfo.NAME || ' ' 
          || CusInfo.EMAIL || ' ' || CusInfo.COUNTRY_CODE || ' ' || CusInfo.BUDGET || ' ' || CusInfo.USED);
    END LOOP;
  END IF;
  
END;

EXEC GET_CUSTOMER('1');

CREATE OR REPLACE PROCEDURE GET_CUSTOMER(pType IN VARCHAR2)
AS
BEGIN

  IF pType = '1' THEN
    FOR CusInfo IN (SELECT * FROM CUSTOMER WHERE COUNTRY_CODE = 'TH')
    LOOP
        DBMS_OUTPUT.PUT_LINE(CusInfo.CUSTOMER_ID || ' ' || CusInfo.NAME || ' ' 
          || CusInfo.EMAIL || ' ' || CusInfo.COUNTRY_CODE || ' ' || CusInfo.BUDGET || ' ' || CusInfo.USED);
    END LOOP;
  ELSIF pType = '2' THEN
    FOR CusInfo IN (SELECT * FROM CUSTOMER WHERE COUNTRY_CODE = 'US')
    LOOP
        DBMS_OUTPUT.PUT_LINE(CusInfo.CUSTOMER_ID || ' ' || CusInfo.NAME || ' ' 
          || CusInfo.EMAIL || ' ' || CusInfo.COUNTRY_CODE || ' ' || CusInfo.BUDGET || ' ' || CusInfo.USED);
    END LOOP;
  ELSE
    FOR CusInfo IN (SELECT * FROM CUSTOMER WHERE COUNTRY_CODE = 'UK')
    LOOP
        DBMS_OUTPUT.PUT_LINE(CusInfo.CUSTOMER_ID || ' ' || CusInfo.NAME || ' ' 
          || CusInfo.EMAIL || ' ' || CusInfo.COUNTRY_CODE || ' ' || CusInfo.BUDGET || ' ' || CusInfo.USED);
    END LOOP;
  END IF;
  
END;

EXEC GET_CUSTOMER('3');