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
Comments
Post a Comment