Overview

Introduction xxix

Week 1 at a Glance 1

Day
1. Learning the Basics of PL/SQL 3
2. Selecting a Development Tool 23
3. Writing Declarations and Blocks 39
4. Writing PL/SQL Expressions 67
5. Using Functions, IF Statements, and Loops 101
6. Implementing Loops and GOTOs 125
7. Using Oracle's Built-in Functions 149

Week 1 in Review 183

Week 2 at a Glance 185

DAY
8. Procedures, Packages, Errors, and Exceptions 187
9. Using SQL: INSERT, SELECT, Advanced Declarations, and Tables 209
10. Manipulating Data with Cursors, DELETE, and UPDATE 231
11. Writing Database Triggers 243
12. Using Cursors for Complex Processing 279
13. Using Oracle8 Objects for Object-Oriented Programming 291
14. Debugging Your Code and Preventing Errors 327

Week 2 in Review 353

Week 3 at a Glance 355

Day
15. Exploring Advanced Topics 357
16. Managing Transactions and Locks 385
17. Generating Dynamic SQL 395
18. Writing to Files and the Display 425
19. Managing Database Jobs 451
20. Alerting Other Procedures: The DBMS_ALERT Package 485
21. Implementing J/SQL for Web Applications 499

Week 3 in Review 509

Appendixes

A Answers 511

B Oracle Functions Reference 551

Index 609

Contents

Introduction xxix

Week 1 at a Glance 1

Day 1 Learning the Basics of PL/SQL 3

What Is PL/SQL? 4
Why Learn PL/SQL? 5
SQL, SQL*Plus, PL/SQL: What's the Difference? 5


SQL 5
PL/SQL 6
SQL*Plus 6
What You Need to Finish This Book 8


How to Obtain Personal Oracle 9
Getting Started with PL/SQL 9


PL/SQL Is Block Structured 9
Compiling and Executing a Simple Block 11
What About Some Output? 12
Alternatives to Retyping 14
Writing Your First Function 17
Finding Compilation Errors 18
Displaying the Function's Return Value 20
Can Even This Simple Function Be Useful? 21


Summary 21
Q&A 21
Workshop 22


Quiz 22
Exercises 22

2 Selecting a Development Tool 23

SQL*Plus 24


Environment Commands 24
Session Commands 25
Display Controls for Headers and Footers 26
Display Controls for Columns 27
Bind Variables 28
Substitution Variables 29
Building a Simple Break Report 30
Summary Computations 32


Developer/2000 Tools (Forms, Reports, and Graphs) 35
Oracle Precompiler 36

Summary 36
Q&A 37
Workshop 37


Quiz 37
Exercise 37

3 Writing Declarations and Blocks 39

Datatypes 40


VARCHAR2 41
CHAR 42
NUMBER 44
BINARY_INTEGER 47
PLS_INTEGER 50
DATE 50
BOOLEAN 51
LONG 52
RAW 52
LONG RAW 53
ROWID 53
MSLABEL 54


Block Structure 54


Anonymous Blocks 54
Function and Procedure Blocks 56
Trigger Blocks 58
Nested Blocks 59
Nesting Functions and Procedures 61
Scope Rules 63


Summary 64
Q&A 65
Workshop 65


Quiz 65
Exercises 66

4 Writing PL/SQL Expressions 67

PL/SQL Basics: Operators, Expressions, and Conversion 67
Operators 68


Arithmetic Operators 69
Comparison Operators 72
Logical Operators 79
String Operators 81


Use of Comparison Operators with Strings 82


The Effect of Character Set on String Comparisons 82
The Datatype's Effect on String Comparisons 82
The Effect of Case on String Comparisons 84

Use of Comparison Operators with Dates 84
Expressions 86


Expressions Defined 86
Operator Precedence 87
Types of Expressions 88
Null Values in Expressions 89


Datatype Conversions 93


Implicit Conversion 93
Explicit Conversion 94


Summary 96
Q&A 97
Workshop 98


Quiz 98
Exercise 99

5 Using Functions, IF Statements, and Loops 101

PL/SQL Functions 102


