A great overview about Oracle cursors



Oracle Cursor

Oracle Uses work area called Private SQL areas to execute SQL Statements and store information. A Oracle Cursor is a PL/SQL construct that allows you to name these work areas, and to access their stored information

Types of Cursor

  • Implicit Cursors
  • Explicit Cursors

 

Implicit cursors Explicit Cursors
Implicit Cursor are declared by PL/SQL implicitly for all DML Statements and for single row queries

Example: Select Statement issued directly within the BEGIN .. END part of a block opens up an implicit cursor.

 

Declared and named by the programmer

Explicit Cursors allow multiple rows to be processed from the query.

 

Use explicit cursor to individually process all the rows returned by multiple row select statement

Example

DECLARE

  v_salary number;

BEGIN

  SELECT  emp_ salary INTO v_salary  FROM emp where empno=1111;

  DBMS_OUTPUT.PUT_LINE(v_salary);

END;

/

 

 
Four attributes

 

•          SQL%NOTFOUND

•           SQL%FOUND

•           SQL%ISOPEN

       SQL%ROWCOUNT

 

 

Explicit Cursors

Explicit cursors have the following stages

1) Declare : We declare the name of the cursor and define the structure of the query

2) Open:  The open statement executes the query and bind any variable that are referenced. Rows identified by the query is called Active set. And now that is available for fetching

What is Active Set:  The Set of rows returned by a multiple row query

Its Size is the number of rows that meets your search criteria

3) Fetch: In this stage , rows are fetched from the cursor and after each fetch you test the cursor for any remaining rows, if no rows, you proceed for closing the cursor

4) Close: The close statement release the active set of rows and we can again open the cursor to fetch refresh active set

Oracle Cursor

Stages in details

Declaring a Cursor

  • Cursor Name
  • Structure of the Query

 Syntax:

CURSOR <cursor-name> IS <select statement>

<select statement>:  It includes most of the usual clauses,

but INTO Clause is not allowed

Example:

DECLARE

CURSOR cur1 is SELECT address,salary,emp_no,emp_name FROM emp

WHERE region=’US’;

………………………………

BEGIN

……………<Executable Statements>…………………

END;

Opening a Cursor

Here Parsing and Query execution is done. After Opening the Cursor the rows returned by the query are available for fetching.

Syntax:

Open <cursor-name>;

This statement is used within the executable section of the block.

It also establishes an active set of the rows

Example:

            OPEN cur1;

DECLARE

v_salary number;

CURSOR cur1 is SELECT salary FROM emp WHERE   region=’US’;

BEGIN

OPEN cur1;

LOOP

FETCH  cur1 into v_salary;

EXIT WHEN cur1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE (v_salary);

END LOOP;

CLOSE cur1;

END;

/

The cursor will now point to the first row in the active set.

Fetching the rows

After the cursor is opened the current row is loaded  into variables. The current row is the row at which

the cursor is currently pointing The retrieval of data into PL/SQL variable or host

Variable is done through FETCH statement

 

Syntax:

FETCH <cursor-name> INTO <variables>;

  • For each column value returned by the query associated with the cursor, there must be a

corresponding variable in the INTO list.

  • ALSO their Datatypes must be compatible

 

CLOSING A CURSOR

It explicitly closes the cursor, allowing it to be  Reopened , if required.

 

Syntax:

CLOSE <cursor-name>;

Example:

CLOSE cur1

 

Example of Using CLOSE <Cursor Name>

DECLARE

v_address emp.address%TYPE;

CURSOR cur1 is select address from emp;

BEGIN

OPEN cur1;

LOOP

FETCH cur1 into v_address;

EXIT WHEN cur1%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_address);

END LOOP;

CLOSE cur1;

END;

/

 

 

Explicit Cursor Attributes

 

