type a_type is table of number;
a a_type := a_type();if((substr(queryname,0,4)= 'jglx') ) then
jglx1:=substr(queryname,6);select count(*) into countjglx from t_rcsibd t where c_value = jglx1 and c_code ='JGLX';
a.extend(countjglx);
i:=0;
while i<countjglx loop
select c_rcsiid into a[i] from t_rcsibd t where c_value = jglx1 and c_code ='JGLX';
open type_cursor1 for
select c_rcsiid ,
max(decode(C_CODE,'ZZJGDM' ,c_value,null)) ZZJGDM,
max(decode(C_CODE,'JGMC' ,c_value,null)) JGMC ,
max(decode(C_CODE,'JGLX' ,c_value,null)) JGlX
from t_rcsibd t where c_rcsiid = a[i] GROUP BY c_rcsiid;i:=i+1;
end loop;提示:select c_rcsiid into a[i] from t_rcsibd t where c_value = jglx1 and c_code ='JGLX';有错误。请问该如何让给数组赋值?
a a_type := a_type();if((substr(queryname,0,4)= 'jglx') ) then
jglx1:=substr(queryname,6);select count(*) into countjglx from t_rcsibd t where c_value = jglx1 and c_code ='JGLX';
a.extend(countjglx);
i:=0;
while i<countjglx loop
select c_rcsiid into a[i] from t_rcsibd t where c_value = jglx1 and c_code ='JGLX';
open type_cursor1 for
select c_rcsiid ,
max(decode(C_CODE,'ZZJGDM' ,c_value,null)) ZZJGDM,
max(decode(C_CODE,'JGMC' ,c_value,null)) JGMC ,
max(decode(C_CODE,'JGLX' ,c_value,null)) JGlX
from t_rcsibd t where c_rcsiid = a[i] GROUP BY c_rcsiid;i:=i+1;
end loop;提示:select c_rcsiid into a[i] from t_rcsibd t where c_value = jglx1 and c_code ='JGLX';有错误。请问该如何让给数组赋值?
a a_type := a_type();if((substr(queryname,0,4)= 'jglx') ) then
jglx1:=substr(queryname,6);select count(*) into countjglx from t_rcsibd t where c_value = jglx1 and c_code ='JGLX';
a.extend(countjglx);
i:=1; --嵌套表下标从1开始
while i<countjglx loop
select c_rcsiid into a(i) from t_rcsibd t where c_value = jglx1 and c_code ='JGLX'; --使用小括号引用下标
open type_cursor1 for
select c_rcsiid ,
max(decode(C_CODE,'ZZJGDM' ,c_value,null)) ZZJGDM,
max(decode(C_CODE,'JGMC' ,c_value,null)) JGMC ,
max(decode(C_CODE,'JGLX' ,c_value,null)) JGlX
from t_rcsibd t where c_rcsiid = a(i) GROUP BY c_rcsiid;--使用小括号引用下标i:=i+1;
end loop;
select c_rcsiid into a(i) from t_rcsibd t where c_value = jglx1 and c_code ='JGLX';
实际返回的行数超出请求的行数。为什么?我得值是赋给数组的
select c_rcsiid into a from t_rcsibd t where ....然后通过循环再引用a(i)
select c_rcsiid into a from t_rcsibd t where ....然后通过循环再引用a(i)
这样好像不行,
Error: PLS-00642: 在 SQL 语句中不允许使用本地收集类型
Line: 41
Text: select c_rcsiid into a from t_rcsibd t where c_value = jglx1 and c_code ='JGLX';
精通Oracle 10g PL/SQL编程(http://download.csdn.net/source/552111)
select c_rcsiid ,
max(decode(C_CODE,'ZZJGDM' ,c_value,null)) ZZJGDM,
max(decode(C_CODE,'JGMC' ,c_value,null)) JGMC ,
max(decode(C_CODE,'JGLX' ,c_value,null)) JGlX
from t_rcsibd t where c_rcsiid = i
GROUP BY c_rcsiid;end loop;这样为什么只能得到 c_rcsiid = 100的值呢?应该怎么写?