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;
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.