PL SQL

1
PL SQL

• Overview

2
Programming in SQL

• In previous classes we described SQL as as
  lacking computational completeness, that it is
  not a procedural language
• Within the last 2-3 years, Oracle has come up
  with language, PL SQL, that is fully procedural

What does it mean for a language to be
procedural? Benefits of Oracle having a
procedural language?
3
Procedural SQL

• Provides DDL and DML
• Additionally, provides ability to manage flow of
  control through commands such as IF, THEN, ELSE,
  GO TO, etc.
• Benefits Can create object such as triggers and
  stored procedures without the use of a higher
  level language within Oracle.

4
PL SQL Example

• A good way to get acquainted with PL/SQL is to
  look at a sample program.
• The program below processes an order for a tennis
  racket. First, it declares a variable of type
  NUMBER to store the quantity of tennis rackets on
  hand.
• Then, it retrieves the quantity on hand from a
  database table named inventory. If the quantity
  is greater than zero, the program updates the
  table and inserts a purchase record into another
  table named purchase_record.
• Otherwise, the program inserts an out-of-stock
  record into the purchase_record table.

5
PL SQL Example
6
PL SQL Overview

• With PL/SQL, you can use SQL statements to
  manipulate Oracle data and flow-of-control
  statements to process the data.
• Moreover, you can declare constants and
  variables, define procedures and functions, and
  trap runtime errors.
• Thus, PL/SQL combines the data manipulating power
  of SQL with the data processing power of
  procedural languages.

7
PL SQL Structure

• PL SQL uses a block structure for declaring
  variables (DECLARE), executing routines (BEGIN)
  and handling exceptions (EXCEPTION)

8
PL SQL Features

• Allows creation of standalone programs (Packages)
  that can be executed like any other program
• Allows creation of Triggers and Stored
  Procedures, which are stored as objects within
  the Oracle database and are executed based on
  some event occurring in the database.