动态语句创建游标的问题

 

游标不支持用动态语句打开，使用游标变量可以，但动态语句中的表名本身不确定的情况下无法确定行变量，
最终使用原始表作为行变量模板，问题解决。

curdatestr varchar2(50):=to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');
        isexists number:=0;
    s_sql varchar2(30000);
    type t_cursor is ref cursor;
         c_job t_cursor;
         c_row TB_TRADE_EX%rowtype;   --这里tb_trade_ex作为行模板，这个表是确定的，字段跟动态生成的表完全一样
         begin
           s_sql:='select * from TB_TRADE_EX_'||sid||'  where TE_SELLER_NICK=''cntaobao'||sellerNick||''' and datachangedate > to_date('''||curdatestr||''',''yyyy-mm-dd hh24:mi:ss'')-1';  
              open c_job for s_sql;
              loop
                   fetch c_job into  c_row;
                   exit when c_job%notfound;
                   s_sql:='
                   select count(1)  from TB_TRADE_EX_'||sid||' @dblink183 
                       where  TE_NICK=:TE_NICK AND TE_BUYER_NICK = :TE_BUYER_NICK AND TE_TID= :TE_TID AND TE_STATUS=:TE_STATUS and te_date=:te_date';
                   execute immediate s_sql into isexists 
                    using c_row.TE_NICK,c_row.TE_BUYER_NICK,c_row.TE_TID,c_row.TE_STATUS,c_row.TE_DATE;  
                       if(isexists=0) then
                       s_sql:='
                         INSERT INTO TB_TRADE_EX_'||sid||' @dblink183 ( 
                          TE_SELLER_NICK ,
                          TE_NICK ,
                          TE_BUYER_NICK ,
                          TE_TID ,
                          TE_STATUS ,
                          TE_CHATLOG_BEGIN_TIME ,
                          TE_CHATLOG_END_TIME ,
                          TE_DATE ,
                          TE_ORDER_SUCCESS_TIME ,
                          TE_CREATED 
                           ) 
                          VALUES (:TE_SELLER_NICK,:TE_NICK,:TE_BUYER_NICK,:TE_TID,:TE_STATUS,
                          :TE_CHATLOG_BEGIN_TIME,:TE_CHATLOG_END_TIME,:TE_DATE,:TE_ORDER_SUCCESS_TIME,:TE_CREATED)'
                          ;
                         execute immediate s_sql 
                         using c_row.TE_SELLER_NICK,c_row.TE_NICK,c_row.TE_BUYER_NICK,c_row.TE_TID,c_row.TE_STATUS,
                          c_row.TE_CHATLOG_BEGIN_TIME,c_row.TE_CHATLOG_END_TIME,c_row.TE_DATE,c_row.TE_ORDER_SUCCESS_TIME,c_row.TE_CREATED;
                          commit;
                        end if;
                   
                end loop;
               close c_job;