CODEWITHSUNDEEP
oracle-databasestored-procedurescursor
user1783170
user1783170

Reputation: 113

Optimization of query

I have written below stored procedure which uses cursor to find and select id and maximum hire date.Then i updated the emp_hire_date column of employee table with maximum hire date on the basis of id.

But,whenever my application uses this procedure,then table employee getting locked.So,is it possible to update emp_hire_date column of employee table with maximum hire date on the basis of id without using cursor?
How can i optimize this query so that my table will not get locked.Any help would be appreciated.

CREATE OR REPLACE PROCEDURE updatehiredatefield
(
p_empno number
)
AS
BEGIN
CURSOR new_cursor IS
SELECT  emp.ID,MAX (act.hire_date) AS hire_date FROM employee emp LEFT JOIN department dept
               ON  dept.col1 = rec.col2 LEFT JOIN salary act ON act.sal_id = dept.id
               WHERE rec.col3 = p_empno GROUP BY emp.ID ;            
    TYPE t_row_new IS TABLE OF new_cursor%rowtype;
    t_new t_row_new;
   BEGIN           
            OPEN new_cursor ;
              LOOP
              FETCH new_cursor  BULK COLLECT
                INTO t_new LIMIT 10000;                
              FORALL i IN 1 .. t_new.COUNT
               UPDATE employee 
               SET employee.emp_hire_date = t_new(i).hire_date
                 WHERE
                 employee.ID = t_new(i).ID ;                                        
              COMMIT;
              EXIT WHEN new_cursor%NOTFOUND;
            END LOOP;
            close new_cursor ;
            COMMIT;                             
    END;
END;

Upvotes: 0

Views: 47

Answers (2)

Jon Heller
Jon Heller

Reputation: 36808

First replace the procedural code with a single SQL statement. Declarative code in Oracle is usually simpler and faster than the equivalent procedural code. There will still be some row locks but hopefully they will be held for much less time.

create or replace procedure update_hire_date_field(p_empno number) is
begin
    merge into employee
    using
    (
        select emp.id, max(act.hire_date) as hire_date
          from employee emp
          left join department dept
            on dept.col1 = rec.col2
          left join salary act
            on act.sal_id = dept.id
         where rec.col3 = p_empno --As Wernfried pointed out, this seems wrong
         group by emp.id;
    ) new_values
        on (employee.id = new_values.id)
    when matched then update set emp_hire_date = new_values.hire_date;
end;
/

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59446

I just reformat your query:

SELECT emp.ID, MAX(act.hire_date) AS hire_date 
FROM employee emp 
    LEFT OUTER JOIN department dept ON dept.col1 = rec.col2 
    LEFT OUTER JOIN salary act ON act.sal_id = dept.ID
WHERE rec.col3 = p_empno 
GROUP BY emp.ID ;

Where is table rec? This query does not work, please post entire query, then we can help you.

Upvotes: 1

Related Questions