Oracle PL/SQL by Example

Elena Rakhimov has more than 20 years of experience in software architecture and development in a wide spectrum of enterprise and business environments ranging from nonprofit organizations to Wall Street to her current position with a prominent consulting company. 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.

• Preface xviiIntroduction to PL/SQL New Features in Oracle 21c xxiiiChapter 1 PL/SQL Concepts 1Lab 1.1: PL/SQL Architecture 2PL/SQL Architecture 2PL/SQL Block Structure 5How PL/SQL Gets Executed 9Lab 1.2: PL/SQL Development Environment 10Getting Started with SQL Developer 10Getting Started with SQL*Plus 13Executing PL/SQL Scripts 14Lab 1.3: PL/SQL: The Basics 18DBMS_OUTPUT.PUT_LINE Statement 18Substitution Variable Feature 21Summary 25Chapter 2 PL/SQL Language Fundamentals 27Lab 2.1: PL/SQL Language Components 27PL/SQL Variables 29PL/SQL Reserved Words 31Delimiters 32Literals in PL/SQL 33Lab 2.2: Anchored Data Types 33Lab 2.3: Scope of a Variable, Block, Nested Blocks, and Labels 35Scope of a Variable 35Nested Blocks and Labels 36Summary 38Chapter 3 SQL in PL/SQL 39Lab 3.1: SQL Statements in PL/SQL 39Initializing Variables with the SELECT INTO Statement 40Using DML Statements in a PL/SQL Block 41Using a Sequence in a PL/SQL Block 43Lab 3.2: Transaction Control in PL/SQL 44The COMMIT, ROLLBACK, and SAVEPOINT Statements 44The SET TRANSACTION Statement 47Summary 48Chapter 4 Conditional Control: IF Statements 49Lab 4.1: IF Statements 50IF-THEN Statements 50IF-THEN-ELSE Statements 52Lab 4.2: ELSIF Statements 55Lab 4.3: Nested IF Statements 59Logical Operators 61Summary 62Chapter 5 Conditional Control: CASE Statements 65Lab 5.1: CASE Statements 65CASE Statements 66Searched CASE Statements 68Lab 5.2: CASE Expressions 74Lab 5.3: NULLIF and COALESCE Functions 78NULLIF Function 78COALESCE Function 80Summary 82Chapter 6 Iterative Control: Part I 85Lab 6.1: Simple Loops 86EXIT Statement 87EXIT WHEN Statement 91Lab 6.2: WHILE Loops 92Using WHILE Loops 92Terminating the WHILE Loop Prematurely 95Lab 6.3: Numeric FOR Loops 97Using the IN Option in the Loop 100Using the REVERSE Option in the Loop 103Using Iteration Controls in the Loop 104Terminating the Numeric FOR Loop Prematurely 108Summary 109Chapter 7 Iterative Control: Part II 111Lab 7.1: CONTINUE Statement 111Using the CONTINUE Statement 112Using the CONTINUE WHEN Statement 116Lab 7.2: Nested Loops 119Using Nested Loops 119Using Loop Labels 120Summary 122Chapter 8 Error Handling and Built-in Exceptions 125Lab 8.1: Handling Errors 125Lab 8.2: Built-in Exceptions 128Summary 133Chapter 9 Exceptions 135Lab 9.1: Exception Scope 135Lab 9.2: User-Defined Exceptions 139Lab 9.3: Exception Propagation 143Re-raising Exceptions 148Summary 149Chapter 10 Exceptions: Advanced Concepts 151Lab 10.1: RAISE_APPLICATION_ERROR 151Lab 10.2: EXCEPTION_INIT Pragma 155Lab 10.3: SQLCODE and SQLERRM 157Summary 160Chapter 11 Introduction to Cursors 161Lab 11.1: Types of Cursors 162Implicit Cursor 162Explicit Cursor 164Lab 11.2: Table-Based and Cursor-Based Records 171Table-Based Records 172Cursor-Based Records 174Lab 11.3: Cursor FOR Loops 175Lab 11.4: Nested Cursors 177Summary 179Chapter 12 Advanced Cursors 181Lab 12.1: Parameterized Cursors 181Lab 12.2: Cursor Variables and Expressions 186Cursor Variables 187Cursor Expressions 193Lab 12.3: FOR UPDATE Cursors 196Summary 199Chapter 13 Triggers 201Lab 13.1: What Triggers Are 201Database Trigger 202BEFORE Triggers 205AFTER Triggers 210Autonomous Transaction 211Lab 13.2: Types of Triggers 213Row and Statement Triggers 213INSTEAD OF Triggers 215Summary 219Chapter 14 Mutating Tables and Compound Triggers 221Lab 14.1: Mutating Tables 221Lab 14.2: Compound Triggers 223Summary 228Chapter 15 Collections 229Lab 15.1: PL/SQL Tables 230Associative Arrays 231Nested Tables 233Collection Methods 236Lab 15.2: Varrays 240Lab 15.3: Multidimensional Collections 245Lab 15.4: Collection Iteration Controls andQualified Expressions 247Collection Iteration Controls 247Qualified Expressions 251Summary 258Chapter 16 Records 259Lab 16.1: User-Defined Records 259User-Defined Records 260Qualified Expressions with Records 262Record Compatibility 263Lab 16.2: Nested Records 265Lab 16.3: Collections of Records 268Summary 271Chapter 17 Native Dynamic SQL 273Lab 17.1: EXECUTE IMMEDIATE Statements 274EXECUTE IMMEDIATE Statement 275Lab 17.2: OPEN FOR, FETCH, and CLOSE Statements 283Summary 287Chapter 18 Bulk SQL 289Lab 18.1: FORALL Statements 290FORALL Statements 290SAVE EXCEPTIONS Option 294INDICES OF Option 296VALUES OF Option 297Lab 18.2: The BULK COLLECT Clause 299Lab 18.3: Binding Collections in SQL Statements 308Binding Collections with EXECUTE IMMEDIATE Statements 308Binding Collections with OPEN FOR, FETCH, and CLOSE Statements 314Summary 318Chapter 19 Procedures 319Lab 19.1: Creating Nested Procedures 320Nested Procedures 320Parameter Modes 321Forward Declaration 326Lab 19.2: Creating Stand-Alone Procedures 327Summary 330Chapter 20 Functions 331Lab 20.1: Creating Nested Functions 331Lab 20.2: Creating Stand-Alone Functions 336Summary 340Chapter 21 Packages 341Lab 21.1: Creating Packages 341Creating a Package Specification 342Creating a Package Body 343Lab 21.2: Package Instantiation and Initialization 348Package Instantiation and Initialization 349Package State 351Lab 21.3: SERIALLY_REUSABLE Packages 351Summary 356Chapter 22 Stored Code Advanced Concepts 357Lab 22.1: Subprogram Overloading 357Lab 22.2: Result-Cached Functions 363Lab 22.3: Invoking PL/SQL Functions from SQL Statements 366Invoking Functions in SQL Statements 367Using Pipelined Table Functions 368Using SQL Macros 370Summary 375Chapter 23 Object Types in Oracle 379Lab 23.1: Object Types 380Creating Object Types 381Using Object Types with Collections 385Lab 23.2: Object Type Methods 388Using Constructor Methods 389Using Member Methods 392Using Static Methods 393Comparing Objects 393Summary 398Chapter 24 Storing Object Types in Tables 399Lab 24.1: Storing Object Types in Relational Tables 400Lab 24.2: Storing Object Types in Object Tables 403Lab 24.3: Type Evolution 405Summary 410Chapter 25 Dynamic SQL with the DBMS_SQL Package 411Lab 25.1: Generating Dynamic SQL with the DBMS_SQL Package 412Summary 420Appendix A PL/SQL Formatting Guide 421Case 421Whitespace 421Naming Conventions 422Comments 423Appendix B Student Database Schema 425Table and Column Descriptions 4259780138062835    TOC    4/20/2023