Sunday, December 19, 2010

IF-THEN-ELSIF-ELSE-END IF statement

SET VERIFY OFF
SET SERVEROUTPUT ON
DECLARE
    v_empno NUMBER := &emp_number;
    v_sal        NUMBER;
BEGIN
    SELECT sal INTO  v_sal FROM emp
        WHERE empno = v_empno;
    IF v_sal BETWEEN 0 AND 1000 THEN
        DBMS_OUTPUT.PUT_LINE ( 'Emp No  '|| v_empno || '  is getting LOW salary' );
    ELSIF  v_sal BETWEEN 1001 AND 2000 THEN 
        DBMS_OUTPUT.PUT_LINE ( 'Emp No  '|| v_empno || '  is getting NORMAL salary' );
    ELSIF  v_sal BETWEEN 2001 AND 3000 THEN 
        DBMS_OUTPUT.PUT_LINE ( 'Emp No  '|| v_empno || '  is getting HIGH salary' );
    ELSE
        DBMS_OUTPUT.PUT_LINE ( 'Emp No  '|| v_empno || '  salary is more  than 3000 INR' );
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE(' Emp No  '|| v_empno || ' does not exist' );
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error  '|| sqlcode ||' - '||sqlerrm);
END;
/

Enter value for emp_number: 7566
Emp No  7566  is getting HIGH salary

PL/SQL procedure successfully completed.

No comments:

Post a Comment

External Table

Oracle External Table External tables are defined as tables that do not resides in the database allows you to access data that is stor...