Oracle Internal & Oracle Academy Use Only

462 Pages·2009·3.06 MB·English

Oracle Database 11g: PL/SQL
Fundamentals y
m
Student Guide e
d
a
c
A

e
l
c
a
r
O
y

l
& n
O
l
a

D49990GC11 n e
r s
Edition 1.1 e
U
t
April 2009 n
I
D59428
e
l
c
a
r
O
Authors Copyright © 2009,Oracle. All rights reserved.
Tulika Sri vastava Disclaimer

LauranK.Serhal This document contains proprietary information and is protected by copyright and other intellectual

property laws. You may copy and print this document solely for your own use in an Oracle training
Technica l Contributors course. The document may not be modified or altered in any way. Except where your use constitutes
"fair use" under copyright law, you may not use, share, download, upload, copy, print, display,
and Reviewers
perform, reproduce, publish, license, post, transmit, or distribute this document in whole or in part
without the express authorization of Oracle.
Tom Best

Christoph Burandt The information contained in this document is subject to change without notice. If you find any
problems in the document, please report them in writing to: Oracle University, 500 Oracle Parkway,
YantiChang
Redwood Shores, California 94065 USA. This document is not warranted to be error-free.
Ashita Dh ir
Peter Driv er Restricted Rights Notice

Gerlinde Frenzen If this documentation is delivered to the United States Government or anyone using the

documentation on behalf of the United States Government, the following notice is applicable:
Nancy Greenberg

Chaitanya Kortamaddi U.S. GOVERNMENT RIGHTS
Tim Leblanc The U.S. Government’s rights to use, modify, reproduce, release, perform, display, or disclose these
training materials are restricted by the terms of the applicableOracle license agreement and/or the
Wendy Lo applicable U.S. Government contract.
Bryan Roberts
Trademark Notice
AbhishekX Singh
PujaSingh Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be
trademarks of their respective owners.
Lex.Van.Der Werff
y
Editors m
Aju Kumar e
d
Raj Kumar
a
Graphic Designer c
A
Priya Saxena

e
Publishers l
c
Syed Ali a
Giri Venugopal r
O
y

l
& n
O
l
a

n e
r s
e
U
t
n
I

e
l
c
a
r
O
Contents

I Introduction

Lesson Objectives I-2

Course Objectives I-3

Human Resources (HR) Schema for This Course I-4

Course Agenda I-5
Class Account Information I-6

Appendixes Used in This Course I-7
PL/SQL Development Environments I-8
What Is Oracle SQL Developer? I-9
Coding PL/SQL in SQL*Plus I-10
Coding PL/SQL in Oracle JDeveloper I-11
Starting SQL Developer and Creating a Database Connection I-12
y
Creating Schema Objects I-13
m
Using the SQL Worksheet I-14
e
Executing SQL Statements I-16
d
Saving SQL Scripts I-17 a
Executing Saved Script Files: Method 1 I-18 c
A
Executing Saved SQL Scripts: Method 2 I-19

Oracle 11g SQL and PL/SQL Documentation I-20 e
l
Summary I-21 c
Practice I Overview: Getting Started I-22 a
r
O
y

l
& n
1 Introduction to PL/SQL
O
Objectives 1-2 l
a

About PL/SQL 1-3 n e
PL/SQL Environmrent 1-5 s
e
U
Benefits of PL/SQL 1-6
t
n
PL/SQL Block Structure 1-9
I
Bloc k Types 1-11
e
lProgram Constructs 1-13
c
a Create an Anonymous Block 1-15
r Execute an Anonymous Block 1-16
O
Test the Output of a PL/SQL Block 1-17
Quiz 1-19
iii
S ummary 1-20

Practice 1: Overview 1-21

2 Declaring PL/SQL Variables

O bjectives 2-2

Use of Variables 2-3

Requirements for Variable Names 2-4

H andling Variables in PL/SQL 2-5

Declaring and Initializing PL/SQL Variables 2-6

D elimiters in String Literals 2-8
T ypes of Variables 2-9
Guidelines for Declaring and Initializing PL/SQL Variables 2-11
Guidelines for Declaring PL/SQL Variables 2-12
Scalar Data Types 2-13
Base Scalar Data Types 2-14
Declaring Scalar Variables 2-18
%TYPE Attribute 2-19 y
m
Declaring Variables with the %TYPE Attribute 2-21
e
Declaring Boolean Variables 2-22
d
Bind Variables 2-23
a
Printing Bind Variables 2-25 c
LOB Data Type Variables 2-27 A

Composite Data Types 2-28 e
Quiz 2-29 l
c
Summary 2-30 a
r
Practice 2: Overview 2-31
O
y

