Как создать и выполнить динамический запрос в хранимой процедуре oracle?

Мне нужно создать и выполнить запрос динамически в хранимой процедуре.

У меня есть два пользователя таблицы и user_updates.

Таблица Employee имеет 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; 

Пожалуйста помоги.

Здесь у вас есть несколько вопросов, в том числе:

  • IN_DATE объявляется как дата, поэтому вам не нужно передавать его через TO_DATE() .
  • Вам нужен только один контур курсора; если вы хотите обработать все обновления для employee_id вместе, по какой-то причине вы можете добавить предложение order by .
  • Вам вообще не нужен динамический SQL; вы можете использовать значения из курсора как часть статического обновления SQL.

Таким образом, простая версия с одним циклом может выглядеть примерно так:

 CREATE OR REPLACE PROCEDURE sp_run_employee_updates (p_date IN DATE) IS CURSOR c_updates IS SELECT * FROM bi_employee_update WHERE effective_date = p_date AND executed = 'N' AND activity_id = '0' FOR UPDATE; BEGIN -- loop around all pending records FOR r_update IN c_updates LOOP -- apply this update to the bi_employee record UPDATE bi_employee SET col1 = r_update.col1, col2 = r_update.col2 WHERE emp_id = r_update.employee_id; -- mark this update as executed UPDATE bi_employee_update SET executed = 'Y' WHERE CURRENT OF c_updates; END LOOP; END sp_run_employee_updates; 

Это используется for update и where current of конструкций для блокировки строки, с которой вы работаете, и для упрощения обновления; см. документацию здесь .

Стоит отметить, что если у параметра effective_date или p_date есть компонент времени, они не будут совпадать. Это маловероятно для p_date , но сложнее догадаться об effective_date . Если это так, вам нужно либо trunc() , либо использовать between поиском диапазона раз.