通过perl从表中获取所有记录

Question

我希望通过Perl从表中获取所有5812750条记录。目前，我遇到了内存不足的错误。读取所有记录的任何其他最佳替代方式。

sub sane {
my $self = shift;
my $dbh = DBI->connect(
'dbi:Oracle:usbmfs',
'US', 'states',
{ AutoCommit => 0, RaiseError => 1 }
);
my $sth = $dbh->prepare(qq{
select cpu_id, system, generation, vendor, item,
week_first_moved, week_last_moved
from us_item_tbl
});
$sth->execute();
my $rows = @{ $dbh->selectall_arrayref('
select upc_id, system, generation, vendor, item,
week_first_moved, eek_last_moved
from uk_item_tbl
') };
my %lookup;
foreach my $row (@$rows) {
my($cpu, sys, $gen, $vend, $item, $wad, $wlm) = @$rows;
my $ean = sprintf "%02s%05s%05s", $sys, $vend, $item;
$cpu = sprintf "%014s", $cpu;
$lookup{$nae}{$cpu} = [$wad, $wlm];
}       
}

Answer 1

我认为使用selectall_arrayref是出现内存不足错误的原因。请尝试以下代码。

我将DBI->connect()调用更改为使用设置为较小值(20(的RowCacheSize，将selectall_arrayref更改为使用prepare()/execute()，并在两个查询结果中迭代，每次只获取一行。此外，我修复了您代码中的几个拼写错误：$sys缺少美元符号，写$ean而不是$nae，更改了"；upc_ id"；至"；cpu_id"；并修复了"的拼写；eek_last_moved"；。

while条件中的//是定义的或运算符，而不是m//正则表达式运算符。当左边的自变量是undef时，//评估它的右边自变量。当fetchrow_arrayref的返回行数用完时，返回undef；当这种情况发生时，对第二个查询结果调用fetchrow_arrayref，并将结果放入$row中。如果Perl抱怨//(可能是因为您的Perl早于5.10(，请尝试使用||。

sub sane {
my $self = shift;
my $dbh = DBI->connect(
'dbi:Oracle:usbmfs',
'US', 'states',
{ AutoCommit => 0, RaiseError => 1, RowCacheSize => 20 }
);
my $sth = $dbh->prepare(qq{
select cpu_id, system, generation, vendor, item,
week_first_moved, week_last_moved
from us_item_tbl
});
$sth->execute();
my $sth2 = $dbh->prepare('
select cpu_id, system, generation, vendor, item,
week_first_moved, week_last_moved
from uk_item_tbl
');
$sth2->execute();
my %lookup;
while (my $row = $sth->fetchrow_arrayref() // $sth2->fetchrow_arrayref()) {
my($cpu, $sys, $gen, $vend, $item, $wad, $wlm) = @$row;
my $ean = sprintf "%02s%05s%05s", $sys, $vend, $item;
$cpu = sprintf "%014s", $cpu;
$lookup{$ean}{$cpu} = [$wad, $wlm];
}
}