非税代收系统主机创建对账文件的perl脚本
非税代收系统主机每天晚上需要自动生成text格式的对账文本，因此编写了如下perl脚本，加入crontab，让它每天自动执行。
#!/usr/bin/perl# createDuizhangText.pluse strict;use warnings;use DBI;#use Text::CSV_XS;my $server='localhost';my $username='dsywfs';my $password='*******';my $dbname='dsywfs';my $dsn = "DBI:mysql:host=$server;database=$dbname";my %conn_attrs = (PrintError => 0, RaiseError => 1, AutoCommit => 1);my $yhbm='440900015';#@ _CONNECT_my $dbh = DBI->connect ($dsn, $username, $password, \%conn_attrs) or die "Connection error: " . "$DBI::errstr ($DBI::err/$DBI::state)\n";#@ _CONNECT_my ($year,$mon,$day)=(localtime)[5,4,3];$year+=1900;$mon+=1;#$day-=1; //by laihanfenmy $jfdate=sprintf("%04u-%02u-%02u",$year,$mon,$day);my $dzdate=sprintf("%04u%02u%02u",$year,$mon,$day);my $filename = '/tmp/YHSC/YHSK' . $yhbm . $dzdate . '01.txt';#$jfdate = '2010-01-05';#@ _COUNT_ZBS_my $sth = $dbh->prepare ("SELECT count(*) AS `zbs` FROM `skmx` WHERE `jfdate` LIKE '$jfdate%' AND `dzLog`='0' AND `czLog`='0'");$sth->execute ();my $zbs = $sth->fetchrow_array ();#my $zbs = $bs[0];$sth->finish ();#@ _COUNT_ZBS_#@ _COUNT_FKJE_$sth = $dbh->prepare ("SELECT sum(`fkje`) AS `fkje` FROM `skmx` WHERE `jfdate` LIKE '$jfdate%' AND `dzLog`='0' AND `czLog`='0'");$sth->execute ();my $fkje = $sth->fetchrow_array ();#my $fkje = $je[0];$sth->finish ();#@ _COUNT_FKJE_#@ _COUNT_JSJE_$sth = $dbh->prepare ("SELECT sum(`jsje`) AS `jsje` FROM `skmx` WHERE `jfdate` LIKE '$jfdate%' AND `dzLog`='0' AND `czLog`='0' AND `jsje`>0");$sth->execute ();my $jsje = $sth->fetchrow_array ();#my $jsje = $js[0];$sth->finish ();#@ _COUNT_JSJE_?
注：
在perl中可以使用map来转化任何undel（NULL）值：
// 摘自《MySQL CooKbook中文版》（第二版）115页while (my @var = $sth->fetchrow_array()){ @var = map { defined ($_) ? $_ : "NULL" } @var;}??