Please help me what is cursors and where the cursors are used? please explain me by giving some suitable examples.....
Cursors A cursor is a variable that runs through the tuples of some relation. This relation can be a stored table, or it can be the answer to some query. By fetching into the cursor each tuple of the relation, we can write a program to read and process the value of each such tuple. If the relation is stored, we can also update or delete the tuple at the current cursor position. The example below illustrates a cursor loop. It uses our example relation T1(e,f) whose tuples are pairs of integers. The program will delete every tuple whose first component is less than the second, and insert the reverse tuple into T1. 1) DECLARE /* Output variables to hold the result of the query: */ 2) a T1.e%TYPE; 3) b T1.f%TYPE; /* Cursor declaration: */ 4) CURSOR T1Cursor IS 5) SELECT e, f 6) FROM T1 7) WHERE e < f 8) FOR UPDATE; 9) BEGIN 10) OPEN T1Cursor; 11) LOOP /* Retrieve each row of the result of the above query into PL/SQL variables: */ 12) FETCH T1Cursor INTO a, b; /* If there are no more rows to fetch, exit the loop: */ 13) EXIT WHEN T1Cursor%NOTFOUND; /* Delete the current tuple: */ 14) DELETE FROM T1 WHERE CURRENT OF T1Cursor; /* Insert the reverse tuple: */ 15) INSERT INTO T1 VALUES(b, a); 16) END LOOP; /* Free cursor used by the query. */ 17) CLOSE T1Cursor; 18) END; 19) . 20) run; Code (markup): Here are explanations for the various lines of this program: Line (1) introduces the declaration section. Lines (2) and (3) declare variables a and b to have types equal to the types of attributes e and f of the relation T1. Although we know these types are INTEGER, we wisely make sure that whatever types they may have are copied to the PL/SQL variables (compare with the previous example, where we were less careful and declared the corresponding variables to be of type NUMBER). Lines (4) through (8) define the cursor T1Cursor. It ranges over a relation defined by the SELECT-FROM-WHERE query. That query selects those tuples of T1 whose first component is less than the second component. Line (8) declares the cursor FOR UPDATE since we will modify T1 using this cursor later on Line (14). In general, FOR UPDATE is unnecessary if the cursor will not be used for modification. Line (9) begins the executable section of the program. Line (10) opens the cursor, an essential step. Lines (11) through (16) are a PL/SQL loop. Notice that such a loop is bracketed by LOOP and END LOOP. Within the loop we find: On Line (12), a fetch through the cursor into the local variables. In general, the FETCH statement must provide variables for each component of the tuple retrieved. Since the query of Lines (5) through (7) produces pairs, we have correctly provided two variables, and we know they are of the correct type. On Line (13), a test for the loop-breaking condition. Its meaning should be clear: %NOTFOUND after the name of a cursor is true exactly when a fetch through that cursor has failed to find any more tuples. On Line (14), a SQL DELETE statement that deletes the current tuple using the special WHERE condition CURRENT OF T1Cursor. On Line (15), a SQL INSERT statement that inserts the reverse tuple into T1. Line (17) closes the cursor. Line (18) ends the PL/SQL program. Lines (19) and (20) cause the program to execute.
a cursor comprises a control structure for the successive traversal (and potential processing) of records in a result set. Cursors provide a mechanism by which a database client iterates over the records in a database. Using cursors, the client can get, put, and delete database records. Database programmers use cursors for processing individual rows returned by the database system for a query. Cursors address the problem of impedance mismatch, an issue that occurs in many programming languages. Most procedural programming languages do not offer any mechanism for manipulating whole result-sets at once. In this scenario, the application must process rows in a result-set sequentially. Thus one can think of a database cursor as an iterator over the collection of rows in the result set. Several SQL statements do not require the use of cursors. That includes the INSERT statement, for example, as well as most forms of the DELETE and UPDATE statements. Even a SELECT statement may not involve a cursor if it is used in the variation of SELECT INTO. A SELECT INTO retrieves at most a single row directly into the application.
A cursor gives you the ability to process rows (or result sets) individually. Typically this is used for complex processing rather than simple updates. You process the cursor result set in a loop and handle each result set row by using your own SQL code (T-SQL, PL/SQL, whatever your SQL environment is).
A cursor is a variable that runs through the tuples of some relation and flexible to performe required action of each tuple
Cursors are of 2 types. Implicit and Explicit. Every Select statement is an Implcit Cursor. Cursors which you need to need Define, open, fetch and close is known as an Explicit cursor. Cursor in the above example is an Explicit cursor. Again once more classification: Static and Dynamic (Ref Cursor) Cursor attributes %FOUND - records fetched successfully %NOTFOUND - no records fetched %ROWCOUNT - Number of records fetched %ISOPEN - returns TRUE if cursor is open Read more about: Oracle Cursors Sippsin Author, Oraclepassport .com Oracle Tutorials, FAQs and Discussions Board
Cursor is the mechanism in which client can intract with database can fetch the records and based on that perform and insert,update,delete action on each and every row. here i have used one cursor to insert the information into other table by fetching from one table i have used it into MYSqL CREATE DEFINER=`root`@`localhost` PROCEDURE `cursor_to_insert_another_time_and_education`( mindoc int, maxdoc int ) BEGIN DECLARE done INT DEFAULT 0; DECLARE ID1 INT ; DECLARE cur1 CURSOR FOR SELECT ID from dtbl_test where ID between mindoc and maxdoc; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN cur1; REPEAT FETCH cur1 INTO ID1; IF NOT done THEN insert into dtbl_office_time (ID,Monday_Start,Monday_End,Tues_start,Tues_End,Wed_Start,Wed_End,Thu_Start,Thu_End,Fri_start,Fri_End,Sat_Start,Sat_end,Sun_start,Sun_End) values (ID1,'9.00','17.00','9.00','17.00','9.00','17.00','9.00','17.00','9.00','17.00','0','0','0','0'); insert into dtbl_education(ID)values(ID1); END IF; UNTIL done END REPEAT; CLOSE cur1; END $$ DELIMITER ;