perl oracle动态更新多个字段,perl数据库添加、删除、更新、查询操作例子

#!/usr/bin/perl

use strict;

use warnings;

use DBI;

my $db_name="geneva_admin";

my $db_passwd="geneva_admin";

my $dbh=DBI->connect("dbi:Oracle:","$db_name","$db_passwd")

or die "Can't connect to oracle database:$DBI::errstr\n";

my $sth=$dbh->prepare("select a,b

from a_tmp

where a=2")

or die "Can't prepare SQl prepare:$DBI::errstr\n";

$sth->execute or die "Can't execute:$DBI::errstr\n";

while (my @row = $sth->fetchrow_array()){

my ($a,$b) = @row;

print "1..\$a=$a,\$b=$b\n";

}

$sth->finish();

my $row=3;

my $sql="select a,b

from a_tmp

where a = ?";

$sth=$dbh->prepare($sql) or die "Can't prepare SQl prepare:$DBI::errstr\n";

$sth->execute($row) or die "Can't execute:$DBI::errstr\n";

while (my @row = $sth->fetchrow_array()){

my ($a,$b) = @row;

print "2..\$a=$a,\$b=$b\n";

}

$sth->finish();

my $row_a=3;

my $row_c=0;

$sql="select a,b

from a_tmp

where a = ?

and c = ?";

$sth=$dbh->prepare($sql) or die "Can't prepare SQl prepare:$DBI::errstr\n";

$sth->execute($row_a,$row_c) or die "Can't execute:$DBI::errstr\n";

while (my @row = $sth->fetchrow_array()){

my ($a,$b) = @row;

print "3..\$a=$a,\$b=$b\n";

}

$sth->finish();

for $row(1,2,3){

$sql="select a,b

from a_tmp

where a = ?";

$sth=$dbh->prepare($sql) or die "Can't prepare SQl prepare:$DBI::errstr\n";

$sth->execute($row) or die "Can't execute:$DBI::errstr\n";

while (my @row = $sth->fetchrow_array()){

my ($a,$b) = @row;

print "4..\$a=$a,\$b=$b\n";

}

}

$sth->finish();

#for $row(1,2,3){

#$sql="insert into a_tmp

# values (?,?,?)";

#$sth=$dbh->prepare($sql) or die "Can't prepare SQl prepare:$DBI::errstr\n";

#$sth->execute($row,$row+1,$row+2) or die "Can't execute:$DBI::errstr\n";

#}

##$dbh->commit;

#$sth->finish();

#$sql="insert into a_tmp

# values (100,30,2)";

#$sth=$dbh->prepare($sql) or die "Can't prepare SQl prepare:$DBI::errstr\n";

#$sth->execute or die "Can't execute:$DBI::errstr\n";

##$dbh->commit;

#$sth->finish();

for $row(1,2,3){

$sql="update a_tmp

set b = ?

, c = ?

where a = ?";

$sth=$dbh->prepare($sql) or die "Can't prepare SQl prepare:$DBI::errstr\n";

$sth->execute($row+100,$row+50,$row) or die "Can't execute:$DBI::errstr\n";

}

#$dbh->commit;

$sth->finish();

for $row(1,2,3){

$sql="delete from a_tmp

where c=2";

$sth=$dbh->prepare($sql) or die "Can't prepare SQl prepare:$DBI::errstr\n";

$sth->execute or die "Can't execute:$DBI::errstr\n";

}

#$dbh->commit;

$sth->finish();

$dbh->do("insert into a_tmp values (1,1,1)") or die "$DBI::errstr\n";

$dbh->do("delete from a_tmp where c=51") or die "$DBI::errstr\n";

#$dbh->commit;

$sth->finish();

$dbh->disconnect;