1 / 7

Cursors_in_SQL

Cursors in SQL <br>dbms

Dr1872
Download Presentation

Cursors_in_SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Cursors in SQL Understanding Cursors, Types, Syntax & Usage

  2. Introduction to Cursors • A cursor is a database object used to retrieve data row by row. • Unlike a SELECT statement, which retrieves all rows at once, a cursor processes rows individually. • Cursors are commonly used when operations need to be performed on each row separately.

  3. Types of Cursors 1. Implicit Cursor: Automatically created by SQLwhen DML statements are executed. 2. Explicit Cursor: Defined by the user for complex row-wise operations. 3. Static Cursor: Works with a snapshot of data. 4. Dynamic Cursor: Reflects real-time changes in thedatabase. 5. Forward-Only Cursor: Can only move forward throughrecords. 6.Scrollable Cursor: Can move both forward andbackward.

  4. How Cursors Work • 1. Declare the cursor. • 2. Open the cursor. • 3. Fetch rows one by one. • 4. Process each row. • 5. Close the cursor. • 6. Deallocate the cursor.

  5. Step 1: Declare a Cursor Define the cursor and specify the SQL query that retrieves the data. sql DECLARE my_cursor CURSOR FOR SELECT column1, column2 FROM my_table; Step 2: Open the Cursor Execute the SQL query and make the result set available. sql OPEN my_cursor; Step 3: Fetch Rows Retrieve data from the cursor one row at a time using FETCH. sql FETCH NEXT FROM my_cursor INTO @var1, @var2;

  6. Step 3: Fetch Rows Retrieve data from the cursor one row at a time using FETCH. sql FETCH NEXT FROM my_cursor INTO @var1, @var2; Step 4: Process the Data You can use a WHILE loop to process each row. sql WHILE @@FETCH_STATUS = 0 BEGIN -- Process data here (e.g., print, insert into another table) FETCH NEXT FROM my_cursor INTO @var1, @var2; END; Step 5: Close and Deallocate the Cursor • CLOSE releases the current result set. • DEALLOCATE removes the cursor definition. sql CLOSE my_cursor; DEALLOCATE my_cursor;

  7. Advantages & Disadvantages Advantages: • Allows row-by-row processing. • Useful for handling complex business logic. Disadvantages: • Slower than set-based operations. • Consumes more memory and processing power.

More Related