Oracle PL/SQL by Example

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.  

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