perl : 將SQL抓出的字串做比對 (Oracle : utf8)

#!/usr/bin/perl

require "$ENV{HOME}/perl/setEnv.pl";

use DBI;
use Mail::Sendmail;
use MIME::QuotedPrint;
use MIME::Base64;
use Encode;
#use utf8;

$ENV{LANG} = "zh_TW.Big5";
#$ENV{LANG} = "zh_TW.UTF-8";

my $dbh = DBI->connect("$connectString", "$username", "$pass")
  or print "Can't connect to database: ", $DBI::errstr, "\n";

my $mailList;
my $mailListCC;
my @dt = &getDate(-1);
my $html;

&main();

sub main {
    &getWO();
}

sub getWO {
    my $str  = encode("utf8",decode("big5","公司人員"));
    my $str2 = encode("utf8",decode("big5","公用電話區"));
    my $sql = q{
select a.*,rownum
  from (
        select depart_no,depart_name,round(sum(telamt)) telamt,
               --replace(c.email,'@','\@') email_leader
               c.email email_leader
          from tel_vw_report a,hr_tbl_glsegment b,hr_tbl_personnel c
         where rmnth like to_char(sysdate-1,'yyyymm')
           and telcat = ?
           and a.depart_no = b.segment_no
           and b.leader = c.id
         group by depart_no,depart_name,c.email
         order by telamt desc
       ) a
 where rownum <= 20
        };
    #print "$sql";
    #$sql = encode("utf8",decode("big5",$sql));
    my $sth = $dbh->prepare($sql);
    $sth->execute($str);
      
    $html .= qq{<table width = "50%" border="1">};
    $html .= qq{<tr><th width = "250%" bgcolor="#8fb3f3">排名</th><th width = "10%" bgcolor="#8fb3f3">部門代碼</th><th width = "5%" bgcolor="#8fb3f3">部門名稱</th>};
    $html .= qq{<th width = "5%" bgcolor="#8fb3f3">金額</th></tr>};
  
    while (my @data = $sth->fetchrow_array()) {
        #print "@data\n";
        $html .= qq {<tr>};
        $data[1] = encode("big5",$data[1]); #寄出內容要big5
        $html .= qq {<td>$data[4]</td><td>$data[0]</td><td>$data[1]</td><td>$data[2]</td>};
        $html .= qq {</tr>};
        #$mailList   .= $data[3] .";";
      #$mailListCC .= $data[6] .";";
    }
    $html .= qq{</table>};
    #$mailListCC .= "ty.ruan\@aot.com.tw";
  
    $sth->execute($str);
    $mailListCC = "";
    while (my @data = $sth->fetchrow_array()) {
        $data[1] = encode("utf8",$data[1]); #比對內容要utf8
        if ($data[1] =~ m/$str2/o) {
            $mailList   = "xxx\@yyy.com.tw";
        }
        else {
            $mailList   = $data[3];
        }
        #print "$data[4],$data[0],$data[1]\t$mailList\n";
      $mailListCC = "xxx1\@yyy.com.tw" if ($data[4] == 1); #只要收一封第一名的就好，多了很煩
      $data[1] = encode("big5",$data[1]);
        &mailTelAMT($data[4],$data[0],$data[1]);
    }
  
}

sub mailTelAMT {
    my ($rank,$depart,$deptnm) = @_;
    my %mail = (
                From => 'MIS <xxx1@yyy.com.tw>',
                  To => "$mailList",
                                Cc => "$mailListCC",
                                #To => "xxx1\@yyy.com.tw",
             Subject => "本月部門撥打公司人員手機(不經MVPN)排名 -- $depart $deptnm 第$rank名",
               );
    $mail{smtp} = "$smtp";

    my $url = "http://yyy-reporting/ReportServer/Pages/ReportViewer.aspx?%2f%e9%9b%bb%e8%a9%b1%e8%b2%bb%e7%b5%b1%e8%a8%88%2f%e9%9b%bb%e8%a9%b1%e8%b2%bb%e7%b5%b1%e8%a8%88Summary&rs:Command=Render";
    #print "$url\n";
 
  $message = qq {"$dt[5]/$dt[4]/$dt[3] $dt[2]:$dt[1]:$dt[0]"<br>
<br>更多資訊，請至 <a href=${url}>這裡</a>查詢
<br>請鼓勵部門員工撥打公司人員手機時多使用MVPN平台，協助公司減低電信通訊費用；<font color=blue>謝謝你的幫忙</font>
$html
};
  #$message = encode("utf8",decode("big5",$message));
  
    $boundary = "====" . time() . "====";
    $mail{'content-type'} = "multipart/html; boundary=\"$boundary\"";
  
$boundary = '--'.$boundary;
$mail{body} = <<END_OF_BODY;
$boundary
Content-Type: text/html; charset="big5"
Content-Transfer-Encoding: quoted-printable

$message
$boundary

$mail{body}
$boundary--
END_OF_BODY
  
    sendmail(%mail) || print "Error: $Mail::Sendmail::error\n";

}