Attribute Type Description
%ISOPEN Boolean Evaluates to TRUE if the cursor is open
%NOTFOUND Boolean Evaluates to TRUE if the most recent fetch does not return a row
%FOUND Boolean Evaluates to TRUE if the most recent fetch returns a row
%ROWCOUNT NUMBER Evaluates to the total number of rows returned so far

 

Advanced concept with cursors

Cursor and Records

1)We have already read about PLSQL records.

2)We can process the rows of the active set by fetching values into a PL/SQL Record also

3) We can also define a plsql record based on the selected list of the columns in the explicit cursors also

Example

DECLARE

CURSOR dept_cursor is Select deptno,dept_name  FROM   dept;

dept_record dept_cursor%rowtype;

BEGIN

OPEN dept_cursor;

LOOP

FETCH dept_cursor INTO dept_record;

Insert into dept_temp (deptno, deptname) values (dept_record.deptno, dept_record.dept_name);

EXIT WHEN dept_cursor%notfound;

END LOOP;

Commit;

CLOSE dept_cursor;

END;

/

 

Cursor FOR Loops

Oracle Cursor

1)The cursor FOR loop is a shortcut to process Explicit cursors

2) Implicit Open,fetch,exit and close occurs

3) The record is implicitly declared

 

Example

DECLARE

CURSOR dept_cursor is Select deptno,dept_name  FROM   dept;

BEGIN

FOR dept_record IN dept_cursor

LOOP

Insert into dept_temp (deptno, deptname) values (dept_record.deptno, dept_record.dept_name);

END LOOP;

Commit;

END;

/

 

 

Cursor For Loops Using Sub queries

We can also cursor for loops using subqueries . No need to declare the cursor

Example

BEGIN

FOR dept_record IN (Select deptno,dept_name  FROM   dept)

LOOP

Insert into dept_temp (deptno, deptname) values (dept_record.deptno, dept_record.dept_name);     END IF;

END LOOP;

Commit;

END;

/

 

Cursor with Parameters

Syntax:

CURSOR cursor_name(parameter_name datatype,  )

IS

Select_statement;

 

1)Pass parameter values to a cursor when the cursor is opened and the query is executed

2) Open an explicit cursor several times with different active set each time

Open cursor_name(parameter_value , ……);

 

 

Example

DECLARE

CURSOR cur1(v_deptno number) is

Select deptname,dept_loc,dept_pincode from dept where deptno=v_deptno;

Dept_record      cur1%rowtype;

l_deptno  number:=111;

BEGIN

OPEN  cur1(112);

LOOP

FETCH cur1 into dept_record;

IF cur1%FOUND THEN

Dbms_output.put_line(dept_record.deptname);

ELSE

Exit;

END IF;

END LOOP;

CLOSE cur1;

OPEN cur1(l_deptno);

LOOP

FETCH cur1 into dept_record;

IF cur1%FOUND THEN

Dbms_output.put_line(dept_record.deptname);

ELSE

Exit;

END IF;

END LOOP;

CLOSE cur1;

END;

/

 

 

For Update clause of Oracle Cursor

1) For Update Clause can be used within the cursor query. This means that rows returned by the query are locked exclusively when the OPEN statement is processed. Since locks are released at the end of the transaction. Commit command should not be given across fetches from an explicit cursor if FOR UPDATE is used.

2) The For Update clause is the last clause in a select statement, even   after the ORDER BY.

3)  Lock only those records which are satisfied by condition..

4) NOWAIT : Returns an Oracle error if the rows are locked by another   session

5) we use the below clause when to update the rows in the cursor with update

Where current of <cursor name>

 

Example

DECLARE

Cursor cur1 is SELECT emp_salary from emp_master where country=’IN’  FOR UPDATE OF emp_salary  NOWAIT;

BEGIN

FOR emp_record in cur1

LOOP

IF emp_record. emp_salary <10000 then

Update emp_master set emp_salary= 2*emp_record.emp_salary

Where  Current of cur1;

END IF;

END LOOP;

END;

/

 

 


Leave a Reply