Perl操作Oracle

原作者: [db:作者] 来自: [db:来源] 收藏邀请

一、 perl连接Oracle数据库

[plain] view plain copy

[oracle@oracle11gR2 perl_script]$ more connect.pl
#!/usr/bin/perl
#perl script used to connect to Oracle
use strict;
use DBI;
my $tnsname="ora11gR2";
my $username="scott";
my $password="tiger";
my $dbh=DBI->connect("dbi:Oracle:$tnsname", $username, $password) or die "Cannot conenct db: $DBI::errstr\n";
print "I have connected to the Oracle database!\n";
$dbh->disconnect or warn "DB disconnect failed: $DBI::errstr\n";
print "Disconnected from Oracle databae!\n";
[oracle@oracle11gR2 perl_script]$ ./connect.pl
I have connected to the Oracle database!
Disconnected from Oracle databae!

二、向数据库插入数据

[plain] view plain copy

[oracle@oracle11gR2 perl_script]$ more insert.pl
#!/usr/bin/perl
# this code is used to insert data to Oracle Database
use strict;
use DBI;
my $id = 2;
my $name = "denver";
my $dbh = DBI->connect("dbi:Oracle:ora11gR2", "test","test") or die " Cannot connect db: $DBI::errstr\n";
my $sql = qq{INSERT INTO m VALUES(?,?)};
my $sth = $dbh->prepare($sql);
$sth->execute($id, $name);
print "I have inserted the record!\n";
$dbh->disconnect or warn "DB disconnect failed: $DBI::errstr\n";
[oracle@oracle11gR2 perl_script]$ ./insert.pl
I have inserted the record!
[oracle@oracle11gR2 perl_script]$

三、删除数据

[plain] view plain copy

[oracle@oracle11gR2 perl_script]$ more delete.pl
#!/usr/bin/perl
# Delete Data From Oracle Database
use strict;
use DBI;
my $id=2;
my $dbh = DBI->connect("dbi:Oracle:ora11gR2", "test", "test") or die "Cannot connect db: $DBI::errstr\n";
my $sql = qq{DELETE FROM m WHERE id=$id};
my $sth = $dbh->prepare($sql);
$sth->execute();
print "I have deleted the record!\n";
$dbh->disconnect or warn "DB disconnect failed:$DBI::errstr\n";
[oracle@oracle11gR2 perl_script]$ ./delete.pl
I have deleted the record!

四、查询

[plain] view plain copy

[oracle@oracle11gR2 perl_script]$ more select.pl
#!/usr/bin/perl
# Here is an example code piece to select data from Oracle
use strict;
use DBI;
my $host = "localhost";
my $sid = "denver";
my $dbh = DBI->connect("dbi:Oracle:ora11gR2", "test", "test") or die "Cannot connect db:$DBI::errstr\n";
print "I have connected to the Oracle 11g R2 database!\n";
my $sql = qq{SELECT id, name FROM m};
my $sth = $dbh->prepare($sql);
$sth->execute();
my ($pid, $pname); #declare columns
$sth->bind_columns(undef, \$pid, \$pname);
print "The results are:\n\n";
while ( $sth->fetch() ) { #fetch rows from DataBase
print "ID:$pid, --- NAME:$pname\n";
}
$sth->finish();
$dbh->disconnect or warn "DB disconnect failed: $DBI::errstr\n";
[oracle@oracle11gR2 perl_script]$ ./select.pl
I have connected to the Oracle 11g R2 database!
The results are:
ID:0, --- NAME:**e
ID:1, --- NAME:**e
[oracle@oracle11gR2 perl_script]$