1、申 请 I D：tsoap007
2、个人邮箱：297003572@qq.com
原创代码：我是delphi的忠实爱好者，习惯用delphi编写数据库类的程序。如下为我个人完成的一个小脚本，完成从oracle导入数据到sqlserver的小程序代码，请斑竹给予机会，多谢！
unit Unit4;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, DB, MemDS, DBAccess, ADODB, Uni, UniProvider,
OracleUniProvider, ODBCUniProvider, AdvantageUniProvider, MySQLUniProvider,
SQLServerUniProvider, Vcl.Imaging.jpeg, Vcl.ExtCtrls;
type
TForm4 = class(TForm)
Button1: TButton;
UniConnection1: TUniConnection;
UniQuery1: TUniQuery;
UniConnection2: TUniConnection;
UniQuery2: TUniQuery;
OracleUniProvider1: TOracleUniProvider;
AdvantageUniProvider1: TAdvantageUniProvider;
SQLServerUniProvider1: TSQLServerUniProvider;
Label1: TLabel;
Image1: TImage;
Image2: TImage;
Button2: TButton;
Button3: TButton;
Label2: TLabel;
Button4: TButton;
Button5: TButton;
Label3: TLabel;
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure Button3Click(Sender: TObject);
procedure Button4Click(Sender: TObject);
procedure Button5Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form4: TForm4;
implementation
{$R *.dfm}
procedure TForm4.Button1Click(Sender: TObject);
var
str2,constr,ID,USER_NAME,SEX,EDUCATION,DEGREE,MAJOR,CARD_ID,JOB_TITLE, MAIL_ADDR,Work_Unit,PHONE,LINK_TEL,EMAIL,MAJOR_TYPE,APPLY_EXPERT_TYPE:string;
i:integer;
strs:Tstrings;
begin
//连接sqlserver库
with UniQuery2 do
begin
Close ;
UniQuery2.SQL.Clear ;
Str2 := 'insert into expert(name,sex,EducationID,DEGREEID,MajorID,IDCard,TechnicalID,cAddr,WorkUnit,Mobile,cTel,EMAIL,B_MainMajorID,M_MainMajorID,M_OneMajorID,M_TwoMajorID,ExpertType,ExpertID ) ' +
' values(:P1,:P2,:P3,:P4,:P5,:P6,:P7,:P8,:P9,:P10,:P11,:P12,:P13,:P14,:P15,:P16,:P17,:P18)' ;
UniQuery2.SQL.Clear;
UniQuery2.SQL.Add(Str2) ;
end ;
//连接oracle库
with UniQuery1 do
begin
Close ;
UniQuery1.sql.clear ;
UniQuery1.SQL.Add('select USER_NAME,SEX,EDUCATION,DEGREE,MAJOR,CARD_ID,JOB_TITLE,MAIL_ADDR,Work_Unit,PHONE,LINK_TEL,EMAIL,APPLY_EXPERT_TYPE,MAJOR_TYPE,APPLY_EXPERT_TYPE,ID from expert_info_tmp');
UniQuery1.Open;
First ;
while not Eof do
begin
for i := 0 to UniQuery1.FieldCount-1 do
begin
//ORACLE库字段
USER_NAME := UniQuery1.FieldByName('USER_NAME').AsString ;
//BIRTH_DATE := UniQuery1.FieldByName('BIRTH_DATE').AsString ;
SEX := UniQuery1.FieldByName('SEX').AsString ;
EDUCATION := UniQuery1.FieldByName('EDUCATION').AsString ;
DEGREE := UniQuery1.FieldByName('DEGREE').AsString ;
MAJOR := UniQuery1.FieldByName('MAJOR').AsString ;
CARD_ID := UniQuery1.FieldByName('CARD_ID').AsString ;
JOB_TITLE := UniQuery1.FieldByName('JOB_TITLE').AsString ;
MAIL_ADDR := UniQuery1.FieldByName('MAIL_ADDR').AsString ;
Work_Unit := UniQuery1.FieldByName('Work_Unit').AsString ;
PHONE := UniQuery1.FieldByName('PHONE').AsString ;
LINK_TEL := UniQuery1.FieldByName('LINK_TEL').AsString ;
EMAIL := UniQuery1.FieldByName('EMAIL').AsString ;
APPLY_EXPERT_TYPE := UniQuery1.FieldByName('APPLY_EXPERT_TYPE').AsString ;
MAJOR_TYPE := UniQuery1.FieldByName('MAJOR_TYPE').AsString ;
APPLY_EXPERT_TYPE := UniQuery1.FieldByName('APPLY_EXPERT_TYPE').AsString ;
ID := UniQuery1.FieldByName('ID').AsString ;
end;
//sql库字段
UniQuery2.ParamByName('P1').Value := USER_NAME ;
//UniQuery2.ParamByName('P2').value :=formatdatetime('yyyy-mm-dd',strtodate(BIRTH_DATE));
//UniQuery2.ParamByName('P2').Value := BIRTH_DATE ;
UniQuery2.ParamByName('P2').Value := SEX ;
UniQuery2.ParamByName('P3').Value := EDUCATION ;
UniQuery2.ParamByName('P4').Value := DEGREE ;
UniQuery2.ParamByName('P5').Value := MAJOR ;
UniQuery2.ParamByName('P6').Value := CARD_ID ;
UniQuery2.ParamByName('P7').Value := JOB_TITLE ;
UniQuery2.ParamByName('P8').Value := MAIL_ADDR ;
UniQuery2.ParamByName('P9').Value := Work_Unit ;
UniQuery2.ParamByName('P10').Value := PHONE ;
UniQuery2.ParamByName('P11').Value := LINK_TEL ;
UniQuery2.ParamByName('P12').Value := EMAIL ;
UniQuery2.ParamByName('P13').Value := APPLY_EXPERT_TYPE ;
begin
constr:=MAJOR_TYPE;
begin
strs:= TStringList.Create;
strs.CommaText := constr;
for i := 0 to Strs.Count-1 do
UniQuery2.ParamByName('P14').Value := Strs[0] ;
UniQuery2.ParamByName('P15').Value := Strs[1] ;
UniQuery2.ParamByName('P16').Value := Strs[2] ;
end;
end;
UniQuery2.ParamByName('P17').Value := APPLY_EXPERT_TYPE ;
UniQuery2.ParamByName('P18').Value := copy(FieldByName('ID').AsString, 13,11 );
UniQuery2.ExecSQL;
next;
end;
end;
ShowMessage('导入成功');
end;
procedure TForm4.Button2Click(Sender: TObject);
begin
with UniQuery2 do
begin
Close ;
UniQuery2.SQL.Clear ;
UniQuery2.SQL.Add('delete from expert') ;
UniQuery2.ExecSQL;
end;
ShowMessage('清空完毕');
end;
procedure TForm4.Button3Click(Sender: TObject);
begin
with UniQuery2 do
begin
Close ;
UniQuery2.SQL.Clear ;
UniQuery2.SQL.Add('delete from expert where mobile='''' ') ;
UniQuery2.ExecSQL;
end;
ShowMessage('筛查完毕');
end;
procedure TForm4.Button4Click(Sender: TObject);
begin
with UniQuery2 do
begin
Close ;
UniQuery2.SQL.Clear ;
UniQuery2.SQL.Add('update expert set sex=''男''where sex=''1''') ;
UniQuery2.SQL.Add('update expert set sex=''女''where sex=''2''') ;
UniQuery2.SQL.Add('update expert set B_MainMajorID=''食品安全专家''where B_MainMajorID=''31''') ;
UniQuery2.SQL.Add('update expert set B_MainMajorID=''医疗器械评价专家''where B_MainMajorID=''21''') ;
UniQuery2.SQL.Add('update expert set B_MainMajorID=''医疗器械安全性评价专家''where B_MainMajorID=''22''') ;
UniQuery2.SQL.Add('update expert set B_MainMajorID=''药品注册现场检查及评审专家''where B_MainMajorID=''11''') ;
UniQuery2.SQL.Add('update expert set B_MainMajorID=''保健食品评审专家''where B_MainMajorID=''41''') ;
UniQuery2.SQL.Add('update expert set B_MainMajorID=''化妆品评审专家''where B_MainMajorID=''51''') ;
UniQuery2.SQL.Add('update expert set B_MainMajorID=''保健用品评审专家''where B_MainMajorID=''42''') ;
UniQuery2.SQL.Add('update expert set B_MainMajorID=''药品安全性评价专家''where B_MainMajorID=''12''') ;
UniQuery2.SQL.Add('update expert set B_MainMajorID=''化妆品安全性评价专家''where B_MainMajorID=''52''') ;
//UniQuery2.SQL.Add('update expert set ExpertType=''资深专家''') ;
UniQuery2.SQL.Add('update expert set EducationID=''大专''where EducationID=''4''') ;
UniQuery2.SQL.Add('update expert set EducationID=''本科''where EducationID=''5''') ;
UniQuery2.SQL.Add('update expert set EducationID=''研究生''where EducationID=''6''') ;
UniQuery2.ExecSQL;
end;
ShowMessage('数据字段一致完成');
end;
procedure TForm4.Button5Click(Sender: TObject);
begin
close;
end ;
end.
|
吾爱游客
发表于 2015-6-15 16:10
