CREATE OR REPLACE PROCEDURE road_info_upd_proc ( old_cRoadID char(5), new_cRoadID char(5), cVloc varchar2(100), cIPAddr varchar2(20), cLinkman varchar2(30) , cPhoneNum varchar2(16) , dtActiveTime date , Flag INT ) RETURN INTEGER AS BEGIN cLinkman := NULL; cPhoneNum := NULL; dtActiveTime := NULL; Flag := NULL; BEGIN if old_cRoadID=new_cRoadID then UPDATE road_info SET cVloc = cVloc, cIPAddr = cIPAddr, cLinkman = cLinkman, cPhoneNum = cPhoneNum, dtActiveTime = dtActiveTime, Flag = Flag WHERE cRoadID = old_cRoadID; else insert into road_info (cRoadID, cVloc, cIPAddr, cLinkman, cPhoneNum, dtActiveTime, Flag) values (new_cRoadID, cVloc, cIPAddr, cLinkman, cPhoneNum, dtActiveTime, Flag);
EXCEPTION WHEN OTHERS THEN raise_application_error( -20001, 'road_infoUpdProc: Cannot update in road_info '); ROLLBACK; RETURN 1; END;
begin update violation_record set cRoadID = new_cRoadID where cRoadID = old_cRoadID;
EXCEPTION WHEN OTHERS THEN raise_application_error( -20002, 'road_infoUpdProc: Cannot update in road_info '); ROLLBACK ; RETURN 1; END; COMMIT; RETURN 0; END; /
CREATE or replace PROCEDURE dali.road_info_upd_proc ( p_old_cRoadID varchar2, p_new_cRoadID varchar2, p_cVloc varchar2, p_cIPAddr varchar2, p_cLinkman varchar2, p_cPhoneNum varchar2, p_dtActiveTime date, p_Flag boolean) iS BEGIN if p_old_cRoadID=p_new_cRoadID then UPDATE road_info SET cVloc = p_cVloc, cIPAddr = p_cIPAddr, cLinkman = p_cLinkman, cPhoneNum = p_cPhoneNum, dtActiveTime = p_dtActiveTime, Flag = p_Flag WHERE cRoadID = p_old_cRoadID; else insert into road_info (cRoadID, cVloc, cIPAddr, cLinkman, cPhoneNum, dtActiveTime, Flag) values (p_new_cRoadID, p_cVloc, p_cIPAddr, p_cLinkman, p_cPhoneNum, p_dtActiveTime, p_Flag);
update violation_record set cRoadID = p_new_cRoadID where cRoadID = p_old_cRoadID; END if; COMMIT; exception when others then rollback; END;
参考 LGQDUCKY(飘) 哥的写法,我已经修改为如下,LGQDUCKY(飘) 哥请看: 不能正确编译,我的begin、exception、end是一一对应了的啊CREATE OR REPLACE procedure road_info_upd_proc ( old_cRoadID in varchar2, new_cRoadID in varchar2, cVloc in varchar2, cIPAddr in varchar2, cLinkman in varchar2, cPhoneNum in varchar2, dtActiveTime date , Flag integer ) AS begin cLinkman := NULL; cPhoneNum := NULL; dtActiveTime := NULL; Flag := NULL; BEGIN if old_cRoadID=new_cRoadID then UPDATE road_info SET cVloc = cVloc, cIPAddr = cIPAddr, cLinkman = cLinkman, cPhoneNum = cPhoneNum, dtActiveTime = dtActiveTime, Flag = Flag WHERE cRoadID = old_cRoadID; else insert into road_info (cRoadID, cVloc, cIPAddr, cLinkman, cPhoneNum, dtActiveTime, Flag) values (new_cRoadID, cVloc, cIPAddr, cLinkman, cPhoneNum, dtActiveTime, Flag);
EXCEPTION WHEN OTHERS THEN raise_application_error( -20001, 'road_infoUpdProc: Cannot update in road_info '); ROLLBACK;
END;
begin update violation_record set cRoadID = new_cRoadID where cRoadID = old_cRoadID;
EXCEPTION WHEN OTHERS THEN raise_application_error( -20002, 'road_infoUpdProc: Cannot update in road_info '); ROLLBACK ;
END; END;
CREATE PROCEDURE road_info_upd_proc ( old_cRoadID varchar2, new_cRoadID varchar2, cVloc varchar2, cIPAddr varchar2, cLinkman varchar2 default NULL, cPhoneNum varchar2 default NULL, dtActiveTime datetime default NULL, Flag varchar2 default NULL) AS BEGIN if old_cRoadID=new_cRoadID then UPDATE road_info SET cVloc = cVloc, cIPAddr = cIPAddr, cLinkman = cLinkman, cPhoneNum = cPhoneNum, dtActiveTime = dtActiveTime, Flag = Flag WHERE cRoadID = old_cRoadID; else begin insert into road_info (cRoadID, cVloc, cIPAddr, cLinkman, cPhoneNum, dtActiveTime, Flag) values (new_cRoadID,cVloc,cIPAddr,cLinkman,cPhoneNum,dtActiveTime,Flag); exception when others then ROLLBACK; RETURN; END --update violation_record begin update violation_record set cRoadID = new_cRoadID where cRoadID = old_cRoadID; exception when others then ROLLBACK; RETURN; END; end if;commit; exception when others then rollback; END; /
(
old_cRoadID char(5),
new_cRoadID char(5),
cVloc varchar2(100),
cIPAddr varchar2(20),
cLinkman varchar2(30) ,
cPhoneNum varchar2(16) ,
dtActiveTime date ,
Flag INT ) RETURN INTEGER
AS
BEGIN
cLinkman := NULL;
cPhoneNum := NULL;
dtActiveTime := NULL;
Flag := NULL;
BEGIN
if old_cRoadID=new_cRoadID then
UPDATE road_info
SET cVloc = cVloc,
cIPAddr = cIPAddr,
cLinkman = cLinkman,
cPhoneNum = cPhoneNum,
dtActiveTime = dtActiveTime,
Flag = Flag
WHERE cRoadID = old_cRoadID;
else
insert into road_info (cRoadID, cVloc, cIPAddr, cLinkman, cPhoneNum, dtActiveTime, Flag)
values (new_cRoadID, cVloc, cIPAddr, cLinkman, cPhoneNum, dtActiveTime, Flag);
EXCEPTION
WHEN OTHERS THEN
raise_application_error( -20001, 'road_infoUpdProc: Cannot update in road_info ');
ROLLBACK;
RETURN 1;
END;
begin
update violation_record
set cRoadID = new_cRoadID
where cRoadID = old_cRoadID;
EXCEPTION
WHEN OTHERS THEN
raise_application_error( -20002, 'road_infoUpdProc: Cannot update in road_info ');
ROLLBACK ;
RETURN 1;
END; COMMIT;
RETURN 0;
END;
/
(
p_old_cRoadID varchar2,
p_new_cRoadID varchar2,
p_cVloc varchar2,
p_cIPAddr varchar2,
p_cLinkman varchar2,
p_cPhoneNum varchar2,
p_dtActiveTime date,
p_Flag boolean)
iS
BEGIN
if p_old_cRoadID=p_new_cRoadID then UPDATE road_info
SET cVloc = p_cVloc,
cIPAddr = p_cIPAddr,
cLinkman = p_cLinkman,
cPhoneNum = p_cPhoneNum,
dtActiveTime = p_dtActiveTime,
Flag = p_Flag
WHERE cRoadID = p_old_cRoadID;
else
insert into road_info (cRoadID, cVloc, cIPAddr, cLinkman, cPhoneNum, dtActiveTime, Flag)
values (p_new_cRoadID, p_cVloc, p_cIPAddr, p_cLinkman, p_cPhoneNum, p_dtActiveTime, p_Flag);
update violation_record
set cRoadID = p_new_cRoadID
where cRoadID = p_old_cRoadID;
END if;
COMMIT;
exception when others then rollback;
END;
不能正确编译,我的begin、exception、end是一一对应了的啊CREATE OR REPLACE procedure road_info_upd_proc
(
old_cRoadID in varchar2,
new_cRoadID in varchar2,
cVloc in varchar2,
cIPAddr in varchar2,
cLinkman in varchar2,
cPhoneNum in varchar2,
dtActiveTime date ,
Flag integer )
AS
begin
cLinkman := NULL;
cPhoneNum := NULL;
dtActiveTime := NULL;
Flag := NULL;
BEGIN
if old_cRoadID=new_cRoadID then
UPDATE road_info
SET cVloc = cVloc,
cIPAddr = cIPAddr,
cLinkman = cLinkman,
cPhoneNum = cPhoneNum,
dtActiveTime = dtActiveTime,
Flag = Flag
WHERE cRoadID = old_cRoadID;
else
insert into road_info (cRoadID, cVloc, cIPAddr, cLinkman, cPhoneNum, dtActiveTime, Flag)
values (new_cRoadID, cVloc, cIPAddr, cLinkman, cPhoneNum, dtActiveTime, Flag);
EXCEPTION
WHEN OTHERS THEN
raise_application_error( -20001, 'road_infoUpdProc: Cannot update in road_info ');
ROLLBACK;
END;
begin
update violation_record
set cRoadID = new_cRoadID
where cRoadID = old_cRoadID;
EXCEPTION
WHEN OTHERS THEN
raise_application_error( -20002, 'road_infoUpdProc: Cannot update in road_info ');
ROLLBACK ;
END;
END;
(
old_cRoadID varchar2,
new_cRoadID varchar2,
cVloc varchar2,
cIPAddr varchar2,
cLinkman varchar2 default NULL,
cPhoneNum varchar2 default NULL,
dtActiveTime datetime default NULL,
Flag varchar2 default NULL)
AS
BEGIN
if old_cRoadID=new_cRoadID then
UPDATE road_info
SET cVloc = cVloc,
cIPAddr = cIPAddr,
cLinkman = cLinkman,
cPhoneNum = cPhoneNum,
dtActiveTime = dtActiveTime,
Flag = Flag
WHERE cRoadID = old_cRoadID;
else
begin
insert into road_info (cRoadID, cVloc, cIPAddr, cLinkman, cPhoneNum, dtActiveTime, Flag)
values (new_cRoadID,cVloc,cIPAddr,cLinkman,cPhoneNum,dtActiveTime,Flag);
exception
when others then
ROLLBACK;
RETURN;
END --update violation_record
begin
update violation_record
set cRoadID = new_cRoadID
where cRoadID = old_cRoadID;
exception
when others then
ROLLBACK;
RETURN;
END;
end if;commit;
exception
when others then
rollback;
END;
/