ITPub博客

首页 > 数据库 > 数据库开发技术 > perl协程操作数据库

perl协程操作数据库

原创 数据库开发技术 作者:liiinuuux 时间:2016-09-26 15:32:43 0 删除 编辑
perl用open2打开两个管道,接管sqlplus的标准输入输出,管理数据库。

点击(此处)折叠或打开

  1. use IPC::Open2;
  2. use POSIX;

  3. sub conn
  4. {
  5.      my $r = shift;
  6.      my $w = shift;
  7.      my $conn_str = shift;
  8.      my $pid = open2($r, $w, "sqlplus -S ".$conn_str) or die "$!";
  9.      print $w "set lin 200\n" or die "$!";
  10.      print $w "set pages 0\n";
  11.      print $w "set feedback off\n";
  12.      $pid;
  13. }

  14. sub exec_sql
  15. {
  16.      my $r = shift;
  17.      my $w = shift;
  18.      my $sql = shift;
  19.      my @ret;
  20.      if($sql !~ /;$/){$sql .= ";";}
  21.      print $w $sql."\n";
  22.      print Writer "select 'OK' from dual;\n";
  23.      while(<Reader>)
  24.      {
  25.           chomp;
  26.           trim;
  27.           if(/^OK$/) {last;}
  28.           push @ret, $_;
  29.      }
  30.      @ret;
  31. }

  32. sub exec_sql_single_row
  33. {
  34.      my $r = shift;
  35.         my $w = shift;
  36.      my $sql = shift;
  37.      my @ret = &exec_sql($r, $w, $sql);
  38.      shift @ret;
  39. }

  40. sub get_param
  41. {
  42.      my $r = shift;
  43.         my $w = shift;
  44.      my $param = shift;
  45.      my $sql = "select value from v\$parameter where name = '$param'";
  46.      my @ret = &exec_sql($r, $w, $sql);
  47.      shift @ret;
  48. }

  49. sub date_format
  50. {
  51.      my $r = shift;
  52.         my $w = shift;
  53.      my $date = shift;
  54.      my $from_format = shift;
  55.      my $to_format = shift;
  56.      my $sql = "select to_char(to_date('$date', '$from_format'), '$to_format') from dual";
  57.      &exec_sql_single_row($r, $w, $sql);
  58. }

  59. sub get_db_time
  60. {
  61.      my $r = shift;
  62.      my $w = shift;
  63.      my $format = shift;
  64.      my $add = shift;
  65.      my $sql = "select to_char(sysdate $add, '$format') from dual";
  66.      &exec_sql_single_row($r, $w, $sql);
  67. }

  68. sub get_alert
  69. {
  70.      my $r = shift;
  71.         my $w = shift;
  72.      my $bdump = &get_param($r, $w, "background_dump_dest");
  73.         my $sid = &get_param($r, $w, "instance_name");
  74.         my $logfile = $bdump."/alert_".$sid.".log";
  75. }

  76. sub get_alert_time
  77. {
  78.      my $add_days = shift;
  79.      @months = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
  80.         @days = qw(Sun Mon Tue wed Thu Fri Sat);
  81.      my $str = strftime "%m-%d-%w", localtime(time + $add_days*60*60*24);
  82.      my($mm, $dd, $day) = split /-/, $str;
  83.      my $ret = "$days[$day] $months[$mm-1] $dd";
  84. }

  85. sub check_alert
  86. {
  87.      my $r = shift;
  88.      my $w = shift;
  89.         my $check_days = shift;
  90.      my $logfile = &get_alert($r, $w);
  91.         my $start = &get_alert_time($check_days * -1);
  92.         my $flag = 0;
  93.         my $last_time = "";
  94.      my @ret;
  95.         open(ALERT, $logfile) or die "open $logfile error: $!";
  96.         while(<ALERT>)
  97.         {
  98.                 if(/^$start/)
  99.                 {
  100.                         $flag = 1;
  101.                         $last_time = $_;
  102.                 }
  103.                 if($flag && /ORA-|Err/)
  104.                 {
  105.                         push @ret, "$last_time";
  106.                $last_time = "";
  107.                         push @ret, $_;
  108.                 }
  109.         }
  110.         close ALERT;
  111.      @ret;
  112. }

  113. sub check_logfile
  114. {
  115.         my $logfile = shift;
  116.      my $start = shift;
  117.      my $parts = shift;
  118.      my $flag = 0;
  119.      my @ret;
  120.      my $tmp_line;
  121.      open(LOGFILE, $logfile) or die "open $logfile error: $!";
  122.      while(<LOGFILE>)
  123.      {
  124.           $tmp_line = $_;
  125.           if(/^$start/)
  126.                 {
  127.                         $flag = 1;
  128.                         $last_time = $_;
  129.                 }
  130.           if( $flag && $tmp_line =~ /$parts/)
  131.           {
  132.                push @ret, $_;
  133.           }
  134.      }
  135.      close LOGFILE;
  136.      @ret;
  137. }

  138. my $r = \*Reader;
  139. my $w = \*Writer;

  140. my @ret;
  141. my $pid = &conn($r, $w, "/ as sysdba");

  142. print "logfiles:\n";
  143. @ret = &exec_sql($r, $w, "select member from v\$logfile order by 1");
  144. foreach (@ret)
  145. {
  146.      print $_."\n";
  147. }


  148. $sid = &get_param($r, $w, "instance_name");
  149. print "sid = $sid\n";

  150. $date = &exec_sql_single_row($r, $w, "select sysdate from dual;");
  151. print "date = $date\n";

  152. $str = &date_format($r, $w, "2014-1-1 23:11:44", "yyyy-mm-dd hh24:mi:ss", "dy mon dd hh24:mi");
  153. print "$str\n";

  154. $str = &get_db_time($r, $w, "yyyymmdd", -100);
  155. print "$str\n";

  156. my $timeStr1 = strftime "%Y-%m-%d %H:%M:%S", localtime;
  157. print $timeStr1."\n";

  158. print "alert time ".&get_alert_time(-22)."\n";

  159. print "check alert <".&get_alert($r, $w).">\n";
  160. @errs = &check_alert($r, $w, 22);
  161. foreach(@errs)
  162. {
  163.       print ;
  164. }

  165. @errs = &check_logfile(&get_alert($r, $w), &get_alert_time(-22), "^ORA-|Err");
  166. foreach(@errs)
  167. {
  168.         print ;
  169. }

  170. close Reader;
  171. close Writer;
  172. waitpid $pid, 0;











来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26239116/viewspace-2125606/,如需转载,请注明出处,否则将追究法律责任。

上一篇: perl文件操作
下一篇: perl数据操作
请登录后发表评论 登录
全部评论

注册时间:2012-11-12

  • 博文量
    94
  • 访问量
    309770