Oracle PL/SQL tutorial

Define PL/SQL and understand PL/SQL basics

PL/SQL definition:

What is PL/SQL? PL/SQL is a procedural language extension to Structured Query Language (SQL). The purpose of PL/SQL is to combine database language and procedural programming language. Because PL/SQL allows you to mix SQL statements with procedural constructs, it is possible to use PL/SQL blocks and subprograms to group SQL statements before sending them to Oracle for execution. Without PL/SQL, Oracle must process SQL statements one at a timeand, in a network environment, this can affect traffic flow and slow down response time. (The full PL/SQL definition can be read at Whatis.com).

In an excerpt from their book  "Learning Oracle PL/SQL", authors Bill Pribyl & Steven Feuerstein define PL/SQL as the following:

"Structured Query Language is a language based on set theory, so it is all about manipulating sets of data. SQL consists of a relatively small number of main commands such as SELECT, INSERT, CREATE, and GRANT; in fact, each statement accomplishes what might take hundreds of lines of procedural code to accomplish. That's one reason SQL-based databases are so widely used. The big joke about the name "SQL" is that it is not really structured, is not just for queries, and (some argue) is not even a real language. Nevertheless, it's the closest thing there is to a lingua franca for relational databases such as Oracle's database server, IBM's DB2 and Microsoft's SQL Server.

"A language extension is a set of features that somehow enhance an existing language. This phrase might imply, incorrectly, that PL/SQL is a special version of SQL. That isn't the case, however. PL/SQL is a programming language in its own right; it has its own syntax, its own rules, and its own compiler. You can write PL/SQL programs with or without any SQL statements. Some authors assert that PL/SQL is a superset of SQL, but that's a bit of an overstatement, because only the most common SQL statements can be used easily in a PL/SQL program.

"PL/SQL, then, is a language that is closely related to SQL, but one that allows you to write programs as an ordered series of statements. Or, if you want a definition of PL/SQL that befits a programmer:

"PL/SQL is a procedural (Algol-like) language with support for named program units and packages; much of its syntax is borrowed from Ada, and from Oracle's SQL it derives its datatype space and many built-in functions."

Pribyl & Feuerstein's book, Learning Oracle PL/SQL, can be purchased at the O'Reilly Media web site.

PL/SQL basics

Here are some basic PL/SQL definitions and terms (some taken from an excerpt of the book Oracle 11g: PL/SQL Basics by Michael McLaughlin):

Procedural programming language (like PL/SQL): Allow the programmer to define an ordered series of steps to follow in order to produce a result.

Anonymous- block program: An anonymous-block PL/SQL program that supports batch scripting; requires only the execution section.

Named-block program: A named-block program is a PL/SQL program that delivers stored programming units.

Execution section: Starts with a BEGIN statement and stops at the beginning of the optional EXCEPTION block or the END statement of the program.

Stored procedure: A stored procedure set of SQL statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs.

Trigger: A PL/SQL trigger is a PL/SQL stored procedure that is implicitly started when an INSERT, UPDATE or DELETE statement is issued against an associated table.

Control structures: PL/SQL control structures either check a logical condition and branch program execution, or they iterate over a condition until it is met or they are instructed to exit.

Conditional structures: PL/SQL conditional statements check whether a value meets a condition before taking action. There are two types of conditional structures in PL/SQL, the IF statement and the CASE statement.