Oracle PL/SQL by example

Oracle PL/SQL by example......Page 1
Contents......Page 8
Acknowledgments......Page 15
About the Authors......Page 16
Introduction......Page 18
CHAPTER 1 PL/SQL Concepts......Page 30
 LAB 1.1 PL/SQL in Client/Server Architecture......Page 31
  1.1.1 Use PL/SQL Anonymous Blocks......Page 37
  1.1.2 Understand How PL/SQL Gets Executed......Page 39
 LAB 1.2 PL/SQL in SQL*Plus......Page 41
  1.2.1 Use Substitution Variables......Page 45
  1.2.2 Use the DBMS_OUTPUT.PUT_LINE Statement......Page 46
 Chapter 1 Try It Yourself......Page 48
CHAPTER 2 General Programming Language Fundamentals......Page 50
 LAB 2.1 PL/SQL Programming Fundamentals......Page 51
  2.1.1 Make Use of PL/SQL Language Components......Page 52
  2.1.2 Make Use of PL/SQL Variables......Page 53
  2.1.3 Handle PL/SQL Reserved Words......Page 55
  2.1.4 Make Use of Identifiers in PL/SQL......Page 56
  2.1.5 Make Use of Anchored Datatypes......Page 57
  2.1.6 Declare and Initialize Variables......Page 60
  2.1.7 Understand the Scope of a Block, Nested Blocks, and Labels......Page 63
 Chapter 2 Try It Yourself......Page 66
CHAPTER 3 SQL in PL/SQL......Page 68
 LAB 3.1 Making Use of DML in PL/SQL......Page 69
  3.1.1 Use the Select INTO Syntax for Variable Initialization......Page 70
  3.1.2 Use DML in a PL/SQL Block......Page 71
  3.1.3 Make Use of a Sequence in a PL/SQL Block......Page 73
 LAB 3.2 Making Use of SAVEPOINT......Page 74
  3.2.1 Make Use of COMMIT, ROLLBACK, and SAVEPOINT in a PL/SQL Block......Page 77
 Chapter 3 Try It Yourself......Page 80
CHAPTER 4 Conditional Control: IF Statements......Page 82
 LAB 4.1 IF Statements......Page 83
  4.1.1 Use the IF-THEN Statement......Page 87
  4.1.2 Use the IF-THEN-ELSE Statement......Page 91
 LAB 4.2 ELSIF Statements......Page 94
  4.2.1 Use the ELSIF Statement......Page 98
 LAB 4.3 Nested IF Statements......Page 103
  4.3.1 Use Nested IF Statements......Page 105
 Chapter 4 Try It Yourself......Page 109
CHAPTER 5 Conditional Control: CASE Statements......Page 110
 LAB 5.1 CASE Statements......Page 111
  5.1.1 Use the CASE Statement......Page 118
  5.1.2 Use the Searched CASE Statement......Page 120
 LAB 5.2 CASE Expressions......Page 125
  5.2.1 Use the CASE Expression......Page 129
 LAB 5.3 NULLIF and COALESCE Functions......Page 132
  5.3.1 The NULLIF Function......Page 136
  5.3.2 Use the COALESCE Function......Page 138
 Chapter 5 Try It Yourself......Page 141
CHAPTER 6 Iterative Control: Part I......Page 142
 LAB 6.1 Simple Loops......Page 143
  6.1.1 Use Simple Loops with EXIT Conditions......Page 147
  6.1.2 Use Simple Loops with EXIT WHEN Conditions......Page 149
 LAB 6.2 WHILE Loops......Page 153
  6.2.1 Use WHILE Loops......Page 157
 LAB 6.3 Numeric FOR Loops......Page 161
  6.3.1 Use Numeric FOR Loops with the IN Option......Page 166
  6.3.2 Use Numeric FOR Loops with the REVERSE Option......Page 168
 Chapter 6 Try It Yourself......Page 171
CHAPTER 7 Iterative Control: Part II......Page 172
 LAB 7.1 The CONTINUE Statement......Page 173
  7.1.1 Use the CONTINUE Statement......Page 175
  7.1.2 Use the CONTINUE WHEN Condition......Page 181
 LAB 7.2 Nested Loops......Page 183
  7.2.1 Use Nested Loops......Page 186
 Chapter 7 Try It Yourself......Page 190
