How do I set a variable as the return value of an Oracle SQL statement?

Question

I'm using a reporting program in which I give it the SQL statement and the values selected, become the variables. For example in this case I'm trying to determine whether a record already exists or not. If it doesn't, return RecordExists with a value of 0; otherwise 1.

This is much simpler (to me) using SQL Server, but I'm trying to figure out why the below will not work. The syntax error generated suggests I need an "INTO" clause with my SELECT RecordExists FROM Dual; line even though I'm not trying to assign a value at that point.

DECLARE 
  RecordExists NUMBER;
BEGIN
 SELECT COUNT(*) INTO RecordExists FROM Times
 WHERE Times.OrderNumber = '123456789' AND Times.StopSequence = '1';
 IF (RecordExists >= 1)
THEN
 SELECT 1 INTO RecordExists FROM dual;
 SELECT RecordExists FROM dual;
ELSE
 SELECT 0 INTO RecordExists FROM dual;
 SELECT RecordExists FROM dual;
END IF;
END;

What are you doing with RecordExists? It has to be either returned (if in a function), or declared as an OUT parameter (if a procedure). — Joe
– Joe, Commented Mar 7, 2014 at 21:01

deanosaur · Accepted Answer · 2014-03-07 20:56:40Z

1

Could you use something a bit simpler, like this:

SELECT CASE 
  WHEN count(*) > 0 then 1 
  ELSE 0 END 
FROM times WHERE OrderNumber = '123456789' AND Times.StopSequence = '1' ;

answered Mar 7, 2014 at 20:56

deanosaur

6113 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I set a variable as the return value of an Oracle SQL statement?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related