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.