PROGRAMS ON PL/SQL CURSORS

Q)Write a PL/SQL Block Display the Employee Details such as employee name,employee number,and salary when department passed as input value?

A)DECLARE
  a emp%Rowtype;
  CURSOR c IS SELECT * FROM emp WHERE DEPTNO=&DEPTNO;
  BEGIN
  OPEN c;
  FETCH c into a;
  DBMS_OUTPUT.PUT_LINE(a.empno||' '||a.ename||' '||a.sal);
  CLOSE c;
  END;

OUTPUT: 7788 CLARK 2475

  In above Example returns only one record because statements are not lies with inthe loops.
Whenever we are working with loops we need to use the following attributes.
1)%FOUND
2)%NOT FOUND
3)%ROWCOUNT
4)%%IS OPEN

1)%FOUND: This attribute is used to cheeck whether the cursor contains records or not.
                        It  returns boolean value i.e., either true or false.
If the record is found then it is true
If the record is not found then it is false.

EXAMPLE:

DECLARE
A  EMP%TYPE;
CURSOR C IS SELECT * FROM EMP WHERE DEPTNO=&DEPTNO;
BEGIN 
OPEN C;
LOOP
FETCH C INTO A;
IF C%FOUND THEN 
DBMS_OUTPUT.PUT_LINE(A.EMPNO||' '||A.ENAME||' '||A.SAL);
ELSE 
EXIT;
END IF;
END LOOP;
CLOSE C;
END;

2)% NOT FOUND: This attribute is also used to checj whether the cursor contains records or not.
                                  It is  also returns boolean value i.e., either true or false.
If the record is found then it is false.
If the record is not found then it is true.

3)%ROWCOUNT: This attribute is used to count the number of records in the cursor.

4)IS OPEN: This attribute is used to check whether cursor is opened or not.