Defining Formal Parameters 104
Return Types 105
Exception Handling 106
Creating a Stored Function 106
Finding Errors 108


The NULL Statement 110


Why Use the NULL Statement? 110
How to Call a Function from PL/SQL With or
Without Parameters 110


PL/SQL Statements 111


The IF Statement 111
The IF...THEN...ELSE Statement 112
Nested IF Statements 114
Using IF...ELSIF 116
Nested IF Versus ELSIF 117
The Formatting of IF Statements 117
Avoiding Common Mistakes When Using IF 118
Looping Statements 119
Reversing the Loop 120
Different Incrementing Through a Loop 121


Summary 122
Q&A 123
Workshop 123


Quiz 123
Exercises 124

6 Implementing Loops and GOTOs 125

Labels and the GOTO Statement 126


Statement Labels 126
The GOTO Statement 126
Scoping Rules for the GOTO Statement 126
An Example of the GOTO Statement in Action 131
Why Use the GOTO Statement? 131


WHILE Loops 132


Examples of WHILE Loops 132


The EXIT and EXIT WHEN Statements 136


Examples Using EXIT and EXIT WHEN 136
Using Labels and EXIT Statements with Loops 140


Simple LOOPs 141


Sample Simple LOOPs 141
Creating a REPEAT...UNTIL Loop 142
An Example of a Simulated REPEAT...UNTIL Loop 143


What Loop Should I Use? 143
Loop Guidelines 144


Style Tips for Loops 145


Summary 146
Q&A 147
Workshop 147


Quiz 147
Exercises 148

7 Using Oracle's Built-in Functions 149

SQL Functions Versus PL/SQL Functions 150
The Major Categories of Functions 150
Experimenting with Functions 155
Conversion Functions 156


Using TO_DATE 156
Using TO_CHAR for Dates 162
Using TO_NUMBER 163
Using TO_CHAR for Numbers 169


DATE Functions 170


SYSDATE 170
The TRUNC Function 171
The ADD_MONTHS Function 173
The NEXT_DAY Function 174
The LAST_DAY Function 176
The MONTHS_BETWEEN Function 177
The NEW_TIME Function 178
The ROUND Function 180

Summary 180
Q&A 180
Workshop 181


Quiz 181
Exercises 182

Week 1 in Review 183

Week 2 at a Glance 185

Day 8 Procedures, Packages, Errors, and Exceptions 187

Procedures 187


Why Use Procedures? 188
Procedures Versus Functions 188
Creating Procedures 188
The RETURN Statement 189
Procedure Dependencies 190
Discovering Dependencies 190
Recompiling a Stored Procedure 190
Re-creating and Modifying Procedures 191
Invoking Stored Procedures 192
Parameters 192
Parameter Definitions 193
Listing Stored Procedure Information 193
Dropping a Stored Procedure 194
Overloading 194
Recursion 194


Packages 195


Why Use Packages? 195
Package Helpful Hints 196
Package Specification 196
The Package Body 197
Creating Packages 197
Calling Package Subprograms 198
Recompiling Packages 199
Private Versus Public Package Objects 199
Variables, Cursors, and Constant Persistence 200
Package State 200
Package Dependency 200


Trapping Errors and Exceptions 200


Exception-Handling Structures 201
SQLCODE and SQLERRM 204
Continue Processing After an Exception 205
Retry After an Exception 206

Reraising an Exception 206
Exception Scope Rules 207
Propagate Exceptions 207


Summary 207
Q&A 208
Workshop 208


Quiz 208
Exercises 208

9 Using SQL: INSERT, SELECT, Advanced Declarations,
and Tables 209

Types of SQL Statements 211
Creating Some Oracle Tables 211
The INSERT Statement 213


Inserting Some Data 214


Singleton SELECT Statement 216


Some SELECT Examples 216


Types of Exceptions 217
The UPDATE and DELETE Statements 218
Methods of Declaring Variables 219


Variables Based on Database Columns 219
Record Variables 220
Declarations at the Package Level 222


PL/SQL Tables 223


Declaring a PL/SQL Table 223
Referencing Values in a PL/SQL Table 223
Using a PL/SQL Table 224
Nested Tables 227


Summary 229
Q&A 229
Workshop 229


