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;
解决方案 »
- derby数据库不能更新数据ERROR: java.sql.BatchUpdateException: 以只读方式打开容器。
- MySql 重新安装出问题,查了各种资料都没法解决
- ora-12560: tns: 协议适配器错误
- 请教SQL
- 关于oracle初始化表的问题!
- oracle dbstart启动错误
- 大虾们,请教一下,在PL/SQL中,在SELECT的FROM后引用一个变量,怎么做?????此变量前面赋了值
- 数据导入,导出的问题!急急急急急急急急急急急急急!!!!!!!!
- 9i的Forms和Reports是在那安装的,
- 请问,如何直接对嵌套表中的数据进行操作??
- ORALCE 如何区分系统自带的表和用户后创建的表
- 这道题怎么做呀?
你前面要定义一个PLSQL表,两列的。然后into进去,游标的情况,我没试过,你可以试试
你前面要定义一个PLSQL表,两列的。然后into进去,游标的情况,我没试过,你可以试试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游标中
你前面要定义一个PLSQL表,两列的。然后into进去,游标的情况,我没试过,你可以试试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游标中
这个好像不能再放到 游标里面了。里面的内容只能遍历的方式 查询。