select sum( c.c1*d.c1*m1*r1 +c.c2*d.c2*m2*r2 +c.c3*d.c3*m3*r3 +c.c4*d.c4*m4*r4 +
c.c5*d.c5*m5*r5 +c.c6*d.c6*m6*r6 +c.c7*d.c7*m7*r7 +c.c8*d.c8*m8*r8 +
c.c9*d.c9*m9*r9 +c.c10*d.c10*m10*r10 +c.c11*d.c11*m11*r11 +c.c12*d.c12*m12*r12 ) ,
sum( decode(c.c1*d.c1,0,0,sign(m1))+ decode(c.c2*d.c2,0,0,sign(m2))+ decode(c.c3*d.c3,0,0,sign(m3))
+ decode(c.c4*d.c4,0,0,sign(m4))+ decode(c.c5*d.c5,0,0,sign(m5))+ decode(c.c6*d.c6,0,0,sign(m6))
+ decode(c.c7*d.c7,0,0,sign(m7))+ decode(c.c8*d.c8,0,0,sign(m8))+ decode(c.c9*d.c9,0,0,sign(m9))
+ decode(c.c10*d.c10,0,0,sign(m10))+ decode(c.c11*d.c11,0,0,sign(m11))+ decode(c.c12*d.c12,0,0,sign(m12)) )
into offset_v,sign_v
from jg_coefficient c, jg_coefficient d, jg_account a, jg_rate b
where a.grbh= grbh_v and ( a.year_n between trunc(d1/100) and trunc(d2/100) )
and c.serial_n=decode(a.year_n,trunc(d1/100),mod(d1,100),1)
and d.serial_n=decode(a.year_n,trunc(d2/100),- mod(d2,100),-12)
and b.year_n= a.year_n ; jg_coefficient 为一三角距阵
c.c5*d.c5*m5*r5 +c.c6*d.c6*m6*r6 +c.c7*d.c7*m7*r7 +c.c8*d.c8*m8*r8 +
c.c9*d.c9*m9*r9 +c.c10*d.c10*m10*r10 +c.c11*d.c11*m11*r11 +c.c12*d.c12*m12*r12 ) ,
sum( decode(c.c1*d.c1,0,0,sign(m1))+ decode(c.c2*d.c2,0,0,sign(m2))+ decode(c.c3*d.c3,0,0,sign(m3))
+ decode(c.c4*d.c4,0,0,sign(m4))+ decode(c.c5*d.c5,0,0,sign(m5))+ decode(c.c6*d.c6,0,0,sign(m6))
+ decode(c.c7*d.c7,0,0,sign(m7))+ decode(c.c8*d.c8,0,0,sign(m8))+ decode(c.c9*d.c9,0,0,sign(m9))
+ decode(c.c10*d.c10,0,0,sign(m10))+ decode(c.c11*d.c11,0,0,sign(m11))+ decode(c.c12*d.c12,0,0,sign(m12)) )
into offset_v,sign_v
from jg_coefficient c, jg_coefficient d, jg_account a, jg_rate b
where a.grbh= grbh_v and ( a.year_n between trunc(d1/100) and trunc(d2/100) )
and c.serial_n=decode(a.year_n,trunc(d1/100),mod(d1,100),1)
and d.serial_n=decode(a.year_n,trunc(d2/100),- mod(d2,100),-12)
and b.year_n= a.year_n ; jg_coefficient 为一三角距阵
TYPE t_Numbers IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE t_MultiNumbers IS TABLE OF t_Numbers INDEX BY BINARY_INTEGER;
v_num t_MultiNumbers;
cursor t_sor is
select * from table1 where id in('1','2',....);
n number:=1;
m number:=1;
begin
for v_sor in t_sor loop
v_num(n)(m):=v_sor.col1;
m=m+1;
v_num(n)(m):=v_sor.col2;
m=m+1;
.....
n=n+1;
end loop;
end;自已测试吧,让我想想,想个比较简洁的方法。
对不起!
不是说你,我是没看懂:xuedscn()写的!
你的我看了正在试!
不过他似乎报错了
我不是很明白!
他说: TYPE t_MultiNumbers IS TABLE OF t_Numbers INDEX BY BINARY_INTEGER;
PLSQL表可能不包含具有组合字段的表或记录!
他说: v_num(n)(m):=v_sor.jylz;
在此上下文中必需有简单名称!
对不起!
不是说你,我是没看懂:xuedscn()写的!
你的我看了正在试!
不过他似乎报错了
我不是很明白!
他说: TYPE t_MultiNumbers IS TABLE OF t_Numbers INDEX BY BINARY_INTEGER;
PLSQL表可能不包含具有组合字段的表或记录!
他说: v_num(n)(m):=v_sor.jylz;
在此上下文中必需有简单名称!
TYPE t_varchar2s IS TABLE OF varchar2 INDEX BY BINARY_INTEGER;
TYPE t_MultiNumbers IS TABLE OF t_varchar2s INDEX BY BINARY_INTEGER;建义用对象实现不同字段类型
如:CREATE TYPE MyType AS OBJECT (
field1 NUMBER,
field2 VARCHAR2(50));
/
还是不行啊!
你说的是什么版本才可以啊
我的数据库服务器是oracle8i
但是我的程序是在ORACLE FORMS BUILDER 6i上面运行!
编译不了
/
CREATE TYPE MyType AS OBJECT (
field1 t_number,
field2 t_number,
..............);
/declare
o_var mytype:=mytype();
begin
...
end;这个方法我也没试过,或有问题可拿出来讨论。good luck!