查询优化

Question

我编写了下面的存储过程，它使用游标查找并选择id和maximum date.Then，我根据id更新了employee表的emp_hire_date列的最大雇用日期。

但是，每当我的应用程序使用此过程时，表employee获取locked.So，是否可以在不使用游标的情况下根据id更新employee表的emp_hire_date列？

我如何优化这个查询，这样我的表就不会得到locked.Any帮助了。

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;

Answer 1

首先，用一个SQL语句替换过程代码。Oracle中的声明性代码通常比等效的过程代码更简单和更快。仍然会有一些行锁，但希望它们能保持更短的时间。

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;
/

Answer 2

我只是重新格式化您的查询：

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 ;

rec桌在哪里？这个查询不工作，请张贴整个查询，然后我们可以帮助您。