l
3 Writing Executable Statements & n
O
Objectives 3-2 l
a
Lexical Units in a PL/SQL Block 3-3
n e
PL/SQL Block Syntax anrd Guidelines 3s-5
e
U
Commenting Code 3-6
t
n
SQL Functions in PL/SQL 3-7
I
SQL Functi ons in PL/SQL: Examples 3-8
e
Using Sequences in PL/SQL Expressions 3-9
l
c
Data Type Conversion 3-10
a
rNested Blocks 3-13
O
Nested Blocks: Example 3-14
Variable Scope and Visibility 3-15
iv
Qualify an Identifier 3-17

Determining Variable Scope: Example 3-18

Operators in PL/SQL 3-20
Operators in PL/SQL: Examples 3-21

Programming Guidelines 3-22

Indenting Code 3-23

Quiz 3-24

Summary 3-25

Practice 3: Overview 3-26

4 Interacting with the Oracle Database Server
Objectives 4-2
SQL Statements in PL/SQL 4-3
SELECT Statements in PL/SQL 4-4
Retrieving Data in PL/SQL: Example 4-8
Retrieving Data in PL/SQL 4-9
Naming Conventions 4-10
y
Using PL/SQL to Manipulate Data 4-12
m
Inserting Data: Example 4-13
e
Updating Data: Example 4-14
d
Deleting Data: Example 4-15
a
Merging Rows 4-16 c
SQL Cursor 4-18 A
SQL Cursor Attributes for Implicit Cursors 4-20
e
Quiz 4-22 l
c
Summary 4-23 a
Practice 4: Overview 4-24 r
O
y

l
5 Writing Control Structures & n
Objectives 5-2 O
l
a
Controlling Flow of Execution 5-3
n e
IF Statement 5-r4 s
e
Simple IF Statement 5-6 U
t
n
IF THEN ELSE Statement 5-7
I
IF ELSIF ELSE Clause 5-8
e
lNULL Value in IF Statement 5-9
c
a CASE Expressions 5-10
r CASE Expressions: Example 5-11
O
Searched CASE Expressions 5-12
CASE Statement 5-13
v
H andling Nulls 5-14

Logic Tables 5-15

B oolean Conditions 5-16
I terative Control: LOOP Statements 5-17

Basic Loops 5-18

W HILE Loops 5-20

WHILE Loops: Example 5-21

F OR Loops 5-22

FOR Loops: Example 5-24

F OR Loops 5-25

Guidelines for Loops 5-26

Nested Loops and Labels 5-27
PL/SQL CONTINUE Statement 5-29
PL/SQL CONTINUE Statement: Example 5-30
Quiz 5-32
Summary 5-33
Practice 5: Overview 5-34
y
m
6 Working with Composite Data Types
e
Objectives 6-2 d
Composite Data Types 6-3 a
c
PL/SQL Records 6-5
A
%ROWTYPE Attribute 6-6

e
Advantages of Using the %ROWTYPE Attribute 6-8
l
Creating a PL/SQL Record 6-9 c
a
Creating a PL/SQL Record: Example 6-10
r
PL/SQL Record Structure 6-11 O
y
%ROWTYPE Attribute: Example 6-12 l
& n
Inserting a Record by Using %ROWTYPE 6-13
O
l
Updating a Row in a Table by Usaing a Record 6-14

n e
INDEX BY Tables or Associative Arrays 6-15
r s
Creating an INDEX BYe Table 6-16 U
t
INDEX BY Tablen Structure 6-18
Creating an IINDEX BY Table 6-19

e
Using INDEX BY Table Methods 6-20
l
c
INDEX BY Table of Records 6-21
a
rINDEX BY Table of Records: Example 6-23
O
Nested Tables 6-24
VARRAY 6-26
vi
Quiz 6-27

Summary 6-28

Practice 6: Overview 6-29

7 Using Explicit Cursors

Objectives 7-2

Cursors 7-3

Explicit Cursor Operations 7-4

Controlling Explicit Cursors 7-5

Declaring the Cursor 7-7

Opening the Cursor 7-9
Fetching Data from the Cursor 7-10
Closing the Cursor 7-13
Cursors and Records 7-14
Cursor FOR Loops 7-15
Explicit Cursor Attributes 7-17
%ISOPEN Attribute 7-18
y
%ROWCOUNT and %NOTFOUND: Example 7-19
m
Cursor FOR Loops Using Subqueries 7-20
e
Cursors with Parameters 7-21
d
FOR UPDATE Clause 7-23 a
WHERE CURRENT OF Clause 7-25 c
A
Cursors with Subqueries: Example 7-26

