Perl DBI MySQL数据库访问示例

#!?/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)
??{
???#$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?$colsn";
?while?(my?$rowref?=?$sth->fetchrow_hashref()?)
?{
??my?$delim?=?"";
??for(?my?$i?=?0;?$i?<?$cols;?++$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?deletedn";
?$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)?insertedn";
?
?#?执行查询语句
?print?">>>>?fetch?result?1n";
?my?$sth?=?$dbh->prepare("select?*?from?member");
?$sth->execute;
?fetch_and_print_results?$sth;
?$sth->finish;
?
?print?">>>>?fetch?result?2n";
?$sth?=?$dbh->prepare("select?username,?password?from?member");
?$sth->execute;
?fetch_and_print_results2?$sth;
?$sth->finish;
?
?#?执行带参数的查询语句
?print?">>>>?fetch?result?3n";
?$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,??)/,?undef,??????????'george',?'cardon');
?
?$dbh->disconnect;
}
#?绑定select输出
sub?test_select_out_param_bind?
{
?print?">>>>>>>?test_select_param_bindn";
?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,?$passn")?while?$sth->fetch();?
?$sth->finish();
?
?$sth?=?$dbh->prepare(qq{select?username,?password?from?member});
?$sth->execute();
?$sth->bind_columns($user,?$pass);
?print(">>2?==?$user,?$passn")?while?$sth->fetch();?
?$sth->finish();
?
?$dbh->disconnect;
}
#?事务调用
sub?test_transaction
{
?print?">>>>>>>?test_transactionn";
?my?$dbh?=?create_conn;
?$dbh->{AutoCommit}?=?0;
?my?$sth?=?$dbh->prepare(qq{insert?into?member?(username,?)});
?$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?);