谁帮我将这个存储过程改成Oracle版

[barrywei]

如题，具体过程如下：

CREATE procedure nowbalance @beginT datetime,@endT datetime --当前金额查询
as


create table #nowb (crsid char(36),firstbook money,costbook money,redubook money,addbook money,firstgreen int,shopgreen int,buygreen int,elseget int,elseredu int)
insert #nowb(crsid) select distinct clubid from yh_bookclub where (yd_date between @beginT and @endT) and bookstate='61' and courtype<>'00' and courtype<>'03'

update #nowb set firstbook=clubaccount.FirstBook,firstgreen=clubaccount.FirstGreen from clubaccount,#nowb where clubaccount.crsid=#nowb.crsid

--预存费用
update #nowb set redubook=(select sum(ActChargeMoney) from YH_bookclub where bookstate='61' and (yd_date between @beginT and @endT) and (ClubID=crsid) and CourType='01' group by clubid) where (redubook is null or redubook=0)

update #nowb set redubook=(select sum(ActMoney) from YH_bookclub where bookstate='61' and (yd_date between @beginT and @endT) and (ClubID=crsid) and (crsid=ClubID) and CourType='02' group by clubid) where (redubook is null or redubook=0)

--预存增加
update #nowb set addbook=(select sum(costmoney) from CrsNumMon where (happydate between @beginT and @endT) and (CrsNumMon.crsid=#nowb.crsid) and optype='01' group by crsid)

--违约扣预存
update #nowb set costbook=(select sum(CANCELMONEY) from YH_bookclub where (yd_date between @beginT and @endT) and (crsid=ClubID) and CANCELMONEY>0 group by clubid)

--购买果岭
update #nowb set buygreen=(select sum(costnum) from CrsNumMon where (happydate between @beginT and @endT) and (CrsNumMon.crsid=#nowb.crsid) and optype='02' group by crsid)
update #nowb set elseget=(select sum(costnum) from CrsNumMon where (happydate between @beginT and @endT) and (CrsNumMon.crsid=#nowb.crsid) and optype='03' group by crsid)

update #nowb set elseredu=(select sum(costnum) from CrsNumMon where (happydate between @beginT and @endT) and (CrsNumMon.crsid=#nowb.crsid) and optype='04' group by crsid)

--消费扣次
update #nowb set shopgreen=(select sum(CountEnd) from YH_bookclub where bookstate='61' and (yd_date between @beginT and @endT) and (crsid=ClubID) and CourType='05' group by clubid)

update #nowb set redubook=0 where redubook is null
update #nowb set addbook=0 where addbook is null
update #nowb set buygreen=0 where buygreen is null
update #nowb set elseget=0 where elseget is null

update #nowb set elseredu=0 where elseredu is null
update #nowb set shopgreen=0 where shopgreen is null

update #nowb set firstbook=0 where firstbook is null

update #nowb set firstgreen=0 where firstgreen is null
update #nowb set costbook=0 where costbook is null


select convert(varchar(15),firstbook,1) firstbook,convert(varchar(15),costbook,1) costbook,convert(varchar(15),redubook,1) redubook,convert(varchar(15),addbook,1) addbook,convert(varchar(15),firstbook+addbook-costbook-redubook,1) restbook,firstgreen,shopgreen,buygreen,elseget,
elseredu ,firstgreen+buygreen+elseget-elseredu-shopgreen restgreen,name from #nowb,yh_crs where #nowb.crsid=yh_crs.id
GO

还可以在加 100分

[barrywei]

怎么没人回复呢？没人能帮我么？

[icedut]

代码实在是太长了

[zealot_001]

试试这个：
CREATE or replace procedure nowbalance(beginT in date, endT in date) --当前金额查询
as
v_sql varchar2(100);
begin

v_sql := 'create table nowb (crsid char(36),firstbook money,costbook money,redubook money,addbook money,firstgreen int,shopgreen int,buygreen int,elseget int,elseredu int)';
execute immediate v_sql;

insert into nowb
(crsid)
select distinct clubid
from yh_bookclub
where (yd_date between beginT and endT)
and bookstate = '61'
and courtype <> '00'
and courtype <> '03';

update nowb set(nowb.firstbook,nowb.firstgreen)= (select clubaccount.FirstBook,clubaccount.FirstGreen from clubaccount where clubaccount.crsid=nowb.crsid);


