Dynamic SQL loop traversal with Oracle dynamic cursors

Dynamic cursors can traverse dynamic tables,


TYPE cursor TYPE IS REF CURSOR; -- define a dynamic cursor
 Cursor type of game name;

If the data of the query table is different and dynamic, dynamic cursors can be used.

It should be noted that the definition of dynamic cursors,

In normal stored procedures: the first line after is is is needed.

Dynamic cursors are used in the form of: open cursor for string, traversal.

create or replace procedure P_TEST_SQL 
TYPE ref_cursor_type IS REF CURSOR;  --Define a dynamic cursor     
tablename varchar2(200) default 'ess_client';    
v_sql varchar2(1000);    
mobile varchar2(15);    
usrs ref_cursor_type;    
  --Use connectors to make a complete line SQL     
  v_sql := 'select usrmsisdn from '||tablename||' where rownum < 11';    
  --Open cursor     
  open usrs for v_sql ;    
      fetch usrs into mobile;     
      exit when usrs%notfound;    
      insert into tmp(usrmsisdn) values(mobile);    
  end loop;    
  close usrs;    

Here is an example

create or replace procedure DDGZ1--Order tracking
       P_flag varchar,--Order type
       P_operate varchar,--Operation type
       P_BH varchar --Incoming number ID
      TYPE ref_cursor_type IS REF CURSOR;
       v_cur_CKD  ref_cursor_type;
   open v_cur_CKD for 'select v_ERP,v_DRP,v_jxsmc,v_jxs,v_DWMC,v_czr,v_jlsj from tmp_DDGZ1'; 
   fetch v_cur_CKD into v_ERP,v_DRP,v_jxsmc,v_jxs,v_DWMC,v_czr,v_jzsj;
   while v_cur_CKD%found
     insert into DRPDDGZJL2 (JLID,DRP,ERP,JXS,JXSMC,JLSJ,GZJL,CZR,BHID) values(SYS_GUID(),v_drp,v_erp,v_jxs,v_jxsmc,v_jzsj,v_DWMC||'Consigned finished goods warehouse receives orders and organizes shipment',v_CZR,'Outbound order'||P_BH); 
     fetch v_cur_CKD into v_ERP,v_DRP,v_jxsmc,v_jxs,v_DWMC,v_czr,v_jzsj;
     end loop;
     close v_cur_CKD;

Turn from https://www.cnblogs.com/alsf/p/6285558.html#undefined

Keywords: Mobile SQL

Added by arctushar on Sat, 04 Jan 2020 19:07:55 +0200