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