perl dbi占位符的使用方法
戻る
使用REF
<![CDATA[
use strict;
use DBI;
my(%t,$n,$row,$d_ref,$ref);
($d_ref) = open_db($d_ref);
$ref = $$d_ref{dbh}->selectall_arrayref(
"SELECT id, name FROM main_type1
WHERE id > ? AND series = ?",
undef, 1290, "XXXSERIES");
$$d_ref{dbh}->disconnect;
for $row ( @$ref ) {
($t{id1},$t{name1}) = @$row;
print "id=$t{id1},name=$t{name1}\n";
}
sub open_db {
my($d_ref) = @_;
$$d_ref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$d_ref{dbh} = DBI->connect($$d_ref{dsn},"cbuser","cbpass")
or die "Cannot connect to server\n";
return($d_ref);
}
运行结果
id=1291,name=KWV 30x3
id=1292,name=DAD-32SSSD4
id=1293,name=VWK-1628-580/550
id=1294,name=BBC-VTR 354A.32
id=1295,name=CIT-2000
]]>
使用do()
<![CDATA[
use strict;
use DBI;
my(%t,$n,$d_ref);
($d_ref) = open_db($d_ref);
for $n ( 1 .. 23 ) {
$t{nf} = sprintf("%02d",$n);
$t{makerref1} = 'TEST' . $t{nf};
$t{sth} = $$d_ref{dbh}->do("UPDATE quo1 SET makerref = ? WHERE id = ?",
undef,
$t{makerref1},$n);
}
$$d_ref{dbh}->disconnect;
sub open_db {
my($d_ref) = @_;
$$d_ref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$d_ref{dbh} = DBI->connect($$d_ref{dsn},"cbuser","cbpass")
or die "Cannot connect to server\n";
return($d_ref);
}
]]>
使用prepare()和execute()
<![CDATA[
use strict;
use DBI;
my(%t,$n,$d_ref);
($d_ref) = open_db($d_ref);
for $n ( 1 .. 23 ) {
$t{nf} = sprintf("%03d",$n);
$t{makerref1} = 'TTT' . $t{nf};
$t{sth} = $$d_ref{dbh}->prepare("UPDATE quo1 SET makerref = ? WHERE id = ?");
$t{count} = $t{sth}->execute($t{makerref1},$n);
print "$n==>$t{count}\n";
}
$$d_ref{dbh}->disconnect;
sub open_db {
my($d_ref) = @_;
$$d_ref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$d_ref{dbh} = DBI->connect($$d_ref{dsn},"cbuser","cbpass")
or die "Cannot connect to server\n";
return($d_ref);
}
运行结果
1==>1
2==>1
3==>1
4==>1
5==>1
6==>1
7==>1
8==>1
9==>1
10==>1
11==>1
12==>1
13==>1
14==>1
15==>1
16==>1
17==>1
18==>1
19==>1
20==>1
21==>1
22==>1
23==>1
]]>
使用bind_param()
<![CDATA[
use strict;
use DBI;
my(%t,$n,$d_ref);
($d_ref) = open_db($d_ref);
for $n ( 1 .. 23 ) {
$t{nf} = sprintf("%03d",$n);
$t{makerref1} = 'XXX' . $t{nf};
$t{sth} = $$d_ref{dbh}->prepare("UPDATE quo1 SET makerref = ? WHERE id = ?");
$t{sth}->bind_param(1,$t{makerref1});
$t{sth}->bind_param(2,$t{nf});
$t{count} = $t{sth}->execute();
print "$n==>$t{count}\n";
}
$$d_ref{dbh}->disconnect;
sub open_db {
my($d_ref) = @_;
$$d_ref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$d_ref{dbh} = DBI->connect($$d_ref{dsn},"cbuser","cbpass")
or die "Cannot connect to server\n";
return($d_ref);
}
运行结果
1==>1
2==>1
3==>1
4==>1
5==>1
6==>1
7==>1
8==>1
9==>1
10==>1
11==>1
12==>1
13==>1
14==>1
15==>1
16==>1
17==>1
18==>1
19==>1
20==>1
21==>1
22==>1
23==>1
use strict;
use DBI;
my(%t,$n,$d_ref,$ref);
($d_ref) = open_db($d_ref);
$t{sth} = $$d_ref{dbh}->prepare("SELECT * FROM quo1 WHERE makerref LIKE ?");
$t{sth}->bind_param(1,"%3%");
$t{sth}->execute();
while ( $ref = $t{sth}->fetchrow_hashref ())
{
print "id: $ref->{id}, makerref: $ref->{makerref}, price: $ref->{price}\n";
}
$t{sth}->finish();
$$d_ref{dbh}->disconnect;
sub open_db {
my($d_ref) = @_;
$$d_ref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$d_ref{dbh} = DBI->connect($$d_ref{dsn},"cbuser","cbpass")
or die "Cannot connect to server\n";
return($d_ref);
}
运行结果
id: 3, makerref: XXX003, price: P
id: 13, makerref: XXX013, price: P
id: 23, makerref: XXX023, price: P
]]>
戻る