Oracle PL/SQL动态更改变量值

问题描述：

我试图在Oracle中使用动态查询概念在查询中更改表名。Oracle PL/SQL动态更改变量值

初始执行情况良好。但是，一旦表名称修改为新值，即使它显示为旧值。

检查下面的代码....

CREATE OR REPLACE PROCEDURE Test 
AS 
BEGIN 
    DECLARE 
    DELETE_OLD_YEARS NUMBER(2); 
    RECORD_COUNT NUMBER(10); 
    INTERVAL_UNIT VARCHAR2(4); 
    DYNA_QUERY_DEL VARCHAR2(280); 
    DYNA_TABLE_NAME VARCHAR(30); 

    BEGIN 

    INTERVAL_UNIT := 'YEAR'; 
    RECORD_COUNT := 0; 
    DYNA_TABLE_NAME := 'UserData'; 
    DELETE_OLD_YEARS := 7; 

    DYNA_QUERY_DEL := 'SELECT COUNT(*) 
         FROM ' || DYNA_TABLE_NAME || ' UD 
         WHERE UD.LOGIN_DT < (SYSDATE - NUMTOYMINTERVAL(:1, :2)) 
         OR UD.LOGIN_DT IS NULL ORDER BY UD.LOGIN_DT DESC'; 

    -- Delete older than 7 years data 
    DBMS_OUTPUT.PUT_LINE('Dynamic Query with UserData: ' || DYNA_QUERY_DEL); 
    EXECUTE IMMEDIATE DYNA_QUERY_DEL INTO RECORD_COUNT USING DELETE_OLD_YEARS, INTERVAL_UNIT; 
    DBMS_OUTPUT.PUT_LINE('Record Count : ' || RECORD_COUNT); 


    -- Delete older than 7 years data from backup table. 
    DYNA_TABLE_NAME := 'UserData_Backup'; 
    DELETE_OLD_YEARS := 7; 
    DBMS_OUTPUT.PUT_LINE('Dynamic Query with UserData_Backup : ' || DYNA_QUERY_DEL); 
    EXECUTE IMMEDIATE DYNA_QUERY_DEL INTO RECORD_COUNT USING DELETE_OLD_YEARS, INTERVAL_UNIT; 
    DBMS_OUTPUT.PUT_LINE('Record Count 2 : ' || RECORD_COUNT); 

    END; 

END; 
/

exec Test;

，输出是....

Dynamic Query with UserData : SELECT COUNT(*) 
         FROM UserData UD 
         WHERE UD.LOGIN_DT < (SYSDATE - NUMTOYMINTERVAL(:1, :2)) 
         OR UD.LOGIN_DT IS NULL ORDER BY UD.LOGIN_DT DESC 
Record Count : 6220 
Dynamic Query with UserData_Backup : SELECT COUNT(*) 
         FROM UserData UD 
         WHERE UD.LOGIN_DT < (SYSDATE - NUMTOYMINTERVAL(:1, :2)) 
         OR UD.LOGIN_DT IS NULL ORDER BY UD.LOGIN_DT DESC 
Record Count 2 : 6220

但这里的第二个查询应与UserData_Backup表准备。

请帮我找出这个问题....

希望我的解决方案将帮助你..检查。！ –

您没有更改表名，它已经被硬编码到DYNA_QUERY_DEL中。您只更改了未再使用的变量的值。使用动态SQL有什么特别的原因吗？ –

答

动态查询将起到一个时间的计划，除非你是使用For循环。如果你不想使用循环，你可以使用相同的查询来第二次动态执行，在这里我使用循环函数。

代码

