#! /usr/bin/perl -wuse 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 {$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 [$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 );