How do I declare a variable and use it in a subsequent query-CodePudding

I'm trying to do something that's really simple in TSQL but I'm utterly stuck doing the same in PlSQL.

I want to do the equivalent of this:-

declare @Today Date = GetDate();

declare @FirstDate Date;
declare @LastDate Date;

with cteStartDates as
(
    Select START_DATE
    From TABLE1
    

Union All

Select START_DATE
From TABLE2

)
Select @FirstDate = MIN(START_DATE),
        @LastDate = DateAdd(Day, 1, @Today)
From cteStartDates;

Basically, I'm trying to get the a start date and end date where start date is the date of a first record in one of two tables and end date is tomorrow. I then need to use @FirstDate and @LastDate as parameters in a whole bunch of subsequent queries.

I'm falling at the first hurdle in PLSQL. I can't even work out how to do the equivalent of this:-

declare @Today Date = GetDate();

I've tried reading around Oracle Variables but I'm not really understanding it. I don't understand the difference between DEFINE, DECLARE and VARIABLE and no matter which I try I can't seem to get it to work and keep getting problems I don't really understand.

For example (based on Declare but I've tried all the following with Define and Variable also), I've tried this as an experiment (assign a value to variable and then issue an otherwise valid query which doesn't even use the variable):-

Declare 
    v_Today Date;
Begin
    Select sysdate into v_Today from dual;
    Select ID From atable;
End;

That tells me I need an Into clause on the second select. I don't really understand why, I'm not trying to assign ID to anything, I just want to select it. I've seen some examples that sort of imply that an into will define the column names (I'm not sure I've understood that correctly though). OK, I tried this:-

Declare 
    v_Today Date;
Begin
    Select sysdate into v_Today from dual;
    Select ID into IDColumn From atable;
End;

That gives me a error saying identifier IDColumn must be declared so clearly the into can't simply name columns.

From examples I get the impression that perhaps the begin and end surround the bock in which variables are assigned values that can then be used later in the script. So I tried this:-

Declare 
    v_Today Date;
Begin
    Select sysdate into v_Today from dual;
End;
Select v_Today from Dual;

That tells me that it encountered the keyword Select, so it seem I can't just simply follow up the declare begin and end block with a query.

Some example seem to show that you can assign a variable, execute then use the variable. So I tried executing the Declare/Begin/End Block on it's own - that gave me message saying it ran successfully. Then I tried executing the subsequent Select v_Today from Dual, v_Today's not recognised, so clearly I've lost the value of the variable by splitting up the executions.

I feel like this should be trivially easy but I'm clearly not getting it. Can someone point me in the right direction?

Edit> Ah, finally figured it out. I can use the variables within the Begin and end but I can't just issue a select in there.

CodePudding user response：

The variables in the Declare section can be used in the Begin and End block

CodePudding user response：

PL/SQL block is enclosed with BEGIN/END keywords. Once you leave the block you end your PL/SQL context. And enter plain SQL context where PL/SQL variables are not known.

In simple words :-)

This is correct what you have learned:

you need to select values into variables
variables must be declared in DECLARE part of PL/SQL block
if you want to return a variable to the client - e.g. to have it displayed - you need to use Oracle's package dbms_output.

Like this:

Declare
    v_Today Date;
Begin
    Select sysdate into v_Today from dual;
    dbms_output.put_line(v_Today);
End;

You will not see a thing until you issue before PL/SQL block:

SET SERVEROUTPUT ON

This blog post can help: https://blogs.oracle.com/connect/post/building-with-blocks Steve published whole series of posts for PL/SQL beginners. This is just the first one.