Quiz 229
Exercises 230

10 Manipulating Data with Cursors, DELETE, and UPDATE 231

What Is a Cursor? 232
Explicit Cursors 232


Declaring a Cursor 233
Opening the Cursor 233
Fetching Data in a Cursor 234
Closing the Cursor 235
Explicit Cursor Attributes 235


Explicit Cursor Example 237


Automated Explicit Cursors 237

Implicit Cursors 238


Implicit Cursor Attributes 239


Summary 240
Q&A 241
Workshop 241


Quiz 241
Exercise 241

11 Writing Database Triggers 243

What Is a Trigger? 243


An Example of a Trigger 244
Types of Triggers 245
Syntax 248


Uses for Triggers 250


Enforcing a Business Rule 250
Maintaining Data Integrity 254
Enforcing Security 258
Maintaining History 259


Managing Triggers 262


Listing Triggers 263
Viewing Trigger Code 264
Enabling and Disabling Triggers 266


Trigger Limitations 267


Triggers and Mutating Tables 268


Summary 275
Q&A 276
Workshop 277


Quiz 277
Exercises 277

12 Using Cursors for Complex Processing 279

Passing Parameters to Cursors 279


Declaring Cursor Parameters 280
Using Cursor Parameters 281


Scope of Cursor Parameters 281
Cursor Return Clause and Packages 281
Cursor Variables 282


The Cursor Variable Declaration 282
Cursor Usage with Cursor Variables 283
Cursor Variable Assignments 284
Cursor Variables as Arguments 285


The Current Row of Parameter and Cursors 286
Cursor Scoping 286
Cursor Alias 287

Summary 288
Q&A 288
Workshop 288


Quiz 289
Exercise 289

13 Using Oracle8 Objects for Object-Oriented Programming 291

A Quick Primer on Object-Oriented Programming 292


Encapsulation 292
Inheritance 293
Polymorphism 293
Classes, Objects, Attributes, and Methods 293
Advantages over Traditional Methods 294


How Oracle Implements Objects 294


Object Types 295
Object Tables 295
Object Views 295


Defining an Object Type 295


Constructor Methods 298
Accessor Methods 299
Mutator Methods 300


Instantiating and Using an Object 300
Storing and Retrieving Objects 302


Storing Objects as Table Columns 302
Retrieving and Updating Objects in a Table Column 304


Object Tables 305


Storing Objects in an Object Table 307
Retrieving and Updating Objects in an Object Table 309
Deleting Objects from an Object Table 312


Nesting and Sharing Objects 312


Dot Notation 312
Object References 313


The SELF Parameter 317
Overloading 318
Comparing Objects 319


The ORDER Method 320
The MAP Method 322


Limitations of Oracle's Implementation 322
Summary 323
Q&A 323
Workshop 324


Quiz 324
Exercises 325

14 Debugging Your Code and Preventing Errors 327

Syntax Errors 328
Logic Errors 331


Order of Operations 332
Nonterminating Loops 333


The Nontool Approach 333


Setting Up a Test Environment 333
Setting Up Test Data 334
Narrowing Down the Location of a Bug 334


Using Tools to Help in Debugging a Program 337


Writing a Debugging Package 337
Using DBMS_OUTPUT as a Debugging Tool 340


Error Prevention and Planning for Debugging in the Future 344


Defining Requirements and Project Planning 345
Using a Modular Approach to Coding 345
Commenting Your Code 346
Formatting Your Code 347


Summary 350
Q&A 350
Workshop 351


Quiz 351
Exercises 351

Week 2 in Review 353

Week 3 at a Glance 355

Day 15 Exploring Advanced Topics 357

What Is Recursion? 358
Practicing Recursion 358
Why Use Recursion? 360
Review of Large Object Datatypes 360
Using External Files in Your Oracle8 Database 361


Creating the Directory Object 361


Limitations of BFILE 362
The DBMS_LOB Package with BFILE 362


The BFILENAME Function 363
The COMPARE Function 364
The FILECLOSE Procedure 364
The FILECLOSEALL Procedure 364
The FILEEXISTS Function 365
The FILEGETNAME Procedure 365
The FILEISOPEN Function 365
The FILEOPEN Procedure 366
The GETLENGTH Function 366

