Procedure if (select count(column) from table) > 0 then

Question

I'm trying to create a procedure in Oracle PL/SQL and I'm struggeling at the moment with the following code:

 CREATE OR REPLACE PROCEDURE MyProcedure(
   cName number, 
   cStatus varchar2
  ) AS
  BEGIN
    declare recCount;
    BEGIN
      select count(*) 
        into recCount 
        from myTable 
      where colName=cName 
        AND colStatus=cStatus;
    END;
    If recCount = 0 Then
      do something...
    Else
      do something else...
    End if;
      COMMIT;
    END;

Basically I want to count the lines from one table and then check if there is a certain amount of them already in the table. At the moment I'm getting PLS-00103 Errors.

I searched a lot for a solution, but unfortunately nothing worked. I'm relatively new to oracle sql, so maybe I did a common mistake.

Thanks for your time and effort.

See ["What is the effect of placing the commit after DML in procedure?"](http://stackoverflow.com/a/41998936/1509264) for a commentary on why putting `COMMIT` statements in procedures is, generally, a bad idea. — MT0, Feb 15 '17 at 15:26

Aleksej · Accepted Answer · 2017-02-15T16:40:40.663

You have some issues in your syntax:

the DECLARE is wrong in a procedure definition; you would use a DECLARE, for example, to build an anonymous block where you need to declare variables (DECLARE ... BEGIN ... END) or in a trigger definition.
you need to specify a type for the variable recCount, and the variable definition goes before the BEGIN
you have an unnecessary BEGIN...END block; this is not an error, but the block is unuseful here; it only would be useful to add exception handling to a specific part of code

I would even consider adding some exception handling; here I give an example with OTHERS, but you should better specify the exceptions to handle depending on your code.

Also, as already noticed in MT0's comment, the COMMIT could not be a great idea.

CREATE OR REPLACE PROCEDURE MyProcedure(cName NUMBER, cStatus VARCHAR2) AS
    recCount                                NUMBER;
BEGIN
    SELECT COUNT(*)
      INTO recCount
      FROM myTable
     WHERE     colName = cName
           AND colStatus = cStatus;

    IF recCount = 0
    THEN
        -- your code
    ELSE
        -- your code
    END IF;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
         -- exception handling    
END;

If you only need to check if a row exists, no matter how many rows match the conditions, you can avoid counting all the matching rows, by adding a condition to only get one row:

SELECT COUNT(*)
  INTO recCount
  FROM myTable
 WHERE     colName = cName
       AND colStatus = cStatus
       AND rownum = 1;

You could even use this to build an example of a block within a procedure, with exception handling ( I only post this as an example, I do not like this approach):

BEGIN       
    SELECT 1
      INTO recCount
      FROM myTable
     WHERE     colName = cName
           AND colStatus = cStatus
           AND rownum = 1;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        recCount := 0;
END;

Please add an explanation of what was wrong (both for the OP and for future visitors' benefit) - things like declaring a variable requires stating its data type, not just its name; keyword `declare` is used for anonymous blocks, not named procedures, etc. Understanding what was wrong with the original attempt may be just as helpful and instructive as how to do it right. — , Feb 15 '17 at 15:32
...and exception handling is fine, but `when others then` should never be used by beginners. (There may be a very small number of cases when it is actually needed, but they are all "advanced" cases.) — , Feb 15 '17 at 15:33
I'd add an `and rownum = 1` condition to the query, or else rewrite it as an `exists`, since it seems to be an existence check that doesn't need the full count. — William Robertson, Feb 15 '17 at 16:16
@mathguy, @ William Robertson: Just edited, thanks for the suggestions — Aleksej, Feb 15 '17 at 16:33

Procedure if (select count(column) from table) > 0 then

1 Answers1