Perl操作Oracle
一、 perl连接Oracle数据库view plaincopy
[*]$ 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";
[*]
[*]$ ./connect.pl
[*]I have connected to the Oracle database!
[*]Disconnected from Oracle databae!
二、向数据库插入数据
view plaincopy
[*]$ 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";
[*]
[*]$ ./insert.pl
[*]I have inserted the record!
[*]$
三、删除数据
view plaincopy
[*]$ 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";
[*]
[*]$ ./delete.pl
[*]I have deleted the record!
四、查询
view plaincopy
[*]$ 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";
[*]$ ./select.pl
[*]I have connected to the Oracle 11g R2 database!
[*]The results are:
[*]
[*]ID:0, --- NAME:**e
[*]ID:1, --- NAME:**e
[*]$
页:
[1]