Oracle Database 12c PL-SQL programming

1194 Pages·2014·26.026 MB·English

by  McLaughlin, Michael

Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM /
®
Oracle Database 12c
PL/SQL Programming
Michael McLaughlin
New York Chicago San Francisco
Athens London Madrid Mexico City
Milan New Delhi Singapore Sydney Toronto
00-FM.indd 1 12/17/13 4:07 PM
Copyright © 2014 by McGraw-Hill Education (Publisher). All rights reserved. Printed in the United States of America.
Except as permitted under the Copyright Act of 1976, no part of this publication may be reproduced or distributed in any
form or by any means, or stored in a database or retrieval system, without the prior written permission of publisher, with
the exception that the program listings may be entered, stored, and executed in a computer system, but they may not be
reproduced for publication.
ISBN: 978-0-07-181244-3
MHID: 0-07-181244-X
e-book conversion by Cenveo® Publisher Services
Version 1.0
The material in this e-book also appears in the print version of this title: ISBN: 978-0-07-181243-6,
MHID: 0-07-181243-1
McGraw-Hill Education e-books are available at special quantity discounts to use as premiums and sales
promotions, or for use in corporate training programs. To contact a representative, please visit the Contact Us
pages at www.mhprofessional.com.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. All other trademarks are the property of their
respective owners, and McGraw-Hill Education makes no claim of ownership by the mention of products that contain
these marks.
Screen displays of copyrighted Oracle software programs have been reproduced herein with the permission of Oracle
Corporation and/or its affiliates.
Information has been obtained by McGraw-Hill Education from sources believed to be reliable. However, because of the
possibility of human or mechanical error by our sources, McGraw-Hill Education, or others, McGraw-Hill Education
does not guarantee the accuracy, adequacy, or completeness of any information and is not responsible for any errors or
omissions or the results obtained from the use of such information.
Oracle Corporation does not make any representations or warranties as to the accuracy, adequacy, or completeness of any
information contained in this Work, and is not responsible for any errors or omissions.
TERMS OF USE
This is a copyrighted work and McGraw-Hill Education (“McGraw-Hill”) and its licensors reserve all rights in and to the
work. Use of this work is subject to these terms. Except as permitted under the Copyright Act of 1976 and the right to
store and retrieve one copy of the work, you may not decompile, disassemble, reverse engineer, reproduce, modify, create
derivative works based upon, transmit, distribute, disseminate, sell, publish or sublicense the work or any part of it without
McGraw-Hill’s prior consent. You may use the work for your own noncommercial and personal use; any other use of the
work is strictly prohibited. Your right to use the work may be terminated if you fail to comply with these terms.
THE WORK IS PROVIDED “AS IS.” McGRAW-HILL AND ITS LICENSORS MAKE NO GUARANTEES OR
WARRANTIES AS TO THE ACCURACY, ADEQUACY OR COMPLETENESS OF OR RESULTS TO BE OBTAINED
FROM USING THE WORK, INCLUDING ANY INFORMATION THAT CAN BE ACCESSED THROUGH THE
WORK VIA HYPERLINK OR OTHERWISE, AND EXPRESSLY DISCLAIM ANY WARRANTY, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS
FOR A PARTICULAR PURPOSE. McGraw-Hill and its licensors do not warrant or guarantee that the functions contained
in the work will meet your requirements or that its operation will be uninterrupted or error free. Neither McGraw-Hill nor
its licensors shall be liable to you or anyone else for any inaccuracy, error or omission, regardless of cause, in the work or
for any damages resulting therefrom. McGraw-Hill has no responsibility for the content of any information accessed
through the work. Under no circumstances shall McGraw-Hill and/or its licensors be liable for any indirect, incidental,
special, punitive, consequential or similar damages that result from the use of or inability to use the work, even if any of
them has been advised of the possibility of such damages. This limitation of liability shall apply to any claim or cause
whatsoever whether such claim or cause arises in contract, tort or otherwise.
eBook 243-1cr_pg.indd 1 12/17/13 4:26 PM
Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / iii
To Lisa, my eternal companion, inspiration, wife,
and best friend; and to Sarah, Joseph, Elise, Ian, Ariel,
Callie, Nathan, Spencer, and Christianne—our terrific,
heaven-sent children. Thank you for your constant support,
patience, and sacrifice that made writing
yet another book possible.
00-FM.indd 3 12/17/13 4:07 PM
Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / iv
About the Author
Michael McLaughlin is a professor at BYU–Idaho in the Computer Information Technology
Department of the Business and Communication College. He is also the founder of McLaughlin
Software, LLC, and is active in the Utah Oracle User’s Group. He is the author of eight other
Oracle Press books, such as Oracle Database 11g & MySQL 5.6 Developer Handbook, Oracle
Database 11g PL/SQL Programming, and Oracle Database 11g PL/SQL Workbook.
Michael has been writing PL/SQL since it was an add-on product for Oracle 6. He also writes
C, C++, Java, Perl, PHP, and Python.
Michael worked at Oracle Corporation for over eight years in consulting, development, and
support. While at Oracle, he led the release engineering efforts for the direct path CRM upgrade
of Oracle Applications 11i (11.5.8 and 11.5.9) and led PL/SQL forward compatibility testing for
Oracle Applications 11i with Oracle Database 9i. He is the inventor of the ATOMS transaction
architecture (U.S. Patents #7,206,805 and #7,290,056). The patents are assigned to Oracle
Corporation.
Prior to his tenure at Oracle Corporation, Michael worked as an Oracle developer, systems and
business analyst, and DBA beginning with Oracle 6. His blog is at http://blog.mclaughlinsoftware.com.
Michael lives in eastern Idaho within a two-hour drive to Caribou-Targhee National Forest,
Grand Teton National Park, and Yellowstone National Park. He enjoys outdoor activities with his
wife and children (six of nine of whom still live at home).
About the Contributing Author
John Harper currently works for the Church of Jesus Christ of Latter-day Saints as a principal
database engineer. He greatly enjoys working with the data warehousing, business intelligence,
and database engineers there.
John’s mentors include Michael McLaughlin, Robert Freeman, Danette McGilvary, and many
others who have spent considerable time becoming the experts in their industry. He is both awed
and inspired by their abilities and feels lucky to be associated with them.
Recently, John has had the opportunity to work closely with some of the top-notch minds in
database security. He hopes to produce a series of publications focused on Oracle products such
as Oracle Audit Vault and Database Firewall, and Oracle Data Redaction.
John enjoys Japanese martial arts. During his teenage years and early adulthood, he took
jujitsu, karate, judo, and aikido. He loves aikido and hopes to teach it one day. He would also
love to learn kyudo if he can find any spare time.
John lives with his wife of over 23 years in Northern Utah County, Utah. They have one adopted
daughter, whom they cherish and thoroughly spoil. He has been working with databases for the
past 14 years, specializing in Oracle administration, database architecture, database programming,
database security, and information quality.
About the Technical Editor
Joseph McLaughlin is an iPhone and Ruby web developer at Deseret Book in Salt Lake City, Utah.
He has extensive backend database development experience with Oracle, MySQL, and PostgreSQL.
His favorite development languages are Objective-C and Ruby.
Joseph is a recent graduate of BYU–Idaho with a degree in Computer Information Technology.
While a college student and independent consultant, Joseph designed, developed, and deployed
four mobile applications for the iPhone or iPod Touch.
Aside from programming, Joseph enjoys playing basketball and watching the Boston Red Sox
win, especially when they win the World Series.
00-FM.indd 4 12/17/13 4:07 PM
Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / v
Contents at a Glance
PART I
Oracle PL/SQL
1 Oracle PL/SQL Development Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
2 New Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
3 PL/SQL Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
4 Language Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
5 Control Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
6 Collections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217
7 Error Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261
PART II
PL/SQL Programming
8 Functions and Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293
9 Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 347
10 Large Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385
11 Object Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 449
12 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491
13 Dynamic SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 545
PART III
Appendixes and Glossary
A Oracle Database Primer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 595
B SQL Primer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 695
v
00-FM.indd 5 12/17/13 4:07 PM
Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / vi
vi Oracle Database 12c PL/SQL Programming
C SQL Built-in Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 893
D PL/SQL Built-in Packages and Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 965
E Regular Expression Primer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 999
F Wrapping PL/SQL Code Primer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1019
G PL/SQL Hierarchical Profiler Primer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1029
H PL/SQL Reserved Words and Keywords . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1045
I Mastery Check Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1055
Glossary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1085
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1101
00-FM.indd 6 12/17/13 4:07 PM
Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / vii
Contents
Acknowledgments .................................................... xix
Introduction ......................................................... xxi
PART I
Oracle PL/SQL
1 Oracle PL/SQL Development Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
PL/SQL’s History and Background ......................................... 4
Oracle Development Architecture ........................................ 6
The Database .................................................. 7
The PL/SQL Language ............................................ 9
The Oracle Processing Architecture ................................. 12
Two-Tier Model ................................................ 13
N-Tier Model .................................................. 13
Summary ........................................................... 15
Mastery Check ....................................................... 15
2 New Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
New SQL Features .................................................... 18
Data Catalog DIRECTORY Qualifies a LIBRARY Object .................. 19
Define Tables with Valid-Time (VT) Support ........................... 19
Enhanced Oracle Native LEFT OUTER JOIN Syntax ..................... 20
Default Values for Columns Based on Sequences ....................... 20
Default Values for Explicit Null Insertion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Identity Columns ............................................... 23
Increased Size Limits of String and Raw Types ......................... 24
Pass Results from SQL Statements to External Programs .................. 24
Native SQL Support for Query Row Limits and Offsets ................... 26
Oracle Database Driver for MySQL Applications ....................... 29
SQL CROSS APPLY, OUTER APPLY, and LATERAL ...................... 29
Bequeath CURRENT_USER Views .................................. 31
vii
00-FM.indd 7 12/17/13 4:07 PM
Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM / viii
viii Oracle Database 12c PL/SQL Programming
New PL/SQL Features .................................................. 32
Caching of Invoker Rights Functions ................................. 32
Ability to White List PL/SQL Program Unit Callers ...................... 32
Native Client API Support for PL/SQL Types ........................... 34
New PL/SQL Package UTL_CALL_STACK ............................. 34
DBMS_UTILITY Adds EXPAND_SQL_TEXT Subprogram .................. 34
DBMS_SQL Adds a New Formal Schema
to the PARSE Procedure ........................................ 35
PL/SQL Functions in SQL WITH Clause .............................. 35
PL/SQL-Specific Data Types Allowed in SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Implicit REF CURSOR Parameter Binding ............................. 40
Supporting Scripts ..................................................... 40
Summary ........................................................... 41
Mastery Check ....................................................... 41
3 PL/SQL Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
Block Structure ....................................................... 44
Execution Block ................................................ 44
Basic Block Structure ............................................ 45
Declaration Block ............................................... 48
Exception Block ................................................ 49
Behavior of Variables in Blocks ........................................... 50
Anonymous Blocks .............................................. 50
Nested Anonymous Blocks ........................................ 55
Local Named Blocks ............................................. 57
Stored Named Blocks ............................................ 60
Basic Scalar and Composite Data Types .................................... 63
Scalar Data Types ............................................... 63
Attribute and Table Anchoring ..................................... 65
Composite Data Types ........................................... 68
Control Structures ..................................................... 81
Conditional Structures ........................................... 81
Iterative Structures .............................................. 83
Exceptions .......................................................... 92
User-Defined Exceptions ......................................... 93
Dynamic User-Defined Exceptions .................................. 94
Bulk Operations ...................................................... 95
Functions, Procedures, and Packages ...................................... 97
Functions ..................................................... 97
Procedures .................................................... 99
Packages ...................................................... 100
Transaction Scope .................................................... 106
Single Transaction Scope ......................................... 106
Multiple Transaction Scopes ....................................... 107
00-FM.indd 8 12/17/13 4:07 PM
Oracle TIGHT / Oracle Database 12c PL/SQL Programming / Michael McLaughlin / 181243-1 / FM /
Contents ix
Database Triggers ..................................................... 108
Summary ........................................................... 109
Mastery Check ....................................................... 109
4 Language Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Lexical Units ........................................................ 112
Delimiters ..................................................... 112
Identifiers ..................................................... 118
Literals ....................................................... 119
Comments .................................................... 121
Variables and Data Types ............................................... 122
Variable Data Types ............................................. 123
Scalar Data Types ............................................... 126
Large Objects (LOBs) ............................................ 142
Composite Data Types ........................................... 144
System Reference Cursors ......................................... 147
Summary ........................................................... 150
Mastery Check ....................................................... 150
5 Control Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Conditional Statements ................................................. 154
IF Statements .................................................. 162
CASE Statements ................................................ 166
Conditional Compilation Statements ................................. 169
Iterative Statements .................................................... 172
Simple Loop Statements .......................................... 172
FOR Loop Statements ............................................ 179
WHILE Loop Statements .......................................... 181
Cursor Structures ..................................................... 185
Implicit Cursors ................................................ 185
Explicit Cursors ................................................. 190
Bulk Statements ...................................................... 203
BULK COLLECT INTO Statements .................................. 203
FORALL Statements ............................................. 208
Supporting Scripts ..................................................... 213
Summary ........................................................... 214
Mastery Check ....................................................... 214
6 Collections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217
Introduction to Collections .............................................. 218
Object Types: Varray and Table Collections .................................. 221
Varray Collections .............................................. 221
Table Collections ............................................... 225
Associative Arrays ..................................................... 240
Defining and Using Associative Arrays ............................... 241
00-FM.indd 9 12/17/13 4:07 PM