The INSTR Function 366
The READ Procedure 367
The SUBSTR Function 367


Examples of BFILEs Using the DBMS_LOB Package 368


Accessing BFILEs 368
Comparing Files 369


Working with Locators 371
The DBMS_LOB Package with Internal LOBs 371


The APPEND Procedure 372
The COMPARE Function 372
The COPY Procedure 373
The EMPTY_BLOB Function 374
The EMPTY_CLOB Function 374
The ERASE Procedure 374
The GETLENGTH Function 374
The INSTR Function 375
The READ Procedure 375
The SUBSTR Function 376
The TRIM Procedure 376
The WRITE Procedure 376


Examples of Internal LOBs Using the DBMS_LOB Package 377


Creating the Table 377
Adding Data to the Table 377
Populating the LOBS Table with the COPY Procedure 378
Manipulating Internal LOBs with APPEND and WRITE 379
Analyzing the Contents of an Internal LOB 381
Using TRIM and ERASE to Edit CLOBs 382


Summary 383
Q&A 384
Workshop 384


Quiz 384
Exercise 384

16 Managing Transactions and Locks 385

Types of Transactions 386
Starting a Transaction 386
Ending a Transaction 387
Canceling a Transaction 388
Two-Phase Commit 389
Savepoints 390
Locking 390


Table Locking 390
Row Locking 392
Other Locks 392
Monitoring Locks 392

Summary 393
Q&A 393
Workshop 393


Quiz 393
Exercise 393

17 Generating Dynamic SQL 395

Steps for Using DBMS_SQL 396
Three Types of Statements Processed with DBMS_SQL 397


Using the DBMS_SQL Package with Non-Query DDL
and DML Statements 397
Using the DBMS_SQL Package with Queries 404
Using the DBMS_SQL Package with Anonymous
PL/SQL Blocks 412


Error Handling 418


IS_OPEN 418
LAST_ROW_COUNT 419
LAST_ROW_ID 419
LAST_ERROR_POSITION 420
LAST_SQL_FUNCTION_CODE 421


Fetching Long Data 421


DEFINE_COLUMN_LONG 421
COLUMN_VALUE_LONG 421


Summary 422
Q&A 422
Workshop 423


Quiz 423
Exercises 423

18 Writing to Files and the Display 425

UTL_FILE Definition 426


File Input 426
File Output 430
Closing Files 435
Examples Utilizing the UTL_FILE Package 436


TEXT_IO 440
The DBMS_OUTPUT Package 441


Enabling the DBMS_OUTPUT Package 441
Disabling the DBMS_OUTPUT Package 441
Using PUT with the DBMS_OUTPUT Package 442
The NEW_LINE Procedure 442
Working with PUT_LINE 442
Testing PUT, NEW_LINE, and PUT_LINE 442
GET_LINE and the DBMS_OUTPUT Package 444
Using GET_LINES 445
Exceptions Raised from the DBMS_OUTPUT Package 446

Summary 447
Q&A 447
Workshop 448


Quiz 448
Exercises 449

19 Managing Database Jobs 451

The DBMS_JOB Package 452
Background Processes 452
Execution of Jobs 453


Submitting Jobs to the Job Queue with SUBMIT 453
Submitting Jobs to the Job Queue with ISUBMIT 455
Using RUN to Execute Jobs Immediately 456
The Job Environment 457


Viewing Jobs 459


Samples for Viewing Jobs 461


Job Management 461


Removing a Job 461
Altering a Job 462
Importing and Exporting Jobs 463
Handling Broken Jobs 463


Hands-On Practice in Job Management 464


Creating Procedures to Submit as Jobs 464
Submitting All Jobs to the Job Queue 466
Running All Three Jobs Immediately 466
Viewing Information About the Jobs 467
Altering the Job Running the Procedure HELLO 467
Removing the Hello Job 468


The DBMS_PIPE Package 469


Public Versus Private Pipes 470
Steps in Using Pipes 470
The Functions and Procedures of DBMS_PIPE 472
An Example of Using Pipes 476
Other Pipe Functions and Procedures 479
Other Uses for Pipes 481


