Oracle stored procedure: cursor and dynamic SQL

Oracle stored procedure: cursor and dynamic SQL 1. create package + CURSOR SQL code CREATE OR REPLACE PACKAGE RefCursor IS type t_RefCursor IS ref CURSOR; END RefCursor; 2. create a stored procedure and define the in and out parameters. SQL code create or replace PROCEDURE prc_stock_HistoryDetail. -- Define the parameter p_stockcode IN VARCHAR2, -- Warehouse Number p_stockId IN VARCHAR2, -- warehouse Id p_fbegdate IN VARCHAR2, -- start date p_fenddate IN VARCHAR2, -- end date p_fitemcode IN VARCHAR2, -- variety cur_result out sys_refcursor -- output cursor) IS -- defines the variable v_stockid VARCHAR2 (32); -- Warehouse Number v_stockName VARCHAR2 (32); -- warehouse name v_stockCode VARCHAR2 (32 ); -- warehouse name v_itemcode VARCHAR2 (32); -- material code v_instockDate DATE; -- warehouse receiving DATE v_in_store NUMBER ;-- Warehouse receiving quantity v_out_store NUMBER; -- warehouse picking quantity v_lost_store NUMBER; -- loss quantity v_qc_store NUMBER; -- Starting quantity v_last_store NUMBER; -- NUMBER of knot stores v_last_qc_date VARCHAR (32 ); -- last start date -- temporary variable v_temp_stockid varchar2 (32); -- warehouse Id v_temp_itemcode varchar2 (32); -- material code v_temp_last_store number; -- final storage -- defines the cursor cur_query RefCursor. t_Refcursor; v_sqlStmt string (10000); v_ SQL VARCHAR2 (2000); BEGIN v_ SQL: = 'select t. pk_id from yy_bd _ Stock t where t. fend = 1 order by t. fcode asc '; -- Query v_sqlStmt: = 'select tt. fstockid, tt. fcode, tt. fname, tt. fitemcode, tt. finstockdate, sum (tt. in_store) as in_store, sum (tt. out_store) as out_store, sum (tt. lost_store) as lost_store, sum (tt. last_store) as last_store from (select t. fstockid,. fcode,. fname, t. fitemcode, t. finstockdate, decode (t. fiostatus, 1, t. fqty, 0) as in_store, decode (t. fiost Atus, 2, t. fqty, 0) as out_store, decode (t. fiostatus, 3, t. fqty, 0) as lost_store, decode (t. fiostatus, 1, t. fqty, 0)-decode (t. fiostatus, 2, t. fqty, 0)-decode (t. fiostatus, 3, t. fqty, 0) as last_store from yy_store_storage t left join yy_bd_stock a on t. fstockid =. pk_id where t. fisreset = 0 and t. fitemcode is not null'; -- repository ID if p_stockId is not null then v_sqlStmt: = v_sqlStmt | 'and t. fstockid = ''' | p_s TockId | '''; end if; -- type if p_fitemcode is not null then v_sqlStmt: = v_sqlStmt | 'and t. fitemcode = ''' | p_fitemcode | '''; end if; -- start date if p_fbegdate is not null then v_sqlStmt: = v_sqlStmt | 'and t. finstockdate> = to_date (''' | p_fbegdate | ''', ''yyyy-MM-dd'') '; end if; -- end date if p_fenddate is not null then v_sqlStmt: = v_sqlStmt | 'and t. finstockdate <to_date (''' | p_fenddate | ''', ''yy Yy-MM-dd '') '; end if; v_sqlStmt: = v_sqlStmt | 'order by. fcode asc, t. fitemcode, t. finstockdate asc) tt group by tt. fstockid, tt. fcode, tt. fname, tt. fitemcode, tt. finstockdate order by tt. fcode asc, tt. fitemcode, tt. finstockdate asc '; dbms_output.put_line (' ==== sqlStmt: '| v_sqlStmt); -- open the cursor to traverse the warehouse table. OPEN cur_query FOR v_sqlStmt; -- initialize the Temporary Variable v_temp_stockId: = ''; v_temp_itemcode: =''; v_temp_last_store: = 0; LOOP -- query the inbound and outbound records in the database table. Obtain the number of warehouse receiving, warehouse picking, loss, and deposit records for each record. And then save it to the temporary table. FETCH cur_query INTO v_stockid, v_stockCode, v_stockName, v_itemcode, v_instockDate, v_in_store, v_out_store, v_lost_store, metadata; metadata ('v _ stockid: '| v_stockName |', fitemcode: '| v_itemcode |', instockdate: '| v_instockDate); exit when cur_query % notfound; if (v_stockid is not null) and (v_itemcode is not null) then -- traverse the record, if not the same warehouse and variety. then, obtain the initial inventory or the number of deposit in the previous day from the corresponding warehouse type. if (v_temp_stockid Is null or v_temp_stockid! = V_stockid) and (v_temp_itemcode is null or v_temp_itemcode! = V_itemcode) then v_temp_stockid: = v_stockid; v_temp_itemcode: = v_itemcode; Limit: = 0; -- call the stored procedure to obtain the number of nodes (v_temp_stockid, p_fbegdate, v_temp_itemcode, v_qc_store ); begin ('starting quantity: '| v_qc_store); v_temp_last_store: = nvl (v_qc_store, 0) + nvl (v_last_store, 0); else v_temp_last_store: = begin + nvl (v_last_store, 0); end if; -- calculate the initial quantity of each transaction by means of the number of balances -- end storage = beginning + warehouse receiving-outbound-loss insert into yy_temp_store_detail (fstockid, fstockcode, finstorkdate, fitemcode, in_store, out_store, lost_store, last_store) values (v_stockid, '', v_instockDate, v_itemcode, v_in_store, v_out_store, v_lost_store, region); end if; -- begin ('name: '| v_stockName |', itemcode: '| v_itemcode |', date: '| v_instockDate |', in_store: '| v_in_store |', out_store: '| v_out_store); end loop; CLOSE cur_query; -- returns the set open cur_result for select. fname, t. finstorkdate, t. fitemcode, t. in_store, t. out_store, t. lost_store, t. last_store from yy_temp_store_detail t left join yy_bd_stock a on t. fstockid =. pk_id order by. fcode asc, t. fitemcode, t. finstorkdate asc; END;