I'm a studentI'm an educator

Oracle PL/SQL by Example, 5th edition

Published by Pearson (February 10, 2015) © 2015

  • Benjamin Rosenzweig
  • Elena Rakhimov
Products list
$57.99
Buy nowOpens in a new tab
Sold by InformIT and ebook resellers
  • Available for purchase from all major ebook resellers, including InformIT.com
Products list
$47.99

Price Reduced From: $59.99

Details

  • A print text
  • Free shipping
  • Also available for purchase as an ebook from all major ebook resellers, including InformIT.com

This product is expected to ship within 3-6 business days for US and 5-10 business days for Canadian customers.

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.

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

 

Need help? Get in touch