NIKHIL

• Oct 12th, 2011

 

A cursor is just like an pointer variable which processes in the memories. Cursors can be used to process multirecord set. There are two types of cursors implicit and explicit cursors.

DECLARE var_rows number(5);
BEGIN
UPDATE employee
SET salary = salary + 1000;
IF SQL%NOTFOUND THEN
dbms_output.put_line('None of the salaries where updated');
ELSIF SQL%FOUND THEN
var_rows := SQL%ROWCOUNT;
dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');
END IF;
END;


The above example is for the implicit cursor.

Explicit Cusrors always process in the following manner

1)Open the Cursor
2)Fetch the Cursor
3)Close the Cursor

Example for the Explicit Cursor

Declare
empid emp.empno%type,
Cursor emp_cur
select sal from emp where eno:=empid;
Begin
vsal Number(5)
Open cur_emp
loop
fetch cur_emp into vsal
Exit When SQL%NOTFOUND
DBMS_OUTPUT.PUT_LINE("SALARY IS:"||vsal);
End;
close cur_emp;
End;
/

Code
DECLARE var_rows NUMBER(5);


BEGIN


  UPDATE employee 


  SET salary = salary + 1000;


  IF SQL%NOTFOUND THEN


    DBMS_OUTPUT.put_line('None of the salaries where updated');


  ELSIF SQL%FOUND THEN


    var_rows := SQL%ROWCOUNT;


    DBMS_OUTPUT.put_line('Salaries for ' || var_rows || 'employees are updated');


  END IF; 


END;