Dynamic statement creation cursor

 

A cursor cannot be opened with a dynamic statement,You can use the cursor variableBut the row variable cannot be determined when the table name in the dynamic statement is not sure,
The original table is used as the row variable template to solve the problem.

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; -- Here tb_trade_ex is used as the row template. This table is determined and the fields are exactly the same as those in the dynamically generated table.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: s'')-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, clerk, clerk, 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, clerk, TE_TID, TE_STATUS, clerk, clerk, TE_DATE, clerk, TE_CREATED) VALUES (: TE_SELLER_NICK,: TE_NICK,: starting,: TE_TID,: TE_STATUS,: starting,: starting,: TE_DATE,: starting,: TE_CREATED) '; execute immediate s_ SQL using success, c_row.TE_NICK, c_row.TE_BUYER_NICK, c_row.TE_TID, c_row.TE_STATUS, clerk, c_row.TE_CHATLOG_END_TIME, c_row.TE_DATE, clerk, c_row.TE_CREATED; commit; end if; end loop; close c_job;