Perl操作Oracle

q986

　　一、 perl连接Oracle数据库



[plain] view plaincopy

• [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 plaincopy

• [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 plaincopy

• [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 plaincopy

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