CURSOR
• A cursor is a handle or pointer to the context area. Through
the cursor a PL/SQL program can control the context area and
what happens to it as the statements is processed.
• 1) Cursor allow you to fetch and process rows returned
by a SELECT
• statement, one row at a time.
• 2) A cursor is named so that it can be referenced.
Types of cursors:
• An implicit cursor is automatically declared by oracle every
time an SQL statement is executed.
• An explicit cursor is defined by the program for any query that
returns more than one row of data.
• That means the programmer has declared the cursor within
the PL/SQL code block.
•
EXPLICIT CURSOR
• The only means of generating an explicit cursor is for the cursor to be named in the
DECLARE section of the PL/SQL block.
•
• Declaring the cursor defines the name of the cursor and associatedit with a SELECT
statements
•
• CURSOR cursor_name IS SELECT statement
•
• Opening the cursor is to process the select statement and set a active pointer to the first row
• OPEN cursor_name;
•
• Fetching cursor into PL/SQL local variables, these variables are declared as ROWTYPE in
declare section
• FETCH cursor_name INTO PL/SQL variables
•
• Closing a cursor by CLOSE cursor_name;
Explicit cursor attributes
Cursor Attribute Syntax Explanation
%NOTFOUND Cursor_name%NOTFOUND A Boolean attribute that returns TRUE
if the previous FETCH did not return a
row and FALSE if it did
%FOUND Cursor_name%FOUND A Boolean attribute that returns TRUE
if the previous FETCH did return a row
and FALSE if it did not
%ROWCOUNT Cursor_name%ROWCOUNT # of records fetched from a cursor at
that point in time
%ISOPEN Cursor_name%ISOPEN A Boolean attribute that returns TRUE
if cursor is open, FALSE if it is not.
Implicit cursor example
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+----+----------+-----+-----------+----------+
• The following program will update the table and increase the
salary of each customer by 500 and use the SQL
%ROWCOUNT attribute to determine the number of rows
affected −
DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 500;
IF sql%notfound THEN
dbms_output.put_line('no customers selected');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers selected ');
END IF;
END;
/
• When the above code is executed at the SQL prompt, it
produces the following result −
• 6 customers selected PL/SQL procedure successfully
completed.
• Select * from customers;
• +----+----------+-----+-----------+----------+
• | ID | NAME | AGE | ADDRESS | SALARY |
• +----+----------+-----+-----------+----------+
• | 1 | Ramesh | 32 | Ahmedabad | 2500.00 |
• | 2 | Khilan | 25 | Delhi | 2000.00 |
• | 3 | kaushik | 23 | Kota | 2500.00 |
• | 4 | Chaitali | 25 | Mumbai | 7000.00 |
• | 5 | Hardik | 27 | Bhopal | 9000.00 |
• | 6 | Komal | 22 | MP | 5000.00 |
• +----+----------+-----+-----------+----------+
Explicit Cursors
• Working with an explicit cursor includes the following steps −
• Declaring the cursor for initializing the memory
• Opening the cursor for allocating the memory
• Fetching the cursor for retrieving the data
• Closing the cursor to release the allocated memory
Declaring the Cursor
• Declaring the cursor defines the cursor with a name and the
associated SELECT statement. For example −
• CURSOR c_customers IS SELECT id, name, address FROM
customers;
Opening the Cursor
• Opening the cursor allocates the memory for the cursor and
makes it ready for fetching the rows returned by the SQL
statement into it. For example, we will open the above
defined cursor as follows −
• OPEN c_customers;
Fetching the Cursor
• Fetching the cursor involves accessing one row at a time. For
example, we will fetch rows from the above-opened cursor as
follows −
• FETCH c_customers INTO c_id, c_name, c_addr;
Closing the Cursor
• Closing the cursor means releasing the allocated memory. For
example, we will close the above-opened cursor as follows −
• CLOSE c_customers;
Example
• DECLARE
• c_id customers.id%type;
• c_name customer.name%type;
• c_addr customers.address%type;
• CURSOR c_customers is
• SELECT id, name, address FROM customers;
• BEGIN
• OPEN c_customers;
• LOOP
• FETCH c_customers into c_id, c_name, c_addr;
• EXIT WHEN c_customers%notfound;
• dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
• END LOOP;
• CLOSE c_customers;
• END;
• /
• Thank you
