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?
- About PL SQL block structure
- About Subprograms in PL SQL
- 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 :
- 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.
- 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 :
| Anonymous Block | Subprograms |
| These are unnamed programming blocks to achieve the functionalities. | These are named programming subprograms which can be reused. |
| We need to compile anonymous blocks again and again. | We need to compile subprogram once. |
| It is not stored in databases | Subprograms are stored in databases. |
| The anonymous blocks cannot invoked by other applications | You can call the subprograms in other applications |
| The anonymous block do not return value | The functions will always return the value |
| It cannot take parameters | The procedures and functions will take input and output parameters |
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.