Oracle IF/ELSE statement

Question

I'm writing a procedure in Oracle12c using Oracle SQL Developer. What I need to do is check a table to see if a record exists. If it does, then I need to edit it. If not, then I need to add it.

So, my "pseudocode" logic would look something like this:

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

How would I write that in Oracle SQL Developer?

EDIT:

Currently my code looks like this, I need to add a piece to update the record. If Line_ID exists, I need to update the record. If not, I need to add it.

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;

I've removed tag for SQL Developer as code not depends on editor you use. — Kacper, Dec 01 '16 at 15:07
@kacper - perhaps (or even "likely") that is true in this case, but not in general. There **are** things that depend on the editor. For example, in Oracle 12c there is `MATCH_RECOGNIZE`, and in the `PATTERN` clause you may use reluctant matching (things like `*?` or `+?`). Those will not work properly in SQL Developer because it's written in Java, the ? is not part of a string, and so the ? will be interpreted as a Java variable placeholder - you'll get errors on perfectly valid code. It's a shame that we need to know about such nonsense, but we do. — , Dec 01 '16 at 15:36
@JohnnyBones..The answer provided is very clear and is what you need. Just follow that and am sure you will do it yourself. — XING, Dec 01 '16 at 15:45
@Boneist - yes, that is correct. Pointed in Oracle's direction, of course - not Kacper's. — , Dec 01 '16 at 16:22

Kacper · Answer 1 · 2016-12-11T19:09:29.727

5

declare
  i number;
begin
  Select count(*) into i from MyTable where MyValue = 0;
If (i > 0)  then
  update ...
else
  insert ...
end;
/

But as mentioned in comment you can have it by:

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 ...

If you provide code of your procedure we can expand for you commands.

edited Dec 11 '16 at 19:09

answered Dec 01 '16 at 15:06

Kacper

4,798
2
19
34

5

@JohnnyBones This isn't a code writing service for you. In addition to Kacper's answer, there's plenty of useful information about merge out there on the internet, such as [here](https://oracle-base.com/articles/9i/merge-statement). Why not give writing your own merge statement a go, and then asking for help if you get stuck? – Boneist Dec 01 '16 at 15:40
@Boneist - Did you look at my profile before writing this? – Johnny Bones Dec 01 '16 at 16:20
2

@JohnnyBones - I am missing your point. If anything, that makes Boneist's remarks even more valid. `MERGE` is in the SQL standard, it is not a proprietary Oracle thing. And it is EXACTLY what is needed to either update or insert, depending on whether a row exists or it doesn't. And it's very easy to understand - very intuitive syntax. So I agree 100% with Boneist and have the same questions for you, even more so after you pointed me/us to your profile. – Dec 01 '16 at 16:27
@mathguy - OK, I have zero to limited experience with Oracle. It's my understanding (and I've been here for quite some time) that this site was designed to be the *de facto* question and answer site, and my hours on Meta tells me that answers should be complete and thorough. Considering my lack of Oracle, I can't see how telling me to look up *MERGE* is either complete or thorough, especially when I've posted my entire block of code. – Johnny Bones Dec 01 '16 at 16:33
1

@JohnnyBones - On your profile you call yourself a SQL Server **master**. In most contexts that means knowledgeable, experienced, a "guru". SQL Server has the `MERGE` statement, and although I don't know SQL Server at all, I asume it implements the SQL Standard syntax (just the same as Oracle does). So I don't see how *limited experience with Oracle* is relevant. This is why in my comment I explicitly said `MERGE` is not an Oracle thing. Unless you mean something unusual by **master**. And no, SO (in my limited experience) is not a "complete and thorough" (and free) code-writing service. – Dec 01 '16 at 17:06
@mathguy - Please read the [first quoted paragraph here](http://meta.stackoverflow.com/questions/254770/what-is-stack-overflow-s-goal). Perhaps you need to understand the site's purpose a little better before answering/commenting? – Johnny Bones Dec 01 '16 at 17:36
1

@JohnnyBones You asked a question to which the answer was "use a merge statement". You were even given an example of what that might look like. Most people would see that as a complete answer and they'd go off and research how to apply that answer to their own situation. For someone who is a self-declared master, I'm surprised you expected to be spoon-fed the exact answer to your situation rather than taking the opportunity to learn more about a new-to-you concept and how to apply it to your situation. – Boneist Dec 01 '16 at 18:19
1

Mom always told me not to expect everyone to be [as thorough as I am](http://stackoverflow.com/questions/38308854/have-autonumbered-column-restart-value-from-1-after-primary-key-value-changes/38309125#38309125)... Oh well, I got it working so I guess I'm done here anyway. – Johnny Bones Dec 01 '16 at 18:30

Oracle IF/ELSE statement

1 Answers1