perl 数据库查询定制程序一例

#!/usr/bin/perl -w

#
#caiyuqiang maker
#2009year@
#

use strict;
use warnings;

my $caiqy_fnlist_file = "caiyqfnlist.unl";
my $g_fncount = 0;

my $argnum = @ARGV; 
my $msisdn;

&main;

sub main()
{
   &printmessage;
}

sub printmessage()
{
            if ($argnum =~ /[2-4]/)
            {
                    $msisdn = $ARGV[0];
            }
            else
            {
                    &menulist;
                    exit();
            }
}

sub menulist()
{
            print "**************************** nPlease choice n1. query the user's famility number. n2. delete the user's famili
ty number. n3. insert the user's famility number. n4. modify the user's famility number. n5. exitn";
            print "**************************** n";
          
            while(<>)
            {
                    chomp $_;
                  
                    if ($_ eq "1")  #queryfamilitynum
                    {
                            print "Please enter the user's telephone number:n";
                            $msisdn = <>;
                            chomp $msisdn;
                            &queryfamilitynum($msisdn);
                    }             
                    elsif ($_ eq "2")  #deletefamilitynumber
                    {
                            print "Please enter the user's telephone number:n";
                            $msisdn = <>;
                            chomp $msisdn;
                            print "Please choice: n1: delete all the number.n2: delete the signal number.n";
                            my $choice = <>;
                            chomp $choice;
                            if ($choice eq "1")          
                            {
                                    &deletefamilitynumber($msisdn, "");   #delete all the number
                            }
                            elsif ($choice eq "2")
                            {
                                    print "Please enter the delete number.n";
                                    my $delnum = <>;
                                    chomp $delnum;
                                    &deletefamilitynumber($msisdn, &translate($delnum));  #delete the signal number
                            }
                    }
                    elsif ($_ eq "3")
                    {
                            print "Please enter the user's telephone number:n";
                            $msisdn = <>;
                            chomp $msisdn;
                            print "Please enter the user's want insert familitynumber:n";
                            my $strfnnum = <>;
                            chomp $strfnnum;
                            &insertfamilitynum($msisdn, &translate($strfnnum));
                    }
                    elsif ($_ eq "4")
                    {
                            print "Please enter the user's telephone number:n";
                  $msisdn = <>;
                  chomp $msisdn;
                  print "Please enter the current familitynumber!n";
                  my $oldfnnum = <>;
                  chomp $oldfnnum;
                  print "Please enter the new familitynumber!n";
                  my $newfnnum = <>;
                  chomp $newfnnum;
                  &updatefamilitynum($msisdn, &translate($oldfnnum), &translate($newfnnum));
                    }
            elsif ($_ eq "5")
            {
                    exit;
            }
            else
            {
                    print "There has something wrong in writen!n";
            }
            sleep(2);
            print "Please choice want do you want!n"; 
            }
}

#
sub queryfamilitynum()   #15347451491    #13556121426
{
            my ($phone) = @_;
            my $fnnum;
          
            open(FD, ">", "./$caiqy_fnlist_file") or die "can not open $caiqy_fnlist_filen";
          
            `sqlplus userdb/userdb@ora11g << EOF
            set colsep '|';           ##设置分割符号'|'
            set pagesize 0;           ##去掉头顶的空行 
            set linesize 2500;        ##设置行字符数  
            set trims on ;            ##去掉空字符 
            set echo off;             ##关掉回显
            set heading off;          ##关掉标题行
            set termout off;          ##关掉终端显示
            set feedback off;         ##关掉行数显示
            spool $caiqy_fnlist_file;
            select FAMILIARITYNUM, SUBGROUPNO from userdb.OCS_SUBFNLIST where SUBSCRIBERKEY in (select SUBSCRIBERKEY from numservice
 where SERVICENUMBER='$phone');
      spool off;
      EOF`;
      close(FD);
    
      open (RFD, "<", "./$caiqy_fnlist_file") or die "can not open $caiqy_fnlist_filen";
      my $outtime = 0;
      while(<RFD>)
      {
            chomp $_;
            if ($_ !~ m/SQL/)
            {
                    &printfntype($_);
            }
      }
    
      if ($g_fncount == 0)
      {
            print "The user's familitynumber was not exit!n";
      }
      close(FD);
      unlink("$caiqy_fnlist_file");
 
}

#
sub printfntype()
{
            my ($fnstr) = @_;
            if ($fnstr =~ m/[0-9]/)
            {
                    my $i = index($fnstr, "|");
                    my $type = substr($fnstr, $i);
                    my $fn = substr($fnstr, 0, $i-1);
                    if ($type =~ m/[8]/)
                    {
                            $g_fncount++;
                            print "The user's famility number is : $fnn";
                    }
                    elsif ($type =~ m/[5]/)
                    {
                            $g_fncount++;
                            print "The user's private friend number is: $fnn";
                    }
                    elsif ($type =~ m/[7]/)
                    {
                            $g_fncount++;
                            print "The user's private friend message number is: $fnn";
                    }
            }
}

