cursor

Cursor is a pointer to a memory area called context area.

CONTEXT AREA: context area is a memory region inside the process global area or PGA which helps oracle server in processing an SQL statement by holding the important information about that statement.

·   Rows returned by a query

·   Number of rows processed by a query.

·   A pointer to the parsed query in the shared pool.

·   Using cursor you can control the context area as it is a pointer to the same.

Assigned to hold the information about the processing of a select statement or DML statement

TYPES OF CURSORS:

·   implicit and

·   explicit cursor

IMPLICIT CURSOR:

Automatically created by the oracle server

User cannot control the behavior of these cursors.

Oracle servere creates an implicit cursor for any pl/sql bolck which executes an sql statement

As long as an explicit cursor does not exists for that sql statement

EXPLICIT CURSOR:

·   Explicit cursors are user defined cursors

·   User has full control of explicit cursor.

STEPS FOR CREATING AN EXPLICIT CURSOR

DECLARE

OPEN

FETCH

CLOSE

Declare

Declaring a cursor means initializing a cursor into memory

You define explicit cursor in declaration section of your pl/sql block

Syntax of declare

Cursor cursor_name is select_statement;

Open

 In order to put that cursor to work we have to open it first.

When you open a cursor the memory will be allotted to it

Syntax of open:

Open cursor_name;

Fetch:

The process of retrieving the data from the cursor is called fetching.

Syntax of fetch:

Fetch cursor_name into pl/sql variable;

Or

Fetch cursor_name into pl/sql record;

Close:

When the server comes across the closing statement of a cursor it will relinquinsh all the resources associated with it.

 Syntax of close

Close cursor_name;

BASISC PROGRAMMING STRUCTURE

Declare

Cursor cursor_name is select_statement;

Begin

Open cursor_name;

Fetch cursor_name into pl/sql variable;

Close cursor_name;

End;

Cursor attribute %notfound is a boolean attribute which returns true only when previous fetch command of the cursor did not return a row.

 1.write pl/sql procedure to update the emp salary using cursors

select *from emp;

ROLL_NO	NAME	ADDRESS	SALARY
2	B	Bangalore	41000
3	C	Mysore	51000
1	ankit	ballari	26000
4	abhishek	ballari	24000

declare

u_sal  number;

begin

update emp

set salary=salary+500;

if sql%notfound then

dbms_output.put_line('no emp selected');

elsif sql%found then

u_sal:=sql%rowcount;

dbms_output.put_line(u_sal||'emp selected');

end if;

end;

select * from emp;

ROLL_NO	NAME	ADDRESS	SALARY
2	B	Bangalore	41500
3	C	Mysore	51500
1	ankit	ballari	26500
4	abhishek	ballari	24500

 2. write pl/sql procedure to select  the emp number and name using cursors

select * from emp;

ROLL_NO	NAME	ADDRESS	SALARY
2	B	Bangalore	41500
3	C	Mysore	51500
1	ankit	ballari	26500
4	abhishek	ballari	24500

 declare

roll  emp.roll_no%type;

name emp.name%type;

cursor emp_c is

select roll_no,name from emp;

begin

open emp_c;

loop

fetch emp_c into roll,name;

exit when emp_c%notfound;

dbms_output.put_line(roll||name);

end loop;

close emp_c;

end;

 

Output:

2   B

3   C

1   ankit

4   abhishek