PL/SQL Tutorial

Table of Contents

PL/SQL Overview ..................................................................... 1

Features of PL/SQL ................................................................................... 1

Advantages of PL/SQL .............................................................................. 2

Environment ............................................................................. 3

Step 1 ........................................................................................................ 3

Step 2 ........................................................................................................ 4

Step 3 ........................................................................................................ 4

Step 4 ........................................................................................................ 5

Step 5 ........................................................................................................ 6

Step 6 ........................................................................................................ 6

Step 7 ........................................................................................................ 7

Step 8 ........................................................................................................ 7

Step 9 ........................................................................................................ 8

Step 10 ...................................................................................................... 9

Step 11 .................................................................................................... 10

Final Step................................................................................................. 11

Text Editor ............................................................................................... 12

Basic Syntax .......................................................................... 13

The 'Hello World' Example: ...................................................................... 13

The PL/SQL Identifiers ............................................................................ 14

The PL/SQL Delimiters ............................................................................ 14

The PL/SQL Comments ........................................................................... 15

PL/SQL Program Units ............................................................................ 15

Data Types ............................................................................. 17

PL/SQL Scalar Data Types and Subtypes ............................................... 17

PL/SQL Numeric Data Types and Subtypes ............................................ 18

PL/SQL Character Data Types and Subtypes ......................................... 19

PL/SQL Boolean Data Types ................................................................... 19

PL/SQL Datetime and Interval Types ...................................................... 19

PL/SQL Large Object (LOB) Data Types ................................................. 20

PL/SQL User-Defined Subtypes .............................................................. 21

iii

NULLs in PL/SQL .................................................................................... 21

Variables ................................................................................ 22

Variable Declaration in PL/SQL ............................................................... 22

Initializing Variables in PL/SQL ................................................................ 23

Variable Scope in PL/SQL ....................................................................... 23

Assigning SQL Query Results to PL/SQL Variables ................................ 24

Constants ............................................................................... 26

Declaring a Constant ............................................................................... 26

The PL/SQL Literals ................................................................................ 27

Operators ............................................................................... 28

Arithmetic Operators ................................................................................ 28

Example: .................................................................................................. 29

Relational Operators ................................................................................ 29

Example: .................................................................................................. 29

Comparison Operators ............................................................................ 30

LIKE Operator: ......................................................................................... 31

BETWEEN Operator: ............................................................................... 31

IN and IS NULL Operators: ...................................................................... 32

Logical Operators .................................................................................... 33

Example: .................................................................................................. 33

PL/SQL Operator Precedence ................................................................. 33

Example: .................................................................................................. 34

Conditions .............................................................................. 35

Syntax: ..................................................................................................... 36

Flow Diagram: .......................................................................................... 36

Example 1: ............................................................................................... 37

Example 2: ............................................................................................... 37

Syntax: ..................................................................................................... 38

Flow Diagram: .......................................................................................... 38

Example: .................................................................................................. 39

Syntax: ..................................................................................................... 40

Example: .................................................................................................. 40

Syntax: ..................................................................................................... 40

Flow Diagram: .......................................................................................... 41

Example: .................................................................................................. 41

Syntax: ..................................................................................................... 42

Flow Diagram: .......................................................................................... 42

Example: .................................................................................................. 42

Syntax: ..................................................................................................... 43

iii

Example: .................................................................................................. 43

Loops ..................................................................................... 44

Syntax: ..................................................................................................... 45

Example: .................................................................................................. 45

Syntax: ..................................................................................................... 46

Example: .................................................................................................. 46

Syntax: ..................................................................................................... 47

Example: .................................................................................................. 47

Reverse FOR LOOP Statement ............................................................... 48

Example: .................................................................................................. 49

Labeling a PL/SQL Loop .......................................................................... 50

The Loop Control Statements .................................................................. 51

Syntax: ..................................................................................................... 51

Flow Diagram: .......................................................................................... 51

Example: .................................................................................................. 52

The EXIT WHEN Statement .................................................................... 52

Syntax: ..................................................................................................... 52

Example: .................................................................................................. 52

Syntax: ..................................................................................................... 53

Flow Diagram: .......................................................................................... 53

Example: .................................................................................................. 54

Syntax: ..................................................................................................... 54

