I have a table demo where I have 5 columns as below:
Create table demo
(
Demo_id number,
demo_a number,
Demo_b number,
Demo_c number
)
So here demo_id is surrogate PK and combination of demo_a,demo_b,demo_cis unique
So I have created a SP like this
save
( pdemo_a in number. ,
p_demo_b in number. ,
p_demp_c in number,
p_demo_id out number
)
......... So I am receiving data from API into this SP and I have to insert the data into my demo_table. So I have written logic where I am checking if the combination of (demo_a,demo_b,demo_c) exists in the table. if it exists then I am selecting demo_id into a variable and returning it to the API otherwise I am inserting the data for this unique combination in my table using no_data_found exception. So basically code is :
enter code here
Begin
Select demo_id
into p_demo_id
from demo
where demo _a=p_demo_a
and demo_b=p_demo_b
and demo_c = p_demo_c;
Exception
When no data found then
insert into demo values()
Problem is the columns that are part of the unique key can be null so in that case when I check if the combination exists then because any of the field can be null because of which my select into clause fails. And the code goes to no_data_found section and tries to insert the record in the table but it fails beca``use that combination exists in the table. So what should I do. How do I return the demo_id to the API without throwing error.
CodePudding user response：
You could write out your actual condition for that case:
(demo_a=p_demo_a OR (demo_a is null and p_demo_a is null)) AND ...
But it will be simpler to use decode. Something like:
where decode(demo_a, p_demo_a, 1, 0) = 1
and decode(demo_b, p_demo_b, 1, 0) = 1
and decode(demo_c, p_demo_c, 1, 0) = 1
decode(demo_a, p_demo_a, 1, 0) simply means "if demo_a = p_demo_a, then return 1, otherwise return 0" but it does the null comparisons the way you seem to be expecting (null==null is true).
Edited to add: I think the answer above addresses your specific concern, but you should probably reconsider the process. As mentioned in the comments, in general the better strategy is probably to just try inserting and catch the error if that fails. That will support concurrency better (if multiple copies of your procedure are being run at the same time) and might be more performant.
CodePudding user response：
If I got it right you need to check if there is an existing combination of parameters A, B, C (received from API) in your demo table in columns A, B, C. There could be NULL values in parameters. If there is no such a combination you have to insert a record. I don't know how you generate your PK (column DEMO_ID), so I invented my own. That primary key column could be anything. First I created the demo table with a few rows:
-- DEMO_ID DEMO_A DEMO_B DEMO_C
-- ---------- ---------- ---------- ----------
-- 100123 1 2 3
-- 100023 2 3
-- 100132 1 3 2
-- 100456 4 5 6
-- 100567 5 6 7
-- 100089 8 9
-- 100293 2 9 3
Next - a Procedure SAVE to check for the existance and to insert the record if it does not exist.
create or replace PROCEDURE SAVE
( p_demo_a in number,
p_demo_b in number,
p_demo_c in number,
p_demo_id out number
) AS
Status VarChar2(200);
BEGIN
Declare
Begin
Select DEMO_ID
Into p_demo_id
From DEMO
Where Nvl(DEMO_A, 999999999) = Nvl(p_demo_a, 999999999) And
Nvl(DEMO_B, 999999999) = Nvl(p_demo_b, 999999999) And
Nvl(DEMO_C, 999999999) = Nvl(p_demo_c, 999999999);
Status := 'OK - NO INSERT - RECORD EXISTS --> DEMO_ID = ' || p_demo_id;
Exception
WHEN NO_DATA_FOUND THEN
Begin
p_demo_id := 100000 Nvl(To_Number(To_Char(p_demo_a) || To_Char(p_demo_b) || To_Char(p_demo_c)), 0);
INSERT INTO DEMO (DEMO_ID, DEMO_A, DEMO_B, DEMO_C)
VALUES(p_demo_id, p_demo_a, p_demo_b, p_demo_c);
Commit;
Status := 'OK - RECORD INSERTED --> DEMO_ID = ' || p_demo_id;
Exception
WHEN DUP_VAL_ON_INDEX THEN
p_demo_id := 0;
Status := 'ERR (SAVE) INNER BLOCK - DUP_VAL_ON_INDEX --> DEMO_ID = ' || p_demo_id;
WHEN OTHERS THEN
p_demo_id := 0;
Status := 'ERR (SAVE) INNER BLOCK - OTHERS --> ' || SQLERRM;
End;
WHEN OTHERS THEN
p_demo_id := 0;
Status := 'ERR (SAVE) OUTER BLOCK - OTHERS --> ' || SQLERRM;
End;
DBMS_OUTPUT.PUT_LINE(Status);
END SAVE;
Test 1.
SET SERVEROUTPUT ON
Declare
p_id NUMBER := Null;
Begin
SAVE(7, 6, 9, p_id);
If Nvl(p_id, 0) = 0 Then
DBMS_OUTPUT.PUT_LINE('Something went wrong - check the status');
End If;
End;
--
-- R e s u l t
--
-- anonymous block completed
-- OK - RECORD INSERTED --> DEMO_ID = 100769
Test 2. with null value
SET SERVEROUTPUT ON
Declare
p_id NUMBER := Null;
Begin
SAVE(7, Null, 9, p_id);
If Nvl(p_id, 0) = 0 Then
DBMS_OUTPUT.PUT_LINE('Something went wrong - check the status');
End If;
End;
--
-- R e s u l t
--
-- anonymous block completed
-- OK - RECORD INSERTED --> DEMO_ID = 100079
And if you try it again with the same parameters then the result is:
--
-- R e s u l t
--
-- anonymous block completed
-- OK - NO INSERT - RECORD EXISTS --> DEMO_ID = 100079
With my system of definition of the PK if you pass parameters A, B, C as Null, 8, 9 - there will be an error because of duplicate key.
--
-- R e s u l t
--
-- anonymous block completed
-- ERR (SAVE) INNER BLOCK - DUP_VAL_ON_INDEX --> DEMO_ID = 0
-- Something went wrong - check the status
But, as mentioned before - I don't know how you generate those surrogat keys. In this case there is a record with that key with A, B, C combination as 8, null, 9 which is different from null, 8, 9 but I created the same PK intentionally to show the possible problem. If there was a distinctive number put in place of null value while generating PK (or, even better, a sequnce number instead) then here will be a new PK and the record would be inserted. So, checking the nulls and inserting shouldn't be a problem, but PK generation could. Regards...