[Perl] SQL 스크립트에서 대상 정보 추출

SQL 스크립트에서 대상 정보 추출

SQL

CREATE TABLE AT_JN
(
        SU_NO      NUMBER(28) NULL ,
        BL_NO      NUMBER(28) NULL ,
        CB_CD      CHAR(1) NULL ,
        RE_CD      CHAR(5) NULL
);

Perl 

위와 같은 SQL 문에서, 테이블명, 컬럼명, 데이터 타입을 추출하기 위해 펄 코드를 작성하였습니다.

use strict;
use warnings;
 
 
my $log_file = "F:\\erd_table_column.txt";
open LOG_FILE, $log_file or die $!;
 
my $new_file = $log_file . "result";
open(my $result_file, '+>', $new_file) or die "Could not open file '$new_file' $!";
 
my $tableName = "";
my $match = "";
my $columnName = "";
my $dataType = "";
my @tableList;
 
 
while (my $line = <LOG_FILE>){
        ## is black
        $match = $line=~/^\s*$/;
        if ($match){
                next;
        }
 
        ## is ()
        $match = $line=~/^[()]\s*$/;
        if ($match){
                next;
        }
 
        ## is TABLE
        $match = $line=~/^CREATE TABLE (\w+)\s*$/;  
        if($match){
                $tableName = $1;
 
                push(@tableList,$tableName);
                next;       
        }
 
        ## is column
        $match = $line=~/\s+(\w+)\s+(\w+(\(.*?\))?) .*$/;     
        if ($match){
                $columnName = $1;
                $dataType = $2;   
 
                ##print $columnName , $dataType , "\n";
 
                my $data =  $tableName."\t".$columnName."\t".$dataType."\n";
 
                print $result_file $data;
                print $data;
        }
 
        ##print $line;
 
}
 
close(LOG_FILE);
close($result_file);
 
print "##END"."\n";
print join'\',\'',@tableList;