Summary 481
Q&A 481
Workshop 482


Quiz 482
Exercises 483

20 Alerting Other Procedures: The DBMS_ALERT Package 485

Using SIGNAL to Issue an Alert 486
Registering for an Alert 487
Waiting for a Specific Alert 488

Waiting for Any Registered Alert 488
Removing One Alert 489
Removing All Alerts 489
Polling versus Events with the SET_DEFAULTS Procedure 489
Alerts Versus Pipes 490
A Demonstration of the DBMS_ALERT Package 490


Creating the Backup Database 491
Creating the Trigger to Signal an Alert 492
Waiting for the Alert 493


Summary 497
Q&A 497
Workshop 497


Quiz 498
Exercises 498

21 Implementing J/SQL for Web Applications 499

J/SQL 500


J/SQL Coding Standards 501


JDBC 504
PL/SQL Versus Java 504
The World Wide Web and SQL 505
Summary 506
Q&A 507
Workshop 507


Quiz 507

Week 3 in Review 509

Appendixes

A Answers 511

Day 1 512

Quiz 512
Exercises 513


Day 2 513


Quiz 513
Exercise 513


Day 3 514


Quiz 514
Exercises 514


Day 4 517


Quiz 517
Exercise 518


Day 5 518


Quiz 518
Exercises 519

Day 6 521

Quiz 521
Exercises 521


Day 7 523


Quiz 523
Exercises 523


Day 8 526


Quiz 526
Exercises 527


Day 9 527


Quiz 527
Exercises 528


Day 10 528


Quiz 529
Exercise 529


Day 11 530


Quiz 530
Exercises 530


Day 12 532


Quiz 532
Exercise 532


Day 13 533


Quiz 533
Exercises 534


Day 14 536


Quiz 536
Exercises 537


Day 15 538


Quiz 538
Exercise 538


Day 16 539


Quiz 539
Exercise 539


Day 17 539


Quiz 539
Exercises 540


Day 18 543


Quiz 543
Exercises 544


Day 19 546


Quiz 546
Exercises 546


Day 20 549


Quiz 549
Exercises 549


Day 21 550


Quiz 550

B Oracle Functions Reference 551

Character Functions 552


ASCII 552
CHR 553
CONCAT 553
INITCAP 554
INSTR 554
INSTRB 555
LENGTH 556
LENGTHB 557
LOWER 557
LPAD 557
LTRIM 558
NLS_INITCAP 559
NLS_LOWER 560
NLS_UPPER 560
NLSSORT 560
REPLACE 561
RPAD 562
RTRIM 562
SOUNDEX 563
SUBSTR 565
SUBSTRB 566
TRANSLATE 566
UPPER 567


Number Functions 567


ABS 567
VACOS 567
ASIN 568
ATAN 568
ATAN2 568
EIL 569
COS 569
COSH 569
EXP 570
FLOOR 570
LN 570
LOG 571
MOD 571
POWER 572
ROUND 572
SIGN 573
SIN 573
SINH 574

SQRT 574
TAN 574
TANH 575
TRUNC 575


Date Functions 575


ADD_MONTHS 575
LAST_DAY 576
MONTHS_BETWEEN 577
NEW_TIME 577
NEXT_DAY 578
ROUND 579
SYSDATE 580
TRUNC 581


Conversion Functions 581


CHARTOROWID 581
CONVERT 582
HEXTORAW 583
RAWTOHEX 583
ROWIDTOCHAR 583
TO_CHAR (with Dates) 584
TO_CHAR (with Labels) 584
TO_CHAR (with Numbers) 585
TO_DATE 585
TO_LABEL 588
TO_MULTI_BYTE 589
TO_NUMBER 589
TO_SINGLE_BYTE 592


Grouping Functions 592


AVG 592
COUNT 593
GLB 593
LUB 594
MAX 594
MIN 594
STDDEV 595
SUM 595
VARIANCE 596


Miscellaneous Functions 597


BFILENAME 597
DECODE 598
DUMP 598
EMPTY_BLOB 600
EMPTY_CLOB 600

