Chapter 1. Overview of PL/SQL Programming Concepts

In the summer of 1970, Dr. E.F. Codd published his paper, A Relational Model of Data for Large Shared Data Banks, for the ACM journal. The projected model was accepted and subsequently an interactive database language, SQL, was developed by IBM Corporation, Inc. In 1979, Relational Software, Inc. stepped into the commercial implementation of SQL as the primary RDBMS language. Later, Relational Software, Inc. transformed into Oracle and since then, its story has been a success.

The Structured Query Language or SQL (pronounced "Sequel") has been used as the primary interactive language for all data operations such as selection, creation, and manipulation. Besides data operations, the language has administrative and monitoring features which ensure data consistency, integrity, and object controllability. By virtue of its multifaceted and versatile behavior in data centric environments, all major RDBMS support SQL as a database interaction language. The universal acceptance of SQL eases the logical usability across the databases (such as MySQL and SQL Server) with minor syntactical modifications.

Over the initial years of exploration, the procedural limitations of SQL were identified which prevented it from being an efficient programming language amongst the fourth generation languages. The head to head competition and demanding expectations of the industry led to the evolution of a procedural version of SQL in the Oracle database family. The first version of PL/SQL was debuted in Oracle 6.0 (in 1991) as an optional procedural extension in SQL* forms. Since its induction, PL/SQL has emerged as a strong and proven database programming language. With the release of Oracle 11g database (in 2007), PL/SQL has successfully stepped into its 11.0 version.

In this chapter, we will tour the Oracle PL/SQL programming concepts to get an overview of PL/SQL block, subprograms, exception handling, and object dependencies. The chapter outlines the benefits and characteristics of the language in the following sections:

• Introduction to PL/SQL

• Oracle development tools—SQL Developer and SQL*Plus

• Recapitulate procedures, functions, packages, and cursors

• Exception handling

• Object dependencies

• Major Oracle supplied packages