Oracle PL/SQL by Example

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