如何在oracle存储过程中创建和执行动态查询? [英] How to create and execute a dynamic query in oracle stored procedure?

问题描述

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

I need to create and execute a query dynamically in a stored procedure.

我有两个表 users 和 user_updates.

I have two tables users and user_updates.

Employee 表有emp_id、用户名、部门、​​产品、地区、职位等

Employee table has emp_id, username, division, product, region, title etc.

Employee_updates 包含 emp_id、effective_date、column_name、new_value 等列

Employee_updates has columns like emp_id, effective_date, column_name, new_value etc.

基本上这就是我想要做的.

Basically this is what I want to do.

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

1. Get all employees having updates in user_udates table for a given effective date.

遍历每位员工.

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

Get all updates for each employeefor the given effective date. A employee may have one or more than one updates in employee_updates table.

创建动态更新"基于这些更新的查询，如

Create a dynamic "UPDATE" query based on those updates like

更新员工集 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;

请帮忙.

推荐答案

这里有几个问题，包括:

You have a few issues here, including:

• IN_DATE 被声明为日期，所以你不需要通过 TO_DATE() 传递它.
• 你只需要一个游标循环；如果您出于某种原因想要一起处理 employee_id 的所有更新，您可以添加一个 order by 子句.
• 您根本不需要动态 SQL；您可以将游标中的值用作静态 SQL 更新的一部分.

• IN_DATE is declared as a date, so you don't need to pass it through TO_DATE().
• You only need one cursor loop; if you want to process all updates for an employee_id together for some reason you can add an order by clause.
• You don't need dynamic SQL at all; you can use the values from the cursor as part of a static SQL update.

因此，具有单个循环的简单版本可能如下所示:

So a simple version with a single loop might look something like:

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 构造来锁定您正在处理的行并简化更新；请参阅此处的文档.

This is using the for update and where current of constructs to both lock the row you're working with and to simplify the update; see the documentation here.

值得注意的是，如果 effective_date 或 p_date 具有时间组件，它们将不匹配.p_date 不太可能，但 effective_date 更难猜测.如果是，那么您要么需要 trunc() 它，要么使用 between 来查找时间范围.

It's worth noting that if either effective_date or p_date has a time component they won't match. It's unlikely for p_date, but harder to guess for effective_date. If it does then you either need to trunc() it, or use between to look for a range of times.

这篇关于如何在oracle存储过程中创建和执行动态查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！