CODEWITHSUNDEEP
sqloracle-databaseif-statementstored-proceduresplsql
Ákos Kolozsvári
Ákos Kolozsvári

Reputation: 13

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?

Upvotes: 1

Views: 2825

Answers (4)

J. Chomel
J. Chomel

Reputation: 8395

The easiest is to use a variable to check:

--- suggested edit: add condition to select 1 row at most and avoid
--  counting big table.
select count(1) into v_count from temp where rownum <=1;

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;

Upvotes: 0

Tenzin
Tenzin

Reputation: 2505

Some answer on here use a SELECT INTO method, but I find those a bit tricky.
Since if for example SELECT ColumnA INTO vcColumnA FROM Temp will not have any records, you will end up with the error ORA-01403: no data found.
And those can be hard to find if you have a bigger database.

To loop through a table and do something with values I think cursors and records are more safe.

For example: 

DECLARE
    CURSOR cTemp IS 
        SELECT  ColumnA, ColumnB 
        FROM    Temp;

    rTemp   cTemp%ROWTYPE;
BEGIN
    OPEN cTemp;
    LOOP
        FETCH cTemp INTO rTemp;
        -- Exit when we read all lines in the Temp table. 
        EXIT WHEN cTemp%NOTFOUND;

        --Do something with every row. 
        --For example, print ColumnB. 
        DBMS_OUTPUT.PUT_LINE(rTemp.ColumnB);
    END LOOP;
    CLOSE cTemp;
END;
/

Upvotes: 0

William Robertson
William Robertson

Reputation: 16001

Just count one row and then test whether the result is 0 or 1:

declare
    l_row_check integer := 0;
begin
    select count(*) into l_row_check from main
    where  rownum = 1;

    if l_row_check = 0 then
        execute immediate 'truncate table main';

        insert --+ append 
        into main
        select * from temp;

        execute immediate 'truncate table temp';
    end if;
end;

Upvotes: 1

Анатолий Предеин
Анатолий Предеин

Reputation: 571

you can use loop, without any variables, just first iteration, something like this

FOR a in (select 1 from temp where rownum = 1) LOOP

   EXECUTE IMMEDIATE 'TRUNCATE TABLE main';
   INSERT --+APPEND 
          INTO main
          Select * from temp;
   EXECUTE IMMEDIATE 'TRUNCATE TABLE temp';

END LOOP;

Upvotes: 1

Related Questions