What is difference between anonymous block and subprogram?

In my previous articles i have given the idea about the anonymous block of PL SQL in detail. The PL SQL always uses the block structure. The every query statements are written in the block structure. You may always face the question in PL SQL that what exactly the difference between anonymous block and subprogram in detail. I would like to explain the difference between the anonymous block an subprogram in this article. But before going deep in to difference i would like to explain the basic program structure of PL SQL with real life examples. You can execute those programs on LiveSQL.oracle.com website by registering to oracle.

What you will find in this article?

1. About PL SQL block structure
2. About Subprograms in PL SQL
3. What is difference between block and subprograms

About PL SQL block structure :

The PL SQL is nothing but Procedural language structured query language. I already explained that all the PL SQL statements are written in between Begin and end. It has a specified block structure.

I have given the PL SQL block structure in above diagram. The different sections in the PL SQL block is as follows :

1. Declare Section : The declare section is first section to run any PL SQL block. This is optional section where you can define multiple variables,cursors and user defined exceptions as well.

Example : Declare i_count number(10);

2.Begin..End Section : This is non optional block structure in which you can write multiple SQL and PL SQL statements. The common mistake programmers made is they forget to write end.

3.Exception : The other section is exception section where user can manage the user defined exceptions as well as the system exceptions.

Example : You can manage the DIVIDE_BY_ZERO exception in exception section.

About Subprograms of PL SQL :

The subprograms are nothing but the reusable blocks which you can save and use those blocks in achieving the functionalities again and again. There are following PL SQL subprograms.

1. Procedures or Stored Procedures :

The procedures are nothing but the subprograms in which you can add and save the related functionality.You can reuse the procedures and call the procedures whenever required.

Functions : The functions are basics building block which will return the value. The functions are also used in select statements or you can call the functions in other PL SQL blocks as well.The function will return at least one value.

What is difference between Anonymous block and Subprogram :

I hope you like this article on difference between anonymous block and subprogram. If you like this article or if you have any issues or concerns kindly comment in comments section.