Differences Between Anonymous Blocks and Subprograms

1. Anonymous is unnamed plsql block
A stored procedure or a named block is a pl/sql block.
2. Anonymous cannot save in database,
oracle stores pl/sql block in the database.
3. We cannot call Anonymous
blocks. We can recall named block
Whenever program require it.
4 Anonymous Block cannot allow any mode of parameter.
Named block accepts the mode of
Parameter like in,inout,out.
5 Anonymous blocks are to be compiled each time when we
Require it.
But once we compile a named pl/sql block it is
Permanently stored as p-code after compilation in the
Shared pool of the system global area.
6. Anonymous block: Starts with
DECLARE or BEGIN.
Named block: Starts with Header Block.
Like name of block, type of block, parameter.
Named Block has NAME: like PROCEDURE, FUNCTION, and PACKAGES


7.The anonymous block statement is an executable statement
That can contain PL/SQL control statements and SQL
Statements. It can be used to implement procedural logic in
a scripting language.
named block or stored procedure is a pl/sql block that
oracle stores in the database and can be called by name
From any application examples are function, procedure and packages,etc..
8.Anonymous block are created on client and subprograms are stored on server. It is possible to call subprograms from anonymous block.
9.Anonymous blocks do not return values.
Subprograms called functions must return values.


10. Anonymous blocks cannot take parameters.
Subprograms can take parameters..


Syntax:-


Whenever you will execute the procedure nth time would appear the following message
and not showing any output anonymous block completed.

Anonymous Blocks
DECLARE(Optional)
Variables,Cursors,etc.
BEGIN(Mandatory)
SQL and PL/SQL cOMMANDS
EXCEPTION(Optional)
Exceptional_Handling actions;
END;(Mandatory)


11. example Anonymous blocks


DECLARE
l_message
VARCHAR2 (100) := 'Hello World!';
BEGIN
DBMS_OUTPUT.put_line (l_message);
END;




SUBPROGRAMS PROCEDURE
Create [Or Replace ] procedure name [parameters] is|as
(Mandatory)
Variables,Cursors,etc(Optional)
BEGIN (Mandatory)
SQL & plsql STATEMENTS;
EXCEPTION(Optional)
When Exception_handling actions;
END[NAME] ; (Mandatory)

Let us take one example of Named Block given below.


CREATE OR REPLACE FUNCTION FINDAREA(LEN IN NUMBER, WID IN NUMBER)
RETURN NUMBER
AS VAREA NUMBER;
BEGIN
VAREA := LEN * WID;
RETURN VAREA;
END;


SQL> SELECT FIND_AREA(25,15) AREA FROM DUAL;


AREA
375