An Oracle stored procedure call returns another stored procedure for the cursor.

An extended stored procedure calls another stored procedure, example:

Called stored procedure: Finally, a cursor is returned, and the cursor returns a value. The stored procedure that calls this stored procedure also needs to get it.

 procedureSearchbill--Check all line numbers according to arrival number(P_codeVARCHAR, P_usercodevarchar, p_orgvarchar, P_warehousevarchar, P_movetypevarchar, P_billtypevarchar, P_notevarchar, p_isredvarchar2, P_exmovetypevarchar2, p_recursor0 out T_cursor) is 　　 V_count int;begin    -------------------------------Code--------------------------    OpenP_recursor0 for  SELECT V_count Num  fromdual;EndSearchbill;

In this process, the result of the return is a numeric cursor. Only one row of data.

So when I receive it, I receive it with an int variable.

When calling a stored procedure at the same time, you need to define a cursor variable to pass in, and the returned result is taken from this cursor.

CREATE OR REPLACEPackage BODY Erp_imim_grreqbill is   procedureSearchbillex (P_codeVARCHAR, P_usercodevarchar, p_orgvarchar, P_warehousevarchar, P_movetypevarchar, P_billtypevarchar, P_notevarchar, p_isredvarchar2, P_exmovetypevarchar2, p_recursor0 out t_cursor ) is P_recursor1 t_cursor; Num      int; beginSearchbill (P_code,p_usercode,p_org,p_warehouse,p_movetype,p_billtype,p_note,p_isred,p_exmovetype,p_         ReCursor1); FETCH  P_recursor1  into num;//assigns the data obtained by the cursor to the INT type variable.         Note that for cursors that have multiple rows of data returned, it is necessary to iterate through the cursors. Open  p_recursor0  for SelectNum num fromdual;//The result back to the same. EndSearchbillex;

In addition, for the received result contains multiple rows of data, the fields do not know the type, but there is a corresponding table query SQL cursor, the row field corresponding type can be unified with a variable definition.

Set the cursor for a field, and then set the type by%rowtype.

Example:

--Header definition return cursor typeTYPE T_cursor isREFCURSOR;--declaration in the package body stored procedureDeclare P_recursor1 t_cursor;

--In this way, you can set the cursor to return the variable, type of each field in the result set. The following p_rerow1 can get the corresponding value of the return cursor directly through the P_REROW1. Field name. cursor  temp_cursor1 is SELECT batchid,batchcode,disableddate,cast (0 as DECIMAL (20 ,8)) as Quantity from Batches WHERE 1=2 ;

  p_rerow1  temp_cursor1 % rowtype;--store internal calls to other stored procedures and get returned resultsErp_imbillinterface.batchhelpiss (P_companyid,p_orgid,p_warehouseid,row_cursor2. Chaintbatch,"',"',"',"', P_materialid,"',"',' on','Z',"', P_batchnumcode,"',"',"',"',"', P_curdate,"','1','0','1', P_islmgr,'Ten', P_movetypeid, ( CaseP_isred when '0'  Then '2' Else '1' End), Row_cursor2. Bcf1,row_cursor2. Bcf2,row_cursor2. Bcf3,row_cursor2. Bcf4,row_cursor2. Bcf5,row_cursor2. Bcf6,row_cursor2. Bcf7,row_cursor2. BCF8, Row_cursor2. Bcf9,row_cursor2. Bcf11,row_cursor2. Bcf12,row_cursor2. Bcf13,row_cursor2. Bcf14,row_cursor2. Bcf16,row_cursor2. BCF20, p_recursor1); Loop Fetch  P_recursor1  into P_rerow1; Exit  whenP_recursor1%notfound; Insert  intoTEMP_SAVEDATA_TB (batchid,batchcode,disableddate,quantity)Values(p_rerow1.batchid,p_rerow1.batchcode, p_rerow1.disableddate,p_rerow1.quantity); End Loop;

An Oracle stored procedure call returns another stored procedure for the cursor.