Description
Cursors are supported inside stored programs. Whenever a client get a connection with the server, by default Oracle server provides an implicit work area to each client for its internal processing of these statements.
This work area is dedicated to a specific client. This implicit work area is called as implicit cursor. A client can access the implicit cursor with the name SQL.Cursors are used for rows repetition returned by a query on a row-by-row process. SQL commands will function on all the rows at one time in the program. Cursors can be created inside the triggers, functions and stored procedures.
Cursors contain 3-properties:
- Asensitive
- Read only
- One directional
Description
Cursors can be classified into the following types:
- Asensitive cursors
- Insensitive cursors.
- Asensitive cursor focus on genuine data.
- Asensitive cursor work faster than the insensitive cursor.
- Asensitive cursor doesn't need any duplicate copy of data.
- Any alteration done in the data through dead end should effect the data contain in asensitive cursors.
- An insensitive cursor uses duplicate copy data.
- Insensitive cursors work very slower.
- Insensitive cursors contain duplicate data.
Description
In read only data, there is only permission to read the data. And it does not contain any permissions to update the data or alter the data.
Description
Cursor moves only in one direction. Data fetching can be done in only one direction, and cannot fetch data in opposite direction.
Description
Creating Cursors is done by creating cursor with all the specifications.
Syntax
Declare <cursor_name>cursor for<select statement>
open <cursor_name>;
fetch<cursor_name>into<variable1>,<variable2>;
close<cursor_name>;
cursor_name => The name assigned to a cursor.
variables => variables are the column values.
Examples
By viewing the below example, the concept of create cursors can be easily understood.
[sql]sql> select * from stu_table03;
+--------+----------+-----------+
| stu_id | stu_name | stu_class |
+--------+----------+-----------+
| 1 | david | 10 |
| 2 | shah | 20 |
| 3 | mike | 30 |
| 4 | maze | 40 |
+--------+----------+-----------+
4 rows in set (0.00 sec)
sql> delimiter $$
sql> create procedure curdemo(id int)
-> begin
-> declare name varchar(255);
-> declare cur1 cursor for select stu_name from stu_table where stu_id=id;
-> open cur1;
-> fetch cur1 into name;
-> select name;
-> close cur1;
-> end $$
Query OK, 0 rows affected (0.13 sec)
sql> delimiter ;
sql> call curdemo(2);
+------+
| name |
+------+
| shah |
+------+
1 row in set (0.39 sec)[/sql]
In the above example, curdemo is a input parameter which accept input values. cur1 is the attribute used in the cursor. open() is used to open the cursor, fetch is used to fetch the row values into names and close cursor will remove the open cursor for same minutes. Then the call cursor will execute the curdemo and return the records(i.e, call cursor will call the stu_id(2) and it will return the name(shah)).
Description
Close Cursor is used to close the Cursor upto some extent, when the open cursor is not required. The keyword close in the cursor will discharge the present records to close and then open the open cursor.
Conceptual
figure
Syntax
Declare <cursor_name>cursor for<select statement>
open <cursor_name>;
fetch<cursor_name>into<variable1>,<variable2>;
close<cursor_name>;
Cursor_name => The name assigned to a cursor.
variables => variables are the column values. Examples
By viewing the below example, the concept of close cursors can be easily understood.
[sql]sql> select * from stu_table03;
+--------+----------+-----------+
| stu_id | stu_name | stu_class |
+--------+----------+-----------+
| 1 | david | 10 |
| 2 | shah | 20 |
| 3 | mike | 30 |
| 4 | maze | 40 |
+--------+----------+-----------+
4 rows in set (0.00 sec)
sql> delimiter $$
sql> create procedure close_cursor(id int)
-> begin
-> declare name varchar(255);
-> declare cur1 cursor for select stu_name from stu_table03 where stu_id=id;
open cur1;
-> fetch cur1 into name;
-> select name;
-> close cur1;
-> end $$
Query OK, 0 rows affected (0.02 sec)
sql> delimiter ;
sql> call close_cursor(4);
+------+
| name |
+------+
| maze |
+------+
1 row in set (0.39 sec)[/sql]
In the above example, close_cursor is a input parameter which accept input values. cur1 is the attribute used in the cursor. open() is used to open the cursor, fetch is used to fetch the record values from cursor, select name will return the name of the record and close cursor is used to close the cur1. Then the call cursor will execute the cur1 and return the records(i.e, call cursor will call the stu_id(4) and it will return the name(maze)).
Key Points
- Cursors are database objects used in the database application used to manipulate the data.
- Read only will only read the data.
- Asensitive cursor focuses to genuine information.
- Insensitive cursor utilizes duplicate information.