#! /usr/bin/perl -w
use strict;
use DBI;
# 创建mysql连接
sub create_conn # returns connection handle
{
my $dsn = "DBI:mysql:test:127.0.0.1:3306";
my $user = "test";
my $pass = "password";
my $dbh = DBI->connect($dsn, $user, $pass, {RaiseError=>0, PrintError => 1})
or die "Could not connect to mysql server: $DBI::err($DBI::errstr)\n";
}
# 使用fetchrow_array获取记录并打印
sub fetch_and_print_results # params: stmt handle
{
my $sth = shift(@_);
while (my @row = $sth->fetchrow_array())
{
print join("\t", @row), "\n";
}
}
# 使用fetchrow_arrayref获取记录引用并打印
sub fetch_and_print_results2 # params: stmt handle
{
my $sth = shift(@_);
while (my $rowref = $sth->fetchrow_arrayref())
{
my $delim = "";
for( my $i = 0; $i
{
#$rowref->{$i} = " " if !defined ($rowref->{$i}); # NULL to space
print $delim . @{$rowref}[$i];
$delim = ',';
}
print "\n";
}
}
# 使用fetchrow_hashref获取记录hash表引用并打印
sub fetch_and_print_results3 # params: stmt handle
{
my $sth = shift(@_);
my $labels = $sth->{NAME};
my $cols = $sth->{NUM_OF_FIELDS};
print ">>>> field count $cols\n";
while (my $rowref = $sth->fetchrow_hashref() )
{
my $delim = "";
for( my $i = 0; $i
{
print $delim . $labels->[$i]. ' = '.%{$rowref}->{$labels->[$i]};
$delim = ',';
}
print "\n";
}
}
# 删除表中所有记录
sub test_clear_table
{
my $dbh = create_conn;
my $rows = $dbh->do(qq/delete from member/); # 直接执行删除语句,并返回删除记录数
print ">>>> total $rows records deleted\n";
$dbh->disconnect;
}
# 数据插入与查询
sub test_insert_and_select
{
my $dbh = create_conn();
# 执行数据插入语句
print '>>>>>>>> test_insert_and_select'."\n";
my $sql_insert = "insert into member (username, password) values ('julia', 'roberts')";
my $rows = $dbh->do($sql_insert);
print "$rows row(s) inserted\n";
# 执行查询语句
print ">>>> fetch result 1\n";
my $sth = $dbh->prepare("select * from member");
$sth->execute;
fetch_and_print_results $sth;
$sth->finish;
print ">>>> fetch result 2\n";
$sth = $dbh->prepare("select username, password from member");
$sth->execute;
fetch_and_print_results2 $sth;
$sth->finish;
# 执行带参数的查询语句
print ">>>> fetch result 3\n";
$sth = $dbh->prepare("select username, password from member where username = ?");
$sth->execute('julia');
fetch_and_print_results3 $sth;
$sth->finish;
$dbh->disconnect;
}
# 参数化insert语句执行
sub test_param_insert
{
my $dbh = create_conn();
my $sth = $dbh->prepare(qq{insert into member (username, password) values (?, ?)});
my $rows = $sth->execute('maria', 'louise');
print "$rows".' inserted: maria louise';
$sth->finish;
# undef处表示未设置的查询选项参数,不能省略
$dbh->do(qq/insert into member (username, password) values (?, ?)/, undef,
'george', 'cardon');
$dbh->disconnect;
}
# 绑定select输出
sub test_select_out_param_bind
{
print ">>>>>>> test_select_param_bind\n";
my ($user, $pass);
my $dbh = create_conn;
my $sth = $dbh->prepare(qq{select username, password from member});
$sth->execute();
$sth->bind_col(1, \$user);
$sth->bind_col(2, \$pass);
print(">>1 == $user, $pass\n") while $sth->fetch();
$sth->finish();
$sth = $dbh->prepare(qq{select username, password from member});
$sth->execute();
$sth->bind_columns(\$user, \$pass);
print(">>2 == $user, $pass\n") while $sth->fetch();
$sth->finish();
$dbh->disconnect;
}
# 事务调用
sub test_transaction
{
print ">>>>>>> test_transaction\n";
my $dbh = create_conn;
$dbh->{AutoCommit} = 0;
my $sth = $dbh->prepare(qq{insert into member (username, password) values (?,?)});
$sth->execute('tom', 'jerry');
$dbh->commit;
$sth->execute('tom', 'tom');
$dbh->rollback;
$sth->finish;
$dbh->disconnect;
}
sub main
{
test_clear_table;
test_param_insert;
test_insert_and_select;
test_transaction;
test_select_out_param_bind;
}
exit( main );