我需要在存储过程中动态创建和执行查询。

我有两个表user和user_updates。

员工表有 emp_id, username, division, product, region, title etc.

Employee_updates具有类似以下的列 emp_id, effective_date, column_name, new_value etc.

基本上，这就是我想要做的。

1. 获取所有在给定生效日期user_udates表中有更新的员工。

2. 遍历每位员工。

3. 获取给定生效日期的每位员工的所有更新。员工可能在employee_updates表中具有一个或多个更新。

4. 根据这些更新创建动态的“ UPDATE”查询，例如

update employee set col1 = new_val_1, col2 = new_val_2 where emp_id = ?

这是我到目前为止所做的

    create or replace
PROCEDURE SP_RUN_EMPLOYEE_UPDATES 
(
  IN_DATE IN DATE
) 
  IS

    update_sql varchar2(225);

employee_id BI_EMPLOYEE_UPDATE.employee_id%TYPE;

    CURSOR  employees 
        IS   SELECT distinct(employee_id)
               FROM BI_EMPLOYEE_UPDATE 
             WHERE EFFECTIVE_DATE = to_date(IN_DATE,'dd-mm-yy') 
              AND EXECUTED = 'N' AND ACTIVITY_ID = '0'
              ;

    CURSOR e_updates 
    IS  SELECT * 
         FROM BI_EMPLOYEE_UPDATE 
        WHERE EFFECTIVE_DATE = to_date(IN_DATE,'dd-mm-yy') 
         AND EXECUTED = 'N' 
         AND ACTIVITY_ID = '0' 
         and employee_id = employee_id ;

     BEGIN

   OPEN employees;
   LOOP
    FETCH employees into employee_id;
        EXIT WHEN employees%NOTFOUND;

           FOR e_update in e_updates
         update_sql :=  'UPDATE BI_EMPLOYEE SET ';
              LOOP
                -- create dynam,ic update statment

                 UPDATE BI_EMPLOYEE_UPDATE
                 SET EXECUTED = 'Y'
                 WHERE EMPLOYEE_UPDATE_ID = e_update.EMPLOYEE_UPDATE_ID ;

                END LOOP;

            -- run dynamic sql

              END LOOP;
 CLOSE employees;
END;

请帮忙。