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

snippet.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
);

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

snippet.perl

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;