perl数据库添加、删除、更新、查询操作例子
注意：连接时候使用SID指定的database，所以没有在连接中指定database.
#!/usr/bin/perl
usestrict;
usewarnings;
useDBI;
my$db_name="geneva_admin";
my$db_passwd="geneva_admin";
my$dbh=DBI->connect("dbi:Oracle:","$db_name","$db_passwd")
ordie"Can'tconnecttooracledatabase:$DBI::errstr\n";
my$sth=$dbh->prepare("selecta,b
froma_tmp
wherea=2")
ordie"Can'tprepareSQlprepare:$DBI::errstr\n";
$sth->executeordie"Can'texecute:$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="selecta,b
froma_tmp
wherea=?";
$sth=$dbh->prepare($sql)ordie"Can'tprepareSQlprepare:$DBI::errstr\n";
$sth->execute($row)ordie"Can'texecute:$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="selecta,b
froma_tmp
wherea=?
andc=?";
$sth=$dbh->prepare($sql)ordie"Can'tprepareSQlprepare:$DBI::errstr\n";
$sth->execute($row_a,$row_c)ordie"Can'texecute:$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="selecta,b
froma_tmp
wherea=?";
$sth=$dbh->prepare($sql)ordie"Can'tprepareSQlprepare:$DBI::errstr\n";
$sth->execute($row)ordie"Can'texecute:$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="insertintoa_tmp
#values(?,?,?)";
#$sth=$dbh->prepare($sql)ordie"Can'tprepareSQlprepare:$DBI::errstr\n";
#$sth->execute($row,$row+1,$row+2)ordie"Can'texecute:$DBI::errstr\n";
#}
##$dbh->commit;
#$sth->finish();
#$sql="insertintoa_tmp
#values(100,30,2)";
#$sth=$dbh->prepare($sql)ordie"Can'tprepareSQlprepare:$DBI::errstr\n";
#$sth->executeordie"Can'texecute:$DBI::errstr\n";
##$dbh->commit;
#$sth->finish();
for$row(1,2,3){
$sql="updatea_tmp
setb=?
,c=?
wherea=?";
$sth=$dbh->prepare($sql)ordie"Can'tprepareSQlprepare:$DBI::errstr\n";
$sth->execute($row+100,$row+50,$row)ordie"Can'texecute:$DBI::errstr\n";
}
#$dbh->commit;
$sth->finish();
for$row(1,2,3){
$sql="deletefroma_tmp
wherec=2";
$sth=$dbh->prepare($sql)ordie"Can'tprepareSQlprepare:$DBI::errstr\n";
$sth->executeordie"Can'texecute:$DBI::errstr\n";
}
#$dbh->commit;
$sth->finish();
$dbh->do("insertintoa_tmpvalues(1,1,1)")ordie"$DBI::errstr\n";
$dbh->do("deletefroma_tmpwherec=51")ordie"$DBI::errstr\n";
#$dbh->commit;
$sth->finish();
$dbh->disconnect;