Oracle dynamic cursors enable dynamic SQL Loop traversal and comparison of static cursors.

A dynamic cursor can traverse a dynamic table,

Format:

 is CURSOR;  -- Defining a dynamic cursor
cursor name cursor type;

If the data of the queried table is different, dynamically changing, this time can be used with dynamic cursors.

It is important to note that the definition of a dynamic cursor,

In normal stored procedures: The first line that needs to be placed behind is .

The dynamic cursor passes through: The open cursor for string, in the form of use, traversal.

Create or Replace procedureP_test_sql
 isTYPE Ref_cursor_type is ref CURSOR;--defining a dynamic cursorTableNamevarchar2( $)default 'ess_client'; V_sqlvarchar2( +); Mobilevarchar2( the);    Usrs Ref_cursor_type; begin      --use connectors to stitch together into a full SQLV_sql:= 'Select Usrmsisdn from'||TableName||'where RowNum <'; --Open Cursor  Open Usrs for V_sql; LoopFetchUsrs intoMobile; Exit  whenUsrs%NotFound; Insert  intoTMP (USRMSISDN)Values(mobile); EndLoop; CloseUsrs; Commit; EndP_test_sql;

The following is an example of a self-made instance where the code retains only the reference section.

Create or Replace procedureDDGZ1--Order Tracking(P_flagvarchar,--Order typeP_operatevarchar,--type of OperationP_bhvarchar --Incoming number ID)is    TYPE ref_cursor_type is ref CURSOR; V_CUR_CKD Ref_cursor_type; begin   Open V_CUR_CKD for ' select V_ERP,V_DRP,V_JXSMC,V_JXS,V_DWMC,V_CZR,V_JLSJ from Tmp_ddgz1 ';     FetchV_cur_ckd intoV_ERP,V_DRP,V_JXSMC,V_JXS,V_DWMC,V_CZR,V_JZSJ;  whileV_cur_ckd%found LoopInsert  intoDRPDDGZJL2 (Jlid,drp,erp,jxs,jxsmc,jlsj,gzjl,czr,bhid)Values(Sys_guid (), V_DRP,V_ERP,V_JXS,V_JXSMC,V_JZSJ,V_DWMC||'entrusted to sell the finished product library to receive orders, organize shipments', V_CZR,'out of stock list'||P_BH); FetchV_cur_ckd intoV_ERP,V_DRP,V_JXSMC,V_JXS,V_DWMC,V_CZR,V_JZSJ; EndLoop; CloseV_CUR_CKD;End;

And for the Baotou package of the storage process. Often the following form:

In the Baotou:

CREATE OR REPLACE

 is  -- Shipping Statement Query type t_cursor is ref CURSOR;   procedure ERP_HY_FYJSDCX  (    re_cursor out t_cursor  ); End BIA_FYJSDCX;

In the package body:

  procedure ERP_HY_FYJSDCX  (    re_cursor out t_cursor  )begin     v_sql:= ' select * from t_fyjsdcx4 ';       End;

Oracle dynamic cursors enable dynamic SQL Loop traversal and comparison of static cursors.