Friday, 19 February 2016

Q)WRITE A PL/SQL BLOCK TO UPDATE THE COMMISSION OF EMPLOYEES BASED ON THE FOLLOWING CONDITION?

Q)WRITE A PL/SQL BLOCK TO UPDATE THE COMMISSION OF EMPLOYEES BASED ON        THE FOLLOWING CONDITION?
1)IF EMPLOYEE DOES NOT HAVE COMM THEN SET 3000 COMMISSION
2)IF EMPLOYEE COMMISSION IS ZERO THEN SET 2500 COMMISSION
3)IF EMPLOYEE HAVING COMMISSION THEN SET 0.25 COMMISSION
A)DECLARE
    CURSOR  C  IS SELECT  EMPNO,ENAME,COMM  FROM  EMP;
    A  EMP.EMPNO%TYPE;
    B  EMP.ENAME%TYPE;
    C  EMP.COMM%TYPE;
    BEGIN
    OPEN C;
    LOOP
    FETCH C INTO A,B,C;
    EXIT WHEN C%NOTFOUND;
    IF C IS NULL THEN
    C:=3000;
    ELSE
    IF C=0 THEN
    C:=2500;
    ELSE
    C:=COMM*0.25;
    END IF;
    END IF;
    UPDATE  EMP  SET  COMM=C  WHERE EMPNO=A;
    DBMS_OUTPUT.PUT_LINE(C%ROWCOUNT||A||' '||B||' '||C);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(C%ROWCOUNT||'EMPLOYEES ARE UPDATED WITH NEW       COMMISSION');
    CLOSE C;
    COMMIT;
    END;

No comments:

Post a Comment

If you Like my blog Spread it and help friends for whom this blog is useful for their career.