Perl DBI – 运行带有多个语句的SQL脚本

my $dbh = DBI->connect(
    "dbi:Oracle:dbname","username","password",{
        ChopBlanks       => 1,AutoCommit       => 1,RaiseError       => 1,PrintError       => 1,FetchHashKeyName => 'NAME_lc',}
);
$dbh->do("
    CREATE TABLE test_dbi1 (
        test_dbi_intr_no    NUMBER(15),test_dbi_name       VARCHAR2(100)
    );

    UPDATE mytable 
        SET col1=1;

    CREATE TABLE test_dbi2 (
        test_dbi_intr_no    NUMBER(15),test_dbi_name       VARCHAR2(100)
    );
");

$dbh->disconnect;

当然,如果你破坏语句,你会得到更好的错误处理.您可以使用简单的解析器将字符串分解为单个语句：

#!/usr/bin/perl

use strict;
use warnings;

my $sql = "
    CREATE TABLE test_dbi1 (
        test_dbi_intr_no    NUMBER(15),test_dbi_name       VARCHAR2(100)
    );

    UPDATE mytable
        SET col1=';yes;'
        WHERE col2=1;

    UPDATE mytable
        SET col1='Don't use ;s and 's together,it is a pain'
        WHERE col2=1;


    CREATE TABLE test_dbi2 (
        test_dbi_intr_no    NUMBER(15),test_dbi_name       VARCHAR2(100)
    );
";

my @statements = ("");
#split the string into interesting pieces (i.e. tokens):
#   ' delimits strings
#    pass on the next character if inside a string
#   ; delimits statements unless it is in a string
#   and anything else
# NOTE: the grep { ord } is to get rid of the nul
# characters the split seems to be adding
my @tokens     = grep { ord } split /([';])/,$sql; 
# NOTE: this ' fixes the stupid SO syntax highlighter
#this is true if we are in a string and should ignore ; 
my $in_string  = 0;
my $escape     = 0;
#while there are still tokens to process
while (@tokens) {
    #grab the next token
    my $token = shift @tokens;
    #if we are in a string
    if ($in_string) {
        #add the token to the last statement
        $statements[-1] .= $token;
        #setup the escape if the token is 
        if ($token eq "") {
                $escape = 1;
                next;
        }
        #turn off $in_string if the token is ' and it isn't escaped
        $in_string = 0 if not $escape and $token eq "'";
        $escape = 0; #turn off escape if it was on
        #loop again to get the next token

        next;
    }
    #if the token is ; and we aren't in a string
    if ($token eq ';') {
        #create a new statement
        push @statements,"";
        #loop again to get the next token
        next;
    }
    #add the token to the last statement
    $statements[-1] .= $token;
    #if the token is ' then turn on $in_string
    $in_string = 1 if $token eq "'";
}
#only keep statements that are not blank
@statements = grep { /S/ } @statements;

for my $i (0 .. $#statements) {
    print "statement $i:n$statements[$i]nn";
}