有一个 存储过程如果往该表插入数据,当插入失败是就更新该表数据,我是这么写的BEGIN
INSERT INTO ........
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
UPDATE .....
END;不过UPDATE不成功!大家帮忙看看要怎么修改!网上听说不能这么写!
INSERT INTO ........
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
UPDATE .....
END;不过UPDATE不成功!大家帮忙看看要怎么修改!网上听说不能这么写!
USING EMPLOYEES E
ON (C.EMPLOYEE_ID = E.EMPLOYEE_ID)
WHEN MATCHED THEN
UPDATE
SET C.FIRST_NAME = E.FIRST_NAME,
C.LAST_NAME = E.LAST_NAME,
C.DEPARTMENT_ID = E.DEPARTMENT_ID
WHEN NOT MATCHED THEN
INSERT
VALUES
(E.EMPLOYEE_ID,
E.FIRST_NAME,
E.LAST_NAME,
E.EMAIL,
E.PHONE_NUMBER,
E.HIRE_DATE,
E.JOB_ID,
E.SALARY,
E.COMMISSION_PCT,
E.MANAGER_ID,
E.DEPARTMETN_ID);去网上查查merge用法,很多哦.
加一个变量p_message := 'TRUE';在exception里面将这个变量赋值
p_message := 'FALSE'
end exception后
if(p_message == 'FALSE') then
begin
update ....
end;
end if
个人赞同2楼的,设置判断值