Oracle PL/SQL by Example
5th Edition
Published on 19. March 2015
Book
Paperback/Softback
528 pages
978-0-13-379678-0 (ISBN)
Description
Using PL/SQL for Oracle Database 12c, you can build solutions that deliver unprecedented performance and efficiency in any environment, including the cloud. Oracle (R) PL/SQL by Example, Fifth Edition, teaches all the PL/SQL skills you'll need, through real-world labs, extensive examples, exercises, and projects. Now fully updated for the newest version of PL/SQL, it covers everything from basic syntax and program control through the latest optimization and security enhancements.
Step by step, you'll walk through every key task, mastering today's most valuable Oracle 12cPL/SQL programming techniques on your own. Start by downloading projects and exercises from informit.com/title/0133796787. Once you've done an exercise, the authors don't just present the answer: They offer an in-depth discussion introducing deeper insights and modern best practices.
This book's approach fully reflects the authors' award-winning experience teaching PL/SQL to professionals at Columbia University. New database developers and DBAs can use it to get productive fast; experienced PL/SQL programmers will find it to be a superb Oracle Database 12csolutions reference.
New in This Edition
Updated code examples throughout
Result-caching of invoker's right functions for better performance
Extended support for PL/SQL-only data types in dynamic SQL, OCI, and JDBC
Security enhancements, including ACCESSIBLE BY whitelists, improved privilege control, and Invisible Columns
Other topics covered
Mastering basic PL/SQL concepts and language fundamentals, and understanding SQL's role in PL/SQL
Using conditional and iterative program control, including CONTINUE and CONTINUE WHEN
Efficiently handling errors and exceptions
Working with cursors and triggers, including compound triggers
Using stored procedures, functions, and packages to write modular code that other programs can run
Working with collections, object-relational features, native dynamic SQL, bulk SQL, and other advanced features
Step by step, you'll walk through every key task, mastering today's most valuable Oracle 12cPL/SQL programming techniques on your own. Start by downloading projects and exercises from informit.com/title/0133796787. Once you've done an exercise, the authors don't just present the answer: They offer an in-depth discussion introducing deeper insights and modern best practices.
This book's approach fully reflects the authors' award-winning experience teaching PL/SQL to professionals at Columbia University. New database developers and DBAs can use it to get productive fast; experienced PL/SQL programmers will find it to be a superb Oracle Database 12csolutions reference.
New in This Edition
Updated code examples throughout
Result-caching of invoker's right functions for better performance
Extended support for PL/SQL-only data types in dynamic SQL, OCI, and JDBC
Security enhancements, including ACCESSIBLE BY whitelists, improved privilege control, and Invisible Columns
Other topics covered
Mastering basic PL/SQL concepts and language fundamentals, and understanding SQL's role in PL/SQL
Using conditional and iterative program control, including CONTINUE and CONTINUE WHEN
Efficiently handling errors and exceptions
Working with cursors and triggers, including compound triggers
Using stored procedures, functions, and packages to write modular code that other programs can run
Working with collections, object-relational features, native dynamic SQL, bulk SQL, and other advanced features
More details
Edition
5th edition
Language
English
Place of publication
United States
Publishing group
Pearson Education (US)
Target group
College/higher education
Product notice
Paperback (trade)
Unsewn / adhesive bound
Dimensions
Height: 231 mm
Width: 179 mm
Thickness: 30 mm
Weight
830 gr
ISBN-13
978-0-13-379678-0 (9780133796780)
Schweitzer Classification
Other editions
New editions
Benjamin Rosenzweig | Elena Rakhimov
Oracle PL/SQL by Example
Book
06/2023
6th Edition
Addison Wesley
€64.50
Shipment within 15-20 days
Additional editions
Benjamin Rosenzweig | Elena Rakhimov
Oracle PL/SQL by Example
E-Book
02/2015
5th Edition
Pearson
€77.79
Available for download
Previous edition
Benjamin Rosenzweig | Elena Rakhimov
Oracle PL/SQL by Example
Book
08/2008
4th Edition
Prentice Hall
€54.46
Article exhausted; check for reprint
Persons
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.
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.
Content
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
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