動態語句建立遊標的問題

 

遊標不支援用動態語句開啟，使用遊標變數可以，但動態語句中的表名本身不確定的情況下無法確定行變數，
最終使用原始表作為行變數模板，問題解決。

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;