Oracle PL/SQL by Example, 5th Edition
- By Benjamin Rosenzweig, Elena Rakhimov
- Published Feb 19, 2015 by Pearson.
eBook
- Your Price: $46.39
- List Price: $57.99
- Includes EPUB and PDF
- About eBook Formats
This eBook includes the following formats, accessible from your Account page after purchase:
EPUB
The open industry format known for its reflowable content and usability on supported mobile devices.
PDF
The popular standard, used most often with the free Acrobat® Reader® software.
This eBook requires no passwords or activation to read. We customize your eBook by discreetly watermarking it with your name, making it uniquely yours.
Also available in other formats.
Register your product to gain access to bonus material or receive a coupon.
Description
- Copyright 2015
- Dimensions: 7" x 9-1/8"
- Edition: 5th
- eBook
- ISBN-10: 0-13-379855-0
- ISBN-13: 978-0-13-379855-5
Using PL/SQL for Oracle Database 12c, you can build solutions that deliver unprecedented performance and efficiency in any environment, including the cloud. Oracle® 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
Sample Content
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