您当前的位置: 有表如下
create table t
(
name varchar2(10),
salary number(10,2),
birth date
);
该表有10002行记录
另有表t2，结构和t完全相同
希望写一个PRO*C程序
1、先从t表读取所有记录，每次fetch 1000行到一个结构体数组
2、然后从这个结构体数据一次性insert到表t2
3、清空结构体数组
4、直到所有记录插入到t2为止
这里t表的字段可能有NULL值，在fetch到结构体数组的时候不能因此报错
先谢谢各位大神，多谢指点
------解决思路----------------------
--创建表：
create table T1(name varchar2(10),salary number(10,2),birth date);
create index index_T1 on T1(name);
insert into T1 select 'name'
------解决思路----------------------
lpad(level,6,'0'),1000+level,sysdate-level from dual connect by level<=10002;
commit;
create table T2 as select * from T1 where 1=2;
--过程：
declare
type N_type is record (nr T1.Name%type,sr T1.Salary%type,br T1.Birth%type);
type NSB_type is table of N_type;
NSB NSB_type;
MaxR number :=1000; --每次fetch数
k number;
begin
execute immediate 'truncate table T2';
k:=0;
loop
k:=k+1;
select name,salary,birth bulk collect into NSB
from (select name,salary,birth,row_number() over(order by name) rn from T1 order by name)
where rn between (k-1)*MaxR+1 and K*MaxR;
exit when SQL%ROWCOUNT<=0;
for i in 1..NSB.count
loop
insert into T2(name,Salary,Birth) values(NSB(i).nr,NSB(i).sr,NSB(i).br);
end loop;
end loop;
commit;
end;
------解决思路----------------------
大致如下，可以根据实际补充：
EXEC SQL BEGIN DECLARE SECTION;
int MAX_NUM = 1000;
int fetch_cnt =0;
int fetch_total =0;
typedef struct xxx
{
...
} yyyy;
yyyy data[MAX_NUM];
EXEC SQL END DECLARE SECTION;
....
while (1 == 1)
{
EXEC SQL for :MAX_NUM FETCH cursor INTO :zzz;
fetch_cnt = sqlca.sqlerrd[2] - fetch_total;
fetch_total = sqlca.sqlerrd[2];
if (fetch_cnt ==0)
break;
....
EXEC SQL for :fetch_cnt INSERT INTO t2 VALUES(:zzz);
...
}
...