CHAPTER 8 Error Handling and Built-in Exceptions......Page 192
 LAB 8.1 Handling Errors......Page 193
  8.1.1 Understand the Importance of Error Handling......Page 196
 LAB 8.2 Built-in Exceptions......Page 198
  8.2.1 Use Built-in Exceptions......Page 203
 Chapter 8 Try It Yourself......Page 207
CHAPTER 9 Exceptions......Page 208
 LAB 9.1 Exception Scope......Page 209
  9.1.1 Understand the Scope of an Exception......Page 212
 LAB 9.2 User-Defined Exceptions......Page 217
  9.2.1 Use User-Defined Exceptions......Page 222
 LAB 9.3 Exception Propagation......Page 226
  9.3.1 Understand How Exceptions Propagate......Page 232
  9.3.2 Reraise Exceptions......Page 235
 Chapter 9 Try It Yourself......Page 238
CHAPTER 10 Exceptions: Advanced Concepts......Page 240
 LAB 10.1 RAISE_APPLICATION_ERROR......Page 241
  10.1.1 Use RAISE_APPLICATION_ERROR......Page 244
 LAB 10.2 EXCEPTION_INIT Pragma......Page 246
  10.2.1 USE the EXCEPTION_INIT Pragma......Page 248
 LAB 10.3 SQLCODE and SQLERRM......Page 251
  10.3.1 Use SQLCODE and SQLERRM......Page 254
 Chapter 10 Try It Yourself......Page 256
CHAPTER 11 Introduction to Cursors......Page 258
 LAB 11.1 Cursor Manipulation......Page 259
  11.1.1 Make Use of Record Types......Page 263
  11.1.2 Process an Explicit Cursor......Page 264
  11.1.3 Make Use of Cursor Attributes......Page 269
  11.1.4 Put It All Together......Page 271
 LAB 11.2 Using Cursor FOR Loops and Nested Cursors......Page 275
  11.2.2 Process Nested Cursors......Page 276
 Chapter 11 Try It Yourself......Page 281
CHAPTER 12 Advanced Cursors......Page 282
 LAB 12.1 Using Parameters with Cursors and Complex Nested Cursors......Page 283
  12.1.2 Use Complex Nested Cursors......Page 284
  12.2.1 For UPDATE and WHERE CURRENT Cursors......Page 287
CHAPTER 13 Triggers......Page 292
 LAB 13.1 What Triggers Are......Page 293
  13.1.1 Understand What a Trigger Is......Page 301
  13.1.2 Use BEFORE and AFTER Triggers......Page 303
 LAB 13.2 Types of Triggers......Page 306
  13.2.1 Use Row and Statement Triggers......Page 312
  13.2.2 Use INSTEAD OF Triggers......Page 314
 Chaper 13 Try It Yourself......Page 319
CHAPTER 14 Compound Triggers......Page 320
 LAB 14.1 Mutating Table Issues......Page 321
  14.1.1 Understand Mutating Tables......Page 325
 LAB 14.2 Compound Triggers......Page 329
  14.2.1 Understand Compound Triggers......Page 335
 Chapter 14 Try It Yourself......Page 342
CHAPTER 15 Collections......Page 344
 LAB 15.1 PL/SQL Tables......Page 345
  15.1.1 Use Associative Arrays......Page 355
  15.1.2 Use Nested Tables......Page 359
 LAB 15.2 Varrays......Page 363
  15.2.1 Use Varrays......Page 367
 LAB 15.3 Multilevel Collections......Page 371
  15.3.1 Use Multilevel Collections......Page 373
 Chapter 15 Try It Yourself......Page 377
CHAPTER 16 Records......Page 378
 LAB 16.1 Record Types......Page 379
  16.1.1 Use Table-Based and Cursor-Based Records......Page 387
  16.1.2 Use User-Defined Records......Page 391
 LAB 16.2 Nested Records......Page 396
  16.2.1 Use Nested Records......Page 398
 LAB 16.3 Collections of Records......Page 402
  16.3.1 Use Collections of Records......Page 403
 Chapter 16 Try It Yourself......Page 407