Quiz 7-27 e
l
Summary 7-28 c
a
Practice 7: Overview 7-29
r
O
y
8 Handling Exceptions

l
& n
Objectives 8-2
O
Example of an Exception l8-3
a

Handling Exceptions nwith PL/SQL 8e-5
Handling Exceptiorns 8-6 s
e
U
Exception Types 8-7
t
n
Trapping Exceptions 8-8
I
Guid elines for Trapping Exceptions 8-10
e
lTrapping Predefined Oracle Server Errors 8-11
c
a Trapping Non-Predefined Oracle Server Errors 8-14
r Non-Predefined Error 8-15
O
Functions for Trapping Exceptions 8-16
Trapping User-Defined Exceptions 8-18
vii
P ropagating Exceptions in a Subblock 8-20

RAISE_APPLICATION_ERROR Procedure 8-21

Q uiz 8-24
S ummary 8-25

Practice 8: Overview 8-26

9 Creating Stored Procedures and Functions

Objectives 9-2

P rocedures and Functions 9-3

Differences Between Anonymous Blocks and Subprograms 9-4

P rocedure: Syntax 9-5
P rocedure: Example 9-6
Invoking the Procedure 9-8
Function: Syntax 9-9
Function: Example 9-10
Invoking the Function 9-11
Passing a Parameter to the Function 9-12
y
Invoking the Function with a Parameter 9-13
m
Quiz 9-14
e
Summary 9-15
d
Practice 9: Overview 9-16
a
c
Appendix A: Practice Solutions A

e
Appendix B: Table Descriptions and Data l
c
a
Appendix C: Using SQL Developer r
O
Objectives C-2 y

l
What Is Oracle SQL Developer? C-3 & n
Specifications of SQL Developer C -4 O
l
a
Installing SQL Developer C-5
n e
SQL Developer 1.2 Interfrace C-6 s
e
Creating a Database Connection C-U7
t
Browsing Databanse Objects C-10
I
Creating a S chema Object C-11
e
Creating a New Table: Example C-12
l
c
Using the SQL Worksheet C-13
a
Executing SQL Statements C-16
r
O
Saving SQL Scripts C-17
Executing Saved Script Files: Method 1 C-18
Executing Saved Script Files: Method 2 C-19
viii
Executing SQL Statements C-20

Formatting the SQL Code C-21

Using Snippets C-22
Using Snippets: Example C-23

Using SQL*Plus C-24

Debugging Procedures and Functions C-25

Database Reporting C-26

Creating a User-Defined Report C-27

Search Engines and External Tools C-28

Setting Preferences C-29

Specifications of SQL Developer 1.5.3 C-30
Installing SQL Developer 1.5.3 C-31
SQL Developer 1.5.3 Interface C-32
Summary C-34

Appendix D: Using SQL*Plus
Objectives D-2
SQL and SQL*Plus Interaction D-3 y
m
SQL Statements Versus SQL*Plus Commands D-4
e
Overview of SQL*Plus D-5
d
Logging In to SQL*Plus D-6
a
Changing the Settings of SQL*Plus Environment D-7
c
Displaying Table Structure D-8 A
SQL*Plus Editing Commands D-10
e
Using LIST, n, and APPEND D-12 l
c
Using the CHANGE Command D-13 a
SQL*Plus File Commands D-14 r
O
Using the SAVE, START, and EDIT Commands D-1y5

l
SERVEROUTPUT Command D-1&7 n
O
Using the SQL*Plus SPOOLl Command D-18
a

Using the AUTOTRACEn Command De-19
Summary D-20 r s
e
U

t
n
Appendix E: Using JDeveloper
I
Orac le JDeveloper E-2
e
lConnection Navigator E-3
c
a Applications - Navigator E-4
r Structure Window E-5
O
Editor Window E-6
Deploying Java Stored Procedures E-7
ix
P ublishing Java to PL/SQL E-8

Creating Program Units E-9

C ompiling E-10
R unning a Program Unit E-11

Dropping a Program Unit E-12

D ebugging PL/SQL Programs E-13

Setting Breakpoints E-16

Stepping Through Code E-17

E xamining and Modifying Variables E-18

App endix F: REF Cursors
Cursor Variables F-2
Using Cursor Variables F-3
Defining REF CURSOR Types F-4
Using the OPEN-FOR, FETCH,and CLOSE Statements F-7
Example of Fetching F-10

y
Additional Practices
m

e
Additional Practice Solutions
d

a
Index c
A

e
l
c
a
r
O
y

l
& n
O
l
a

n e
r s
e
U
t
n
I

e
l
c
a
r
O
x