Perl操作Oracle-白红宇

Perl操作Oracle

阅读量：7237 次

发布时间：2019-06-29

本文共 2655 字，大约阅读时间需要 8 分钟。

一、 perl连接Oracle数据库

[plain]

[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]

[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]

[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]

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