执行更新

删除

# do方法
my $stmt = qq(DELETE from COMPANY where ID=2;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ){
   print $DBI::errstr;
}else{
   print "Total number of rows deleted : $rv\n";
}

# do绑定变量
$stmt = qq(DELETE from COMPANY where ID=?;);
$rv = $dbh->do($stmt,undef,2) or die $DBI::errstr;
if( $rv < 0 ){
   print $DBI::errstr;
}else{
   print "Total number of rows deleted : $rv\n";
}

# prepare方法
my $sth = $dbh->prepare(q{
    DELETE from COMPANY where ID=?});
$sth->bind_param( 1, 3 );
$sth->execute() or die $DBI::errstr;
$sth->finish;

插入

Insert插入数据

# do方法
my $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (1, 'Paul', 32, 'California', 20000.00 ));
my $rv = $dbh->do($stmt) or die $DBI::errstr;

# prepare方法
my $sth = $dbh->prepare(q{
    INSERT INTO t1(id,name,age,address,salary)
      VALUES (?,?,?,?,?)
});
$sth->bind_param( 1, 2 );
$sth->bind_param( 2, 'Allen' );
$sth->bind_param( 3, 25 );
$sth->bind_param( 4, 'Texas' );
$sth->bind_param( 5, 15000.00 );
$sth->execute() or die $DBI::errstr;
$sth->finish;

# 事务
my @data = ([1,'Paul', 32, 'California', 20000.00], [2,'Allen', 25, 'Texas', 15000.00]);
$sth = $dbh->prepare(qq{INSERT INTO t1(id,name,age,address,salary)
      VALUES (?,?,?,?,?)});
$dbh->begin_work();  # start transaction
foreach my $row(@data) {
    $sth->execute(@$row);
}
$dbh->commit();  # end transaction

copy插入数据

my @data= (
    "1,Paul,32,California,20000\n",
    "2,Allen,25,Texas,15000\n",
    "3,Teddy,23,Norway,20000\n",
    "4,Mark,25,Rich-Mond,65000\n",
);
my $sql=qq{COPY COMPANY(ID,NAME,AGE,ADDRESS,SALARY) FROM STDIN WITH DELIMITER ','};
$dbh->do($sql);
foreach my $line (@data) {
    $dbh->pg_putcopydata($line);
}
$dbh->pg_putcopyend();

更新

# do方法
my $stmt = qq(UPDATE COMPANY set SALARY = 25000.00 where ID=1;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ){
   print $DBI::errstr;
}else{
    print "Total number of rows updated : $rv\n";
}

# prepare方法
my $sth = $dbh->prepare(q{
    UPDATE COMPANY set SALARY = ? where ID=?});
$sth->bind_param( 1, 25000.00 );
$sth->bind_param( 2, 2 );
$sth->execute() or die $DBI::errstr;
$sth->finish;