Oracle PL/SQL by Example, 5th Edition

About

Features

• 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



Description

• Copyright 2015
• Dimensions: 7" x 9-1/8"
• Pages: 528
• Edition: 5th

• Book
• ISBN-10: 0-13-379678-7
• ISBN-13: 978-0-13-379678-0

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



Downloads

Downloads

Please download the files associated with Oracle PL/SQL by Example here.

End of chapter exercises

Student Schema

Chapter Example Scripts



Sample Content

Online Sample Chapter

SQL in PL/SQL

Sample Pages

Download the sample pages (includes Chapter 3 and Index)

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



Updates

Submit Errata