CHAPTER 17 Native Dynamic SQL......Page 408
 LAB 17.1 EXECUTE IMMEDIATE Statements......Page 409
  17.1.1 Use the EXECUTE IMMEDIATE Statement......Page 416
 LAB 17.2 OPEN-FOR, FETCH, and CLOSE Statements......Page 421
  17.2.1 Use OPEN-FOR, FETCH, and CLOSE Statements......Page 424
 Chapter 17 Try It Yourself......Page 430
CHAPTER 18 Bulk SQL......Page 432
 LAB 18.1 The FORALL Statement......Page 433
  18.1.1 Use the FORALL Statement......Page 442
 LAB 18.2 The BULK COLLECT Clause......Page 451
  18.2.1 Use the BULK COLLECT Statement......Page 457
 Chapter 18 Try It Yourself......Page 466
CHAPTER 19 Procedures......Page 468
  19.1.1 Create Procedures......Page 470
  19.1.2 Query the Data Dictionary for Information on Procedures......Page 472
 LAB 19.2 Passing Parameters into and out of Procedures......Page 473
  19.2.1 Use IN and OUT Parameters with Procedures......Page 474
  Part 2......Page 476
CHAPTER 20 Functions......Page 478
 LAB 20.1 Creating and Using Functions......Page 479
  20.1.1 Create Stored Functions......Page 480
  20.1.2 Make Use of Functions......Page 481
  20.1.3 Invoke Functions in SQL Statements......Page 482
  20.1.4 Write Complex Functions......Page 483
 Chapter 20 Try It Yourself......Page 484
CHAPTER 21 Packages......Page 486
 LAB 21.1 The Benefits of Using Packages......Page 487
  21.1.1 Create Package Specifications......Page 489
  21.1.2 Create Package Bodies......Page 491
  21.1.3 Call Stored Packages......Page 493
  21.1.4 Create Private Objects......Page 494
  21.1.5 Create Package Variables and Cursors......Page 498
 LAB 21.2 Cursor Variables......Page 500
  21.2.1 Make Use of Cursor Variables......Page 504
  21.3.1 Extend the Package......Page 509
 Chapter 21 Try It Yourself......Page 522
CHAPTER 22 Stored Code......Page 524
  22.1.1 Get Stored Code Information from the Data Dictionary......Page 525
  22.1.2 Enforce the Purity Level with the RESTRICT_REFERENCES Pragma......Page 529
  22.1.3 Overload Modules......Page 535
 Chapter 22 Try It Yourself......Page 541
CHAPTER 23 Object Types in Oracle......Page 542
 LAB 23.1 Object Types......Page 543
  23.1.1 Use Object Types......Page 551
  23.1.2 Use Object Types with Collections......Page 555
 LAB 23.2 Object Type Methods......Page 560
  23.2.1 Use Object Type Methods......Page 573
 Chapter 23 Try It Yourself......Page 583
CHAPTER 24 Oracle Supplied Packages......Page 584
 LAB 24.1 Making Use of Oracle Supplied Packages to Profile PL/SQL, Access Files, and Schedule Jobs......Page 585
  24.1.2 Schedule Jobs with DBMS_JOB......Page 592
  24.1.3 Submit Jobs......Page 593
 LAB 24.2 Making Use of Oracle-Supplied Packages to Generate an Explain Plan and Create HTML Pages......Page 597
  24.2.1 Generate an Explain Plan with DBMS_XPLAN......Page 601
 LAB 24.3 Creating Web Pages with the Oracle Web Toolkit......Page 607
  24.3.1 Create an HTML Page with the Oracle Web Toolkit......Page 623
APPENDIX A: PL/SQL Formatting Guide......Page 626
APPENDIX B: Student Database Schema......Page 630
APPENDIX C: ANSI SQL Standards......Page 636
APPENDIX D: Answers to the Try It Yourself Sections......Page 642
 A......Page 734
 C......Page 735
 D......Page 739
 E......Page 740
 F......Page 742
 G......Page 743
 I......Page 744
 L......Page 745
 N......Page 747
 O......Page 749
 P......Page 750
 R......Page 752
 S......Page 753
 T......Page 756
 V......Page 758
 X-Y-Z......Page 759