Oracle sql Stored Procedure checking if an input already exists in a table if not insert

Question

I want to write a procedure to check whether a particular input value exists in a column in a table. If the value does exists then output an error message else insert a new row.

For example this is the sort of logic required

CREATE OR REPLACE PROCEDURE class_day (p_class_day IN varchar2)
AS
  classday varchar2;
BEGIN

    IF ( SELECT class_Day INTO classday
           FROM tutprac
          WHERE classday = p_class_day) THEN

            dbms_output.put_line('do not allow insert');
    ELSE
      dbms_output.put_line('allow insert');
    END IF;
END;

So what exactly is your question?

Mureinik
– Mureinik

2015-10-29 07:54:07 +00:00
Commented Oct 29, 2015 at 7:54 — Mureinik
– Mureinik, Commented Oct 29, 2015 at 7:54

Ollie · Accepted Answer · 2015-10-29 07:54:19Z

1

You could try something like this:

CREATE OR REPLACE PROCEDURE class_day (p_class_day IN varchar2) 
AS
 classday tutprac.class_Day%TYPE;
BEGIN
   SELECT class_Day 
     INTO classday
     FROM tutprac
    WHERE classday = p_class_day;

   -- If you get here, the record has been selected, therefore it already exists
   dbms_output.put_line('do not allow insert');
EXCEPTION
   WHEN no_data_found
   THEN
      -- The record did not exist, create it!
      dbms_output.put_line('allow insert');
   WHEN others
   THEN
      -- An unexpected error has occurred, report it!
      dbms_output.put_line(sqlerrm);
      RAISE;

END class_day;

answered Oct 29, 2015 at 7:54

Ollie

17.6k8 gold badges50 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

seus Over a year ago

Thank you this helped me alot !

diziaq · Accepted Answer · 2016-02-16 06:24:19Z

To avoid rituals with catching exceptions etc. we simply can count rows where given value appears. And then use the number of rows as a flag of the value existence in the table.

CREATE OR REPLACE PROCEDURE class_day (p_class_day IN varchar2) 
AS
  l_count NUMBER;
BEGIN
   SELECT count(*)
     INTO l_count
     FROM tutprac
    WHERE classday = p_class_day
      AND rownum = 1; -- this line makes the query execution a little bit faster
                      -- and later we can remove it if we need exact amount of rows

   -- coming at this line
   -- we have fetched `l_count` value is either 0 or 1
   -- and guaranteed to avoid NO_DATA_FOUND and TOO_MANY_ROWS exceptions

   IF l_count = 0 THEN 
     -- value `p_class_day` is not found
     dbms_output.put_line('do not allow insert');
   ELSE
     -- at least one `p_class_day` is found
     dbms_output.put_line('allow insert');
   END IF;

END class_day;

Collectives™ on Stack Overflow

Oracle sql Stored Procedure checking if an input already exists in a table if not insert

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related