Wednesday 23 March 2011

Cursor

Cursor is a location in the memory where the SQL statement is processed. That is when ever we send an SQL statement; the system allocates some memory by giving a name. After processing the statement it automatically cleans that memory. There are four steps, which are processed by the system whenever we send an SQL statement they are

1.Creating cursor : This step allocates memory

2.Opening cursor :

In this step process the sql statement

3.Fetching cursor: 

The values satisfied by the sql statement are fetched from the

table into cursor row by row

4.Closing cursor : This statement close the memory allocated for the cursor

Types Of Cursors

There Are Two Types Of Cursors

1. Implicit cursors or simple cursors

2. Explicit Cursors

Implicit Cursors

Oracle implicitly opens a cursor to process each sql statement not associated with explicitly declared cursor. Pl/sql lets you refer to the most recent implicit cursors as the sql cursor.

Implicit cursors have four attributes, you can use in your program

  1. sql%rowcount

It returns the number of rows affected by an insert, update or delete

  1. sql%found

This attributes evaluates to true, if an insert or update or delete

affected to one or more rows. It evaluates to false if no row is affected

  1. sql%notfound

This attributes is logical opposite to sql%found. It evaluates to true,

if an insert or update or delete does not affected to any row. It evaluates to

false if any one row is affected

  1. %isopen

It checks whether the cursor has been opened or not

Example : 1

write a program to check whether there is atleast one row satisfying the given select statement or not using implicit cursors

Declare

e emp%rowtype;

Begin

select * into e from emp where empno=&empno;

if sql%found then

dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');

dbms_output.put_line(' Record Found ');

dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');

End if;

End;

/

Example : 2

write a program to change the ename column values to upper case

using implicit cursors

begin

update employee set ename=upper(ename);

if sql%found then

dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');

dbms_output.put_line(sql%rowcount || ' Records Updated ');

dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');

else

dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');

dbms_output.put_line(' Sorry Changes Are not Made ');

end if;

end;

/

Example : 3

write a program to update the salary of those employees to 20% whose salary is greater than the given salary, using implicit cursors

begin

update employee set sal=sal+(sal*20)/100 where sal>&sal;

if sql%found then

dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');

dbms_output.put_line(sql%rowcount || ' Records Updated ');

dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');

end if;

if sql%notfound then

dbms_output.put_line('~~~~~~~~~~~~~~~~~~~~');

dbms_output.put_line(' Sorry No Record is Satisfied ');

end if;

end;

/

Explicit Cursors

The user creates these cursors for applying 2 or more DML operations on a table. using a cursor we can fetch all the row values from the table into a variable for printing them or performing some action with them

In an explicit cursor, there are four steps related to execute cursors by the user. It is also used to compare a variable value with all values of the column.

The four steps are

1. Declaring a Cursor

2. Opening a Cursor

3. Fetching a Cursor

4. Closing a Cursor

Declaring a Cursor

A cursor is defined in the declarative part of the PL/SQL block by naming it and associating it with a query

Syntax :

Declare

Cursor <cursor_name> is <select Statement>

Opening a Cursor

The cursor must be initialized or opened with the open statement. Opening the cursor excutes the query and identifies the result set which consists of all the rows that meets the query search criteria

Syntax :

Declare

Cursor <cursor_name> is <select Statement>

Begin

Open <cursor Name>

-----------

End;

Fetching With a Cursor

This statement is used to retrieve the cursors current row. Fetching can be excuted repeatedly until all rows have been retrieved

Syntax :

Declare

Cursor <cursor_name> is <select Statement>

Begin

Open <cursor Name>

Fetch <cursor Name> into <variable1>,<variable2>,----

-----------

End;

Closing a Cursor

When the last row has been processed, the cursor is closed with the close statement. The close statement disable the cursor

Syntax :

Declare

Cursor <cursor_name> is <select Statement>

Begin

Open <cursor Name>

Fetch <cursor Name> into <variable1>,<variable2>,----

-----------

