如何快速动态给AdoQeury的SQL赋值？求最佳方法！
如题，有一条 120行的SQL语句，如何快速的传给AdoQeury的SQL ?
比如把
select bas_no mr_no,sex,ceil((admission_date_time-date_of_birth)/365) age,
to_char(date_of_birth,'YYYY-MM-DD') csny,marital_status
转换为：
s:=s+ 'select bas_no mr_no,sex,ceil((admission_date_time-date_of_birth)' +
'/365) age, to_char(date_of_birth,''YYYY-MM-DD'') csny,marital_status '
这种格式。
如果语句只有几行到没什么，要是上百行怎么快速、准确的处理？
首先排除loadformfile的办法，因为语句为了防止串改，是不能写在文本文件里的，求最佳办法！
//-------- zy_pat_master
select bas_no mr_no,sex,ceil((admission_date_time-date_of_birth)/365) age,
to_char(date_of_birth,'YYYY-MM-DD') csny,marital_status ,OCCUPATION_NAME,Nation,v.visit_id,v.charge_type,
to_char(v.admission_date_time,'YYYY-MM-DD') admission_date, dd1.dept_name,p.pat_condition_name ,dc.Diagnosis_code, n.Diagnosis_desc,
to_char(n.DIAGNOSIS_DATE,'YYYY-MM-DD') zdrq,to_char(v.Discharge_date_time,'YYYY-MM-DD') cyrq, dd2.dept_name , f.diagnosis_code,
f.diagnosis_desc,f.treat_result,
'' diagnosis_code1, '' diagnosis_desc1,''treat_result1,
'' diagnosis_code2, '' diagnosis_desc2,''treat_result2,
'' diagnosis_code3, '' diagnosis_desc3,''treat_result3,
'' diagnosis_code4, '' diagnosis_desc4,''treat_result4,
'' diagnosis_code5, '' diagnosis_desc5,''treat_result5,
'' diagnosis_code6, '' diagnosis_desc6,''treat_result6,
'' diagnosis_code7, '' diagnosis_desc7,''treat_result7,
'' yygrbm,'' yygrmc,'' zljg,'' blzd,'' blmc ,
operation_code,to_char(operating_date,'yyyy-mm-dd'),operation_desc ,HEAL,
'' operation_code2, '' operating_date2, '' operation_desc2 , '' HEAL2 ,
'' operation_code3, '' operating_date3, '' operation_desc3 , '' HEAL3 ,
'' operation_code4, '' operating_date4, '' operation_desc4 , '' HEAL4 ,
'' operation_code5, '' operating_date5, '' operation_desc5 , '' HEAL5 ,
EMER_TREAT_TIMES, ESC_EMER_TIMES , zs, chuangwei,huli,xiyao,zhongcheng,zhongcao,fangshe,huayan,shuyang,
shuxue,shoushu,jiancha,mazui,zhiliao,qita,
to_char(sysdate,'YYYY-MM-DD') tbrq
from pat_master_index m ,pat_visit v , OCCUPATION_DICT d ,diagnosis n , FINAL_CHIEF_DIAGNOSIS f ,dept_dict dd1 ,dept_dict dd2,
PAT_ADM_CONDITION_DICT p ,DIAGNOSTIC_CATEGORY dc ,OPERATION o ,
(
select patient_id ,visit_id,
sum(jiancha) jiancha,
sum(qita) qita,
sum(huayan) huayan,
sum(xiyao) xiyao,
sum(yinger) yinger,
sum(zhenliao) zhenliao,
sum(zhongyao) zhongyao,
sum(chuangwei) chuangwei,
sum(shuxue) shuxue,
sum(jiesheng) jiesheng,
sum(mazui) mazui,
sum(zhiliao) zhiliao,
sum(fangshe) fangshe,
sum(shoushu) shoushu,
sum(shuyang) shuyang,
sum(zhongcao) zhongcao,
sum(zhongcheng) zhongcheng,
sum(huli) huli,
sum(peichuang) peichuang
from
(
select med.patient_id,med.visit_id,
decode(fee_type,'检查',costs,0) jiancha,
decode(fee_type,'其他',costs,0) qita,
decode(fee_type,'化验',costs,0) huayan,
decode(fee_type,'西药',costs,0) xiyao,
decode(fee_type,'婴儿',costs,0) yinger,
decode(fee_type,'诊疗',costs,0) zhenliao,
decode(fee_type,'中药',costs,0) zhongyao,
decode(fee_type,'床位',costs,0) chuangwei,
decode(fee_type,'输血',costs,0) shuxue,
decode(fee_type,'接生',costs,0) jiesheng,
decode(fee_type,'麻醉',costs,0) mazui,
decode(fee_type,'治疗',costs,0) zhiliao,
decode(fee_type,'放射',costs,0) fangshe,
decode(fee_type,'手术',costs,0) shoushu,
decode(fee_type,'输氧',costs,0) shuyang,
decode(fee_type,'中草',costs,0) zhongcao,
decode(fee_type,'中成',costs,0) zhongcheng,
decode(fee_type,'护理',costs,0) huli,
decode(fee_type,'陪床',costs,0) peichuang
from medical_costs med
where (patient_id,visit_id) in
(select patient_id,visit_id from pat_visit where
discharge_date_time >= to_date('2012-06-01','YYYY-MM-DD')
and discharge_date_time < to_date('2012-07-01','YYYY-MM-DD'))
)
group by patient_id ,visit_id
) med2 ,
(
select patient_id,visit_id,sum(costs) zs from medical_costs
where (patient_id,visit_id) in
(select patient_id,visit_id from pat_visit where
discharge_date_time >= to_date('2012-06-01','YYYY-MM-DD')
and discharge_date_time < to_date('2012-07-01','YYYY-MM-DD'))
group by patient_id,visit_id
) med3
where m.patient_id=v.patient_id
and v.discharge_date_time >= to_date('2012-06-01','YYYY-MM-DD')
and v.discharge_date_time < to_date('2012-07-01','YYYY-MM-DD')
and bas_no is not null
and v.occupation=d.OCCUPATION_CODE(+)
and v.patient_id=f.patient_id
and v.visit_id=f.visit_id
and v.dept_admission_to=dd1.dept_code
and v.dept_discharge_from =dd2.dept_code
and v.pat_adm_condition=p.pat_condition_code
and v.patient_id=n.patient_id
and v.visit_id=n.visit_id
and n.diagnosis_type=2 and n.diagnosis_no=1
and v.patient_id=dc.patient_id
and v.visit_id=dc.visit_id
and dc.diagnosis_type=2 and dc.diagnosis_no=1
and v.patient_id=o.patient_id(+)
and v.visit_id=o.visit_id(+)
and (o.operation_no is null or operation_no<2)
and v.patient_id=med2.patient_id(+)
and v.visit_id=med2.visit_id(+)
and v.patient_id=med3.patient_id(+)
and v.visit_id=med3.visit_id(+)
;
[解决办法]
做成视图吧
[解决办法]
动态生成代码。。。
unit Unit1;interfaceuses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, StdCtrls;type TForm1 = class(TForm) Memo1: TMemo; Button1: TButton; procedure FormCreate(Sender: TObject); procedure Button1Click(Sender: TObject); private { Private declarations } public { Public declarations } end;var Form1: TForm1;implementation{$R *.dfm}uses Clipbrd;procedure TForm1.Button1Click(Sender: TObject);var S: string;begin S := StringReplace(Memo1.Text, '''', '''''', [rfReplaceAll]);//Memo1中放SQL语句 S := StringReplace(S, sLineBreak, ''' + sLineBreak +' + sLineBreak + ' ''', [rfReplaceAll]); S := 'S := ''' + S + ''';'; Clipboard.SetTextBuf(PChar(S)); ShowMessage('代码已复制到剪贴板！');end;procedure TForm1.FormCreate(Sender: TObject);begin Memo1.Clear; Memo1.ScrollBars := ssBoth;end;end.
[解决办法]
装个CnPack IDE 专家包（CnWizards）开发辅助工具吧，一个快捷键或就搞定
这里有下载：http://bbs.cnpack.org/forumdisplay.php?fid=8
，装好后，只要你选取一段，然后右键你会找到专家包给你提供的辅助操作-转为字符串