0
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
Korhan Ozturk
  • 11,148
  • 6
  • 36
  • 49
user1195695
  • 1
  • 1

2 Answers2

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

Kris Babic
  • 6,254
  • 1
  • 29
  • 18
  • 1
    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. – Cade Roux Feb 07 '12 at 21:40
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;