不用存储过程,用自定义函数为每个项定义一个函数,函数的参数是客户号。然后在select语句中直接引用函数就可以了。
解决方案 »
- oracle链接ms sql问题
- 请问有介绍使用"PLSQL Developer"操作oracle 10g的书吗?
- 求教!!sql语句过滤两个字段重复值
- oracle 返回值
- 关于 ORA-03113: end-of-file on communication channel 的问题,在线等待!!!!!
- 插入后update与将字段计算出来后插入哪个快一些啊?大家讨论一下
- 如何用CASE解决如下问题
- ASP+ORACLE怎样实现连接池技术
- 能远程调用序列吗
- 关于ERWin
- create table a01_up as select * from a01 报错!原因是a01里面有个字段类型是long raw的,怎么解决呢?
- [急在线]:ORACLE怎么开始一个事务,提交,会滚的格式如何,谢谢!!
to tansoul:怎么写阿?能帮帮忙吗?
select "客户名称","美元贷款","人民币贷款","类别A","类别B"
(select tro1.cusman "客户名称",sum(my.je) "美元贷款",sum(rmb.je) "人民币贷款",sum(lba.je) "类别A",sum(lbb.je) "类别B",sum(my.je)*折算率+sum(rmb.je)
from tro1,(select tro2.cusidt,nvl(tro3.OSDAMTC,0) je--美元
from tro2,tro3
where tro2.cusidt = tro3.cusidt
and tro2.clcnum = tro3.clcnum
and tro2.prdcde in ('la','lb')
and tro3.curcde = '014' ) my,(select tro2.cusidt,nvl(tro3.OSDAMTC,0) je--人民币
from tro2,tro3
where tro2.cusidt = tro3.cusidt
and tro2.clcnum = tro3.clcnum
and tro2.prdcde in ('la','lb')
and tro3.curcde = '001') rmb,(select tro2.cusidt,nvl(tro3.OSDAMTC,0) je--类别a
from tro2,tro3
where tro2.cusidt = tro3.cusidt
and tro2.clcnum = tro3.clcnum
and tro3.AUDCLA in ('0','1')) lba,(select tro2.cusidt,nvl(tro3.OSDAMTC,0) je--类别b
from tro2,tro3
where tro2.cusidt = tro3.cusidt
and tro2.clcnum = tro3.clcnum
and tro3.AUDCLA = '2') lbbwhere tro1.cusidt = my.cusidt
and tro1.cusidt = rmb.cusidt
and tro1.cusidt = lba.cusidt
and troo1.cusidt = lbb.cusidtgroup by tro1.cusman
order by sum(my.je)*折算率+sum(rmb.je) desc)
where rownum < 6
to lzj033(南方的狼):我知道可以用sql来实现,但是在现实中由于表和数据量太大,这个需求只能用存储过程来写的
()
as
v_cusidt tro1.cusidt%type;
v_cusman tro1.cusman%type;
v_my tro3.OSDAMTC%type;
v_rmb tro3.OSDAMTC%type;
v_lba tro3.OSDAMTC%type;
v_lbb tro3.OSDAMTC%type;
cursor cur_cusman is
select distinct CUSIDT , CUSNAM from tro1;
begin
create temporary temptable
(khmc tro1.cusman%type,
my tro3.OSDAMTC%type,
rmb tro3.OSDAMTC%type,
lba tro3.OSDAMTC%type,
lbb tro3.OSDAMTC%type)open cur_cusman;
loop
fetch cur_cusman into v_cusidt,v_cusman;
exit when cur_cusman%notfound;insert into temptable(khmc,my,rmb,lba,lbb) values (v_cusman,0,0,0,0 );select nvl(sum(tro3.OSDAMTC),0) je--美元
into v_my
from tro2,tro3
where tro2.cusidt = tro3.cusidt
and tro2.clcnum = tro3.clcnum
and tro2.prdcde in ('la','lb')
and tro3.curcde = '014'
and tro2.cusidt = v_cusidt;select nvl(sum(tro3.OSDAMTC),0) je--人民币
into v_rmb
from tro2,tro3
where tro2.cusidt = tro3.cusidt
and tro2.clcnum = tro3.clcnum
and tro2.prdcde in ('la','lb')
and tro3.curcde = '001'
and tro2.cusidt = v_cusidt;select nvl(sum(tro3.OSDAMTC),0) je--类别a
into v_lba
from tro2,tro3
where tro2.cusidt = tro3.cusidt
and tro2.clcnum = tro3.clcnum
and tro3.AUDCLA in ('0','1')
and tro2.cusidt = v_cusidt;select nvl(sum(tro3.OSDAMTC),0) je--类别b
into v_lbb
from tro2,tro3
where tro2.cusidt = tro3.cusidt
and tro2.clcnum = tro3.clcnum
and tro3.AUDCLA = '2'
and tro2.cusidt = v_cusidt;
end loop
close cur_cusman;update temptable set my = v_my,rmb = v_rmb,lba = v_lba,lbb = v_lbb
end loop;
close cur_cusman;select * from
(select * from temptable order by my*折算率+rmb desc)
where rownum < 6 ;end test1;