oracle存储过程在sqlplus中执行正确,但是用SQL Explore看不到参数信息。
在delphi中调用时报错为:
gernal SQL error
ora-06550:line1 column7:
pls-00201:identifier 'DownUpdate' must be declared
ora-06550:line1 column7:
Pl/SQL:statement ignore存储过程为:
Create Procedure DownUpdate
( pLOGNPRM IN NUMBER,
pLOGNTBN IN VARCHAR2,
pLOGNSTA IN VARCHAR2,
pLOGNCTI IN VARCHAR2,
pLOGNCCN IN NUMBER,
pLOGNDCN IN NUMBER)
IS
rowcount number;
BEGIN SELECT count(*) into rowcount FROM TBLLOGN
WHERE (LOGNPRM=pLOGNPRM) AND (LOGNTBN=pLOGNTBN); IF rowcount=0 THEN Insert into TBLLOGN
(LOGNPRM,LOGNTBN,LOGNSTA,LOGNCTI,LOGNCCN,LOGNDTI,LOGNDCN)
VALUES
(pLOGNPRM,pLOGNTBN,pLOGNSTA,pLOGNCTI,pLOGNCCN,sysdate,pLOGNDCN); ELSE
UPDATE TBLLOGN
SET LOGNSTA=pLOGNSTA,LOGNCTI=pLOGNCTI,LOGNCCN=pLOGNCCN,LOGNDTI=sysdate,LOGNDCN=pLOGNDCN
WHERE (LOGNPRM=pLOGNPRM) AND (LOGNTBN=pLOGNTBN); END IF;
commit;
END;
/delphi的程序为
var
intPrm:integer;
StrLocalTableName:string; P1,P2,P3,P4,P5,P6:Tparam;
begin
intPrm:=200303;
StrLocalTableName:='B3CIWCIM';
with frmCargo3Down.SpUpLocalLog do
begin
StoredProcName:='DownUpdate';
Params.clear;
P1:=TParam.Create(SpUpLocalLog.Params,ptInput);
P2:=TParam.Create(SpUpLocalLog.Params,ptInput);
P3:=TParam.Create(SpUpLocalLog.Params,ptInput);
P4:=TParam.Create(SpUpLocalLog.Params,ptInput);
P5:=TParam.Create(SpUpLocalLog.Params,ptInput);
P6:=TParam.Create(SpUpLocalLog.Params,ptInput); SpUpLocalLog.Params[0].name:='pLOGNPRM';
SpUpLocalLog.Params[1].name:='pLOGNTBN';
SpUpLocalLog.Params[2].name:='pLOGNSTA';
SpUpLocalLog.Params[3].name:='pLOGNCTI';
SpUpLocalLog.Params[4].name:='pLOGNCCN';
SpUpLocalLog.Params[5].name:='pLOGNDCN';
ParamByName('pLOGNPRM').asinteger:=intPrm;
ParamByName('pLOGNTBN').asstring:=StrLocalTableName;
ParamByName('pLOGNSTA').asstring:='W';
ParamByName('pLOGNCTI').asstring:='20031210171600';
ParamByName('pLOGNCCN').asinteger:=0;
ParamByName('pLOGNDCN').asinteger:=0;
prepare;
open;
//ExecProc;
P1.Free;
P2.Free;
P3.Free;
P4.Free;
P5.Free;
P6.Free;
end;
在delphi中调用时报错为:
gernal SQL error
ora-06550:line1 column7:
pls-00201:identifier 'DownUpdate' must be declared
ora-06550:line1 column7:
Pl/SQL:statement ignore存储过程为:
Create Procedure DownUpdate
( pLOGNPRM IN NUMBER,
pLOGNTBN IN VARCHAR2,
pLOGNSTA IN VARCHAR2,
pLOGNCTI IN VARCHAR2,
pLOGNCCN IN NUMBER,
pLOGNDCN IN NUMBER)
IS
rowcount number;
BEGIN SELECT count(*) into rowcount FROM TBLLOGN
WHERE (LOGNPRM=pLOGNPRM) AND (LOGNTBN=pLOGNTBN); IF rowcount=0 THEN Insert into TBLLOGN
(LOGNPRM,LOGNTBN,LOGNSTA,LOGNCTI,LOGNCCN,LOGNDTI,LOGNDCN)
VALUES
(pLOGNPRM,pLOGNTBN,pLOGNSTA,pLOGNCTI,pLOGNCCN,sysdate,pLOGNDCN); ELSE
UPDATE TBLLOGN
SET LOGNSTA=pLOGNSTA,LOGNCTI=pLOGNCTI,LOGNCCN=pLOGNCCN,LOGNDTI=sysdate,LOGNDCN=pLOGNDCN
WHERE (LOGNPRM=pLOGNPRM) AND (LOGNTBN=pLOGNTBN); END IF;
commit;
END;
/delphi的程序为
var
intPrm:integer;
StrLocalTableName:string; P1,P2,P3,P4,P5,P6:Tparam;
begin
intPrm:=200303;
StrLocalTableName:='B3CIWCIM';
with frmCargo3Down.SpUpLocalLog do
begin
StoredProcName:='DownUpdate';
Params.clear;
P1:=TParam.Create(SpUpLocalLog.Params,ptInput);
P2:=TParam.Create(SpUpLocalLog.Params,ptInput);
P3:=TParam.Create(SpUpLocalLog.Params,ptInput);
P4:=TParam.Create(SpUpLocalLog.Params,ptInput);
P5:=TParam.Create(SpUpLocalLog.Params,ptInput);
P6:=TParam.Create(SpUpLocalLog.Params,ptInput); SpUpLocalLog.Params[0].name:='pLOGNPRM';
SpUpLocalLog.Params[1].name:='pLOGNTBN';
SpUpLocalLog.Params[2].name:='pLOGNSTA';
SpUpLocalLog.Params[3].name:='pLOGNCTI';
SpUpLocalLog.Params[4].name:='pLOGNCCN';
SpUpLocalLog.Params[5].name:='pLOGNDCN';
ParamByName('pLOGNPRM').asinteger:=intPrm;
ParamByName('pLOGNTBN').asstring:=StrLocalTableName;
ParamByName('pLOGNSTA').asstring:='W';
ParamByName('pLOGNCTI').asstring:='20031210171600';
ParamByName('pLOGNCCN').asinteger:=0;
ParamByName('pLOGNDCN').asinteger:=0;
prepare;
open;
//ExecProc;
P1.Free;
P2.Free;
P3.Free;
P4.Free;
P5.Free;
P6.Free;
end;
以后,还要向大家学习!
我们马上要写一个关于用oracle的数据库的程序,到时候一起学哈!