GREATEST 601
GREATEST_LB 602
LEAST 602
LEAST_LB 602
NLS_CHARSET_ID 603
NLS_CHARSET_NAME 604
NVL 604
SQLCODE 605
SQLERRM 605
UID 606
USER 606
USERENV 607
VSIZE 608

Index 609

Introduction

Welcome to Teach Yourself PL/SQL in 21 Days. Your purchasing this book indicates that you have an interest in learning the PL/SQL language. PL/SQL is Oracle's relational database procedural programming language. It allows you to develop powerful and complex programs to access and manipulate data in the Oracle database. We have attempted to include as many examples of PL/SQL code as possible to illustrate PL/SQL features.

This book is organized to teach you the major components of Oracle's procedural language in 21 days, a chapter per day. Each chapter covers a specific topic in PL/SQL and should take approximately 3-5 hours to complete. Of course, this time depends on your own pace. This book introduces you to concepts and practical applications of them in your PL/SQL programs. As the authors of this book, we strongly encourage you to practice what you read. Go ahead and type in the listings in your local Oracle database and experience PL/SQL for yourself. Better yet, copy them from the CD-ROM at the end of the book. You will gain a much deeper understanding of PL/SQL by practicing as you go along. Feel free to experiment on your own with any concepts presented, as this will reinforce what you learn.

We have made several assumptions about you, the reader. We have assumed that you are familiar, though not a guru, with SQL and the Oracle database. Specifically, we feel you should have a working knowledge of how SQL works. Additionally, you should have some knowledge about Oracle database objects such as tables and other schema objects. If needed, you might want to read Teach Yourself SQL in 21 Days prior to reading this book. We have also assumed that you want to learn some practical applications of PL/SQL and not just the syntax of the language. As such, we have incorporated examples and notes to meet this goal.

What This Book Is About

The beginning of this book lays the foundation on which you will build PL/SQL programs. We discuss the development environment for writing PL/SQL as well as the fundamental structures of PL/SQL. In the first week, you will discover the building blocks of PL/SQL, such as expressions, various iterations, and built-in functions. After this foundation is built, in the second week, we will move directly into teaching you more advanced PL/SQL. Week 2 teaches you how to build PL/SQL programs with procedures, packages, error-handling routines, composite database structures, triggers, and cursor processing. Finally, the third week covers recursion, dynamic SQL, and transaction processing. When you complete this book, you will be able to develop your own PL/SQL programs. You will have the knowledge to store these programs in the database and execute them.

Many of the chapters build on earlier ones. As the book progresses from chapter to chapter, the topics covered will be drilled down in more detail and complexity. So it is advisable to start at the beginning and work through all 21 days. If you are already familiar with PL/SQL, you can go to the chapters that are of specific interest to you.

You should be aware of the version of your own system in regards to the code that is used here. Although Oracle8 has been released and included in the development of this book, most of the code here is backward compatible with earlier versions of Oracle. We also mention new objects and features of Oracle8 so that those of you who have already upgraded can take advantage of these enhancements.

Is This Book for Me?

This book is developed for the beginning-to-intermediate programmer. Certainly, as a resource, this book is going to be beneficial to the more experienced developer as well. The details covered in this book will allow the novice to get up to speed quickly and start developing PL/SQL applications immediately.

Conventions Used in This Book

This book uses the following typeface conventions:

• New terms are introduced by the New Term icon and appear in italic.
• All code in the listings appears in monospace.
• The Input icon and Output icon also identify the nature of the code.
• The combination Input/Output icon is used with code listings that have both input and output. In these listings, the code that you type in (input) appears in boldface monospace, and the output appears in standard monospace without bold.
• Many code-related terms within the text also appear in monospace.
• Placeholders in code appear in italic monospace.
• When a line of code is too long to fit on one line of this book, it is broken at a convenient place and continued to the next line. A code continuation character (Â) precedes the continuation of a line of code. (You should type a line of code that has this character as one long line without breaking it.)

• Paragraphs that begin with the Analysis icon explain the preceding code example.
• The syntax icon identifies syntax statements.

Special design features enhance the text material:

• Notes
• Tips
• Warnings
• Do/Don'ts