Does data( row ) exist in stored procedure

[jphillips]

I am trying to see if a record has already been enter into table_1 (don't want redundancy). If table 1 has the data i want to enter, just add data to the second table. If Table_1 has no entry add data to table_1 and Table_2. I do a select to find out if there is a record that matches table_1 into a varible
*******
select value_1 into v_var from table_1 where service_number = value_1.
*******
I then do a if then statement
*******
if v_var is null then
INSERT INTO Table_1 VALUES (x,c,b);
END IF;
INSERT INTO table_2 VALUES (q,e,t);
COMMIT;

If the select statement does not find a row,(no rows return) the stored procedure stops and says 'no data found' If it does find data it works great and just adds data to the second table.

My question is how can I do a select statement for a value and if it is not present (no rows or none existent in the table) keeping moving down to the if-then part of the script.

This stored procedure is called through a webdb application.
JP

 

[Guest_imported]

First of all, I will replace the first part of your if statement with if v_var = 0 instead of if v_var is null. It is a somewhat suspicious statement because null and 0 are not the same.
On the second part of your if statement, what are looking to accomplish by moving down to the next if statement if the no record present in the first if statemnt?
I think this will help lead us to the right part as far as helping you is concerned.

 

[lewisp]

INSERT INTO Table_1
SELECT x,c,b
FROM sys.dual WHERE EXISTS
(SELECT 1
FROM table_1
WHERE service_number = value_1);

INSERT INTO table_2 VALUES (q,e,t);

 

[sem]

When the row does not exist your select statement does not return null, it raises an exception NO_DATA_FOUND instead. It also raises an exception TOO_MANY_ROWS in the case there are 2 or more rows. Fortunately you may catch them and process in EXCEPTIONS block. But of course the 'pure sql' suggestion of Lewisp will work also.

 

[jphillips]

hello,
I do not want to over write the orginal entry(if there is one), will this work(WHERE NOT EXISTS):

INSERT INTO Table_1
SELECT x,c,b
FROM sys.dual WHERE NOT EXISTS
(SELECT 1
FROM table_1
WHERE service_number = value_1);

INSERT INTO table_2 VALUES (q,e,t);

JP

 

[jphillips]

I did some reading on Exceptions,
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT TABLE_1 VALUES (X,C,B);
END;

at the end of the store procedure

and got it to work. I am going to keep playing with the sql statement, and see if I can get it to work also...

Thanks so much for the all help and the time you all spent helping me out.

JP