Ákos Kolozsvári Ákos Kolozsvári - 5 months ago 8
SQL Question

If table have 0 rows exit procedure

I have a procedure inside a package and I want to implement a logic, wich will not insert the temp table into the main table if the temp table have 0 rows, and then go to the next procedure of the package.

IF (not exists(select 1 from temp)) THEN
RETURN;
ELSE
EXECUTE IMMEDIATE 'TRUNCATE TABLE main';
INSERT --+APPEND
INTO main
Select * from temp;
EXECUTE IMMEDIATE 'TRUNCATE TABLE temp';
END IF;


With this solution, the package is compiled with error.

Can anyone give me some tips?

Answer

The easiest is to use a variable to check:

select count(1) into v_count from temp;

IF (v_count=0) THEN
   RETURN;
ELSE
   EXECUTE IMMEDIATE 'TRUNCATE TABLE main';
   INSERT --+APPEND 
          INTO main
          Select * from temp;
   EXECUTE IMMEDIATE 'TRUNCATE TABLE temp';
END IF;