Oracle PL/SQL by Example, 5th edition
- Benjamin Rosenzweig |
- Elena Rakhimov |
Title overview
This tutorial is an integrated learning solution that teaches all the Oracle PL/SQL skills students will need, hands-on, through real-world labs, extensive examples, exercises, and projects. Completely updated for Oracle 12c, it covers all the fundamentals, from PL/SQL syntax and program control through packages and optimization. Concepts are thoroughly explained, then illustrated via examples and interactive labs. At the end of each chapter there is a "Test Your Thinking" section made up of a series of projects designed to solidify all of the skills learned in the chapter.
- Teaches all the Oracle PL/SQL skills needed to start developing PL/SQL fast
- Includes hundreds of extensive exercises, examples, and projects
- Covers the new features in Oracle 12c, which was recently released
- Written by authors with extensive experience in training and teaching students and employees in the use of PL/SQL
Expands on the material covered in the previous editions of the book--more examples as well as imore depth and complexity, especially the latter chapters which focus on the advanced features of PL/SQL.
Chapter 10--extended to cover error reporting available with PL/SQL. Its main focus will be on the UTL_CALLSTACK package that has been introduced in Oracle 12c.
Chapter 18 --expanded to cover a new Oracle 12c feature introduced specifically for the bulk operations, FETCH FIRST
Chapter 25 is a new chapter that focuses on the optimization of the PL/SQL code. While it does not contain any new features specific to 12c, all the features and techniques employ new features introduced in 11g. This chapter will demonstrate to the readers how to analyze PL/SQL code and determine potential performance bottlenecks
This edition will again use the "CTA Schema" set of tables as basis for the hands-on examples and exercises. This schema represents imaginary application system that tracks students, their enrollments and grades, and was formally used by the CTA (Computer Technology and Application) program at Columbia University. The schema is provided as a set of files that can be downloaded into the reader's database so that he/she may be able to participate in the book examples.
Table of contents
Preface xvii
Acknowledgments xxi
About the Authors xxiii
Introduction to PL/SQL New Features in Oracle 12c xxv
Invoker’s Rights Functions Can Be Result-Cached xxvi
More PL/SQL-Only Data Types Can Cross the PL/SQL-to-SQL Interface Clause xxvii
ACCESSIBLE BY Clause xxvii
FETCH FIRST Clause xxviii
Roles Can Be Granted to PL/SQL Packages and Stand-Alone Subprograms xxix
More Data Types Have the Same Maximum Size in SQL and PL/SQL xxx
Database Triggers on Pluggable Databases xxx
LIBRARY Can Be Defined as a DIRECTORY Object and with a CREDENTIAL Clause xxx
Implicit Statement Results xxxi
BEQUEATH CURRENT_USER Views xxxii
INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES Privileges xxxii
Invisible Columns xxxiii
Objects, Not Types, Are Editioned or Noneditioned xxxiv
PL/SQL Functions That Run Faster in SQL xxxiv
Predefined Inquiry Directives $$PLSQL_UNIT_OWNER and $$PLSQL_UNIT_TYPE xxxvi
Compilation Parameter PLSQL_DEBUG Is Deprecated xxxvii
Chapter 1: PL/SQL Concepts 1
Lab 1.1: PL/SQL Architecture 2
Lab 1.2: PL/SQL Development Environment 9
Lab 1.3: PL/SQL: The Basics 18
Summary 25
Chapter 2: PL/SQL Language Fundamentals 27
Lab 2.1: PL/SQL Programming Fundamentals 28
Summary 41
Chapter 3: SQL in PL/SQL 43
Lab 3.1: DML Statements in PL/SQL 44
Lab 3.2: Transaction Control in PL/SQL 49
Summary 55
Chapter 4: Conditional Control: IF Statements 57
Lab 4.1: IF Statements 58
Lab 4.2: ELSIF Statements 63
Lab 4.3: Nested IF Statements 67
Summary 70
Chapter 5: Conditional Control: CASE Statements 71
Lab 5.1: CASE Statements 71
Lab 5.2: CASE Expressions 80
Lab 5.3: NULLIF and COALESCE Functions 84
Summary 89
Chapter 6: Iterative Control: Part I 91
Lab 6.1: Simple Loops 92
Lab 6.2: WHILE Loops 98
Lab 6.3: Numeric FOR Loops 104
Summary 109
Chapter 7: Iterative Control: Part II 111
Lab 7.1: CONTINUE Statement 111
Lab 7.2: Nested Loops 118
Summary 122
Chapter 8: Error Handling and Built-in Exceptions 123
Lab 8.1: Handling Errors 124
Lab 8.2: Built-in Exceptions 126
Summary 132
Chapter 9: Exceptions 133
Lab 9.1: Exception Scope 133
Lab 9.2: User-Defined Exceptions 137
Lab 9.3: Exception Propagation 141
Summary 147
Chapter 10: Exceptions: Advanced Concepts 149
Lab 10.1: RAISE_APPLICATION_ERROR 149
Lab 10.2: EXCEPTION_INIT Pragma 153
Lab 10.3: SQLCODE and SQLERRM 155
Summary 158
Chapter 11: Introduction to Cursors 159
Lab 11.1: Types of Cursors 159
Lab 11.2: Cursor Loop 165
Lab 11.3: Cursor FOR LOOPs 175
Lab 11.4: Nested Cursors 177
Summary 181
Chapter 12: Advanced Cursors 183
Lab 12.1: Parameterized Cursors 183
Lab 12.2: Complex Nested Cursors 185
Lab 12.3: FOR UPDATE and WHERE CURRENT Cursors 187
Summary 190
Chapter 13: Triggers 191
Lab 13.1: What Triggers Are 191
Lab 13.2: Types of Triggers 205
Summary 211
Chapter 14: Mutating Tables and Compound Triggers 213
Lab 14.1: Mutating Tables 213
Lab 14.2: Compound Triggers 217
Summary 223
Chapter 15: Collections 225
Lab 15.1: PL/SQL Tables 226
Lab 15.2: Varrays 235
Lab 15.3: Multilevel Collections 240
Summary 242
Chapter 16: Records 243
Lab 16.1: Record Types 243
Lab 16.2: Nested Records 250
Lab 16.3: Collections of Records 253
Summary 257
Chapter 17: Native Dynamic SQL 259
Lab 17.1: EXECUTE IMMEDIATE Statements 260
Lab 17.2: OPEN-FOR, FETCH, and CLOSE Statements 271
Summary 280
Chapter 18: Bulk SQL 281
Lab 18.1: FORALL Statements 282
Lab 18.2: The BULK COLLECT Clause 291
Lab 18.3: Binding Collections in SQL Statements 299
Summary 309
Chapter 19: Procedures 311
Benefits of Modular Code 312
Lab 19.1: Creating Procedures 312
Lab 19.2: Passing Parameters IN and OUT of Procedures 315
Summary 319
Chapter 20: Functions 321
Lab 20.1: Creating Functions 321
Lab 20.2: Using Functions in SQL Statements 327
Lab 20.3: Optimizing Function Execution in SQL 329
Summary 331
Chapter 21: Packages 333
Lab 21.1: Creating Packages 334
Lab 21.2: Cursor Variables 344
Lab 21.3: Extending the Package 353
Lab 21.4: Package Instantiation and Initialization 366
Lab 21.5: SERIALLY_REUSABLE Packages 368
Summary 371
Chapter 22: Stored Code 373
Lab 22.1: Gathering Information about Stored Code 373
Summary 382
Chapter 23: Object Types in Oracle 385
Lab 23.1: Object Types 386
Lab 23.2: Object Type Methods 394
Summary 404
Chapter 24: Oracle-Supplied Packages 405
Lab 24.1: Extending Functionality with Oracle-Supplied Packages 406
Lab 24.2: Error Reporting with Oracle-Supplied Packages 419
Summary 429
Chapter 25: Optimizing PL/SQL 431
Lab 25.1: PL/SQL Tuning Tools 432
Lab 25.2: PL/SQL Optimization Levels 438
Lab 25.3: Subprogram Inlining 444
Summary 453
Appendix A: PL/SQL Formatting Guide 455
Case 455
White Space 455
Naming Conventions 456
Comments 457
Other Suggestions 457
Appendix B: Student Database Schema 461
Table and Column Descriptions 461
Index 469
Author bios
Benjamin Rosenzweig is a Senior Project Manager at Misys Financial Software, where he has worked since 2002. Prior to that he was a principal consultant for more than three years at Oracle Corporation in the Custom Development Department. His computer experience ranges from creating an electronic Tibetan—English Dictionary in Kathmandu, Nepal, to supporting presentation centers at Goldman Sachs and managing a trading system at TIAA-CREF. Benjamin has been an instructor at the Columbia University Computer Technology and Application program in New York City since 1998. In 2002 he was awarded the “Outstanding Teaching Award” from the Chair and Director of the CTA program. He holds a B.A. from Reed College and a certificate in database development and design from Columbia University. His previous books with Prentice Hall are Oracle Forms Developer: The Complete Video Course (2000), and Oracle Web Application Programming for PL/SQL Developers (2003).
Elena Rakhimov has more than twenty years of experience in database architecture and development in a wide spectrum of enterprise and business environments ranging from non-profit organizations to Wall Street to her current position with a prominent software company where she heads up the database team. Her determination to stay “hands-on” notwithstanding, Elena managed to excel in the academic arena having taught relational database programming at Columbia University’s highly esteemed Computer Technology and Applications program. She was educated in database analysis and design at Columbia University and in applied mathematics at Baku State University in Azerbaijan. She currently resides in Vancouver, Canada.