Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

oracle procedure returns integer

Tags:

sql

oracle

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.

like image 825
shaunf Avatar asked Nov 04 '08 16:11

shaunf


People also ask

Can a procedure return a value in Oracle?

A stored procedure does not have a return value but can optionally take input, output, or input-output parameters. A stored procedure can return output through any output or input-output parameter.

How do I return a stored procedure to a variable in Oracle?

To return a cursor from an Oracle stored procedure, the output parameter of the procedure must be declared as a cursor type. You must also declare that parameter as a cursor in the LWJDBC adapter. Below is a complete working example utilizing existing pools and a Sterling Integrator table.

Can we use RETURN statement in procedure?

You can use one or more RETURN statements in a stored procedure. The RETURN statement can be used anywhere after the declaration blocks within the SQL-procedure-body. To return multiple output values, parameters can be used instead. Parameter values must be set before the RETURN statement runs.


2 Answers

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

like image 195
stjohnroe Avatar answered Oct 10 '22 08:10

stjohnroe


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.

like image 33
Justin Cave Avatar answered Oct 10 '22 07:10

Justin Cave