通常,有一个表有多个字段,有时候会只修改其中的某些字段,定义为NULL的字段不更新,如下updatefield函数。
#!/usr/bin/perl
use strict;
use DBI;
use constant {
NULL => '',
null => ''
};
# 连接到数据库
my $dbh = DBI->connect("dbi:SQLite:dbname=gspider.db","","",{ RaiseError => 1, AutoCommit => 0 });
# 创建表
#$dbh->do("create table test(id INT primary key, number, name)");
# 插入数据
#$dbh->do("insert into test values(1,6838472,'a')");
#$dbh->do("insert into test values(2,6838472,'e')");
#$dbh->do("insert into test values(3,6838472,'f')");
#updatefield(123,'e');
updatefield(NULL,'e');
#查询数据
my $sql = "SELECT * FROM test";
my $dbconn = $dbh->prepare($sql);
$dbconn->execute();
my $table = $dbconn->fetchall_arrayref;
my($i, $j);
for $i ( 0 .. $#{$table} ) {
for $j ( 0 .. $#{$table->[$i]} ) {
print "$table->[$i][$j]\t";
}
print "\n";
}
$dbh->commit();
$dbh->disconnect();
<>;
sub updatefield{
my ($number,$name) = @_;
my ($field,$fieldword);
if(undef == $number){
$field='%s';
$fieldword='';
}
else{
$field=',number=%s';
$fieldword=$dbh->quote_identifier($number);
}
my $sql = sprintf("update test set name= 's' $field where name=%s ",$fieldword,$dbh->quote($name));
$dbh->do($sql);
$dbh->commit();
}