Declare YESTERDAYS Date as a variable in Oracle

Question

I'm very new to using Oracle (I'm using TOAD 11.6), I would like to turn this code into something that would work in Oracle, how do I do it?!

declare @yesterday  datetime
set     @yesterday  =   (select cast(cast(getdate() as varchar(12)) as datetime)-1)

select  *
from    my_table
where   disp_cret_dt    >=  @yesterday

Thanks in advance!!

Answer 1

Below is an equivalent code for oracle

declare yesterday  date;
    begin
    select to_char(sysdate-1,'dd/mm/yyyy hh:mi:ss') into yesterday from dual;
    select * into var1,var2..varn from my_table 
    where disp_cret_dt>=yesterday;
    end;

1.Dual is temporary table in oracle which contains one column named as dummy with data type of varchar2(1). For more Refer here.

2.The SELECT INTO clause of SQL is used to retrieve one row or set of columns from the Oracle database. The SELECT INTO is actually a standard SQL query where the SELECT INTO clause is used to place the returned data into predefined variables.

If you want to return three items you have to define three variables in our pl/sql block with respective data types after applying these changes to above code it looks

declare 
yesterday  date;
v_item1 number;
v_item2 varchar2(11);
v_item3 date;
    begin
    select to_char(sysdate-1,'dd/mm/yyyy hh:mi:ss') into yesterday from dual;
    select  item1, item2,item3 into v_item1,v_item2,v_item3 from my_table 
    where disp_cret_dt>=yesterday;
Dbms_output.put_line('Item1: '||v_item1||'Item2: '||v_item2||'Item3: '||v_item3);--Displaying values
    end;

Note: In the above code if your select query will returns more than one row for each yesterday value then it will throws an error. Because at a time a variable will holds one value. In that scenario we have to choose collections in oracle for more Refer here.