260 likes | 419 Views
Database Cursors. Database Cursor. Definition. Cursor is a control structure for the successive traversal of records in a result set. Cursor is a record pointer in a database. . DATABASE. CURSOR. Database Cursor. Use For.
E N D
Database Cursor • Definition Cursoris a control structure for the successive traversal of records in a result set. Cursor is a record pointer in a database. DATABASE CURSOR
Database Cursor • Use For A cursor is used for processing individual rows returned by the database system for a query. And use for handle a result set
Working with Cursor • Declare • Open • Fetch • Close • Free
Declare • This instruction associates a database cursor with an SQL statement in the current connection.
Cursor Type • Forward only cursors (non-scrollable) • Scrollable cursors • Hold cursors
Forward only cursor • Sequential cursor • Forward only cursor can fetch only the next row in sequence from the result set. • On each execution of the FETCH statement, the database server returns the contents of the current row and locates the next row in the result set.
Forward only cursor • Syntax • Example DECLARE cursor_name CURSOR FOR SELECT ... FROM ... DECLARE cursor1 CURSOR FOR SELECT * FROM customer_Table
Scrollable cursor • Scrollable cursor can fetch rows of the result set in any sequence. • You can fetch the first, last, or any intermediate rows of the result set as well as fetch rows repeatedly without having to close and reopen the cursor.
Scrollable cursor • Syntax • Example DECLARE cursor_name SCROLL CURSOR FOR SELECT ... FROM ... DECLARE cursor1 SCROLL CURSOR FOR SELECT * FROM customer
Hold cursor • Ordinarily, all cursors close at the end of a transaction (COMMIT or ROLLBACK). • A hold cursor does not close. It remains open after a transaction ends. • A hold cursor can be either a sequential cursor or a scrollable cursor.
Hold cursor • Syntax • Example DECLARE cursor_nameCURSOR WITH HOLD FOR SELECT ... FROM ... DECLARE cursor1 CURSOR WITH HOLD FOR SELECT * FROM customer
Open • Executes the SQL statement associated with a database cursor declared in the same connection.
Open Syntax OPEN cursor_name
Fetch • Moves a cursor to a new row in the corresponding result set and retrieves the row values into fetch buffers.
Fetch Syntax • Forward only cursor • Scrollable cursor FETCH cursor_name INTO variable [,variable2…] FETCH [ Direction ] cursor_name INTO variable [,variable2…]
Direction for Scrollable cursor • NEXT -> retrieves the next row in the result set • PREVIOUS,PRIOR -> retrieves the previous row in the result set • CURRENT -> retrieves the current row in the result set • FIRST -> retrieves the first row in the result set. • LAST -> retrieves the last row in the result set. • ABSOLUTE position -> retrieves the row at position in the result set • RELATIVE offset -> moves offset rows in the result set and returns the row at the current position
Close • Closes a database cursor and frees resources allocated on the database server for the result set.
Close Syntax CLOSE cursor_name
Free • This instruction releases resources allocated to the database cursor with the DECLARE instruction.
Free Syntax FREE cursor_name
Cursor Attributes • CURSOR_NAME%ROWCOUNT -> Return the number of row fetched so far • CURSOR_NAME%FOUND -> Return TRUE if the last FETCH returned a row -> Return FALSE if the last FETCH did not return • CURSOR_NAME%NOTFOUND -> Return FALSE if the last FETCH returned a row -> Return TRUE if the last FETCH did not return • CURSOR_NAME%ISOPEN -> Return TRUE if the cursor is open -> Return FALSE if the cursor is close
Position update/delete • Cursors can not only be used to fetch data from the DBMS into an application but also to identify a row in a table to be updated or deleted
Position update/delete • Syntax UPDATE table_name SET ... WHERE CURRENT OFcursor_name DELETE table_name SET ... WHERE CURRENT OFcursor_name
The end… • Thank you
Member • PurisChalermpug5088055 • AnawatBoochabuppajarn 5088009 • ManthanaAtipremanon 5088016 • ThunyalukKumnurdchati 5088129