How to create a stored procedure using declare variables in PL/SQL

I want to create a stored procedure in PL/SQL using declare variable. But it did not work. How can I create this?

My code looks like:

create or replace procedure only_for_test as
begin
declare
maxdate date;
BEGIN
SELECT to_date(max(create_date)) 
INTO maxdate
FROM table_a where product = 'A' and create_date >= date'2023-09-01';
END;
if
maxdate = to_date(sysdate)
then
begin
proc_test;
end;
end if;
commit;
end only_for_test;

The above code showing error:

PLS-00201: identifier 'MAXDATE' must be declared
PL/SQL: Statement ignored

Please help me. Thank you a lot.

Your problem is that you have nested PL/SQL blocks and you have tried to use MAXDATE in the outer block when it is only DECLAREd in the inner block.

CREATE OR REPLACE PROCEDURE only_for_test
AS                  -- Variable definitions for outer block
BEGIN               -- Begin outer block
  DECLARE           -- Variable definitions for inner block
    maxdate DATE;
  BEGIN             -- Begin inner block
    -- ...          -- Do stuff in the inner block
  END;              -- End the inner block
                    -- maxdate was declared in the inner block and stops being
                    -- usable when you end the block
  -- ...            -- Do stuff in the outer block
END only_for_test;  -- End the outer block
/

Start by creating an anonymous PL/SQL block (rather than a procedure) and test that what you want to do works:

DECLARE
  maxdate DATE;
BEGIN
  SELECT TRUNC(MAX(create_date))
  INTO   maxdate
  FROM   table_a
  WHERE  product = 'A'
  AND    create_date >= DATE '2023-09-01';

  IF maxdate = TRUNC(sysdate) THEN
    proc_test;
  END IF;
END;
/

Note:

• Unless you are catching exceptions, you (almost always) want a single DECLARE where you declare the variables and then a single BEGIN and an END at the end.
• NEVER use TO_DATE on a value that is already a DATE. If you want to compare values ignoring the time component of a DATE then use the TRUNC to truncate the dates back to midnight.
• You should not COMMIT in procedures as this prevents you from running multiple procedures and then using ROLLBACK to rollback the changes from the 1st procedure if a later procedure fails. Instead, COMMIT from the code that calls the procedure.

Once a simple PL/SQL block is working then swap the DECLARE statement at the start of the block for the procedure signature:

CREATE OR REPLACE PROCEDURE only_for_test
AS
  maxdate DATE;
BEGIN
  SELECT TRUNC(MAX(create_date))
  INTO   maxdate
  FROM   table_a
  WHERE  product = 'A'
  AND    create_date >= DATE '2023-09-01';

  IF maxdate = TRUNC(sysdate) THEN
    proc_test;
  END IF;
END;
/