PROGRAM TO DISPLAY THE INFORMATION OF GIVEN DEPTNO USING CURSOR IN ORACLE?



SQL>DECLARE
  2  CURSOR EMP_DATA IS
  3  SELECT EMPNO, ENAME, SAL FROM EMP WHERE
      DEPTNO=&DEPTNO;
  4  ECODE EMP.EMPNO%TYPE;
  5  NAME EMP.ENAME%TYPE;
  6  SALARY EMP.SAL%TYPE;
  7  BEGIN
  8  OPEN EMP_DATA;
  9  LOOP
 10  FETCH EMP_DATA INTO ECODE, NAME,SALARY;
 11  EXIT WHEN EMP_DATA%NOTFOUND;
 12  DBMS_OUTPUT.PUT_LINE (ECODE||' '||NAME||' '||
       SALARY);
 13  END LOOP;
 14  CLOSE EMP_DATA;
 15  END;
 16  /
Enter value for deptno: 10
old   3: SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO=&DEPTNO;
new   3: SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO=10;
7782  CLARK    4540.34
7839  KING       11450
7934  MILLER   2262.94

PL/SQL procedure successfully completed.

SQL> @ EMPLOYEE.SQL;
Enter value for deptno: 30
old   3: SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO=&DEPTNO;
new   3: SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO=30;
7499    ALLEN      2242.05
7521   WARD       1795.36
7654   MARTIN    1795.36
7698   BLAKE      5248.96
7844  TURNER    2114.42
7900  JAMES      1707.54

PL/SQL procedure successfully completed.

Comments

Popular posts from this blog

Write a program to add two number using inline function in C++?

Traversing of elements program with algorithm and Flowchart