perl 生成SQL语句

如上图显示，在公司每当起一个新项目时，都需要分析一些常用字段，及字段类型、字段长度。那分析完后，要需要对数据库进行相应的建表操作了，当然现在有很多工具，比如：powerdesin、ERwin之类的建模工具能省任，那比如公司给提供了一个类似excel这类的数据，用手工来完成，这太费事了，所以才有这个想法。

使用Perl脚本，对excel进行读取操作，然后根据相应的格式，生成对应的SQL语句。

#!/usr/bin/perl -w
#use strict;
use Encode;
use Spreadsheet::ParseExcel::FmtUnicode;
use Spreadsheet::ParseExcel;
use POSIX qw(strftime);
use Sys::Hostname;
use Socket; 
 
my $parser = Spreadsheet::ParseExcel->new();
my $formatter = Spreadsheet::ParseExcel::FmtUnicode->new(Unicode_Map=>"cp936");#设置字符编码

open (DATA,">D:/14-perl/excel/create_table.sql"); 
my $workbook = $parser->Parse('D:/14-perl/excel/table_column.xls',$formatter);
my %hash_col;
my %hash_cre;
my %hash_comm;
my $rn=0;

   
for my $worksheet ( $workbook->worksheets() ) {
	my ( $row_min, $row_max ) = $worksheet->row_range();
    #my ( $col_min, $col_max ) = $worksheet->col_range();
   
   my $rn=$row_min+1;
for ($row_min .. $row_max) {
	
my $cell0 = $worksheet->get_cell( $rn, 0 );next unless $cell0; $a0=$cell0->value();$a0=encode('utf8',decode('gbk',$a0));
my $cell1 = $worksheet->get_cell( $rn, 1 );next unless $cell1; $a1=$cell1->value();$a1=encode('utf8',decode('gbk',$a1));
my $cell2 = $worksheet->get_cell( $rn, 2 );next unless $cell2; $a2=$cell2->value();$a2=encode('utf8',decode('gbk',$a2));
my $cell3 = $worksheet->get_cell( $rn, 3 );next unless $cell3; $a3=$cell3->value();$a3=encode('utf8',decode('gbk',$a3));
my $cell4 = $worksheet->get_cell( $rn, 4 );next unless $cell4; $a4=$cell4->value();$a4=encode('utf8',decode('gbk',$a4));
my $cell5 = $worksheet->get_cell( $rn, 5 );next unless $cell5; $a5=$cell5->value();$a5=encode('utf8',decode('gbk',$a5));
my $cell6 = $worksheet->get_cell( $rn, 6 );next unless $cell6; $a6=$cell6->value();$a6=encode('utf8',decode('gbk',$a6));
my $cell7 = $worksheet->get_cell( $rn, 7 );next unless $cell7; $a7=$cell7->value();$a7=encode('utf8',decode('gbk',$a7));
my $cell8 = $worksheet->get_cell( $rn, 8 );next unless $cell8; $a8=$cell8->value();$a8=encode('utf8',decode('gbk',$a8));


 $hash_col{"\"$a0\".$a2"} .="COMMENT ON COLUMN \"$a0\".$a2.$a4 is '$a3';\n";
 $hash_comm{"\"$a0\".$a2"} ="$a1";
  
if ($a5 eq 'Y') {$a5='PRIMARY KEY'; $a5flag='Y'} else {$a5='';$a5flag='N'}
if ($a7 eq 'N') {if ($a5flag eq 'Y') {$a7='';} else{$a7='NOT NULL';}} else {$a7='';}
$hash_cre{"\"$a0\".$a2"} .="    $a4 $a6 $a5 $a7,";

	$rn +=1;
}

	}  
	


my $datetime=strftime("%Y-%m-%d %H:%M:%S\n", localtime(time));
my $name; 
my $aliases; 
my $type; 
my $len; 
my @thisaddr; 
my $host = hostname(); 
($name,$aliases,$type,$len,@thisaddr)=gethostbyname($host);
my $str=inet_ntoa($thisaddr[2]);
       
print DATA "spool SDATA_DDL.log\n";
print DATA "--***********************************************************************\n";
print DATA "-- XXX数据库建表语句\n";
print DATA "-- author: $str\n";
print DATA "-- date: $datetime";
print DATA "--***********************************************************************\n";

 foreach $key (sort keys  %hash_cre) {
	 	   $_= "drop table $key;\n create table $key (\n".$hash_cre{$key}.");\n\n"."COMMENT ON TABLE $key is '".$hash_comm{$key}."';\n".$hash_col{$key}."\n";
	 	    ##$hash_cre{$key};
	 	  s/\,\);/);/g;
	 	  s/\, /,\n/g;
	 	      print DATA  "$_\n";
	  }

 
print DATA "spool off\n";

=pod excel 模板
主题名称	表名称	表英文名	字段中文名	字段英文名	是否主键	字段类型	是否可空	是否视图
SDATA	预算权限项目机构关联表	BU_AUTHORITYPROJECTAGENCY	ID	ID	Y	NUMBER	N	N
SDATA	预算权限项目机构关联表	BU_AUTHORITYPROJECTAGENCY	预算权限ID	AUTHORITYID		NUMBER	N	N
SDATA	预算权限项目机构关联表	BU_AUTHORITYPROJECTAGENCY	预算项目ID	PROJECTID		NUMBER	N	N
=cut

如下是想要的结果：