oracle procedure returns integer

Question

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.

Answer 1

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.

Answer 2

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;

Answer 3

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

Answer 4

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.

Answer 5

The SQL%ROWCOUNT will only return a value immediately after a DML its value will reset if another DML is executed.

To get around the problem as I was executing deletes in a loop I issued the following command after the dml:

row_count := row_count + SQL%ROWCOUNT;

Please make sure you declare and initialize row_count := 0;