oracle Perl 脚本中的 SQL*Plus
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1727623/
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
SQL*Plus inside Perl script
提问by Vijay
I am trying to connect to a table using SQL*Plusand fetch the data in a Perlscript and store that output in a Perl variable.
我正在尝试使用SQL*Plus连接到表并在Perl脚本中获取数据并将该输出存储在 Perl 变量中。
In a shell script I would do this:
在 shell 脚本中，我会这样做：
SQL_RESULT=`sqlplus -s ${CONNECT_STRING} << EOF
${SQLPLUS_SETTINGS}
select foo||'|'||bar ||'|'|| xyz from temp where dfg='some';
exit;
EOF`
But how can I do this in Perl?
但是我怎样才能在 Perl 中做到这一点呢？
回答by Robert P
Check out the DBI module. In fact, there's a whole website dedicated to it: dbi.perl.org. Also, check out the CPAN module reference for DBI.
查看 DBI 模块。事实上，有一个专门的网站：dbi.perl.org。另外，请查看DBI 的 CPAN 模块参考。
Here's a code example, straight from the first DBI tutorial on google:
这是一个代码示例，直接来自google 上的第一个 DBI 教程：
use DBI;
my $dbh = DBI->connect('DBI:Oracle:payroll')
or die "Couldn't connect to database: " . DBI->errstr;
my $sth = $dbh->prepare('SELECT * FROM people WHERE lastname = ?')
or die "Couldn't prepare statement: " . $dbh->errstr;
$sth->execute($lastname) # Execute the query
or die "Couldn't execute statement: " . $sth->errstr;
# Read the matching records and print them out
while (@data = $sth->fetchrow_array()) {
my $firstname = $data[1];
my $id = $data[2];
print "\t$id: $firstname $lastname\n";
}
if ($sth->rows == 0) {
print "No names matched `$lastname'.\n\n";
}
$sth->finish;
print "\n";
print "Enter name> ";
$dbh->disconnect;
Perl also has that EOF style multiline comment; you can make a long query like this:
Perl 也有 EOF 风格的多行注释；您可以像这样进行长查询：
my $query = <<'END_QUERY';
${SQLPLUS_SETTINGS}
select foo||'|'||bar ||'|'|| xyz from temp where dfg='some';
exit;
END_QUERY
回答by cms
The advice to use the DBI is good, and definitely the rightway to do things, if you're wanting to program Perl scripts against databases.
如果您想针对数据库编写 Perl 脚本，那么使用 DBI 的建议很好，而且绝对是正确的做法。
However, to answer your exact question, if you specifically want to script SQL*Plus, the syntax to do this with a Perl script is fairly similar to the shell version
但是，为了回答您的确切问题，如果您特别想编写 SQL*Plus 脚本，则使用 Perl 脚本执行此操作的语法与 shell 版本非常相似
my $connect_string = 'scott/tiger@test';
my $sqlplus_settings = '';
my $result = qx { sqlplus $connect_string <<EOF
$sqlplus_settings
select 1 from dual;
exit;
EOF
};
print $result;
The qxoperator I use there, is just a politer form of backtick, everything within the brace delimited block is run by a subshell, and the output returned to the assignment. Variables aren't usually upper cased in Perl.
qx我在那里使用的运算符只是一种更礼貌的反引号形式，大括号分隔块中的所有内容都由子shell 运行，并且输出返回给赋值。变量在 Perl 中通常不是大写的。
回答by draegtun
A couple of things:
几件事：
DBI is definitely the best way to go. However please note a previous answer I gave to an Oracle question which maybe "still" relevant: How can I use a database server from a Perl CGI script?
SQL*Plus throws an error if your SQL was too long. It has a fixed line length buffer (I can't recall what it was but I think it was below 2000 chars with Oracle 8). There maybe a workaround (splitting lines up? configuration settings?) but I found switching to DBI the best solution for this and other reasons.
DBI绝对是最好的选择。但是，请注意我之前对一个 Oracle 问题的回答，该问题可能“仍然”相关：How can I use a database server from a Perl CGI script?
如果 SQL 太长，SQL*Plus 会抛出错误。它有一个固定的行长缓冲区（我不记得它是什么，但我认为它在 Oracle 8 中低于 2000 个字符）。可能有一个解决方法（拆分线路？配置设置？）但我发现由于这个和其他原因切换到 DBI 是最好的解决方案。
Caveat: All my information above is based on Oracle 8.
警告：我上面的所有信息均基于 Oracle 8。
/I3az/
/I3az/
回答by runrig
Any question of this type should be prefaced with "I can't use DBI because..." Because you really want to use DBI if at all possible. You might have good reason(s) not to use it, but maybe, we can tell you why your reasons aren't very good and what to do about it. That being said, here's one way to do what you asked, using fork and filehandles, and getting output one line at a time (warning: if you print too much to a process like this, it may block due to buffer issues):
任何此类问题都应该以“我不能使用 DBI 因为...”作为开头，因为如果可能的话，您真的很想使用 DBI。您可能有充分的理由不使用它，但也许，我们可以告诉您为什么您的理由不是很好以及如何处理。话虽如此，这是执行您所要求的操作的一种方法，使用 fork 和文件句柄，并一次输出一行（警告：如果向这样的进程打印太多，它可能会因缓冲区问题而阻塞）：
use strict;
use warnings;
pipe(my($p_rdr, $c_wtr)) or die "Err: $!";
pipe(my($c_rdr, $p_wtr)) or die "Err: $!";
my $pid = fork;
die "Could not fork: $!" unless defined $pid;
unless ($pid) {
close $p_rdr;
close $p_wtr;
open(STDOUT, ">&=", $c_wtr) or die "dup: $!";
open(STDIN, "<&=", $c_rdr) or die "dup: $!";
print "Exec sqlplus\n";
exec qw(sqlplus user/passwd@dbname);
die "Could not exec: $!";
}
close $c_wtr;
close $c_rdr;
print "Print sql\n";
print $p_wtr "select * from table_name where col1 = 'something';\n";
print "Close fh\n";
close $p_wtr;
print "Read results\n";
while (<$p_rdr>) {
print "O: $_";
}
close $p_rdr;
