CODEWITHSUNDEEP
sqloracle-databasestored-proceduressyntaxplsql
seus
seus

Reputation: 568

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

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;

Upvotes: 0

Views: 4374

Answers (2)

diziaq
diziaq

Reputation: 7795

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;

Upvotes: 0

Ollie
Ollie

Reputation: 17538

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;

Upvotes: 1

Related Questions