How do I declare and use variables in Oracle?
Inside pl/sql block:
declare
startdate number;
begin
select 20110501 into startdate from dual;
end;
/
using a bind variable:
var startdate number;
begin
select 20110501 into :startdate from dual;
end;
/
PL/SQL procedure successfully completed.
SQL> print startdate
STARTDATE
----------
20110501
in a query:
select object_name
from user_objects
where created > to_date (:startdate,'yyyymmdd'); /*prefix the bind variable wïth ":" */
SQL*Plus supports an additional format:
DEFINE StartDate = TO_DATE('2016-06-21');
DEFINE EndDate = TO_DATE('2016-06-30');
SELECT
*
FROM
MyTable
WHERE
DateField BETWEEN &StartDate and &EndDate;
Note the ampersands where the substitutions are to be performed within the query.