To the point guide to PL/SQL

Context area is memory creates by oracle for execution of an SQL statement. A cursor is a pointer to this context area and the context area is control by using this cursor. The set of rows are hold by cursor returned by SQL statements and this set is known as active set.

User can name a cursor which is referred to in a program to get and process the rows returned by the statement of SQL.

Cursor types:

• Implicit cursors
• Explicit cursors

Implicit Cursor:

Implicit cursor uses for internal processing by oracle. When user executes a DML/SELECT statement, a private SQL area reserves in memory by Oracle called cursor.

The implicit cursor attributes are:

Syntax:

cursor_attribute ::=
	{ 
	  cursor_name |  --name of cursor
	  cursor_variable_name | -- cursor variable or parameter
	 :host_cursor_variable_name -- must be prefixed with a colon.
	}
	% {FOUND | ISOPEN | NOTFOUND | ROWCOUNT}

Example:  

Consider the following stud_details table:

With stud_details table, Let's update the student name David's subject from 'Science' to ‘Economics '.

edit implicit_cursor
BEGIN
	UPDATE stud_details SET subject='Economics'
		WHERE sname='David';

	IF SQL%FOUND THEN
		dbms_output.put_line('If Found - Updated successful');
	END IF;

	IF SQL%NOTFOUND THEN
		dbms_output.put_line(' If NOT Found - NOT Updated');
	END IF;	

	IF SQL%ROWCOUNT>0 THEN
		dbms_output.put_line(SQL%ROWCOUNT||' Rows are Updated');
	ELSE
		dbms_output.put_line('NO Rows Updated Found!!!');
END;
/

 Above will result in 

SQL>@implicit_cursor
If Found - Updated successful
1 Rows are Updated

PL/SQL procedure successfully operation.
 

Explicit Cursors: 

Explicit cursor are defined by user where user can declare the cursor, reserve the memory by opening cursor, get the data records from the active set and finally close the cursor. Hence by using explicit cursor user/programmer gain more control over the context area.

User need to create subprogram or use expression to assign value for variable of explicit cursor where user cannot assign value to a variable of explicit cursor directly.

Step need to follow for using Explicit Cursor:

1. Cursor Declaration
2. Cursor Opening
3. Loop
4. Fetch cursor data
5. Exit loop
6. Cursor closing

Let's get to know above with relevant syntax and example

• Cursor Declaration:
  
  Syntax:
  CURSOR name_of_cursor [ parameter ] RETURN return_type;
  
  
  
• Cursor Opening:
  
  Once CURSOR is declared, we can use OPEN CURSOR and this step will make sure that cursor is allocated memory and is ready to retrieve data from our database.
  
  Syntax:
  OPEN name_of_cursor [( cursor_parameter )];
  

• Loop
  
  Loop is what we use to iterate over the found/returned data 
  
  
• Fetching data from cursor:
  
  User can fetch data of CURSOR into explicit variable by using FETCH statement.
  
  Syntax:
  FETCH name_of_cursor  INTO variable;
  
  
• Loop exit
  
  If we want a conditional exit from our loop, we will then use Loop exit
  
  
• Closing Explicit Cursor
  
  Syntax
  CLOSE name_of_cursor [( cursor_parameter )];
  

Let's check out an Example: 

stud_details table:

Let's update subject 'Science' to 'Economics' for sname David.

edit explicit_cursor
DECLARE
	cursor c is select * from stud_details
	where sname='David';
	temp stud_details%rowtype;
BEGIN 
	OPEN c;
	Loop exit when c%NOTFOUND;
	FETCH c into temp;
	update stud_details set temp.subject='Economics' WHERE sname='David';
	END Loop;
          IF c%ROWCOUNT>0 THEN
	dbms_output.put_line(SQL%ROWCOUNT||' Rows are Updated Successfully');
          ELSE
	dbms_output.put_line('Rows are not Updated');
          END IF;
CLOSE c;
END;	
/

 Result of above code is shown below

SQL>@explicit_cursor
If Found - Updated successful
1 Rows are Updated

PL/SQL procedure successfully operation.