Lesson Objectives

• What is PL/SQL?
• What is it good for?
• Basic structure of a PL/SQL program
• Simple examples of common PL/SQL objects
  • Anonymous block
  • Procedure
  • Function
  • Package
  • Trigger
• Tips for working in SQL*Plus

What is PL/SQL?

• PL/SQL = Procedural Language extensions for SQL
• Proprietary language for Oracle database
• 3rd generation procedural language
  • variable definition, assignment
  • conditional processing
    • IF and CASE
  • looping constructs
  • error handling
• Seamless integration of SQL, SQL functions

Why Use PL/SQL?...

• Efficient data manipulation
  • Good for lots of SQL, limited procedural code
  • Tight integration with SQL
    • Embed in PL/SQL statements
    • Lots of work done automatically
      • Open / close queries and cursors
  • Compatible datatypes
    • No conversions
• Not great for lots of complex procedural code
  • However, consider Native Compilation

...Why Use PL/SQL?

• Simple language
  • Learn quickly
  • Only need SQL*Plus to develop & deploy
• Portable within Oracle database
  • No code change to migrate to another Oracle server
    • Even on different platform
• Callable from any client
  • Java, .NET, COBOL
  • Anyone who can connect to DB can call

PL/SQL Program Structure

• PL/SQL is a block-structured language
• 3 possible blocks
• Only BEGIN block required

Anonymous Blocks

• Execute block from SQL*Plus

Continuation of Notes

• This is a full page of notes.
• The slide is hidden.

Compile Errors

Output to SQL*Plus

• Use DBMS_OUTPUT.PUT_LINE to display text on screen

Procedures

Procedure Compile Errors

Continuation of Notes

• This is a full page of notes.
• The slide is hidden.

Functions

Packages...

• Package is a group of PL/SQL objects

...Packages

• Qualify packaged objects with package name

Data Dictionary

• Dictionary records existence of PL/SQL objects

Triggers...

• Code tied to a table
• Executes automatically when DML executes

...Triggers

Tools for Development

• SQL*Plus is a common tool
  • Included with all Oracle database products
• Procedure Builder
  • Sophisticated IDE GUI tool
  • An extra cost product
  • Sophisticated debugging features
    • Breakpoints
    • stepping through code
    • variable examination, etc.
• TOAD
  • Developer and DBA tool

Working in SQL*Plus

• SET ECHO ON
  • Show compiled source on your screen
• SET SERVEROUTPUT ON
  • PUT_LINE calls will be displayed after program returns control to SQL*Plus
• Put in LOGIN.SQL
• SHOW ERRORS
  • Error messages will refer to a line and column number
  • Oracle Error messages manual is available to help

Introduction to PL/SQL Workshops

• Hello World
• Setup