如何做"插入到...选择。。。在 PL/SQL 块 (IF/THEN) 中

Question

我试图在PL/SQL Developer中运行下面的脚本，但我收到一个错误，说"V_INSERT"不是过程或未定义，该语句被忽略。

有人能帮忙吗？谢谢

DECLARE chktime date; v_trunc varchar2(200); v_insert varchar2(200);
BEGIN
Select trunc(max(a.action_timestamp)) into chktime from hcr_dm.hcr_dm_fact a;
If chktime <> trunc(sysdate) then 
  v_trunc:='truncate table yxg3509.account_crosswalk_hcrdm';
  execute immediate v_trunc;
Else
  v_trunc:='truncate table yxg3509.product_to_ah_092514'; 
  v_insert:='insert into yxg3509.product_to_ah_092514 
             select prod.oracle_prod_code,
                    prod.oracle_prod_description,
                    prod.ah_code,
                    prod.effective_date
             from hcr_sandbox.product_to_ah prod';
  execute immediate v_trunc; v_insert;
END IF;
END;

Answer 1

您需要使用两个EXECUTE IMMEDIATE语句来执行此操作：

DECLARE
  chktime  date;
  v_trunc  varchar2(2000);
  v_insert varchar2(2000);
BEGIN
  Select trunc(max(a.action_timestamp)) into chktime from hcr_dm.hcr_dm_fact a;
  If chktime <> trunc(sysdate) then 
    v_trunc:='truncate table yxg3509.account_crosswalk_hcrdm';
    execute immediate v_trunc;
  Else
    v_trunc:='truncate table yxg3509.product_to_ah_092514'; 
    v_insert:='insert into yxg3509.product_to_ah_092514 
                 select prod.oracle_prod_code,
                        prod.oracle_prod_description,
                      prod.ah_code,
                      prod.effective_date
               from hcr_sandbox.product_to_ah prod';
    execute immediate v_trunc;
    execute immediate v_insert;
  END IF;
END;

尽管IMO没有理由对INSERT语句使用EXECUTE IMMEDIATE，我也看不出有什么好的理由将TRUNCATE TABLE语句放入变量中，因此您可能会更好地使用：

DECLARE
  chktime  date;
BEGIN
  Select trunc(max(a.action_timestamp))
    into chktime
    from hcr_dm.hcr_dm_fact a;
  If chktime <> trunc(sysdate) then 
    execute immediate 'truncate table yxg3509.account_crosswalk_hcrdm';
  Else
    execute immediate 'truncate table yxg3509.product_to_ah_092514';
    insert into yxg3509.product_to_ah_092514 
      select prod.oracle_prod_code,
             prod.oracle_prod_description,
             prod.ah_code,
             prod.effective_date
        from hcr_sandbox.product_to_ah prod;
  END IF;
END;

分享并享受。