Close <cursor Name>

End;

Examples

1. Write a program using an explicit cursor, which displays all the rows from the table

Declare

Cursor c1 is select * from emp;

e emp%rowtype;

Begin

Open c1;

Loop

Fetch c1 into e;

Dbms_output.put_line(e.empno||' '||e.ename||' '||e.sal);

Exit when c1%notfound;

End loop;

Close c1;

End;

/

2.Write a program using an explicit cursor, to insert values into a table empc from emp table, whose experience is >=23 years

Declare

cursor c2 is select * from emp where (sysdate-hiredate)/365>=23;

e emp%rowtype;

x number:=0;

begin

open c2;

loop

fetch c2 into e;

exit when c2%notfound;

insert into empc values(e.empno,e.ename,e.job,e.sal,e.hiredate);

x:=x+sql%rowcount;

end loop;

dbms_output.put_line(x||' Records inserted Successfully....');

close c2;

end;

/

3.Write a program using an explicit cursor, to display first 5 records

Declare

cursor c3 is select * from emp;

e emp%rowtype;

x number;

begin

open c3;

loop

fetch c3 into e;

exit when c3%rowcount>5;

dbms_output.put_line(e.empno||' '||e.ename||' '||e.sal);

end loop;

close c3;

end;

/

Cursor For Loop

A cursor for loop implicitly declares its loop index as a %rowtype, opens a cursor, repeatedly fetches the rows of values from the result set into fields in the records and closes the cursor when all rows have been processed or exit command is encountered

Syntax :

Declare

Cursor <cursor_name> is <select statement>

Begin

For <index> in <cursor name> loop

---------

end loop;

end;

/

Example : 1

Write a program to display all employee’s details working in the accepted department number using cursor for loop

declare

cursor c1 is select * from emp where deptno=&deptno;

e c1%rowtype;

begin

dbms_output.put_line('****************');

for e in c1 loop

dbms_output.put_line(e.empno||' '||e.ename||' '||e.job||' '||e.sal);

end loop;

dbms_output.put_line('****************');

end;

/

Example : 2

Write a program to display all employee’s details working in the accepted department number and also display all department details for the given department number using cursor for loops

declare

cursor c1 is select * from emp where deptno=&deptno;

e c1%rowtype;

cursor c2 is select * from dept where deptno=&deptno;

d c2%rowtype;

begin

dbms_output.put_line(' ****Employees Details ***** ');

for e in c1 loop

dbms_output.put_line(e.empno||' '|| e.ename||' '||e.job||' '||e.sal);

end loop;

dbms_output.put_line(' ****Department Details ***** ');

for d in c2 loop

dbms_output.put_line(d.dname||' '||d.loc

||' '||d.deptno);

end loop;

end;

/

3 comments :

  1. Nice effert...!!!

    You can also go through some more simple examples on cursor from below posts.
    http://www.oracle-tf-world.in/2015/01/cursor-in-plsqlbasic-part-1.html
    http://www.oracle-tf-world.in/2015/06/cursor-in-plsql-part-2.html

    ReplyDelete
  2. (IN THIS PROGRAM ONE ERROR IS THERE .WHAT IS ERROR PLZ TELL ME )
    declare
    cursor lc is
    select ename,sal,deptno
    from emp
    for update;
    i lc%rowtype;
    begin
    display('the emp det are');
    display('empname'||''||'salary'||''||'deptnum');
    open lc;
    loop
    fetch lc into i;
    exit when lc%notfound;
    display(i.ename||''||i.sal||''||i.deptno);
    i.deptno=50 then
    delete from emp
    where current of lc;
    end if;
    end loop;
    close lc;
    display('after delete the 50th dept emps');
    open lc;
    loop;
    fetch lc into i;
    exit when lc%notfound;
    display(i.ename||''||i.sal||''||i.deptno);
    end loop;
    close lc;
    enD;

    ReplyDelete
  3. good program logic thanks for sharing oracle training in chennai

    ReplyDelete