Declare local variable: how convert this mssql script to oracle?

Question

declare @amount float
declare @result char (20)

select @amount = cost from PurchaseDoc where id = 1

if @amount > 0 set @result = 'ok'
else set @result = 'empty'

print @result

Kris Babic · Accepted Answer · 2012-02-07 21:42:48Z

2

Here is one representation of your script which can be executed against an Oracle database:

DECLARE
    amount    NUMBER;
    result    varchar2(20);
BEGIN
    SELECT SUM(cost) INTO amount
      from PurchaseDoc
     WHERE id = 1;

    if (amount > 0) 
    then
        result := 'ok';
    else 
        result := 'empty';      
    end if;

    dbms_output.put_line(result);    
END;
/

See this link for some useful information about dbms_output.
I recommend taking a look at some PL/SQL tutorials, such as the ones located at www.plsql-tutorial.com.

EDIT Updated select statement based on suggestion by Cade Roux

edited Feb 7, 2012 at 21:42

answered Feb 7, 2012 at 21:37

Kris Babic

6,3141 gold badge31 silver badges18 bronze badges

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

1 Comment

Cade Roux Over a year ago

I would change that to SUM(cost) for simplicity, because if no rows exist, you with get a NO_DATA_FOUND exception which will need to be handled in the transition from SQL to PL/SQL (SELECT is SQL, INTO variable is PL/SQL). You don't have this issue in T-SQL, where they are more tightly integrated.

user507484 · Accepted Answer · 2012-02-07 22:11:37Z

0

There is really no need for PL/SQL here, a normal select statement with 'decode' function can do the trick.

SELECT DECODE(SUM(COST),0,'empty','ok') FROM PurchaseDoc where id = 1;

answered Feb 7, 2012 at 22:11

user507484

Collectives™ on Stack Overflow

Declare local variable: how convert this mssql script to oracle?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related