-3

I am researching issues regarding oracle. I'm creating stored procedures and boot the following errors I also show them in the picture, I hope to help me resolve the error.

[enter image description here]

Diego Armando Contreras Ishuiz
  • 1
  • 3
  • what you need is a MERGE statement. [Find out more](http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606). – APC Oct 30 '15 at 22:49
  • It is wrongly closed. Question is about use of `IF Not Exists` not upsert. – Utsav Oct 31 '15 at 05:43
  • @OP-Instead of using If not exists, use count * and see if it is 0. – Utsav Oct 31 '15 at 05:45
  • 1
    In general it's more useful to copy/paste the query then link to a picture which is not like;y to be a helpful to others that might have the same problem in the future.. – Mike Dinescu Oct 31 '15 at 07:04
  • duplicate of [this SO post](http://stackoverflow.com/q/237327/146325) – APC Oct 31 '15 at 07:09
  • @Utsav - I have re-opened this question but with hindsight I was wrong to do so and my original close vote was correct. Unfortunately the OP has pasted a hard-to-read image of their code instead of posting their actual code. But it appears they are testing for the existence of a specific key (`where id_datos=1`), and depending on the outcome updating the row or inserting a row *for that key*. Which is the canonical use case for MERGE. – APC Oct 31 '15 at 07:12

2 Answers2

1

You can add variable v_count number :=0; in your procedure to check if value exists.

Example:

CREATE OR REPLACE PROCEDURE PROCEDURE_NAME(PARAMETERS) IS
V_COUNT NUMBER := 0;

BEGIN
SELECT COUNT(1)
INTO V_COUNT
FROM YOUR_TABLE
WHERE .. .

IF V_COUNT = 0 THEN INSERT ... 
ELSIF UPDATE ... 
COMMIT;
END IF;

END;
Drenmi
  • 8,492
  • 4
  • 42
  • 51
Costa
  • 26
  • 1
0

Merge is one way to do this. Another way is

INSERT INTO..
SELECT .... 
FROM DUAL 
WHERE NOT EXISTS (SELECT * FROM...)

I'm not going to try and transcribe your screenshot

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91