6 Very Simple PLSQL Programs to Explain Cursors

Cursors in PL/SQL are used to retrieve more than one row at a time. The data that is stored in cursors is known as Active Data Set. These cursors are of two types:

1. Implicit Cursors : predefined cursors
2. Explicit Cursors : user defined cursors

Here are simple 6 programs illustrating the concept of cursors.

1. Program to illustrate the use of attribute SQL%FOUND in Implicit Cursor. The Program is to find out the salary of an employee from emp table whose two fields are emp_sal and emp_no.

DECLARE
salary number(5);
BEGIN
SELECT emp_sal INTO salary FROM emp WHERE emp_no=&empno;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE(’Record Found’);
DBMS_OUTPUT.PUT_LINE(’Salary = ‘ || salary);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(’Record Not Found’);
END;

2. Program to illustrate the use of attribute SQL%NOTFOUND in Implicit Cursor. The Program is to find out the salary of an employee from emp table whose two fields are emp_sal and emp_no.

DECLARE
salary number(5);
BEGIN
SELECT emp_sal INTO salary FROM emp WHERE emp_no=&empno;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE(’Record Not Found’);
ELSE
DBMS_OUTPUT.PUT_LINE(’Record Found’);
DBMS_OUTPUT.PUT_LINE(’Salary = ‘ || salary);
END IF;
END;

3. Program to illustrate the use of attribute SQL%ROWCOUNT in Implicit Cursor. The Program is to update the salary of each employee by 1000.

BEGIN
UPDATE emp SET emp_sal = emp_sal +1000;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ‘Records Updated’);
END;

4. Program to illustrate the use of Explicit Cursors. The Program is to display the information of employess (Emp No, Name and Salary) of a given department.

DECLARE
CURSOR empdata IS
SELECT emp_no, emp_name, emp_sal FROM emp WHERE emp_deptno = &deptno;
ecode emp.emp_no%TYPE;
ename emp.emp_name%TYPE;
esal emp.emp_sal%TYPE;
BEGIN
OPEN empdata;
LOOP
FETCH empdata INTO ecode, ename, esal;
EXIT WHEN empdata%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(ecode || ename || esal);
END LOOP;
CLOSE empdata;
END;

5. Program to illustrate the use of Explicit Cursors with FOR LOOP. The Program is to display the information of employess of a given department (same as program 4)

DECLARE
CURSOR empdata IS
SELECT emp_no, emp_name, emp_sal FROM emp WHERE emp_deptno = &deptno;
BEGIN
FOR rec IN empdata
LOOP
DBMS_OUTPUT.PUT_LINE(rec.emp_no || rec.emp_name || rec.emp_sal);
END LOOP;
END;

6. Program to illustrate the use of Explicit Cursors with Parameter Passing Concept. The Program is to display the information of employees of a given department (same as program 4)

DECLARE
CURSOR empdata(n number) IS
SELECT emp_no, emp_name, emp_sal FROM emp WHERE emp_deptno = n;
ecode emp.emp_no%TYPE;
ename emp.emp_name%TYPE;
esal emp.emp_sal%TYPE;
BEGIN
OPEN empdata(n);
LOOP
FETCH empdata INTO ecode, ename, esal;
EXIT WHEN empdata%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(ecode || ename || esal);
END LOOP;
CLOSE empdata;
END;