oracle procedure returns integer

In oracle, I want to create a delete sproc that returns an integer based on the outcome of the deletion.

this is what i have so far.

create or replace
PROCEDURE Testing
( 
iKey IN VARCHAR2
)
 AS 

BEGIN
  delete from MyTable WHERE 
  TheKey = iKey;

END Testing;

i've tried putting a RETURNS INTEGER in but the sproc won't compile.

From stackoverflow

• A procedure does not return a value. A function returns a value, but you shouldn't be doing DML in a function (otherwise you cannot do things like reference the function in a SQL statement, you confuse permission grants since normally DBAs want to be able to grant read-only users access to all the functions so that users are doing computations consistently, etc.).

  You can add an OUT parameter to the procedure to return the status. If "success" means that one or more rows were updated, you can use SQL%ROWCOUNT to get a count of the number of rows modified by the prior SQL statement and use that to populate the return parameter, i.e.

  CREATE OR REPLACE PROCEDURE test_proc (
    p_iKey    IN VARCHAR2,
    p_retVal OUT INTEGER
  )
  AS
  BEGIN
    DELETE FROM myTable
     WHERE theKey = p_iKey;
  
    IF( SQL%ROWCOUNT >= 1 )
    THEN
      p_retVal := 1;
    ELSE
      p_retVal := 0;
    END IF;
  END test_proc;
  

  Of course, from a general code clarity standpoint, I'm dubious about OUT parameters that appear to be trying to return a status code. You are generally much better served by assuming success and throwing exceptions in the event of an error.

• You are probably looking for a function instead.

  FUNCTION TESTING (iKEY IN VARCHAR2) RETURN NUMBER
  IS
    v_count NUMBER;
    yourNumber NUMBER;
  BEGIN
  
    SELECT COUNT(*) INTO v_count
    FROM MyTable
    WHERE TheKey = iKey;
  
    IF v_count > 0
      THEN
         DELETE FROM MyTable 
         WHERE TheKey = iKey;
  
         SELECT COUNT(*) INTO v_count
         FROM MyTable
         WHERE TheKey = iKey;
  
         IF (v_count = 0)
           THEN
             yourNumber :=  1; --means successful deletion
         END IF;
    ELSE
         yourNumber := 0; --means no items to delete
    END IF;
    return yourNumber;
  
    EXCEPTION
        WHEN OTHERS THEN
          RETURN -1; --means error was encountered
  END TESTING;
  

  Note: Where I work we generally put functions inside a sql package.

  Dave Costa : 2 selects plus the delete? Use the SQL%ROWCOUNT method suggested by stjohnrow instead.

  jschoen : That is pretty cool. Did not know about that. That is definitly better.

• going down the function path..

  create or replace
  FUNCTION Testing
  ( 
  iKey IN VARCHAR2
  )
  RETURN INTEGER
   AS 
  
  BEGIN
    delete from MyTable WHERE 
    TheKey = iKey;
  
    RETURN 1;
  
  END Testing;
  

  What would be the best way to return the success of a deletion?

  There can be cases where the record doesnt exist

• Use a function and the implicit SQL cursor to determine the number of rows deleted

  create or replace
  FUNCTION Testing
  ( 
  iKey IN VARCHAR2
  ) RETURN INTEGER
   AS 
  
  BEGIN
    delete from MyTable WHERE 
    TheKey = iKey;
  
    RETURN SQL%ROWCOUNT;
  
  END Testing;
  

  That should work

• cheers, the functions compile and stuff.

  maybe someone can set something straight for me.

  coming from a sqlserver world, we can do something like: exec mydelproc(1)

  to execute a sproc and get a return value.

  with an oracle function, i can't run anything like this. what is the best practice in oracle to perform such functionality?

  stjohnroe : You could declare a bind variable and set autoprint on in SQLPLUS, then do EXEC :ret := testing(1234); that should print the output to the console.

• You can use a stored procedure to return results.

  CREATE OR REPLACE PROCEDURE testing (iKey IN VARCHAR2, oRes OUT NUMBER)
  AS
  BEGIN
     DELETE FROM MyTable
           WHERE TheKey = iKey;
  
     oRes := SQL%ROWCOUNT;
  END;
  

  To call the procedure use something like:

  DECLARE
     pRes   NUMBER;
  BEGIN
     testing ('myspecialkey', pRes);
     DBMS_OUTPUT.put_line (pRes);
  END;