插入到mysql数据库时，可以用insert t(a,b,c,d) values(x,x,x,x), (x,x,x,x),...形式的批量语句提高速度。

字符串可以用 DBI 的 quote函数处理一下，处理转义字符。

sub do_it {
?? my $str_sql_from_oracle =
?????? "select /*+ parallel(t,4) */ a,b,c,d from tab t";

?? my $ora_conn = &get;_oracle_connection("ora", "user", "password");
?? my $mysql_conn = &get;_mysql_connection("ip", port, "user", "password");

?? #get result set from oracle
?? my $rs = &get;_result_set($ora_conn, $str_sql_from_oracle);

?? #use batch fetch to get 100 recrods per time.
?? while ( my $rows = $rs->fetchall_arrayref(undef, 100)) {
? ?? ? #construct the batch insert statement
?????? my $str_ins_batch = "insert into tab(a, b, c, d) values ";

??? ?? my $i = 0;

?????? for my $r (@$rows) {
?????????? #quote string
?????????? my $str_q = $mysql_conn->quote($r->[0]);

?????????? if ($i == 0 ) {
????????????? $str_ins_batch = $str_ins_batch . "(" . $str_q . "," . $r->[1] . "," . $r->[2] . ",'" . $r->[3] . "')";
??????????? } else {
?????????????? $str_ins_batch = $str_ins_batch. ",(" . $str_q . "," . $r->[1] . "," . $r->[2] . ",'" . $r->[3] . "')";
??????????? }
???????????? $i = $i + 1;
???????? }
?????? my $stmt_batch = $mysql_conn->prepare($str_ins_batch);
?????? $stmt_batch->execute();
?????? $mysql_conn->commit();
?? }
?? $mysql_conn->disconnect();
?? $ora_conn->disconnect();
}