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		   $tablespace_name		$status
";  
}  
$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 "
@arr
";
}
  
$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 "
";
};