Home / file parsed but not updating / Updating records using cursors

Updating records using cursors

In addition, if the department name is “Sales”, we will increase the salary of the employee by 10% by multiplying the actual salary by 1.1.

If the department name is “HR”, we will increase the salary of the employee by 20% by multiplying the actual salary by 1.2.

Unfortunately, cursors are extremely slow and so where possible should be replaced with JOINS.

To populate a cursor, database tables are iterated on row-by-row basis rather than in sets.

While a cursor is being populated, the table being iterated is locked.

Row operations can take a long time to execute depending upon the type of task being performed on each row.

To move the cursor to next row we again use FETCH NEXT.The following lines create Emp Cursor that will contain the id and name from the employee table: At this point, data is added to the cursor.Before we can use the cursor we have to open it using the OPEN keyword.We will use a JOIN statement to increment the salaries of the employees from Sales, HR and IT department by of 10%, 20%, and 50% respectively, as we did in the previous example.Note: Before you execute this script, truncate the employee table and insert dummy records as we did at the start of this article.The new salaries are shown in the following table: As explained earlier, cursors are extremely slow and should be replaced with JOINs whenever possible.Let’s look at how we replace the cursor that we saw in the last example with a JOIN statement.In the above script, we used a WHILE loop to iterate through all the records in Emp Cursor until @@FETCH_STATUS returns 0.When there are no more rows left, the @@FETCH_STATUS returns a non-zero value and the loop will exit.Let’s start by creating some dummy data to work with.Next, we need two tables “department” and “employee” with the department table having two columns: id and dep_name, and the employee table having four columns: id, name, dep_id, and salary.

324 comments

  1. PLSQL S5L5. Using Cursors for Update. Objectives. This lesson covers the following objectives • Create PL/SQL code to lock rows before an update using the.

  2. Sep 22, 2011. One way to do it is with a cursor which will update the rows. @value int SET @max_id = 1 DECLARE cursor_name CURSOR FOR SELECT.

Leave a Reply

Your email address will not be published. Required fields are marked *

*