PL/SQL Query with Variables

I have a fairly complex query that will be r开发者_如何学运维eferencing a single date as a start or stop date multiple times throughout. I'm going to have to run this query for 3 different fiscal years, and don't want to have to hunt down the date 17 times in order to change it throughout my query.

Is there a way to set a variable at the beginning of my query and reference it throughout? I'm not looking to write a whole function, just reference a variable throughout my query.

Yes, depends how you want to do it.

You could use an anonymous procedure IE:

BEGIN

   v_date DATE := TO_DATE(your_date, your_date_mask);

   [your query referencing v_date where ever you need];

END;

Or if you run the query in SQLPlus, you use & to note variables (IE: &your_date), and will be prompted for the value when you run the script.

As OMG Ponies says, inside PL/SQL you can always refer to any PL/SQL variable (including parameters) right in the SQL as long as it's static SQL. Outside PL/SQL, or if your SQL is dynamic (because native dynamic SQL doesn't support reusable named parameters at least as of 10g) you can use the following trick. Add the following before the WHERE clause in your query:

CROSS JOIN (SELECT :dateparam Mydate FROM dual) Dateview

And everywhere you want to refer to that value in your main query, call it Dateview.Mydate Then when you execute the query, you need only pass in the one bind parameter.

You're not really saying how you reference this so I'll just show from SQL*Plus point of view.

Two ways

Have it prompt you for the value. Since you use the same variable many times you'll want to use the && operator.

    SQL> SELECT &&var, &&var FROM Dual;
    Enter value for var: 'PUMPKIN'
    old   1: SELECT &&var, &&var FROM Dual
    new   1: SELECT 'PUMPKIN', 'PUMPKIN' FROM Dual

    'PUMPKI 'PUMPKI
    ------- -------
    PUMPKIN PUMPKIN

Alternatively you could set it before you ran your SQL.

    SQL> VARIABLE new_var VARCHAR2(20);
    SQL> EXECUTE :new_var := 'PUMPKIN PIE';

    PL/SQL procedure successfully completed.

    SQL> SELECT :new_var, :new_var FROM DUAL;

    :NEW_VAR                         :NEW_VAR
    -------------------------------- --------------------------------
    PUMPKIN PIE                      PUMPKIN PIE

If you use Toad with second mouse button -> Execute as Script, so not prompt you for values:

var myVar varchar2(20);

exec :req := 'x';

delete from MYTable where Field = :myVar;