代码如下 DECLARE type curtyp is ref cursor; c1 curtyp; c2 curtyp; begin open c1 for 'select ACCNAME,CURRTYPE,OPENDATE,substr(OPENDATE,6,2) from grkh.grkh_accname where accno=accno'; LOOP FETCH c1 INTO V_name,V_curr,V_opendate,V_month; EXIT WHEN c1 % NOTFOUND; END LOOP; CLOSE c1;
open c1 for 'select balance from ' || V_TABLE || ' where v_workdate=' || V_opendate || 'and accno=' || accno; FETCH c1 INTO V_balance; EXIT WHEN c1 % NOTFOUND; END LOOP; CLOSE c1;
end;
不好意思 贴错了 DECLARE type curtyp is ref cursor; c1 curtyp; c2 curtyp; begin open c1 for 'select ACCNAME,CURRTYPE,OPENDATE,substr(OPENDATE,6,2) from grkh.grkh_accname where accno=accno'; LOOP FETCH c1 INTO V_name,V_curr,V_opendate,V_month; /* 取得游标c2需要的表名 */ EXIT WHEN c1 % NOTFOUND; END LOOP; CLOSE c1;
open c2 for 'select balance from ' || V_TABLE || ' where v_workdate=' || V_opendate || 'and accno=' || accno; FETCH c2 INTO V_balance; EXIT WHEN c2 % NOTFOUND; END LOOP; CLOSE c2;
end;
第一个游标静态定义即可, 第二个游标用动态sql。DECLARE type curtyp is ref cursor; c2 curtyp; cursor c1 for select ACCNAME,CURRTYPE,OPENDATE,substr(OPENDATE,6,2) from grkh.grkh_accname where accno=v_accno; begin open c1 ; LOOP FETCH c1 INTO V_name,V_curr,V_opendate,V_month; /* 取得游标c2需要的表名 */ EXIT WHEN c1 % NOTFOUND; V_TABLE:='INPUTDATA.A_NFBALAN_MAIN_'||V_year||'M'||V_month;/* 生成游标c2需要的表名*/
open c2 for 'select balance from '||V_TABLE ||' where v_workdate=:v1 and accno=:v2' using V_opendate,V_accno; FETCH c2 INTO V_balance; EXIT WHEN c2 % NOTFOUND; .... END LOOP; CLOSE c2; END LOOP; CLOSE c1; end;
DECLARE
type curtyp is ref cursor;
c1 curtyp;
c2 curtyp;
begin
open c1 for
'select ACCNAME,CURRTYPE,OPENDATE,substr(OPENDATE,6,2) from grkh.grkh_accname where accno=accno';
LOOP
FETCH c1 INTO V_name,V_curr,V_opendate,V_month;
EXIT WHEN c1 % NOTFOUND;
END LOOP;
CLOSE c1;
V_TABLE:='INPUTDATA.A_NFBALAN_MAIN_' || V_year || 'M' || V_month;
open c1 for
'select balance from ' || V_TABLE || ' where v_workdate=' || V_opendate || 'and accno=' || accno;
FETCH c1 INTO V_balance;
EXIT WHEN c1 % NOTFOUND;
END LOOP;
CLOSE c1;
end;
DECLARE
type curtyp is ref cursor;
c1 curtyp;
c2 curtyp;
begin
open c1 for
'select ACCNAME,CURRTYPE,OPENDATE,substr(OPENDATE,6,2) from grkh.grkh_accname where accno=accno';
LOOP
FETCH c1 INTO V_name,V_curr,V_opendate,V_month; /* 取得游标c2需要的表名 */
EXIT WHEN c1 % NOTFOUND;
END LOOP;
CLOSE c1;
V_TABLE:='INPUTDATA.A_NFBALAN_MAIN_' || V_year || 'M' || V_month;/* 生成游标c2需要的表名 */
open c2 for
'select balance from ' || V_TABLE || ' where v_workdate=' || V_opendate || 'and accno=' || accno;
FETCH c2 INTO V_balance;
EXIT WHEN c2 % NOTFOUND;
END LOOP;
CLOSE c2;
end;
第二个游标用动态sql。DECLARE
type curtyp is ref cursor;
c2 curtyp; cursor c1 for
select ACCNAME,CURRTYPE,OPENDATE,substr(OPENDATE,6,2)
from grkh.grkh_accname
where accno=v_accno;
begin
open c1 ;
LOOP
FETCH c1 INTO V_name,V_curr,V_opendate,V_month; /* 取得游标c2需要的表名 */
EXIT WHEN c1 % NOTFOUND; V_TABLE:='INPUTDATA.A_NFBALAN_MAIN_'||V_year||'M'||V_month;/* 生成游标c2需要的表名*/
open c2 for
'select balance from '||V_TABLE ||' where v_workdate=:v1 and accno=:v2'
using V_opendate,V_accno; FETCH c2 INTO V_balance;
EXIT WHEN c2 % NOTFOUND;
.... END LOOP;
CLOSE c2;
END LOOP;
CLOSE c1;
end;
学习...
...
loop
FETCH c2 INTO V_balance;"
...
然后循环数组