PL/SQL Cursor

 

PL/SQL Cursor

SQL statement processed in a memory area known as context area.

The context area is controlled by the cursor.

A cursor is a pointer.

It contains all information about processing statements such as select statements and accessed the rows data.

The set of rows that are held by the cursor at a time is known as the active set.

 

There are two types of cursors:

• Implicit Cursors
• Explicit Cursors

 

1) Implicit Cursors

Automatically generated by Oracle when an SQL statement is executed.

It is by default to process the DML statements like INSERT, UPDATE, DELETE, etc.

Oracle provides some attributes known as Implicit cursor's attributes to check the status of DML operations.

These cursors have no named and not controlled or referred from another place of the code.

 

Following Implicit cursor's attributes are:-

Attribute	Description
%FOUND   Syntax:- Sql%found	Its the return value is TRUE if DML statements like INSERT, DELETE, and UPDATE effect at least one row or more rows or a SELECT INTO statement returned one or more rows. Otherwise it returns FALSE.
%NOTFOUND   Syntax:- Sql%notfound	Its return value is TRUE if DML statements like INSERT, DELETE and UPDATE affect no row, or a SELECT INTO statement return no rows. Otherwise, it returns FALSE. It is a just opposite of %FOUND?
%ISOPEN   Syntax:- Sql%isopen	It always returns FALSE for implicit cursors, because the SQL cursor is automatically closed after executing its associated SQL statements.
%ROWCOUNT   Syntax:- Sql%rowcount	It returns the number of rows affected by DML statements like INSERT, DELETE, and UPDATE or returned by a SELECT INTO statement.

Example:-

Table:- Customers:

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	23	Allahabad	20000
2	Suresh	22	Kanpur	22000
3	Mahesh	24	Ghaziabad	24000
4	Chandan	25	Noida	26000
5	Alex	21	Paris	28000
6	Sunita	20	Delhi	30000

Create procedure:

1.    DECLARE   

2.    total_rows number(2);  

3.    BEGIN  

4.    UPDATE  customers  

5.       SET salary = salary + 5000;  

6.    IF sql%notfound THEN  

7.          dbms_output.put_line('no customers updated');  

8.    ELSIF sql%found THEN  

9.          total_rows := sql%rowcount;  

10.       dbms_output.put_line( total_rows || ' customers updated ');  

11.    END IF;   

12. END;  

13. /  

 

Output:

6 customers updated

PL/SQL procedure successfully completed.

 

Select * from customers;  

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	23	Allahabad	25000
2	Suresh	22	Kanpur	27000
3	Mahesh	24	Ghaziabad	29000
4	Chandan	25	Noida	31000
5	Alex	21	Paris	33000
6	Sunita	20	Delhi	35000

2) Explicit Cursors

Defined by the programmers for more control over the context area.

Defined in the declaration section of the PL/SQL block.

Created by a SELECT statement which returns one and more than one row.

 

Syntax:-

CURSOR cursor_name IS select_statement from table_name;

Ex:- CURSOR cur1 is   SELECT id, name, address FROM customers;  

 

Using Steps for explicit cursor:

1. Declare the cursor for initializing in the memory.
2. Open the cursor for allocate memory.
3. Fetch the cursor for retrieve data.
4. Close the cursor for release allocated memory.

 

1) Declare the cursor:

Defines the cursor name and the associated SELECT statement.

Syntax:-

1.    CURSOR name IS  SELECT statement;   

 

2) Open the cursor:

Allocate cursor memory to fetch the rows returned by the SQL statements.

Syntax:-

OPEN cursor_name;  

 

3) Fetch the cursor:

Access one row at a time.

Syntax:-

FETCH cursor_name INTO variable_list;  

EX. FETCH c_customers INTO c_id, c_name, c_addr; 

 

4) Close the cursor:

Release the allocated memory.

Syntax:-

Close cursor_name;  

 

Example

Create customers table and have recorded:

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	23	Allahabad	20000
2	Suresh	22	Kanpur	22000
3	Mahesh	24	Ghaziabad	24000
4	Chandan	25	Noida	26000
5	Alex	21	Paris	28000
6	Sunita	20	Delhi	30000

 

 

Create procedure:

Execute the following program to retrieve the customer name and address.

1.    DECLARE  

2.    c_id  customers.id%type;  

3.       c_name  customers.name%type;  

4.    c_addr  customers.address%type;  

5.       CURSOR c0_customers is  

6.       SELECT id, name, address FROM customers;  

7.    BEGIN  

8.    OPEN c_customers;  

9.       LOOP  

10.       FETCH c_customers into c_id, c_name, c_addr;  

11.       EXIT WHEN c_customers%notfound;  

12.       dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);  

13.    END LOOP;  

14.    CLOSE c_customers;  

15. END;  

16. /  

 

Output:

1  Ramesh        Allahabad

2  Suresh          Kanpur

3  Mahesh         Ghaziabad

4  Chandan      Noida

5  Alex               Paris

6  Sunita           Delhi

PL/SQL procedure successfully completed.

 

FOR LOOP Cursor

A Cursor FOR LOOP is a loop. It is automatically checks for the row count and exits the loop when all the data stored in the cursor is iterated.

• A cursor FOR loop automatically perform following tasks:
• Implicitly declares its loop index as a %rowtype record
• Opens the cursor
• Retrieves the record from the cursor for each iteration
• Closes the cursor after processing all the records.
• A cursor can also be closed by using EXIT or GOTO statements.

 Syntax:

FOR variable_name IN cursor_name LOOP

            -- Executable statements

END LOOP;

 

Examples:-

 

DECLARE

               CURSOR student_cursor IS SELECT sname FROM Student;

BEGIN

               FOR snm IN student_cursor LOOP           

                               dbms_output.put_line('' || snm);

               END LOOP;

END;

/

 OUTPUT:- 

Anu

Asha

Arpit

Chetan

Nihal

PL/SQL procedure successfully completed.

==========================================================

  Parameterized Cursor

A parameterized cursor is a cursor with arguments.

it allows us to create dynamic SQL queries with conditions containing the variables.

syntax:

CURSOR cursor_name (variable_name Datatype) IS <SELECT statement...>;

example:

set serveroutput on;

 DECLARE

 CURSOR showRec(sno student.rollno%type) IS SELECT sname, course FROM student WHERE rollno=sno;

    a  student.sname%type;

b  student.course%type;

c  student.rollno%type;

 BEGIN

d := &rollno;

OPEN showRec(d);

IF showRec%Isopen = FALSE then

dbms_output.put_line('Cannot open Cursor');

ELSE

               LOOP

               FETCH showRec into a,b;

               EXIT WHEN showRec%NOTFOUND;

               dbms_output.put_line(a|| '' ||b);

               END LOOP;

               End IF;

               CLOSE showRec;

END;

 

Output:- 

 Enter the value for d:12

--------------------

| SNAME | COURSE |

====================

| Asha | BCOM |

--------------------

PL/SQL procedure successfully completed.