CREATE OR REPLACE PROCEDURE TR_ADD_Maintenance
@TerminalID int,
@FaultInfo varchar(50),
@FaultTime datetime,
@ReportEmployee varchar(20),
@StartRepairTime datetime,
@FinishRepairTime datetime,
@RepairStatus varchar(8),
@RepairResult varchar(20),
@RepairEmployee varchar(20)
AS
if((select count(*) from TR_Maintenance where @TerminalID = @TerminalID) > 0)
BEGIN
return -1
END
else
BEGIN
insert into TR_Maintenance(TerminalID, FaultInfo, FaultTime, ReportEmployee, StartRepairTime,FinishRepairTime,RepairStatus,RepairResult,RepairEmployee)
values(@TerminalID,@FaultInfo,@FaultTime,@ReportEmployee,@StartRepairTime,@FinishRepairTime,@RepairStatus,@RepairResult,@RepairEmployee)
return 1
ENDGO
@TerminalID int,
@FaultInfo varchar(50),
@FaultTime datetime,
@ReportEmployee varchar(20),
@StartRepairTime datetime,
@FinishRepairTime datetime,
@RepairStatus varchar(8),
@RepairResult varchar(20),
@RepairEmployee varchar(20)
AS
if((select count(*) from TR_Maintenance where @TerminalID = @TerminalID) > 0)
BEGIN
return -1
END
else
BEGIN
insert into TR_Maintenance(TerminalID, FaultInfo, FaultTime, ReportEmployee, StartRepairTime,FinishRepairTime,RepairStatus,RepairResult,RepairEmployee)
values(@TerminalID,@FaultInfo,@FaultTime,@ReportEmployee,@StartRepairTime,@FinishRepairTime,@RepairStatus,@RepairResult,@RepairEmployee)
return 1
ENDGO
-----------------------------------------------------------------
CREATE OR REPLACE PROCEDURE TR_ADD_Maintenance(
TerminalID NUMBER,
FaultInfo VARCHAR2(50),
FaultTime DATE,
ReportEmployee VARCHAR2(20),
StartRepairTime DATE,
FinishRepairTime DATE,
RepairStatus VARCHAR2(8),
RepairResult VARCHAR2(20),
RepairEmployee VARCHAR2(20),
Ret OUT NUMBER)
AS
BEGIN
Ret := -1;
IF EXISTS(select 1 from TR_Maintenance where TerminalID=TerminalID)
RETURN;
END IF; insert into TR_Maintenance(TerminalID, FaultInfo, FaultTime, ReportEmployee, StartRepairTime,FinishRepairTime,RepairStatus,RepairResult,RepairEmployee)
values(TerminalID,FaultInfo,FaultTime,ReportEmployee,StartRepairTime,FinishRepairTime,RepairStatus,RepairResult,RepairEmployee);
Ret := 1;
RETURN;
END TR_ADD_Maintenance;
Compilation errors for PROCEDURE DEV.TR_ADD_MAINTENANCEError: PLS-00103: 出现符号 ";"在需要下列之一时:
:= . ) , @ % default
character
Line: 2
Text: TerminalID NUMBER;Error: PLS-00103: 出现符号 "NUMBER"在需要下列之一时:
:= . ( @ % ; not null
range default character
Line: 11
Text: Ret OUT NUMBER)
TerminalID NUMBER,
FaultInfo VARCHAR2,
FaultTime DATE,
ReportEmployee VARCHAR2,
StartRepairTime DATE,
FinishRepairTime DATE,
RepairStatus VARCHAR2,
RepairResult VARCHAR2,
RepairEmployee VARCHAR2,
Ret OUT NUMBER)
AS
BEGIN
CREATE OR REPLACE PROCEDURE TR_ADD_Maintenance(
TerminalID NUMBER,
FaultInfo VARCHAR2,
FaultTime DATE,
ReportEmployee VARCHAR2,
StartRepairTime DATE,
FinishRepairTime DATE,
RepairStatus VARCHAR2,
RepairResult VARCHAR2,
RepairEmployee VARCHAR2,
Ret OUT NUMBER)
AS
BEGIN
Ret := -1;IF EXISTS(select 1 from TR_Maintenance where TerminalID=TerminalID)
RETURN;
END IF;insert into TR_Maintenance(TerminalID, FaultInfo, FaultTime, ReportEmployee, StartRepairTime,FinishRepairTime,RepairStatus,RepairResult,RepairEmployee)
values(TerminalID,FaultInfo,FaultTime,ReportEmployee,StartRepairTime,FinishRepairTime,RepairStatus,RepairResult,RepairEmployee);Ret := 1;RETURN;END TR_ADD_Maintenance;//错误如下-----------------------------------------------------
Compilation errors for PROCEDURE DEV.TR_ADD_MAINTENANCEError: PLS-00103: 出现符号 "RETURN"在需要下列之一时:
then and or
符号 "then" 被替换为 "RETURN" 后继续。
Line: 17
Text: RETURN;
//------------------------------------------------
再次感谢
TerminalID NUMBER,
FaultInfo VARCHAR2,
FaultTime DATE,
ReportEmployee VARCHAR2,
StartRepairTime DATE,
FinishRepairTime DATE,
RepairStatus VARCHAR2,
RepairResult VARCHAR2,
RepairEmployee VARCHAR2,
Ret OUT NUMBER)
AS
BEGIN
Ret := -1;IF EXISTS(select 1 from TR_Maintenance where TerminalID=TerminalID)
then--缺少then
RETURN;
END IF;insert into TR_Maintenance(TerminalID, FaultInfo, FaultTime, ReportEmployee, StartRepairTime,FinishRepairTime,RepairStatus,RepairResult,RepairEmployee)
values(TerminalID,FaultInfo,FaultTime,ReportEmployee,StartRepairTime,FinishRepairTime,RepairStatus,RepairResult,RepairEmployee);Ret := 1;RETURN;END TR_ADD_Maintenance;
麻烦了~~
Compilation errors for PROCEDURE DEV.TR_ADD_MAINTENANCEError: PLS-00204: 函数或伪列 'EXISTS' 只能在 SQL 语句中使用
Line: 16
Text: IF EXISTS(select 1 from TR_Maintenance where TerminalID=TerminalID)Error: PL/SQL: Statement ignored
Line: 16
Text: IF EXISTS(select 1 from TR_Maintenance where TerminalID=TerminalID)Error: PL/SQL: ORA-01733: 此处不允许虚拟列
Line: 21
Text: insert into TR_Maintenance(TerminalID, FaultInfo, FaultTime, ReportEmployee, StartRepairTime,FinishRepairTime,RepairStatus,RepairResult,RepairEmployee)Error: PL/SQL: SQL Statement ignored
Line: 21
Text: insert into TR_Maintenance(TerminalID, FaultInfo, FaultTime, ReportEmployee, StartRepairTime,FinishRepairTime,RepairStatus,RepairResult,RepairEmployee)
TerminalID NUMBER,
FaultInfo VARCHAR2,
FaultTime DATE,
ReportEmployee VARCHAR2,
StartRepairTime DATE,
FinishRepairTime DATE,
RepairStatus VARCHAR2,
RepairResult VARCHAR2,
RepairEmployee VARCHAR2,
Ret OUT NUMBER)
AS
BEGIN
Ret := -1;select 1 from TR_Maintenance where TerminalID=TerminalID;
IF SQL%ROWCOUNT = 0
THEN
return;
END IF;insert into TR_Maintenance(TerminalID, FaultInfo, FaultTime, ReportEmployee, StartRepairTime,FinishRepairTime,RepairStatus,RepairResult,RepairEmployee)
values(TerminalID,FaultInfo,FaultTime,ReportEmployee,StartRepairTime,FinishRepairTime,RepairStatus,RepairResult,RepairEmployee);Ret := 1;RETURN;END TR_ADD_Maintenance;
TerminalID NUMBER,
FaultInfo VARCHAR2,
FaultTime DATE,
ReportEmployee VARCHAR2,
StartRepairTime DATE,
FinishRepairTime DATE,
RepairStatus VARCHAR2,
RepairResult VARCHAR2,
RepairEmployee VARCHAR2,
Ret OUT NUMBER)
AS
v_i int;
BEGIN
Ret := -1;select 1 into v_i from TR_Maintenance where TerminalID=TerminalID;insert into TR_Maintenance(TerminalID, FaultInfo, FaultTime, ReportEmployee, StartRepairTime,FinishRepairTime,RepairStatus,RepairResult,RepairEmployee)
values(TerminalID,FaultInfo,FaultTime,ReportEmployee,StartRepairTime,FinishRepairTime,RepairStatus,RepairResult,RepairEmployee);Ret := 1; EXCEPTION
WHEN no_Data_found
THEN
null;
END TR_ADD_Maintenance;
TerminalID NUMBER,
FaultInfo VARCHAR2,
FaultTime DATE,
ReportEmployee VARCHAR2,
StartRepairTime DATE,
FinishRepairTime DATE,
RepairStatus VARCHAR2,
RepairResult VARCHAR2,
RepairEmployee VARCHAR2,
Ret OUT NUMBER)
AS
BEGIN
set Ret= -1IF EXISTS(select 1 from TR_Maintenance where TerminalID=TerminalID)
RETURN
insert into TR_Maintenance(TerminalID, FaultInfo, FaultTime, ReportEmployee, StartRepairTime,FinishRepairTime,RepairStatus,RepairResult,RepairEmployee)
values(TerminalID,FaultInfo,FaultTime,ReportEmployee,StartRepairTime,FinishRepairTime,RepairStatus,RepairResult,RepairEmployee)set Ret= 1RETURN
end
TerminalID NUMBER,
FaultInfo VARCHAR2(50),
FaultTime DATE,
ReportEmployee VARCHAR2(20),
StartRepairTime DATE,
FinishRepairTime DATE,
RepairStatus VARCHAR2(8),
RepairResult VARCHAR2(20),
RepairEmployee VARCHAR2(20),
Ret OUT NUMBER)
AS
BEGIN
IF((select count(*) From TR_Maintenance where TerminalID=TerminalID)>0)
Ret := -1;
RETURN;
END IF; insert into TR_Maintenance(TerminalID, FaultInfo, FaultTime, ReportEmployee, StartRepairTime,FinishRepairTime,RepairStatus,RepairResult,RepairEmployee)
values(TerminalID,FaultInfo,FaultTime,ReportEmployee,StartRepairTime,FinishRepairTime,RepairStatus,RepairResult,RepairEmployee);Ret := 1;RETURN;END TR_ADD_Maintenance;
(
v_TerminalID int,
v_FaultInfo varchar2,
v_FaultTime date,
v_ReportEmployee varchar2,
v_StartRepairTime date,
v_FinishRepairTime date,
v_RepairStatus varchar2,
v_RepairResult varchar2,
v_RepairEmployee varchar2,
v_rtn out NUMBER
)AS
v_myTerminalID int;
begin
v_rtn := -1;
--不存在则返回
select count(1) into v_myTerminalID from TR_Maintenance where TerminalID=v_TerminalID ;
if (v_myTerminalID >0) --EXISTS( select 1 from TR_Maintenance where TerminalID=v_TerminalID )
then
return;
end if;
--存在则插入insert into TR_Maintenance
(TerminalID, FaultInfo, FaultTime, ReportEmployee, StartRepairTime,FinishRepairTime,RepairStatus,RepairResult,RepairEmployee)
values(
v_TerminalID,v_FaultInfo,v_FaultTime,v_ReportEmployee,v_StartRepairTime,v_FinishRepairTime,v_RepairStatus,v_RepairResult,v_RepairEmployee);
v_rtn := 1;
end;