#! /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 < @{$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 $cols/n"; 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 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 );

下载本文