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;
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;