#
sub translate()
{
         my ($tmpfnnum) = @_;  
         if (substr($tmpfnnum, 0,2) !~  m/86/)
         {
                  my $newnum = join("", "86",$tmpfnnum);
                  return $newnum;                 
         }
         else
         {
                  return $tmpfnnum;
         }
}

#
sub deletefamilitynumber()
{
            my ($phone, $fnnum) = @_;
          
            if ($fnnum !~ m/[0-9]/)   #The key to delete the signal number
            {
                    `mdsql << EOF  #内存库导入文件的格式1
                    unload to $caiqy_fnlist_file select * from numservice where servicenumber = '$phone';
                    EOF`;
                  
                    my $subscriberkey = `linuxjishu/13830.html target=_blank class=infotextkey>awk -F "|" '{print $20}' $caiqy_fnlist_file`;
                    chomp $subscriberkey;
                  
                    `sqlplus userdb/userdb@ora11g << EOF
                    delete from ocs_subfnlist where subscriberkey = $subscriberkey;
                    EOF`;
                  
            `mdsql << EOF
            delete from ocs_subfnlist where subscriberkey = $subscriberkey;
            EOF`;                 
            }
            else   #The key to delete all the number
            {
                  `sqlplus userdb/userdb@ora11g << EOF
                  delete from ocs_subfnlist where familiaritynum = '$fnnum';
                  EOF`;
                
                  `mdsql << EOF
                  delete from ocs_subfnlist where familiaritynum = '$fnnum';
                  EOF`;
            }
            print "delete the familiaritynum successful! n";
}

#
sub insertfamilitynum()
{
          
            my ($phone, $fnnum) = @_;
            `mdsql << !   #内存库导入文件的格式2      
            unload to $caiqy_fnlist_file select * from numservice where servicenumber = '$phone';
            q
            !`;
          
            my $subscriberkey = `awk -F "|" '{print $20}' $caiqy_fnlist_file`;
            chomp $subscriberkey;
          
            my $databasenow = getdatabasenow();
            my $now         = getnow();
          
            open(SQLFD, ">./caiyqfn.sql") or die "can not open tmpfn.sql";
            print SQLFD "insert into ocs_subfnlist(subscriberkey,applytime,expiretime,familiaritynum,subgroupno) values ($subscriber
key,'$now','20370101000000','$fnnum',8) ;";
          
            my $result1 =`mdsql < caiyqfn.sql`;
      if ($result1 !~ m/successful/)
      {
                 print "The user insert familiaritynum neicun error!n";
                 exit; 
      }
    
      `echo "insert into ocs_subfnlist(subscriberkey, applytime, expiretime, familiaritynum,subgroupno, fntype, HOMECBP, CREDATE, LA
STUPDDATE) values($subscriberkey, to_date('$databasenow', 'yyyy-mm-dd'), to_date('20370101', 'yyyy-mm-dd'), '$fnnum',8, 8, 121, to_d
ate('$databasenow','yyyy-mm-dd'), to_date('20370101','yyyy-mm-dd'));" > caiyqfn.sql`;
      my $result2 = `sqlplus userdb/userdb@ora11g < caiyqfn.sql`;
    
      close(SQLFD);
      print "The user insert familiaritynum successful!n";
}

#日期格式为：yyyymmdd
sub getdatabasenow()
{
            my $now = `date +'%Y%m%d'`;
            $now =~ s/n//g;
            return $now;
}

#日期格式：yyyymmddhh24miss
sub getnow()
{
            my $now = `date +'%Y%m%d%H%M%S'`;
            $now =~ s/n//g;
            return $now;
} 

sub updatefamilitynum()
{
            my ($phone, $oldfnnum, $newfnnum) = @_;
            print "The user's telephone number :[$phone] update [$oldfnnum] to [$newfnnum]n";
          
            `mdsql << !
      unload to $caiqy_fnlist_file select * from numservice where servicenumber = '$phone';
      q
      !`;
    
      my $subscriberkey = `awk -F "|" '{print $20}' $caiqy_fnlist_file`;
      chomp $subscriberkey;
    
      my $result = `sqlplus userdb/userdb@ora11g << !
      update ocs_subfnlist set familiaritynum = '$newfnnum' where subscriberkey = $subscriberkey and familiaritynum = '$oldfnnum';
      !`;
    
      if ($result !~ m/updated/)
      {
            print "The use modify the familiaritynum failed!n";
      }
    
      $result = `mdsql << !
      update ocs_subfnlist set familiaritynum = '$newfnnum' where subscriberkey  = $subscriberkey and familiaritynum = '$oldfnnum';
      q
      !`;
    
      print "The use modify has successful!n";
}