Chapter 15 Introduction to PL/SQL

1
Chapter 15Introduction to PL/SQL
2
Chapter Objectives

• Explain the benefits of using PL/SQL blocks
  versus several SQL statements
• Identify the sections of a PL/SQL block and
  describe their contents
• Identify the mandatory and optional sections of a
  PL/SQL block
• Identify an anonymous block and its use

3
Chapter Objectives

• Describe how to execute a PL/SQL block
• Explain the purpose of a variable
• Explain the difference between a constant and a
  variable
• Identify valid variable names
• List the valid datatypes for PL/SQL variables

4
Chapter Objectives

• Assign a dynamic datatype for a PL/SQL variable
• Initialize a PL/SQL variable
• Use DML statements in a PL/SQL block
• Determine when it is appropriate to use an IF
  statement
• Identify all the clauses of an IF statement, and
  state when they should be used

5
Chapter Objectives

• Create an IF statement
• Identify the purpose of a loop, and name the
  types of loops available in Oracle9i
• Create a basic loop
• Create a FOR loop
• Create a WHILE loop

6
PL/SQL

• Procedure Language SQL
• Advanced 4th generation programming language

7
Advantages of PL/SQL

• Can include error handling and control structures
• Can be stored and used by various application
  programs or users
• Allows for tighter security by granting
  privileges for executing stored procedures rather
  than directly on database object

8
Types of Blocks

• Function
• Procedure
• Anonymous block

9
Function

• Named block that is stored on the Oracle9i server
• Accepts zero or more input parameters
• Returns one value

10
Procedure

• Named block
• Can process several variables
• Returns no values
• Interacts with application program using IN, OUT,
  or INOUT parameters

11
Anonymous Block

• Not stored since it cannot be referenced by a
  name
• Usually embedded in an application program,
  stored in a script file, or manually entered when
  needed

12
Basic Structure of a Block

• Has three sections
• Declarative
• Executable
• Exception-handling
• Executable section is the only required section
  the rest are optional

13
Declarative Section

• Identified by the DECLARE keyword
• Used to define variables and constants referenced
  in the block
• Forward execution variable and constants must
  be declared before they can be referenced

14
Executable Section

• Identified by the BEGIN keyword
• Mandatory
• Can consist of several SQL and/or PL/SQL
  statements

15
Exception-handling Section

• Identified by the EXCEPTION keyword
• Used to display messages or identify other
  actions to be taken when an error occurs
• Addresses errors that occur during a statements
  execution

16
END Keyword

• Used to close a PL/SQL block
• Always followed by a semicolon

17
Example PL/SQL Block
18
Declaring a Variable

• Reserves a temporary storage area in the
  computers memory
• Every variable must have
• A name
• A datatype
• Variables can be initialized

19
Variable Names

• Variable name can consist of up to 30 characters,
  numbers, or special symbols
• Variable name must begin with a character

20
Constants

• Variables that have a value that does not change
  during the execution of the block
• Optional CONSTANT keyword can be used to
  designate a constant in the blocks declarative
  section

21
PL/SQL Datatypes

• Scalar holds a single value
• Composite collection of grouped data treated as
  one unit
• Reference holds pointers to other program items
• Large Object (LOB) holds location of large
  objects

22
PL/SQL Scalar Datatypes
23
Variable Initialization

• Use DEFAULT keyword or () assignment operator
• Variable must be initialized if it is assigned a
  NOT NULL constraint

24
Variable Initialization Examples
25
SELECT Statement

• Requires use of INTO clause to identify variable
  assigned to each data element

26
SELECT Statement Example
27
Cursors

• Implicit cursor created for DML operations or a
  SELECT statement that retrieves only one row of
  results
• Explicit cursor required for SELECT statements
  retrieving more than one row of results

28
Execution Control

• IF statement executes statements based on a
  condition
• Basic loop executes statements until condition
  in EXIT clause is TRUE
• FOR loop uses counter
• WHILE loop executes statements until condition
  is FALSE

29
IF Statement Syntax
30
IF Statement Example
31
Basic Loop Syntax
32
Basic Loop Example
33
FOR Loop Syntax
34
FOR Loop Example
35
WHILE Loop Syntax
36
WHILE Loop Example
37
Nested Loops

• Any type of loop can be nested inside another
  loop
• Execution of the inner loop must be completed
  before control is returned to the outer loop

38
Nested Loop Example