oracle Perl DBI - 使用多条语句运行 SQL 脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1232950/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Perl DBI - run SQL Script with multiple statements
提问by guigui42
I have a sql file test.sqlused to run some SQL (create object / update / delete / insert) that can look like this
我有一个 sql 文件 test.sql用于运行一些看起来像这样的 SQL（创建对象/更新/删除/插入）
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);
Usually, i would just use SQLPLUS (from within Perl) to execute this test.sql using this command : @test.sql
通常，我只会使用 SQLPLUS（从 Perl 中）使用以下命令执行此 test.sql：@test.sql
Is there a way to do the same thing, using DBI in Perl ? So far, i found DBI can only execute one statement at a time, and without the ";" at the end.
有没有办法做同样的事情，在 Perl 中使用 DBI？到目前为止，我发现 DBI 一次只能执行一条语句，而且没有“;” 在末尾。
回答by Chas. Owens
The database controls how many statements can be executed at a time. I can't remember if Oracle allows multiple statements per prepareor not (MySQL does). Try this:
数据库控制一次可以执行多少条语句。我不记得 Oracle 是否允许每个语句有多个语句prepare（MySQL允许）。尝试这个：
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;
Of course, you get better error handling if you break the statements up. You can use a simple parser to break the string up into individual statements:
当然，如果将语句分解，您将获得更好的错误处理。您可以使用简单的解析器将字符串分解为单独的语句：
#!/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]\n\n";
}
回答by emazep
Please, have a look at this new CPAN module: DBIx::MultiStatementDo
请看看这个新的 CPAN 模块：DBIx::MultiStatementDo
It has been conceived precisely for that.
它正是为此而设计的。
回答by Gary Myers
Oracle can run multiple SQL statements in one prepare using an anonymous PL/SQL block.
Oracle 可以使用匿名 PL/SQL 块在一次准备中运行多个 SQL 语句。
eg
例如
$dbh->do("
BEGIN
UPDATE table_1 SET col_a = col_a -1;
DELETE FROM table_2 where id in (select id from table_1 where col_a = 0);
END;
");
DDL (creating or dropping objects) is more complicated, mostly because it is something you shouldn't be doing on an ad-hoc basis.
DDL（创建或删除对象）更复杂，主要是因为它是您不应该在临时基础上做的事情。
回答by Беров
You may add yet another layer of logic in Perl which parses the SQL script, splits it into statements and execute it one by one using the technique above
您可以在 Perl 中添加另一层逻辑，用于解析 SQL 脚本，将其拆分为语句并使用上述技术逐个执行
--sql file
-- [statement1]
SQLCODE...
-- [statement2]
SQLCODE...
#Gets queries from file.
sub sql_q {
my ($self) = @_;
return $self->{sql_q} if $self->{sql_q};
my $file = $self->{sql_queries_file};
$self->{sql_q} || do {
-e $file || croak( 'Queries file ' . $file . ' can not be found.' );
my $fh = IO::File->new("< $file");
my @lines;
( $fh->binmode and @lines = $fh->getlines and $fh->close ) or croak $!;
my ($key);
foreach ( 0 .. @lines - 1 ) {
next if ( $lines[$_] =~ /^;/ );
if ( $lines[$_] =~ /^--\s*?\[(\w+)\]/ ) {
$key = ;
}
$self->{sql_q}{$key} .= $lines[$_] if $key;
}
};
return $self->{sql_q};
}
#then in your script
#foreach statement something like
$dbh->prepare($sql_obj->{sql_q}->{statement_name})->execute(@bindvars);
