存储过程如何将update中多条语句其中两字段返回到指定游标中，求职大佬们

[jackleeonlyone]

create or replace procedure BY_STOCK_IN_ADJUST_ACTION( 
                                     requestids in integer,
                                     ids out integer,
                                     modedatacreater out varchar2,
                                     thecursor IN OUT cursor_define.weavercursor)
as
inum integer;
tanum integer;
gpCode  varchar2(255); -- 股票代码
poolLevel  integer;--股票级别
target    varchar2(255);--目标产品
begin
  
-- 根据requestid 查找对应流程所需数据，股票调整流程
    select a.tiaokcpmc ,a.gupjb,substr(b.jiargpdm,0,length(b.jiargpdm)-2) into  target,poolLevel,gpCode from formtable_main_144 a,formtable_main_144_dt1 b where a.id=b.mainid and a.requestid=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;
        --判断是否pool_id
    if tanum > 0 then --pool_id 全部为1

         --其中的问题就是，update语句有多条，怎么将多条数据中 id,modedatacreater 放到thecursor游标中
         update(select * from uf_stock_pool a where stockcode=gpCode  and fundcode=target ) set fundcode=target ,pool_id=0;

      else   --pool_id 全部为0；
         update(select * from uf_stock_pool a where stockcode=gpCode  and fundcode=target) set fundcode= target,pool_id=1;
         
    end if;
    <<success_msg>>
    open thecursor for
    select ids,modedatacreater from  dual;

    when others then
       rollback;

end; 

[jackleeonlyone]

这个存储没有完整，希望大佬补充一下；

[学海无涯-回头是岸]

引用 4 楼 jackleeonlyone的回复:

你的ids和creater 是变量吗？ 你前面要定义一个PLSQL表，两列的。然后into进去，游标的情况，我没试过，你可以试试

[jackleeonlyone]

引用 5 楼 baidu_36457652 的回复:

[quote=引用 4 楼 jackleeonlyone的回复:]

你的ids和creater 是变量吗？
你前面要定义一个PLSQL表，两列的。然后into进去，游标的情况，我没试过，你可以试试[/quote]

create or replace procedure BY_STOCK_IN_ADJUST_ACTION( requestids in varchar2,
                                     ids out integer,
                                     creater out varchar2,
                                     thecursor IN OUT cursor_define.weavercursor)
as
inum integer;
tanum integer;
gpCode  varchar2(255); -- 股票代码
poolLevel  integer;--股票级别
target    varchar2(255);--目标产品
TYPE CIDS IS TABLE OF integer;
ci CIDS;
TYPE MCR IS TABLE OF integer;
cr MCR;
begin

-- 根据requestid 查找对应流程所需数据，股票调整流程
    select a.tiaokcpmc ,a.gupjb,substr(b.jiargpdm,0,length(b.jiargpdm)-2) into  target,poolLevel,gpCode from formtable_main_144 a,formtable_main_144_dt1 b where a.id=b.mainid and a.requestid=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;

    --判断是否pool_id
    select (case when instr(a,'1') >0 then 1 else  0 end) into tanum from (select wm_concat(pool_id) a from uf_stock_pool where stockcode=gpCode and fundcode=target );
    if tanum > 0 then --pool_id 全部为1
            update(select * from uf_stock_pool a where stockcode=gpCode  and fundcode=target ) set fundcode=target ,pool_id=0;

      else   --pool_id 全部为0；
            update(select * from uf_stock_pool a where stockcode=gpCode  and fundcode=target) set fundcode= target,pool_id=1 
            RETURNING id,modedatacreater BULK COLLECT INTO ci,cr;
    end if;
    commit;
    
    open thecursor for
    select ids,creater from dual;
    Exception
    when others then
       rollback;

end;

怎么将ci，cr放到thecursor游标中

[学海无涯-回头是岸]

引用 6 楼 jackleeonlyone 的回复:

[quote=引用 5 楼 baidu_36457652 的回复:]
[quote=引用 4 楼 jackleeonlyone的回复:]

你的ids和creater 是变量吗？
你前面要定义一个PLSQL表，两列的。然后into进去，游标的情况，我没试过，你可以试试[/quote]

create or replace procedure BY_STOCK_IN_ADJUST_ACTION( requestids in varchar2,
                                     ids out integer,
                                     creater out varchar2,
                                     thecursor IN OUT cursor_define.weavercursor)
as
inum integer;
tanum integer;
gpCode  varchar2(255); -- 股票代码
poolLevel  integer;--股票级别
target    varchar2(255);--目标产品
TYPE CIDS IS TABLE OF integer;
ci CIDS;
TYPE MCR IS TABLE OF integer;
cr MCR;
begin

-- 根据requestid 查找对应流程所需数据，股票调整流程
    select a.tiaokcpmc ,a.gupjb,substr(b.jiargpdm,0,length(b.jiargpdm)-2) into  target,poolLevel,gpCode from formtable_main_144 a,formtable_main_144_dt1 b where a.id=b.mainid and a.requestid=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;

    --判断是否pool_id
    select (case when instr(a,'1') >0 then 1 else  0 end) into tanum from (select wm_concat(pool_id) a from uf_stock_pool where stockcode=gpCode and fundcode=target );
    if tanum > 0 then --pool_id 全部为1
            update(select * from uf_stock_pool a where stockcode=gpCode  and fundcode=target ) set fundcode=target ,pool_id=0;

      else   --pool_id 全部为0；
            update(select * from uf_stock_pool a where stockcode=gpCode  and fundcode=target) set fundcode= target,pool_id=1 
            RETURNING id,modedatacreater BULK COLLECT INTO ci,cr;
    end if;
    commit;
    
    open thecursor for
    select ids,creater from dual;
    Exception
    when others then
       rollback;

end;

怎么将ci，cr放到thecursor游标中[/quote]
这个好像不能再放到 游标里面了。里面的内容只能遍历的方式 查询。

[学海无涯-回头是岸]

RETURNING 字段 BULK COLLECT INTO enums，放在update语句的后面

[jackleeonlyone]

[jackleeonlyone]

什么意思啊·

[jackleeonlyone]