Flow Diagram: .......................................................................................... 55

Example: .................................................................................................. 55

Restrictions with GOTO Statement .......................................................... 56

Strings .................................................................................... 57

Declaring String Variables ....................................................................... 57

PL/SQL String Functions and Operators ................................................. 58

Example 1 ................................................................................................ 60

Example 2 ................................................................................................ 60

Arrays..................................................................................... 62

Creating a Varray Type ............................................................................ 62

Example 1 ................................................................................................ 63

Procedures ............................................................................. 65

Parts of a PL/SQL Subprogram ............................................................... 65

Creating a Procedure ............................................................................... 66

Example: .................................................................................................. 66

Executing a Standalone Procedure ......................................................... 67

Deleting a Standalone Procedure ............................................................ 67

iii

Parameter Modes in PL/SQL Subprograms ............................................ 67

IN & OUT Mode Example 1 ..................................................................... 68

IN & OUT Mode Example 2 ..................................................................... 68

Methods for Passing Parameters ............................................................. 69

POSITIONAL NOTATION ....................................................................... 69

NAMED NOTATION ............................................................................... 69

MIXED NOTATION ................................................................................. 69

Functions ............................................................................... 70

Example: .................................................................................................. 71

Calling a Function .................................................................................... 71

Example: .................................................................................................. 72

PL/SQL Recursive Functions ................................................................... 72

Cursors .................................................................................. 74

Implicit Cursors ........................................................................................ 74

Example: .................................................................................................. 75

Explicit Cursors ........................................................................................ 76

Declaring the Cursor ................................................................................ 76

Opening the Cursor ................................................................................. 76

Fetching the Cursor ................................................................................. 76

Closing the Cursor ................................................................................... 77

Example: .................................................................................................. 77

Records .................................................................................. 78

Table-Based Records .............................................................................. 78

Cursor-Based Records ............................................................................ 79

User-Defined Records ............................................................................. 79

Defining a Record .................................................................................... 80

Accessing Fields ...................................................................................... 80

Records as Subprogram Parameters ...................................................... 81

Exceptions ............................................................................. 83

Syntax for Exception Handling ................................................................. 83

Example ................................................................................................... 83

Raising Exceptions .................................................................................. 84

User-defined Exceptions .......................................................................... 84

Example: .................................................................................................. 85

Pre-defined Exceptions ............................................................................ 85

Triggers .................................................................................. 88

Benefits of Triggers .................................................................................. 88

Creating Triggers ..................................................................................... 89

Example: .................................................................................................. 89

iii

Triggering a Trigger ................................................................................. 90

Packages ............................................................................... 92

Package Specification ............................................................................. 92

Package Body .......................................................................................... 93

Using the Package Elements ................................................................... 93

Example: .................................................................................................. 93

THE PACKAGE SPECIFICATION: ......................................................... 94

CREATING THE PACKAGE BODY: ...................................................... 94

USING THE PACKAGE: ......................................................................... 95

Collections ............................................................................. 96

Index-By Table ......................................................................................... 97

Example: .................................................................................................. 97

Example: .................................................................................................. 98

Nested Tables .......................................................................................... 98

Example: .................................................................................................. 99

Example: .................................................................................................. 99

Collection Methods ................................................................................ 100

Collection Exceptions ............................................................................ 101

Transactions ........................................................................ 103

Starting an Ending a Transaction ........................................................... 103

Committing a Transaction ...................................................................... 104

Rolling Back Transactions ..................................................................... 104

Savepoints ............................................................................................. 104

Automatic Transaction Control ............................................................... 105

Date & Time ......................................................................... 106

Field Values for Datetime and Interval Data Types ................................ 106

The Datetime Data Types and Functions .............................................. 107

Examples: .............................................................................................. 109

The Interval Data Types and Functions ................................................. 110

DBMS Output ....................................................................... 111

DBMS_OUTPUT Subprograms ............................................................. 111

Example: ................................................................................................ 112

Object Oriented .................................................................... 113

Instantiating an Object ........................................................................... 114

Member Methods ................................................................................... 114

Using Map method ................................................................................. 115

Using Order method .............................................................................. 116

Inheritance for PL/SQL Objects: ............................................................ 117

Abstract Objects in PL/SQL ................................................................... 119