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
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;
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;
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;
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;
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;
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;
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;
No comments:
Post a Comment