SQL Cursors and It’s Use In VB Dot NET

1
SQL Cursors and Its Use In VB.NET
VB Dot NET courses
2

• Cursor Definition

• Cursor is basically a database object used to
  recover data from a result set line by line,
  rather than the T-SQL commands that work on every
  one of the rows in the result set at one time. We
  utilize cursor when we have to update records in
  a database table in singleton style implies line
  by line.

VB Dot NET courses
3
Basic Syntax of A Cursor

• DECLARE cursor_name CURSOR
• LOCAL GLOBAL scope of cursor is defined over
  here.
• FORWARD_ONLY SCROLL the direction of cursor
  movement is defined in this line
  (forward/backward)
• STATIC KEYSET DYNAMIC FAST_FORWARD
  basic type of cursor is declared over here.
• READ_ONLY SCROLL_LOCKS OPTIMISTIC define
  locks
• FOR select_statement SQL Select statement is
  mentioned
• FOR UPDATE col1,col2,...coln names of columns
  that need to be updated are passed here as
  parameters.

VB Dot NET courses
4
Cursor Life Cycle

• 1. Cursor Declaration
• For declaring a cursor, one needs to define a SQL
  statement which returns a result set.
• 2. Open
• Execute the SQL statement defined by the cursor
  if you wish to populate and open the cursor.
• 3. Fetch
• Once a cursor is opened, then we can access the
  rows from the cursor one by one or in bulk manner
  for data manipulation.

VB Dot NET courses
VB Dot NET courses
5
Cursor Life Cycle

• 4. Close
• A cursor needs to be closed explicitly, once data
  manipulation is done.
• 5. Deallocate
• Once the job is done, the cursor needs to be
  freed for further use. All system resources
  associated with the cursor are released using
  this command. It is just like freeing the memory.

VB Dot NET courses
6
Scrollable Cursors

• Developers may declare cursors as scrollable or
  not scrollable. The scrollability depicts the
  direction in which a cursor can move.
• A line of code may position a scrollable cursor
  anyplace in the result set utilizing the FETCH
  SQL command. The SCROLL keyword must be
  determined while declaration of the cursor. The
  default value is NO SCROLL, albeit distinctive
  language ties like JDBC may apply an alternate
  default.
• Using a non-scrollable (or forward-only) cursor,
  you can FETCH every line at most once, and the
  cursor naturally moves to the following line.

VB Dot NET courses
7
Conclusion

• Utilizing SQL Cursors inside a loop will empower
  you to spare a great deal of time and handling
  power to get the best execution out of your SQL
  databases.
• If its not too much trouble keep investigating
  the universe of SQL to see everything that you
  can do with databases.

VB Dot NET courses
VB Dot NET courses
8
Thank you...
VB Dot NET courses