PROGRAMS ON PROCEDURES

1.Write a procedure display the employee details who are working under blake or clark?

A.CREATE  or  REPLACE Procedure pro1

    (A OUT  SYS_REFCURSOR)

    IS

    BEGIN

    OPEN A  FOR SELECT * FROM  EMP  WHERE   MGR IN(SELECT EMPNO FROM EMP 

                              WHERE ENAME IN('BLAKE','CLARK'));

     END;

2.Write a procedure display the managers & president work locations?

A.CREATE or REPLACE Procedure pro2

    (A OUT  SYS_REFCURSOR)

    IS

    BEGIN

    OPEN  A  FOR  SELECT LOC FROM DEPT WHERE DEPTNO IN(SELECT DEPTNO FROM                                   EMP  WHERE JOB='MANAGER' or JOB='PRESIDENT');

    END;

3.Write a Procedure to display the employee details who are getting commission?

A.CREATE or REPLACE Procedure pro3

    (A OUT SYS_CURSOR)

    IS

    BEGIN

    OPEN  A  FOR  SELECT * FROM EMP WHERE COMM IS NOT NULL;

    END;

4.Write a Procedure to display  the maximum salary employee details who are getting                    commission?

A.CREATE or REPLACE Procedure pro4

    (A  OUT  SYS_REFCURSOR)

    IS

    BEGIN

    OPEN  A  FOR  SELECT * FROM EMP WHERE  SAL=(SELECT MAX(SAL) FROM EMP 

                               WHERE COMM IS NOT NULL);

    END;

5.Write a Procedure to display the top 3 maximum salaries?

A.CREATE or REPLACE Procedure pro5

    (A  OUT SYS_REFCURSOR) 

    IS

    BEGIN

    OPEN  A  FOR  SELECT SAL  FROM(SELECT  DISTINCT SAL FROM EMP ORDER BY                                        SAL DESC) WHERE ROWNUM<=3;

    END;