检查PDM生成的SQL与Oracle数据库中字段是否一致

#!/usr/bin/perl -w

# SCRIPT: chk_db.pl

# AUTHOR: Ray001

# DATE: 2010/03/28

# REV: 1.00

# For Production

#

# PURPOSE:

# 检查PDM生成的SQL与数据库是否匹配


use strict;
use DBI;
my $user = "CREDIT2"; # 数据库用户名

my $password = "PASS"; # 数据库用户密码

my $sid = "CMIS"; # ORACLE_SID实例名

my $lines = undef; # 临时变量,保存一个表的所有列定义

my $col_tmp = undef; # 临时变量,保存一个列名

my @array1 = (); # 存放从sql文件中提取的列名

my @array2 = (); # 存放从Oracle数据库中提取的列名

my $TB_NAME = undef; # 当前处理的表名


# 连接到数据库

my $dbh = DBI->connect( "DBI:Oracle:$sid", $user, $password )
  || die "cannot connect to Oracle:$DBI::errstr\n";

# prepare查询数据库表的SQL

my $sth =
  $dbh->prepare("SELECT COLUMN_NAME FROM USER_TAB_COLUMNS where table_name=?");

# 如果打开配置文件失败, 则报错退出

open PDM_FILE, " or die "Can't open FILE! ($!) ";

# 读取整个文件

$/ = undef;
$_ = <PDM_FILE>;

# 如果打开配置文件失败, 则报错退出

open CFG_FILE, " or die "Can't open FILE! ($!) ";

$/ = "\n";

# 循环读取表名,逐个处理

while ( chomp( $TB_NAME = <CFG_FILE> ) ) {
    @array1 = ();
    @array2 = ();
    print "Checking table $TB_NAME ...\n";

    # 获取表的列定义

    if (/CREATE TABLE\s+$TB_NAME\s+\(\r\n(.*?)\n\)/s) {
        $lines = $1;
    }
    else {
        print "Error, $TB_NAME No matched!!!!\n";
    }

    # 把$1放进array1

    @array1 = split /\n/, $lines;

    # 截取表名

    foreach my $column (@array1) {
        $column =~ s/\s+(\w+)\s+.*/$1/;
    }

    # 以表名为条件, 查询数据库表

    $sth->execute($TB_NAME);

    # 把列名放入array2

    while ( $col_tmp = $sth->fetchrow_array() ) {
        push( @array2, $col_tmp );
    }

    # 按顺序逐个比较每个列名是否相同

    show_arrays( $TB_NAME, \@array1, \@array2 )
      if ( join( " ", @array1 ) ne join( " ", @array2 ) );

}

$sth->finish();
$dbh->disconnect();

# 显示表名,文件中的列,表中的列

sub show_arrays {
    my ( $TB_NAME, $array1_ref, $array2_ref ) = @_;
    print "表 $TB_NAME 列不相等!!!\n";
    my $max_idx = @$array1_ref > @$array2_ref ? @$array1_ref : @$array2_ref;
    for ( my $i = 0 ; $i <= $max_idx ; $i++ ) {
        printf "%20s %20s\n", $$array1_ref[$i] ? $$array1_ref[$i] : " ",
          $$array2_ref[$i] ? $$array2_ref[$i] : " ";
    }

}



xdhs.sql


/*==============================================================*/
/* Table: DE_PROC_LOG */
/*==============================================================*/
CREATE TABLE DE_PROC_LOG (
   SEQ_NO VARCHAR2(4) NOT NULL,
   PRCS_DT VARCHAR2(10) NOT NULL,
   JOB_NAM VARCHAR2(50),
   JOB_STS VARCHAR2(10) NOT NULL,
   ERR_MSG VARCHAR2(200),
   JOB_TYP VARCHAR2(5),
   MAC_TIME VARCHAR2(30)
)
  PCTFREE 10
INITRANS 1
TABLESPACE TB_P01
MONITORING
  NOPARALLEL;

chk_db.cfg

DE_PROC_LOG