SqlServer存储过程转为Oracle?

ALTER proc [dbo].[sp_bookview]
AS
DECLARE @Balance FLOAT
create table #tmp(
rownum int IDENTITY(1,1),
remark varchar(100),
AcctDate char(10),
VoucherStr varchar(20),
VoucherNum varchar(20),
period int,
debit float,
Credit float
)
--查询年初余额
select @Balance = FPreBalance from CN_AcctInfoView where id=3
--将日记账数据排好序放入临时表中
insert into #tmp( remark, AcctDate, VoucherStr,VoucherNum,period,debit,Credit)
SELECT tbl.Summary as remark,CONVERT(char(10), acctDate, 120)as AcctDate, VoucherStr,VoucherNum, period, debit, Credit
FROM CN_AcctBookView tbl WHERE AcctID = 3 order by AcctDate

SELECT * from (

select '前期余额' AS remark,'' AcctDate,'' VoucherStr,'' VoucherNum,'' period,null as debit, null as Credit, @Balance as balance
union all
select remark, AcctDate, VoucherStr,VoucherNum,period,debit,Credit,(select @Balance+ sum(debit)-sum(Credit) from #tmp where rownum<=a.rownum) as balance from #tmp a
union all
select '日合计' as remark,CONVERT(char(10), acctDate, 120) + '合计' as AcctDate,
'' VoucherStr,'' VoucherNum,'' period,sum(debit) debit, sum(Credit) as Credit,
(select @Balance+ sum(debit)-sum(Credit) from #tmp where acctDate<=a.acctDate) as balance
from #tmp a group by CONVERT(char(10), acctDate, 120)

union all
select '月合计' as remark,CONVERT(char(7), acctDate, 120) + '合计'as AcctDate,
'' VoucherStr,'' VoucherNum,'' period,sum(debit) debit, sum(Credit) as Credit,
(select @Balance+ sum(debit)-sum(Credit) from #tmp where CONVERT(char(7), acctDate, 120)<= CONVERT(char(7), a.acctDate, 120)) as balance
from #tmp a group by CONVERT(char(7), acctDate, 120)

union all
select '年合计' as remark,CONVERT(char(4), acctDate, 120) + '合计' as AcctDate,
'' VoucherStr,'' VoucherNum,'' period,sum(debit) debit, sum(Credit) as Credit,
(select @Balance+ sum(debit)-sum(Credit) from #tmp where CONVERT(char(4), acctDate, 120)<= CONVERT(char(4), a.acctDate, 120)) as balance
from #tmp a group by CONVERT(char(4), acctDate, 120)
) b order by AcctDate

--删除临时表
drop table #tmp


Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