What makes PL/SQL a perfect choice?

PL/SQL means Procedural Language extension of SQL. It is a unique combination of SQL with procedural features of programming languages. PL/SQL was developed by the Oracle Corporation in early 1990s to improve the capabilities of SQL.

PL/SQL Block

Every PL/SQL program comprises of SQL & PL/SQL statements that form a PL/SQL block. A typical PL/SQL Block has three sections:

• Declaration section (optional)

• Execution section (mandatory)

• Exception Handling or Error section (optional)

Let us understand each section briefly:

Declaration Section – It starts with DECLARE, the reserved keyword. Well, this section is optional. It is used to declare placeholders such as constants, variables, records & cursors that are used to manipulate data in execution section. And, placeholders may probably be any of Constants, Variables, and Records that store data on a temporary basis. Cursors are declared in this section as well.

Execution Section – It starts with BEGIN, the reserved keyword & ends with END. Execution section is a mandatory section; it is the section wherein the program logic is likely to be written so as to perform any task. Programmatic constructs such as loops, conditional statement & SQL statements form an integral part of the execution section.

Exception Section – This section starts with EXCEPTION, the reserved keyword. And as mentioned above, this section is optional. This section can handle any kind of errors in the program so that finally the PL/SQL Block ends gracefully. Provided the PL/SQL Block comprises of exceptions which cannot be handled, then the Block tends to terminate unexpectedly with errors.

Each statement in the three sections should end with a semicolon. Moreover, PL/SQL blocks can probably be nested within any other PL/SQL blocks. To document code, comments can be used.

What is really great about PL/SQL?

• PL/SQL is SQL’s procedural extension; so it is very simple to write procedural code which includes SQL like it were a single language. Well, a majority of other programming languages need mapping data types, making statements & processing result sets – certain things that demand the knowledge of specific APIs.

• Data types in PL/SQL seem to be a super-set of data types in the database; hence, you rarely are required to do data type conversions while using PL/SQL. You can ask your Java or .NET programmer about how they normally find handling date values that come from a database. For sure, they would only look forward to the simplicity of PL/SQL.

• While coding business logic in the middle tier applications, one business transaction may often be composed of a number of interactions between the database and the application server. And, this adds a remarkable overhead linked with network traffic. In comparison, constructing all business logic as PL/SQL within the database means the client code requires just a single database call for every transaction, thereby minimizing the network overhead considerably.

• Being a multi-platform database, Oracle makes PL/SQL an incredibly portable language. In case your business logic appears to be located in the database, it is a means of protecting yourself from the operating system lock-in.

• Well, programming languages are likely to go in & out of fashion on a continual basis. In the past 35+ years, Oracle databases seem to have remained a major part of enterprise landscape. Moreover, suggesting that any language tends to be a safer bet compared to PL/SQL is somewhat naive. Having your business logic placed in the database in fact makes changing your client layer even simpler especially if you wish to follow fashion.

Learn PL/SQL

You can learn PL/SQL by resorting to the best PL/SQL tutorial available on the internet. There are lots of information and resources on the internet that can make you knowledgeable in the domain. However, if you wish to take a professional approach to learn PL/SQL, you can join a certificate course in PL/SQL offered by a reputed institute. You can find a good one on the internet as well.