create or replace procedure PBB_JJ_YYBBYLFLHZ(cur_result out types.cursorType,i_yyb in number, i_yf in number) is v_jyr number(8); begin Select max(jyr) into v_jyr from dcuser.txtjyr where ny=i_yf; open cur_result for select aa.orgcode 营业部代码,nvl(aa.name,'总计:') as 营业部名称, sum(cast(cast(aa.gpx as number) as decimal(10,2) ))as 股票型保有量, sum(cast(cast(aa.zqx as number) as decimal(10,2) ))as 证券型保有量, sum(decode(aa.jybs,null,nvl(aa.jjfe,0)*nvl(cs.jybs,0),nvl(aa.JJFE,0)*nvl(cs.JYBS,0)) )分仓交易量, sum(decode(aa.fcyjl,null,nvl(aa.jjfe,0)*nvl(cs.jybs,0)*nvl(aa.fcyjl,0),nvl(aa.jjfe,0)*nvl(cs.jybs,0)*nvl(cs.fcyjl,0)) ) 分仓佣金收入 from ( select yb.orgcode,yb.name,jd.jjlb, case when jl.zqlb=80 then js.jjfe end gpx, case when jl.zqlb=81 then js.jjfe end zqx, jd.jybs,js.jjfe,jd.fcyjl from dcuser.TBB_YYB_OFSFETJ js,crmii.tjjdm jd,crmii.lborganization yb,crmii.tzqlb jl where jd.jjdm=js.jjdm and yb.id=js.yyb and jd.jjlb=jl.zqlb and (js.yyb=i_yyb or i_yyb=1) and js.rq=v_jyr ) aa left join tjlbcs cs on aa.jjlb=cs.jjlb group by rollup((aa.name,aa.orgcode)) order by aa.name desc;
end PBB_JJ_YYBBYLFLHZ;我个你一个,过程就是很多sql的集合,能够减少代码量,另外调试直接在pl/sql中点击test就能调试了。希望对你能有点帮助
括号里这些参数cur_result out types.cursorType,i_yyb in number, i_yf in number是作为条件用的吗?还是返回值?
cur_result out types.cursorType是返回游标 i_yyb in number, i_yf in number是作为条件用的
币种 余额
USD 100
JPY 80
我现在要卖90USD买2000JPY,现在要给这个表的USD减去100,给JPY加上2000。这个存储过程要如何写,另外JAVA代码如何调用?
参数1:卖币种
参数2:卖金额
参数3:买币种
参数4:买金额具体存储过程的代码就先进行判断要买卖的金额是否超过余额,要买卖的币种是否存在等等然后再做update
create or replace procedure PBB_JJ_YYBBYLFLHZ(cur_result out types.cursorType,i_yyb in number, i_yf in number)
is
v_jyr number(8);
begin
Select max(jyr) into v_jyr from dcuser.txtjyr where ny=i_yf;
open cur_result for select aa.orgcode 营业部代码,nvl(aa.name,'总计:') as 营业部名称,
sum(cast(cast(aa.gpx as number) as decimal(10,2) ))as 股票型保有量,
sum(cast(cast(aa.zqx as number) as decimal(10,2) ))as 证券型保有量,
sum(decode(aa.jybs,null,nvl(aa.jjfe,0)*nvl(cs.jybs,0),nvl(aa.JJFE,0)*nvl(cs.JYBS,0)) )分仓交易量,
sum(decode(aa.fcyjl,null,nvl(aa.jjfe,0)*nvl(cs.jybs,0)*nvl(aa.fcyjl,0),nvl(aa.jjfe,0)*nvl(cs.jybs,0)*nvl(cs.fcyjl,0)) ) 分仓佣金收入 from
(
select
yb.orgcode,yb.name,jd.jjlb,
case when jl.zqlb=80 then js.jjfe end gpx,
case when jl.zqlb=81 then js.jjfe end zqx,
jd.jybs,js.jjfe,jd.fcyjl
from
dcuser.TBB_YYB_OFSFETJ js,crmii.tjjdm jd,crmii.lborganization yb,crmii.tzqlb jl
where
jd.jjdm=js.jjdm and yb.id=js.yyb and jd.jjlb=jl.zqlb and (js.yyb=i_yyb or i_yyb=1) and js.rq=v_jyr
) aa
left join tjlbcs cs on aa.jjlb=cs.jjlb
group by rollup((aa.name,aa.orgcode))
order by aa.name desc;
end PBB_JJ_YYBBYLFLHZ;我个你一个,过程就是很多sql的集合,能够减少代码量,另外调试直接在pl/sql中点击test就能调试了。希望对你能有点帮助
i_yyb in number, i_yf in number是作为条件用的