PL/SQL Tutorial - Lesson 1: What is PL/SQL?

Introduction

This tutorial is a very basic introduction to PL/SQL, the purpose is to provide you all the knowledge that you could need to start developing scripts in PL/SQL. If you want to  go more in depth and get more information about how PL/SQL works, sentence structure of functions you can go to the resources section.

Finally, before we start I’m sharing the links of the current version of the Database and tools used during the development of this tutorial

• Oracle SQL Developer
• Oracle Database Express edition 11g 

What is PL/SQL?

PL/SQL stands for Procedural Language/Structured Query Language. It has been developed by Oracle and allows user to mix SQL statements with procedural constructs. Some of the advantages of PL/SQL are:

• Integration with SQL. In PL/SQL you can execute all the SQL data manipulation, cursor control, and transaction control commands as well as all the SQL functions, operators, and pseudo columns. Also it supports SQL data types, reducing the need to convert data passed between your application and the database.
• Better performance: PL/SQL integrates all the operations in a single block, reducing the number of calls made to the database and network traffic (refer to fig. 1).
• Reusability: Code can be reused across multiple applications.
• Error handling

Fig. 1

PL/SQL Block Structure

All programming in PL/SQL is structured in logical blocks that group related declarations and statements. Declarations are local to the block and cease to exist when the block completes.

The block has three basic parts that must be declared in the following order:

• A declarative part (DEFINE), to define the variables to be used inside the block.
• Executable part (BEGIN...END),
• Exception-handling part (EXCEPTION)  that handles error conditions

Fig. 2

Declare and exception parts are optional. In addition, you can nest blocks in the executable (Begin) and exception-handling parts.

Here is an example of how a PL/SQL block looks like:

PL/SQL Block Types

PL/SQL Blocks can be classified in three types:

• Anonymous: Anonymous blocks are unnamed, they are not stored in the database and are compiled each time they’re executed.
• Procedures: Procedures are stored in the database, and can be executed whenever you want.
• Functions: Functions, like procedures, can be executed any time and stored in the database.

I hope you have found useful this introduction about PL/SQL. In the next session, we will learn about Variables.

Resources:

• For more information about PL/SQL visit Oracle Docs: Overview of PL/SQL