Perl DBI模块

Oracle

my $dbh = DBI->connect( 'dbi:Oracle:orcl',
             'liaojl',
             'password',
             {
                RaiseError => 1,
                AutoCommit => 0
            }
     ) || die "Database connection not made: $DBI::errstr";

Select语句

Select操作首先通过prepare()方法调用SQL语句，然后执行execute()。

use DBI;
 
my $dbh = DBI->connect("DBI:mysql:HR") 
     or die "Can't connect mysql database:".DBI->errstr;
 
my $sql = qq{ SELECT * FROM employees }; 
 
my $sth = $dbh->prepare($sql) or die $dbh->errstr;
 
$sth->execute() or die "Error: " . $sth->errstr;
 
while (@data = $sth->fetchrow_array()) {
  print "Name:$data[0]\t Dept:$data[1]\n";
}
 
$dbh->disconnect;

输入条件是变量时，变量可以通过execute()方法的参数传入SQL语句，而SQL语句中用?代表参数位置。

my $sql = qq{ SELECT * FROM employees where emp_no=? }; 
$sth->execute($id);

通过bind_columns()方法可以将SQL语句的返回列绑定到某个变量。

my $sql = qq{ SELECT id, name, title, phone FROM employees };
my $sth = $dbh->prepare( $sql );
$sth->execute(); 

my( $id, $name, $title, $phone );
$sth->bind_columns( undef, \$id, \$name, \$title, \$phone );
 
while( $sth->fetch() ) {
    print "$name, $title, $phone\n";
}
 
$sth->finish();

Insert语句

my $sql = q{ INSERT INTO address (id, name,email,telephone) VALUES (?, ?, ?, ?); }; 
 
my $sth = $dbh->prepare($sql);
 
$sth->execute();
 
while (@data = $sth->fetchrow_array()) {
  print "Name:$data[0]\t Dept:$data[1]\n";
}
 
$dbh->disconnect;