存储过程中如何查询动态表并插入一个游标

0 0

存储过程中如何查询动态表并插入一个游标10

下边是存储过程的代码

create or replace procedure JK_ODSO_PROD_CUST_RELA(bat_id in varchar2,area in varchar2) is
table_name varchar2(64) := 'PDT_SERV_'||area;
table_name_jk varchar2(64) := 'ODSO.ODSO_PROD_CUST_RELA_'||area||'@JIEKOU';
Cursor myCur is select prd_id from table_name where trunc(ZH_CHECK_DATE,'dd')=trunc(sysdate,'dd');
v_prd_id varchar2(64);
e_err varchar2(2000) :='';

insertSql_e8_phone varchar2(4000) := 'insert into '||table_name_jk||'(
                  PROD_ID,
                  PROPERTY_CUST_ID,
                  CUST_ID,
                  PROD_ADDRESS,
                  STATUS,
                  BAT_ID,
                  PRD_ID,
                  MEDIUMTYPE,
                  E8PRODUCTINSTANCE) '||'
          select
                  a.SERV_ID,
                  a.OWN_CUST_ID,
                  a.USE_CUST_ID,
                  a.INSTALL_ADDR,
                  decode(a.ZH_CHECK_TYPE,''ori_rec'',''insert'',''add_rec'',''insert'',''mod_rec'',''update'',''del_rec'',''delete'',null),
                  :bat_id,
                  PRD_ID,
                  b.val,
                  b.PRD_INST_ID
          from '||table_name||' a,
              (select DISTINCT PRD_INST_ID, VAL from PDT_SERV_ATTR_'||area||' Where PRD_FEA_TYPE_ID=718 AND ZH_CHECK_TYPE <>''del_rec'') b
              where trunc(a.ZH_CHECK_DATE,''dd'')=trunc(sysdate,''dd'') and a.prd_inst_id=b.prd_inst_id';

insertSql_e8_adsl varchar2(4000) := 'insert into '||table_name_jk||'(
                  PROD_ID,
                  PROPERTY_CUST_ID,
                  CUST_ID,
                  PROD_ADDRESS,
                  STATUS,
                  BAT_ID,
                  PRD_ID,
                  MEDIUMTYPE,
                  E8PRODUCTINSTANCE,
                  BANDWIDTH) '||'
          select
                  a.SERV_ID,
                  a.OWN_CUST_ID,
                  a.USE_CUST_ID,
                  a.INSTALL_ADDR,
                  decode(a.ZH_CHECK_TYPE,''ori_rec'',''insert'',''add_rec'',''insert'',''mod_rec'',''update'',''del_rec'',''delete'',null),
                  :bat_id,
                  PRD_ID,
                  b.val,
                  b.PRD_INST_ID
                  c.val
          from '||table_name||' a,
              (select distinct PRD_INST_ID, VAL from PDT_SERV_ATTR_553 Where PRD_FEA_TYPE_ID=718 AND ZH_CHECK_TYPE <>''del_rec'') b,
              (select distinct PRD_INST_ID, VAL From PDT_SERV_ATTR_553 Where PRD_FEA_TYPE_ID IN (114,115) AND ZH_CHECK_TYPE <>''del_rec'') c
              where trunc(a.ZH_CHECK_DATE,''dd'')=trunc(sysdate,''dd'') and
              a.prd_inst_id=b.prd_inst_id and
              a.prd_inst_id=c.prd_inst_id';
insertSql varchar2(4000) := 'insert into '||table_name_jk||'(
                  PROD_ID,
                  PROPERTY_CUST_ID,
                  CUST_ID,
                  PROD_ADDRESS,
                  STATUS,
                  BAT_ID,
                  PRD_ID) '||'
          select
                  a.SERV_ID,
                  a.OWN_CUST_ID,
                  a.USE_CUST_ID,
                  a.INSTALL_ADDR,
                  decode(a.ZH_CHECK_TYPE,''ori_rec'',''insert'',''add_rec'',''insert'',''mod_rec'',''update'',''del_rec'',''delete'',null),
                  :bat_id,
                  PRD_ID
          FROM '||table_name||'
            a where trunc(a.ZH_CHECK_DATE,''dd'')=trunc(sysdate,''dd'')';
begin
--if myCur%isopen = false then
      open myCur;
-- end if;
LOOP
      --取出产品数据类型
      fetch myCur into v_prd_id;
      --本身是E8-C产品
      if v_prd_id ='1101' THEN
          execute immediate insertSql_e8_phone using bat_id;
          commit;
      --电话挂载的E8-C产品
      ELSIF v_prd_id='1304' THEN
          execute immediate insertSql_e8_phone using bat_id;
          commit;
      --ADSL挂载的E8-C产品
      ELSIF v_prd_id='1307' THEN
          execute immediate insertSql_e8_adsl using bat_id;
          commit;
      else
          execute immediate insertSql using bat_id;
          commit;
      END IF ;
end LOOP;
close myCur;
insert into MSG_INTERFACE_LOG values(sysdate,'1007','产品客户关联','success',bat_id);
commit;
exception
when others then
      rollback;
      e_err := SQLCODE||' <>'||SQLERRM;
      insert into MSG_INTERFACE_LOG values(sysdate,'1007','产品客户关联',e_err,bat_id);
      commit;
      raise;
end JK_ODSO_PROD_CUST_RELA;

不过把红色的部分换成一个固定的表则可以正常执行。否则有错误，提示这个表不存在，其实是存在的，因为area是个变量，存储过程就不认这个表了，
大家帮看下