Stored procedure how to update multiple statements in two fields returned to specify cursor, job bos-CodePudding

 
The create or replace procedure BY_STOCK_IN_ADJUST_ACTION (
Requestids in integer, 
Ids out integer, 
Modedatacreater out varchar2, 
Thecursor OUT IN cursor_define. Weavercursor) 
As 
Inum integer; 
Tanum integer; 
GpCode varchar2 (255); - stock code 
PoolLevel integer; - stock level 
Target varchar2 (255); - target product 
The begin 

- the required data according to requestid to find corresponding process, the stock adjustment process 
Select a.t iaokcpmc al-qeada upjb, substr (b.j iargpdm, 0, length (b.j iargpdm) - 2) into target, poolLevel, gpCode from formtable_main_144 a, formtable_main_144_dt1 b where Anderson, d=b.m ainid and a.r equestid=requestids; 


Select count (1) into inum from uf_stock_pool where stockcode=gpCode and fundcode=target and pool_id=poolLevel; 
If inum=0 then 
return; 
end if; 
- determine whether pool_id 
If tanum & gt; 0 then -- all pool_id 1 

- the problem is that the update statement of multiple, how to set the id in the multiple data, modedatacreater in thecursor cursor in 
The update (select * from uf_stock_pool where a stockcode=gpCode and fundcode=target) set fundcode=target, pool_id=0; 

All else - pool_id 0; 
The update (select * from uf_stock_pool where a stockcode=gpCode and fundcode=target) set fundcode=target, pool_id=1; 

end if; 
 
The open thecursor for 
Select the ids, modedatacreater from dual; 

The when others then 
The rollback. 

end;

CodePudding user response:

This store is not complete, hope to supplement it bosses;

CodePudding user response:

RETURNING field BULK COLLECT INTO enums, put on the back of the update statement

CodePudding user response:

What do you mean ah,

CodePudding user response:

The

reference 4 floor jackleeonlyone reply:

Is your ids and creater variables?
In front of you to define a PLSQL table, two columns, and then into into, the situation of the cursor, I didn't try, you can try

CodePudding user response:

reference 5 floor baidu_36457652 reply:

Quote: refer to 4th floor jackleeonlyone response:

Is your ids and creater variables?
In front of you to define a PLSQL table, two columns, and then into inside, the situation of the cursor, I didn't try, you could try

 
The create or replace procedure BY_STOCK_IN_ADJUST_ACTION (requestids in varchar2, 
Ids out integer, 
Creater out varchar2, 
Thecursor OUT IN cursor_define. Weavercursor) 
As 
Inum integer; 
Tanum integer; 
GpCode varchar2 (255); - stock code 
PoolLevel integer; - stock level 
Target varchar2 (255); - target product 
TYPE CIDS IS TABLE OF integer; 
Ci CIDS; 
TYPE MCR IS TABLE OF integer; 
Cr MCR. 
The begin 

- the required data according to requestid to find corresponding process, the stock adjustment process 
Select a.t iaokcpmc al-qeada upjb, substr (b.j iargpdm, 0, length (b.j iargpdm) - 2) into target, poolLevel, gpCode from formtable_main_144 a, formtable_main_144_dt1 b where Anderson, d=b.m ainid and a.r equestid=requestids; 

Select count (1) into inum from uf_stock_pool where stockcode=gpCode and fundcode=target and pool_id=poolLevel; 
If inum=0 then 
return; 
end if; 

- determine whether pool_id 
Select (case when instr (a, '1') & gt; Then 1 else 0 0 end) into tanum from (select wm_concat (pool_id) from a uf_stock_pool where stockcode=gpCode and fundcode=target); 
If tanum & gt; 0 then -- all pool_id 1 
The update (select * from uf_stock_pool where a stockcode=gpCode and fundcode=target) set fundcode=target, pool_id=0; 

All else - pool_id 0; 
The update (select * from uf_stock_pool where a stockcode=gpCode and fundcode=target) set fundcode=target, pool_id=1 
RETURNING id, modedatacreater BULK COLLECT INTO ci, cr. 
end if; 
commit; 

The open thecursor for 
Select the ids, creater from dual; 
The Exception 
The when others then 
The rollback. 

end;

How to set the ci, cr in thecursor cursor

CodePudding user response:

refer to 6th floor jackleeonlyone response:

Quote: refer to the fifth floor baidu_36457652 reply:

Quote: refer to 4th floor jackleeonlyone response:

Is your ids and creater variables?
In front of you to define a PLSQL table, two columns, and then into inside, the situation of the cursor, I didn't try, you could try

The create or replace procedure BY_STOCK_IN_ADJUST_ACTION (requestids in varchar2,
Ids out integer,
Creater out varchar2,
Thecursor OUT IN cursor_define. Weavercursor)
As
Inum integer;
Tanum integer;
GpCode varchar2 (255); - stock code
PoolLevel integer; - stock level
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull