Search code examples
plsqldeclare

Why does DECLARE exist if it is optional in PL/SQL stored procedures?


I have been told that i can omit declare keyword in a Declaration section of a stored procedure. Is declare keyword optional for a Declaration section? If it is, then why does it exist at all?

create or replace procedure myproc is

-- starting Declaration section
DECLARE  -- is DECLARE keyword optional? Can i omit it here?
cursor mycursor as
select ...;
var1 number;
var2 varchar2;
-- end of Declaration section

BEGIN
...
END myproc;

Solution

  • Following is the syntax for creating a pl/sql procedure. Declaration of variable and cursor is on need basis and is not mandatory. So if you want to declare then declare else ignore:

    CREATE [OR REPLACE] PROCEDURE procedure_name
        [ (parameter [,parameter]) ]
    
    IS
        [declaration_section]
    
    BEGIN
        executable_section
    
    [EXCEPTION
        exception_section]
    
    END [procedure_name];
    

    Also want to add that Declare keyword is not used in procedure as declaration block is implicitly there.

    EDIT: Declare keyword exists for cases where declare block is not implicit. Lets say Anonymous block, now here you need to specify Declare block else there will be no place to declare variables.

    Syntax of Anonymous block:

    DECLARE
     <constant name> CONSTANT <data type> := <value>;
     <constant name> CONSTANT <data type> DEFAULT <value>;
    BEGIN
      <valid statement>;
    EXCEPTION
      <exception handler>;
    END;