使用Perl管理Oracle数据库

1、连接数据库

#!/usr/bin/perl
use strict;
use DBI;

my $tnsname = "DB234";
my $username = "SCOTT";
my $password = "TIGER";

my $dbh = DBI->connect("dbi:Oracle:$tnsname",$username,$password) or die "Cannot connect db : $DBI::errstr\n";

print "I have connect to the Oracle database!\n";

$dbh->disconnect or warn "DB disconnect failed:$DBI::errstr\n";

2、插入数据

#!/usr/bin/perl

use strict;
use DBI;

my $id = 9;
my $name = 'jame';

my $dbh = DBI->connect("dbi:Oracle:DB234","u1","u1") or die "Cannot connect db :$DBI::errstr\n";

my $sql = qq{INSERT INTO T1 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";

3、删除数据

#!/usr/bin/perl
use strict;
use DBI;

my $id = 4;

my $dbh = DBI->connect("dbi:Oracle:DB234","u1","u1") or die "Cannot connect db : $DBI:: errstr \n";

my $sql = qq{ DELETE FROM T1 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";

4、查询数据

#!/usr/bin/perl

use strict;
use DBI;

my $host = "localhost";
my $sid = "DB234";

my $dbh = DBI->connect("dbi:Oracle:DB234","u1","u1") or die "Cannot conect db $DBI::errstr\n";

print "I have connected to the Oracle 11g R2 Database!\n";

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

$sth->execute();

my ($pid,$pname);

$sth->bind_columns(undef,\$pid,\$pname);
print "The results are:\n\n";
while($sth->fetch()){
        print "ID:$pid ---- NAME:$pname\n";
}

$sth->finish();

$dbh->disconnect or warn "DB disconnect failed: $DBI::errstr\n";

[oracle@arcerzhang perl]$ ./select.pl
I have connected to the Oracle 11g R2 Database!
The results are:

ID:5 ---- NAME:lydiazhang
ID:6 ---- NAME:maryhu
ID:9 ---- NAME:jame
ID:1 ---- NAME:arcerzhang
ID:2 ---- NAME:arcerzhang
ID:3 ---- NAME:arcerzhang

5、使用perl调用存储过程

$dbh->do("BEGIN myPackage.myProcedure;END;");