Oracle IF/ELSE语句
我正在使用Oracle SQL Developer在Oracle12c中编写过程。我需要做的是检查一张表以查看是否存在记录。如果确实如此,那么我需要对其进行编辑。如果没有,那么我需要添加它。Oracle IF/ELSE语句
所以,我的 “伪” 逻辑将是这个样子:
If (Select * from MyTable where MyValue = 0) has more than one record, then
This will be an EDIT line of code
else
This will be an ADD line of code
end
我怎么会写在Oracle SQL Developer中?
编辑:
目前我的代码看起来是这样的,我需要添加一块要更新的记录。如果Line_ID存在,我需要更新记录。如果不是,我需要添加它。
create or replace PROCEDURE CST_WRITE_FEEDBACK (
v_AuditID IN NUMBER,
v_BenefitID IN NUMBER,
v_Network1 IN VarChar,
v_Network2 IN VarChar,
v_Network3 IN VarChar,
v_Network4 IN VarChar,
v_Issue1 IN NUMBER,
v_Issue2 IN NUMBER,
v_Issue3 IN NUMBER,
v_Issue4 IN NUMBER,
v_Resolution1 IN NUMBER,
v_Resolution2 IN NUMBER,
v_Resolution3 IN NUMBER,
v_Resolution4 IN NUMBER,
v_Status IN NUMBER,
v_Comment IN VarChar,
v_LineID IN NUMBER,
v_EmpID IN NUMBER
)
AS
BEGIN
IF
INSERT INTO F_AUDIT_LINE ("AUDIT_ID", "BENEFIT_ID", "NETWORK", "NETWORK2", "NETWORK3", "NETWORK4",
"ISSUE_ID", "ISSUE_ID2", "ISSUE_ID3", "ISSUE_ID4",
"RESOLUTION_ID", "RESOLUTION_ID2", "RESOLUTION_ID3", "RESOLUTION_ID4",
"LINE_STATUS")
VALUES (v_AuditID, v_BenefitID, v_Network1, v_Network2, v_Network3, v_Network4,
v_Issue1, v_Issue2, v_Issue3, v_Issue4,
v_Resolution1, v_Resolution2, v_Resolution3, v_Resolution4,
v_Status
);
END CST_WRITE_FEEDBACK;
declare
i number;
begin
Select count(*) into i from MyTable where MyValue = 0;
If (i > 0) then
update ...
else
insert ...
end;
/
但是在留言中提到你可以拥有它:
merge into MyTable m
using (select v_AuditID, v_BenefitID, v_Network1, v_Network2, _Network3, _Network4,
v_Issue1, v_Issue2, v_Issue3, v_Issue4,
v_Resolution1, v_Resolution2, v_Resolution3, v_Resolution4, v_Status
from dual) d
on (d.MyValue = m.Myvalue)
when matched then
update set ..
when not matched then
insert ...
如果您提供的程序代码,我们可以为您扩展命令。
请参阅我的更新了解我当前的代码。 –
@JohnnyBones这不是一个代码编写服务给你。除了Kacper的回答外,还有大量关于在互联网上合并的有用信息,例如[here](https://oracle-base.com/articles/9i/merge-statement)。为什么不把自己的合并声明写下去,然后在被卡住时寻求帮助? – Boneist
@Boneist - 写这个之前你看过我的个人资料吗? –
只需使用Oracle上的合并功能 –
您可以在此扩展吗? –
我已经删除了SQL Developer的标签,因为代码不依赖于您使用的编辑器。 – Kacper