Perl DBI模块

一: select 某些字段:

[oracle@jhoa dbi]$ cat 1.pl 
use DBI; 
my $dbName = 'orcl';  
my $dbUser = 'test';  
my $dbUserPass = 'test';  
my $dbh = DBI->connect("dbi:Oracle:$dbName", $dbUser, $dbUserPass) or die "can't connect to database " ;
my $hostSql = qq{select table_name,tablespace_name,status from user_tables};  


my ($table_name, $tablespace_name, $status);  
my $selStmt = $dbh->prepare($hostSql);  
$selStmt->bind_columns(undef, \$table_name, \$tablespace_name, \$status);  
$selStmt->execute();  
while( $selStmt->fetch() ){  
  print "$table_name\t\t   $tablespace_name\t\t$status\n";  
}  
$selStmt->finish;  



[oracle@jhoa dbi]$ perl 1.pl 
T200       TEST_DATA    VALID
B      TEST_DATA    VALID
A1       TEST_DATA    VALID
T2       TEST_DATA    VALID
TEST_UNDO1       TEST_DATA    VALID
TEST10       TEST_DATA    VALID
A2       TEST_DATA    VALID
HWM_TEST       HWM_TEST_TBS   VALID
T      TEST_DATA    VALID
A      TEST_DATA    VALID
A10      TEST_DATA    VALID
A7       TEST_DATA    VALID
DISK_INFO      TEST_DATA    VALID
BFW_TEST       BFW_TBS    VALID
T1       TEST_DATA    VALID
GCIMONITOR_INFO      TEST_DATA    VALID
A11      TEST_DATA    VALID
A8       TEST_DATA    VALID

二:select  * 全部列
[oracle@jhoa dbi]$ cat 2.pl 
#!/usr/bin/perl   
use strict;  
use DBI;  
my $dbName = 'orcl';  
my $dbUser = 'test';  
my $dbUserPass = 'test';  
my $dbh = DBI->connect("dbi:Oracle:$dbName", $dbUser, $dbUserPass) or die "can't connect to database ";
my $sql = "select * from T100";  
my $sth = $dbh->prepare($sql);  

$sth->execute();  

while (my  @arr = $sth->fetchrow_array()){
print "\n@arr\n";
}

$sth->finish; 
$dbh->disconnect; 

[oracle@oadb perl]$ cat t1.pl 
use DBI;
use Encode;
use Data::Dumper;
my $dbName = 'oadb';
my $dbUser = 'system';
my $dbUserPass = 'a2p13mvh';
my $dbh = DBI->connect("dbi:Oracle:$dbName", $dbUser, $dbUserPass) or die "can't connect to database ";
my $hostSql = qq{select trim(id),trim(a1),trim(a2) from T2};
my $UNLOAD_SRC_DBCONN = DBI->connect("DBI:Oracle:".$dbName,$dbUser,$dbUserPass) or die("DB connect error!n");
use HTTP::Date qw(time2iso str2time time2iso time2isoz);
my $selStmt = $dbh->prepare($hostSql);
$selStmt->execute();
while(my $Rows = $selStmt->fetchrow_arrayref){
   push (@all, $Rows);
} ;
foreach (@all){
  $tmpstr="";
  foreach $a ( @{$_}){
  $tmpstr=$tmpstr.$a."|"; 
};
print $tmpstr;
print "\n";
};