Home > Store

Oracle PL/SQL by Example, 5th Edition

Oracle PL/SQL by Example, 5th Edition

eBook (Watermarked)

  • Your Price: $38.39
  • List Price: $47.99
  • Includes EPUB, MOBI, and PDF
  • About eBook Formats
  • This eBook includes the following formats, accessible from your Account page after purchase:

    ePub EPUB The open industry format known for its reflowable content and usability on supported mobile devices.

    MOBI MOBI The eBook format compatible with the Amazon Kindle and Amazon Kindle applications.

    Adobe Reader PDF The popular standard, used most often with the free Adobe® 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"
  • Pages: 528
  • Edition: 5th
  • eBook (Watermarked)
  • 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

Updates

Submit Errata

More Information

Unlimited one-month access with your purchase
Free Safari Membership