从DOS窗口操作数据库程序范例
返回
make_ptable1.pl(生成一个零件表)
<![CDATA[
use strict;
use DBI;
my(%t,$n,@fld);
# 零件表名称输入
print "Please input parts table name(Enginee.NO)=";
chop($t{input}=<STDIN>);
$t{inputf} = sprintf("%06d",$t{input});
$t{table1} = 'a' . $t{inputf};
# 打开数据库
$t{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$t{dbh} = DBI->connect($t{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$t{dbh}->do("SET NAMES utf8");
if(!$t{dbh}){
print "SQL read ERROR!\n";
exit;
}
# 先删除旧表格
$t{sql} = 'DROP TABLE IF EXISTS ' . $t{table1} . ';';
$t{dbh}->do($t{sql});
# 生成新表格
$t{sql} = 'CREATE TABLE ' . $t{table1};
$t{sql} .= ' (';
$t{sql} .= 'id INT AUTO_INCREMENT,';
$t{sql} .= 'name TEXT,';
$t{sql} .= 'code VARCHAR(100),';
$t{sql} .= 'group_id INT,';
$t{sql} .= 'dwg_id INT,';
$t{sql} .= 'Nuid INT,';
$t{sql} .= 'weight INT,';
$t{sql} .= 'price1 TEXT,';
$t{sql} .= 'time1 TEXT,';
$t{sql} .= 'money1 TEXT,';
$t{sql} .= 'maker_id TEXT,';
$t{sql} .= 'price2 TEXT,';
$t{sql} .= 'time2 TEXT,';
$t{sql} .= 'money2 TEXT,';
$t{sql} .= 'owner_id TEXT,';
$t{sql} .= 'memo TEXT,';
$t{sql} .= 'PRIMARY KEY (id));';
$t{dbh}->do($t{sql});
# 显示结果,这部分还要优化
$t{sth} = $t{dbh}->prepare ("SHOW columns FROM $t{table1}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array )
{
print "@rec\n";
}
$t{sth}->finish;
$t{dbh}->disconnect;
]]>
make_ptables.pl(批量生成数据库表格)
<![CDATA[
use strict;
use DBI;
my(%t,$n,@fld);
open(IN,"main_type1_tmp.txt") or die "Can't open the file main_type1_tmp.txt.\n";
while(<IN>){
chop;
@fld = split(/==>/);
next unless $fld[0];
$t{NO5} = sprintf("%06d",$fld[0]);
push(@{ $t{ID} },$t{NO5});
}
close(IN);
# 连接数据库
$t{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$t{dbh} = DBI->connect($t{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$t{dbh}->do("SET NAMES utf8");
if(!$t{dbh}){
print "SQL read ERROR!\n";
exit;
}
for $n ( 0 .. $#{ $t{ID} } ) {
$t{NO5} = $t{ID}[$n];
# テーブル1を作成する
$t{table1} = 'a' . $t{NO5};
$t{sql} = 'DROP TABLE IF EXISTS ' . $t{table1} . ';';
$t{dbh}->do($t{sql});
$t{sql} = 'CREATE TABLE ' . $t{table1};
$t{sql} .= ' (';
$t{sql} .= 'id INT AUTO_INCREMENT,';
$t{sql} .= 'name TEXT,';
$t{sql} .= 'code VARCHAR(100),';
$t{sql} .= 'group_id INT,';
$t{sql} .= 'dwg_id INT,';
$t{sql} .= 'Nuid INT,';
$t{sql} .= 'weight INT,';
$t{sql} .= 'price1 TEXT,';
$t{sql} .= 'time1 TEXT,';
$t{sql} .= 'money1 TEXT,';
$t{sql} .= 'maker_id TEXT,';
$t{sql} .= 'price2 TEXT,';
$t{sql} .= 'time2 TEXT,';
$t{sql} .= 'money2 TEXT,';
$t{sql} .= 'owner_id TEXT,';
$t{sql} .= 'memo TEXT,';
$t{sql} .= 'PRIMARY KEY (id));';
$t{dbh}->do($t{sql});
print "The tables $t{table1} is created.\n";
}
$t{dbh}->disconnect;
]]>
make_tables.pl
<![CDATA[
my (%t,$n,@fld,@list);
# 读指定文件
open(IN,"../txt/tables.txt") or die "Can't open the file tables.txt.\n";
while(<IN>) {
@fld = split;
last if (/^END/);
if ( /^TABLE/ ) {
$t{table} = $fld[1];
} elsif ( /^LIST/ ) {
chop;
@fld = split(/,/);
$fld[1] =~ s/\s*//;
$fld[2] =~ s/\s*//;
$fld[3] =~ s/\s*//;
push(@{ $t{name} },$fld[1]);
push(@{ $t{type} },$fld[2]);
push(@{ $t{example} },$fld[3]);
}
}
close(IN);
$t{sqlfile} = $t{table} . '.sql';
open(OUT,">../sql/$t{sqlfile}");
print OUT 'DROP TABLE IF EXISTS ';
print OUT $t{table},';',"\n";
print OUT 'CREATE TABLE ';
print OUT $t{table},"\n";
print OUT '(',"\n";
print OUT ' id INT AUTO_INCREMENT,',"\n";
for $n ( 1 .. $#{ $t{name} } ) {
$t{name1} = $t{name}[$n];
$t{type1} = $t{type}[$n];
printf OUT ("\t%-16s ",$t{name1});
print OUT $t{type1},",\n";
}
print OUT ' PRIMARY KEY (id)',"\n";
print OUT ');',"\n\n";
print OUT 'INSERT INTO ';
print OUT $t{table};
$t{name1} = ' (';
for $n ( 1 .. $#{ $t{name} } ) {
if ( $#{ $t{name} } == $n ) {
$t{name1} = $t{name1} . $t{name}[$n];
} else {
$t{name1} = $t{name1} . $t{name}[$n] . ',';
}
}
print OUT $t{name1},') VALUES(';
$t{example1} = '"';
for $n ( 1 .. $#{ $t{example} } ) {
if ( $#{ $t{example} } == $n ) {
$t{example1} = $t{example1} . $t{example}[$n] . '"';
} else {
$t{example1} = $t{example1} . $t{example}[$n] . '","';
}
}
print OUT $t{example1},');';
print OUT "\n";
close(OUT);
print "The output file is ../sql/$t{sqlfile}.\n";
__END__;
tables.txt的设定例
TABLE enq1 ==> From Owner
C name type example
LIST, id, INT, 1
LIST, time, date, 2008-01-20
LIST, ourref, INT, 80105001
LIST, owner, INT, 1
LIST, ownerno, varchar(100), xyz
LIST, hullnoid, INT, 34
LIST, type1id, text, 35==42
LIST, partsid, text, 1=3==2
LIST, QTY, text, 12=10==20
LIST, memo, text, A test memo
]]>
显示columns和取出数据
<![CDATA[
$t{sth} = $t{dbh}->prepare ("SHOW columns FROM makers");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array )
{
print "@rec\n";
}
$t{sth}->finish;
open(OUT,">makers_tmp.txt");
$t{sth} = $t{dbh}->prepare ("SELECT * FROM makers");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array )
{
$t{line} = join('===',@rec);
print OUT "$t{line}\n";
}
$t{sth}->finish;
]]>
返回