下面这个插入过程有没有可读性?要知道每一行+号前面的内容都是从现成的EXCEL中直接粘贴过来的,工作量很小。
pu_insert(fhd,[ //写发货单到数据库中
Fid integer 工厂代号 + factid
FHDCode Varchar 20 单据编号 + cxbuttonedit1.text
OrderNo Varchar 20 必填 定单编号 + cxtextedit3.text
FHDDate datetime 必填 发货日期 + pu_today
Remark Varchar 200 备注 + cxtextedit6.text
car Varchar 10 车队代号 + cxtextedit1.text
receiverman Varchar 10 收货人 + cxtextedit5.text
DeliverTo Varchar 80 交货地点 + cxtextedit2.text
]);
===========pu_insert过程的delphi源码如下====================
procedure pu_insert(tablename:string;sarr:array of string);
var rets,s,s1,s2:string;i,j,k,m,l:integer;c:char;
begin
rets:=(;l:=high(sarr);
for i:=0 to l do
begin
s:=sarr[i];k:=0; s1:=;
m:=length(s);
for j:=0 to m do
begin
if s[j]=#9 then inc(k) else
begin
if k=1 then s1:=s1+s[j];
end;
end;
if i=l then rets:=rets+s1+) values( else rets:=rets+s1+,;
end; //以上取完了所有键名
for i:=0 to l do
begin
s:=sarr[i];k:=0; s1:=;s2:=;
m:=length(s);
for j:=0 to m do
begin
if s[j]=#9 then inc(k) else
begin
if k=2 then s1:=s1+s[j];
if k=11 then s2:=s2+s[j];
end;
end;
c:=upcase(s1[1]);
if i=l then begin
if (c=D) and (s2=) then rets:=rets+ null) else //日期为空时
if (c=F) or (c=I) then rets:=rets+s2+) else //数值类型
rets:=rets+#39+s2+#39+) ; //#39是MSSQL字串分隔符
end
else
begin
if (c=D) and (s2=) then rets:=rets+ null, else
if (c=F) or (c=I) then rets:=rets+s2+, else rets:=rets+#39+s2+#39+,;
end;
end;
if debug then tell(insert into +tablename+ +rets);
pu_exec(insert into +tablename+ +rets);
end;
我还编了另一个过程pu_update也类似,只是多了一个条件参数,就不介绍了。
因为这种方法在运行时要解释执行,比较慢,正式发布前,我会用另一个工具对源代码进行翻译成真正的SQL,这个工具软件的核心源码摘录如下：
function doinsert2(ss:string):string;
var l:tstringlist;i,j:integer;s:string;st:string;hav:boolean;
ch:string;label next1,next2,next3;
begin//
try l:=tstringlist.create;
s:=;
for i:=1 to length(ss) do//分行,第一行专用
begin
if (ss[i]<>#13) and (ss[i]<>#10) then s:=s+ss[i];
if ss[i]=#13 then begin l.Add(s);s:= end;
end;
for i:=1 to l.count-1 do//清除第一个号前的所有字符
begin
if l[i][1]=/ then goto next3;
hav:=false;
s:=;for j:=1 to length(l[i]) do
begin
if l[i][j]=#39 then hav:=true;
if hav then s:=s+l[i][j];
end;
l[i]:=s;
next3:
end;
st:=///insert#13#10+
pu_exec(#39insert into +myfind(ss,12,#39)+ (;
for i:=1 to l.Count-1 do
begin
if l[i][1]=/ then goto next1;
if (i<>l.count-1) and ((i mod 8)=0) then st:=st+#39+#13#10#39;
if i<>l.count-1 then st:=st+mytab(l[i],1)+,
else st:=st+mytab(l[i],1)+) values(#39;
next1:
end;
for i:=1 to l.Count-1 do
begin
if l[i][1]=/ then goto next2;
st:=st+#13#10;
if mytab(l[i],2)[1] in [F,I,f,i] then ch:= else ch:=#39+;
if i<>l.count-1 then st:=st+++ch+mytab(l[i],12)+++ch+#39,#39
else st:=st+++ch+mytab(l[i],12)+++ch+#39)#39);
next2:
end;
result:=st;
finally
l.Free;
end;
end;
function doupdate(ss:string):string;
var l:tstringlist;i,j:integer;s:string;st:string;hav:boolean;
ch:string;label next1,next2,next3;
begin//
try l:=tstringlist.create;
s:=;
for i:=1 to length(ss) do//分行,第一行专用
begin
if (ss[i]<>#13) and (ss[i]<>#10) then s:=s+ss[i];
if ss[i]=#13 then begin l.Add(s);s:= end;
end;
for i:=1 to l.count-1 do//清除第一个号前的所有字符
begin
if l[i][1]=/ then goto next3;
hav:=false;
s:=;for j:=1 to length(l[i]) do
begin
if l[i][j]=#39 then hav:=true;
if hav then s:=s+l[i][j];
end;
l[i]:=s;
next3:
end;
st:=///update#13#10+
pu_exec(#39update +myfind(ss,12,#39)+ set #39;
for i:=1 to l.Count-1 do
begin
if l[i][1]=/ then goto next1;
st:=st+#13#10+#39;
if mytab(l[i],2)[1] in [F,I,f,i] then ch:= else ch:=#39+;
st:=st+mytab(l[i],1)+=#39;
if i<>l.count-1 then st:=st+++ch+mytab(l[i],12)+++ch+#39,#39
else st:=st+++ch+mytab(l[i],12)+++ch;
next1:
end;
i:=pos(,,l[0]);
st:=st+#39 where #39++myfind(l[0],i+1,,)+);
result:=st;
finally
l.Free;
end;
end;
// end of doupdate
function doinsert(ss:string):string;
var st,s,sod,snew:string;i,i1,i2,i3,i4,l:integer;hav:boolean;
begin//
st:=ss;
//开始qkinsert
repeat
i1:=pos(pu_insert(#39,st); if i1<=0 then break;
sod:=;
for i:=i1 to length(st) do
begin
sod:=sod+st[i];
if (st[i]=)) and (st[i-1]=]) and ((st[i+1]=;) or (st[i-2]=#10) or (st[i-2]=#13)) then break;
end;
snew:=doinsert2(sod);
st:=stringreplace(st,sod,snew,[rfReplaceAll]);
until 1>2;
//开始qkupdate
repeat
i1:=pos(pu_update(#39,st); if i1<=0 then break;
sod:=;
for i:=i1 to length(st) do
begin
sod:=sod+st[i];
if (st[i]=)) and (st[i-1]=]) and ((st[i+1]=;) or (st[i-2]=#10) or (st[i-2]=#13)) then break;
end;
snew:=doupdate(sod);
st:=stringreplace(st,sod,snew,[rfReplaceAll]);
until 1>2;
result:=st;
end;
procedure TForm1.Button11Click(Sender: TObject);label lb1;
var
sr: TSearchRec;
i1,FileAttrs,i: Integer;
t,f:file;
a:array[1..1000000]of char;s1,fff:string;
st:string;stin:string;
begin
if open1.Execute=false then exit;
s1:=open1.FileName;
memo2.text:=; FileAttrs := faAnyFile;
s1:=extractfilepath(s1);//showmessage(s1);exit;
if FindFirst(s1+*.pas,FileAttrs, sr) = 0 then
repeat
if sr.attr=fareadonly then begin memo2.text:=memo2.text+操作失败:;goto lb1 end;
if sr.attr=faVolumeID then begin memo2.text:=memo2.text+操作失败:; goto lb1 end;
if sr.attr=fadirectory then begin memo2.text:=memo2.text+操作失败:; goto lb1 end;
assignfile(t,s1+sr.Name);
reset(t,1);
blockread(t,a,1000000,i1);
closefile(t);
if i1>=1000000 then begin memo2.text:=memo2.text+文件太大,操作失败;goto lb1 end;
if i1>0 then
try
stin:=;
for i:=1 to i1 do stin:=stin+a[i];
if deb=10 then showmessage(in +stin);
st:=doinsert(stin);
if deb=10 then showmessage(out +st);
assignfile(f,s1+sr.Name);
rewrite(f,1);
blockwrite(f,st[1],length(st));
closefile(f);
except
memo2.Text:=memo2.text+打开失败:
end;
lb1: memo2.Text:=memo2.text+sr.name+#13#10;
application.ProcessMessages;
until FindNext(sr) <> 0;
end;
我是这样写复杂的查询语句的,如我编了一个查询当前发库的窗口,源程序主体(下例中的前16行)也是从EXCEL排好版粘过来,
注意这个示例中不仅生成了SQL，而且还设定了dbgrid1的各字段的宽度，及字段的中文名。也就是说它的数据显示随源程序而变。
t.s_add(1,s,,a.trnno,发货单号,90,,,,);
t.s_add(1,s,,a.orderno,订单号,90,,,,);
t.s_add(1,s,,c.branchcode,分公司,61,,,,);
t.s_add(1,s,,month(a.times),月份,60,,,,);
t.s_add(1,s,,a.times,发货日期,75,,,,);
t.s_add(1,s,,upper(b.modleserial),系列,60,,,,);
t.s_add(1,s,,a.k_modle,成品型号,100,,,,);
t.s_add(1,s,,b.modlesm,成品说明,100,,,,);
t.s_add(1,s,,(-a.qty),发货数量,75,,,,);
t.s_add(1,s,,a.n_ccj,标准出厂价,85,,,,);
t.s_add(1,s,,(-a.qty * a.n_ccj),出厂价总额,130,,,,);
t.s_add(1,s,,b.factoryprice,当前出厂价,85,,,,);
t.s_add(1,s,,(-a.qty * b.factoryprice),当前价总额,130,,,,);
t.s_add(1,s,,a.realccj,订单出厂价,85,,,,);
t.s_add(1,s,,(-a.qty * a.realccj),订单价总额,130,,,,);
t.s_add(1,s,,d.remark,备注,150,,,,);
t.s_add(1,f,,chg_stkcrd a,modle b,orders c,fhd d,,0,,,,);
t.s_add(1,w,,,,0,,,,a.k_modle=b.modle and a.k_fid=+_factid+ and a.trntype=#39发货#39
+ and a.orderno=c.orderno and a.trnno=d.fhdcode);
t.s_add(1,w,cxbuttonedit2,a.k_modle,,0,=,#39,#39,);
t.s_add(1,w,cxbuttonedit1,b.modlesm,,0,like,#39%,%#39,);
t.s_add(1,w,cxbuttonedit7,b.modleserial,,0,=,#39,#39,);
t.s_add(1,w,cxtextedit5,(-a.qty),,0,>=,,,);
t.s_add(1,w,cxtextedit4,(-a.qty),,0,<=,,,);
t.s_add(1,w,cxdateedit1,a.times,,0,>=,#39,#39,);
t.s_add(1,w,cxdateedit2,a.times,,0,<=,#39,c59+#39,);
t.s_add(1,w,cxbuttonedit3,a.trnno,,0,=,#39,#39,);
t.s_add(1,w,cxbuttonedit5,a.orderno,,0,=,#39,#39,);
t.s_add(1,w,cxbuttonedit6,c.branchcode,,0,=,#39,#39,);
pu_cdsql(q1,t.s_getsql(1)); //执行SQL并放在cd1这个内存表中
t.S_GridWidth(1,dbgrid1); //设dbgrid1各个字段的宽度
其中T是一个专用于生成SQL的对象(源代码较长，略过)，其运行画面及产生的SQL语句见此blog后附的图片
总之我这种方法写SQL,非常快，而且维护方便，编一个查询窗口总共不到50行代码就完事了。