--预存费用
update nowb set redubook=(select sum(ActChargeMoney) from YH_bookclub where bookstate='61' and (yd_date between beginT and endT) and (ClubID=crsid) and CourType='01' group by clubid) where (redubook is null or redubook=0);

update nowb
set redubook = (select sum(ActMoney)
from YH_bookclub
where bookstate = '61'
and (yd_date between beginT and endT)
and (ClubID = crsid)
and (crsid = ClubID)
and CourType = '02'
group by clubid)
where (redubook is null or redubook = 0);

--预存增加
update nowb set
addbook = (select sum(costmoney)
from CrsNumMon
where (happydate between beginT and endT)
and (CrsNumMon.crsid = nowb.crsid)
and optype = '01'
group by crsid);

--违约扣预存
update nowb set
costbook = (select sum(CANCELMONEY)
from YH_bookclub
where (yd_date between beginT and endT)
and (crsid = ClubID)
and CANCELMONEY > 0
group by clubid);

--购买果岭
update nowb set
buygreen = (select sum(costnum)
from CrsNumMon
where (happydate between beginT and endT)
and (CrsNumMon.crsid = nowb.crsid)
and optype = '02'
group by crsid);
update nowb set
elseget = (select sum(costnum)
from CrsNumMon
where (happydate between beginT and endT)
and (CrsNumMon.crsid = nowb.crsid)
and optype = '03'
group by crsid);

update nowb set
elseredu = (select sum(costnum)
from CrsNumMon
where (happydate between beginT and endT)
and (CrsNumMon.crsid = nowb.crsid)
and optype = '04'
group by crsid);

--消费扣次
update nowb set
shopgreen = (select sum(CountEnd)
from YH_bookclub
where bookstate = '61'
and (yd_date between beginT and endT)
and (crsid = ClubID)
and CourType = '05'
group by clubid);

update nowb set redubook = 0 where redubook is null;
update nowb set addbook = 0 where addbook is null;
update nowb set buygreen = 0
where buygreen is null;
update nowb set elseget = 0 where
elseget is null;

update nowb set elseredu = 0 where elseredu is null;
update nowb set
shopgreen = 0 where shopgreen is null;

update nowb set firstbook = 0 where firstbook is null;

update nowb set firstgreen = 0 where firstgreen is null;
update nowb set
costbook = 0 where costbook is null;

/*select convert(varchar(15), firstbook, 1) firstbook,
convert(varchar(15), costbook, 1) costbook,
convert(varchar(15), redubook, 1) redubook,
convert(varchar(15), addbook, 1) addbook,
convert(varchar(15),
firstbook + addbook - costbook - redubook,
1) restbook,
firstgreen,
shopgreen,
buygreen,
elseget,
elseredu,
firstgreen + buygreen + elseget - elseredu - shopgreen restgreen,
name
from nowb, yh_crs
where nowb.crsid = yh_crs.id */
select to_char(firstbook) firstbook,
to_char(costbook) costbook,
to_char(redubook) redubook,
to_char(addbook) addbook,
to_char(firstbook + addbook - costbook - redubook) restbook,
firstgreen,
shopgreen,
buygreen,
elseget,
elseredu,
firstgreen + buygreen + elseget - elseredu - shopgreen restgreen,
name
from nowb, yh_crs
where nowb.crsid = yh_crs.id;
end;

[icedut]

CREATE or replace procedure nowbalance
(p_beginT in date,
p_endT in date
)
is
begin
...
end nowbalance ;

--变量不用@
update语句有些变化
临时表有些变化


--------
3建立临时表
临时表的定义对所有会话SESSION都是可见的,但是表中的数据只对当前的会话或者事务有效.
建立方法:
1) ON COMMIT DELETE ROWS 定义了建立事务级临时表的方法.
CREATE GLOBAL TEMPORARY TABLE admin_work_area
(startdate DATE,
enddate DATE,
class CHAR(20))
ON COMMIT DELETE ROWS;
2)ON COMMIT PRESERVE ROWS 定义了创建会话级临时表的方法.
CREATE GLOBAL TEMPORARY TABLE admin_work_area
(startdate DATE,
enddate DATE,
class CHAR(20))
ON COMMIT PRESERVE ROWS;

[icedut]

zealot_001() (

的方法见没有临时表
如果多用户操作数据上面会有问题

[barrywei]

zealot朋友的方法有没有改进的？这样数据的update会失败的！
要不然我也不会发帖到这里的。