Declare Variables in Oracle (PL/SQL Developer)

Question

I'm new to Oracle / PL/SQL Developer and was wondering the best practise to declare variables.

In TSQL, I'm use to doing the following below and was wondering what the equivalent in oracle is.

DECLARE @WeekNumber Date
SET @WeekNumber '2020-10-01'
SELECT @Date ..... 

Cheers, I appreciate this is quite basic.

Answer 1

In Plsql you have the option to declare a variable based on existing datatype of table column.

Suppose you have a table table1 column Current_week as date datatype then you can declare it like this.

declare
weeknumber table1.current_week%type;
begin
select week into weeknumber from table1;
end;

This is best practice to declare variables in Oracle PL/SQL to avoid any datatype issues.

Answer 2

The equivalent would be

SQL> set serveroutput on size unlimited
SQL>  declare
   weekNumber Date := to_date('2020-10-01','YYYY-MM-DD');
 begin
   dbms_output.put_line(weekNumber);
 end;
 /  2    3    4    5    6
01-OCT-20

PL/SQL procedure successfully completed.

SQL>

Now , if you want to use the variable in a select or any other statement

SQL>  set serveroutput on size unlimited
 declare
   weekNumber Date := to_date('2020-10-01','YYYY-MM-DD');
 begin
        insert into t1 values ( weekNumber ) ;
        commit;
 end;
 / SQL>   2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL> select * from t1 ;

C1
---------
01-OCT-20

SQL>