Long time SQL user of many flavors but brand new to PL/SQL and struggling to learn the "Oracle way". I've seen MANY examples of using variables in queries online and in documentation, but I've been unsuccessful finding a sample of what I want to do and how to do it.
I've written the logic to calculate a date that is a variable number of business days before a given date - took a while (not from the logic perspective but from the "Oracle way" perspective again) but I figured that out and have that date sitting in a variable after the calculation. Now I want to use that date in the WHERE clause of a SELECT statement to pull all rows >= the variable date. All the examples I see are for singleton row returns, i.e. id = <variable> which returns one row, I need ALL rows returned in the result set and displayed/output.
How do I do this in Oracle? I have the query in SQL Developer but ultimately trying to replace this:
AND vod_ins_Date >= TRUNC(sysdate-10)
With
AND vod_ins_Date >= TRUNC(<VARIABLE_VALUE_HERE>)
and have it return all appropriate rows (more than 1).
If you're doing this in pure SQL, you can use a bind variable. Making sure you convert it to a date if necessary:
create table t (
c1 date
);
insert into t
with rws as (
select date'2018-09-30' + level c1 from dual
connect by level <= 10
)
select c1 from rws;
commit;
var dt varchar2(10);
exec :dt := '2018-10-07';
select * from t
where c1 >= to_date ( :dt, 'YYYY-MM-DD' );
C1
07-OCT-2018 00:00:00
08-OCT-2018 00:00:00
09-OCT-2018 00:00:00
10-OCT-2018 00:00:00
If you're in PL/SQL, you can use a PL/SQL variable in your query. This is a bind variable:
declare
dt date := date'2018-10-07';
begin
for rws in (
select * from t
where c1 >= dt
) loop
dbms_output.put_line ( to_char ( rws.c1, 'YYYY-MM-DD' ) );
end loop;
end;
/
2018-10-07
2018-10-08
2018-10-09
2018-10-10
PS - if you're looking for details about how to add/subtract working days from a date, I've got a whole article about it ;)
https://blogs.oracle.com/sql/how-to-find-the-next-business-day-and-add-or-subtract-n-working-days-with-sql