Database Cursors

1. Database Cursors

2. 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

3. 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

4. Working with Cursor • Declare • Open • Fetch • Close • Free

5. Declare • This instruction associates a database cursor with an SQL statement in the current connection.

6. Cursor Type • Forward only cursors (non-scrollable) • Scrollable cursors • Hold cursors

7. 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.

8. Forward only cursor • Syntax • Example DECLARE cursor_name CURSOR FOR SELECT ... FROM ... DECLARE cursor1 CURSOR FOR SELECT * FROM customer_Table

9. 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.

10. Scrollable cursor • Syntax • Example DECLARE cursor_name SCROLL CURSOR FOR SELECT ... FROM ... DECLARE cursor1 SCROLL CURSOR FOR SELECT * FROM customer

11. 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.

12. Hold cursor • Syntax • Example DECLARE cursor_nameCURSOR WITH HOLD FOR SELECT ... FROM ... DECLARE cursor1 CURSOR WITH HOLD FOR SELECT * FROM customer

13. Open • Executes the SQL statement associated with a database cursor declared in the same connection.

14. Open Syntax OPEN cursor_name

15. Fetch • Moves a cursor to a new row in the corresponding result set and retrieves the row values into fetch buffers.

16. Fetch Syntax • Forward only cursor • Scrollable cursor FETCH cursor_name INTO variable [,variable2…] FETCH [ Direction ] cursor_name INTO variable [,variable2…]

17. 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

18. Close • Closes a database cursor and frees resources allocated on the database server for the result set.

19. Close Syntax CLOSE cursor_name

20. Free • This instruction releases resources allocated to the database cursor with the DECLARE instruction.

21. Free Syntax FREE cursor_name

22. 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

23. 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

24. Position update/delete • Syntax UPDATE table_name SET ... WHERE CURRENT OFcursor_name DELETE table_name SET ... WHERE CURRENT OFcursor_name

25. The end… • Thank you

26. Member • Puris Chalermpug 5088055 • Anawat Boochabuppajarn 5088009 • Manthana  Atipremanon 5088016 • Thunyaluk Kumnurdchati 5088129