CREATE OR REPLACE PROCEDURE Test 
     AS 
     BEGIN 
      DECLARE 
      DELETE_OLD_YEARS NUMBER(2); 
      RECORD_COUNT NUMBER(10); 
      INTERVAL_UNIT VARCHAR2(4); 
      DYNA_QUERY_DEL VARCHAR2(280); 
      DYNA_TABLE_NAME VARCHAR(30); 
      BEGIN 
      INTERVAL_UNIT := 'YEAR'; 
      RECORD_COUNT := 0; 
      --DYNA_TABLE_NAME := 'UserData'; 
      -- DYNA_TABLE_NAME := 'UserData_Backup'; 
      DELETE_OLD_YEARS := 7; 
      for c1 in (select 'UserData' as DYNA_TABLE_NAME from dual 
         union all 
         select 'UserData_Backup' as DYNA_TABLE_NAME from dual) 
      loop 
      DYNA_QUERY_DEL := 'SELECT COUNT(*) 
           FROM ' || DYNA_TABLE_NAME || ' UD 
           WHERE UD.LOGIN_DT < (SYSDATE - NUMTOYMINTERVAL(:1, :2)) 
           OR UD.LOGIN_DT IS NULL ORDER BY UD.LOGIN_DT DESC'; 

      -- Delete older than 7 years data 
      DBMS_OUTPUT.PUT_LINE('Dynamic Query with UserData: ' || DYNA_QUERY_DEL); 
      EXECUTE IMMEDIATE DYNA_QUERY_DEL INTO RECORD_COUNT USING DELETE_OLD_YEARS, INTERVAL_UNIT; 
      RECORD_COUNT := RECORD_COUNT +1; 
      end loop; 
      DBMS_OUTPUT.PUT_LINE('Record Count 2 : ' || RECORD_COUNT); 
      END; 
     END; 
     /
     exec Test;

是的，重新分配相同的查询正在工作。但是，我们是否有其他方法来刷新缓存或DYNA_TABLE_NAME变量的会话？ – srihariraom

耶...备用循环方法我已经在我的代码更新检查它。 –

对您有帮助吗？ –

答

重新分配变量DYNA_QUERY_DEL尝试做这样的

CREATE OR REPLACE PROCEDURE Test 
AS 
BEGIN 
    DECLARE 
    DELETE_OLD_YEARS NUMBER(2); 
    RECORD_COUNT NUMBER(10); 
    INTERVAL_UNIT VARCHAR2(4); 
    DYNA_QUERY_DEL VARCHAR2(280); 
    DYNA_TABLE_NAME VARCHAR(30); 

    BEGIN 

    INTERVAL_UNIT := 'YEAR'; 
    RECORD_COUNT := 0; 
    DYNA_TABLE_NAME := 'UserData'; 
    DELETE_OLD_YEARS := 7; 

    DYNA_QUERY_DEL := 'SELECT COUNT(*) 
         FROM ' || DYNA_TABLE_NAME || ' UD 
         WHERE UD.LOGIN_DT < (SYSDATE - NUMTOYMINTERVAL(:1, :2)) 
         OR UD.LOGIN_DT IS NULL ORDER BY UD.LOGIN_DT DESC'; 

    -- Delete older than 7 years data 
    DBMS_OUTPUT.PUT_LINE('Dynamic Query with UserData: ' || DYNA_QUERY_DEL); 
    EXECUTE IMMEDIATE DYNA_QUERY_DEL INTO RECORD_COUNT USING DELETE_OLD_YEARS, INTERVAL_UNIT; 
    DBMS_OUTPUT.PUT_LINE('Record Count : ' || RECORD_COUNT); 


    -- Delete older than 7 years data from backup table. 
     DYNA_TABLE_NAME := 'UserData_Backup'; 

    DYNA_QUERY_DEL := 'SELECT COUNT(*) 
         FROM ' || DYNA_TABLE_NAME || ' UD 
         WHERE UD.LOGIN_DT < (SYSDATE - NUMTOYMINTERVAL(:1, :2)) 
         OR UD.LOGIN_DT IS NULL ORDER BY UD.LOGIN_DT DESC'; 

    DELETE_OLD_YEARS := 7; 
    DBMS_OUTPUT.PUT_LINE('Dynamic Query with UserData_Backup : ' || DYNA_QUERY_DEL); 
    EXECUTE IMMEDIATE DYNA_QUERY_DEL INTO RECORD_COUNT USING DELETE_OLD_YEARS, INTERVAL_UNIT; 
    DBMS_OUTPUT.PUT_LINE('Record Count 2 : ' || RECORD_COUNT); 

    END; 

END; 
/

exec Test;

是的，我也有同样的想法。但重新分配看起来有点奇怪。我是Java开发人员，也是PL/SQL程序的新成员。 – srihariraom

'DYNA_TABLE_NAME'的值改变了，这就是为什么你需要再次分配'DYNA_QUERY_DEL'。无论如何，你能告诉我第二次执行的输出是什么吗？它包含表名'UserData_Backup'吧？ – Moudiz

是的，得到了第二个表名。 :-) – srihariraom

Oracle PL/SQL动态更改变量值

相关推荐