Oracle(r) PL/SQL(tm) by example

✍ Scribed by Rosenzweig, Benjamin;Silvestrova, Elena

• Publisher: Prentice Hall; Professional Technical Reference
• Year: 2003;2004
• Tongue: English
• Leaves: 760
• Edition: 3rd ed
• Category: Library

✦ Synopsis

This integrated learning solution teaches all the Oracle PL/SQL skills you need, hands-on, through real-world labs, extensive examples, exercises, and projects! Completely updated for Oracle 11g, " Oracle PL/SQL by Example ," Fourth Edition covers all the fundamentals, from PL/SQL syntax and program control through packages and Oracle 11g's significantly improved triggers. One step at a time, you'll walk through every key task, discovering the most important PL/SQL programming techniques on your own. Building on your hands-on learning, the authors share solutions that offer deeper insights and proven best practices. End-of-chapter projects bring together all the techniques you've learned, strengthening your understanding through real-world practice. This book's approach fully reflects the authors' award-winning experience teaching PL/SQL programming to professionals at Columbia University. New database developers and DBAs can use its step-by-step instructions to get productive fast; experienced PL/SQL programmers can use this book as a practical solutions reference. Coverage includes - Mastering basic PL/SQL concepts and general programming language fundamentals, and understanding SQL's role in PL/SQL - Using conditional and iterative program control techniques, including the new CONTINUE and CONTINUE WHEN statements - Efficiently handling errors and exceptions - Working with cursors and triggers, including Oracle 11g's powerful new compound triggers - Using stored procedures, functions, and packages to write modular code that other programs can execute - Working with collections, object-relational features, native dynamic SQL, bulk SQL, and other advanced PL/SQL capabilities - Handy reference appendices: PL/SQL formatting guide, sample database schema, ANSI SQL standards reference, and more

✦ Table of Contents

Cover......Page 1
Contents......Page 6
Acknowledgments......Page 14
About the Authors......Page 16
Introduction......Page 18
CHAPTER 1 Programming Concepts......Page 26
LAB 1.1 The Nature of a Computer Program and Programming Languages......Page 27
1.1.1 Understand the Nature of Computer Programs and Programming Languages......Page 29
1.1.2 Understand the Differences Between Interpreted and Compiled Languages......Page 31
LAB 1.2 Good Programming Practices......Page 34
1.2.1 Understand the Nature of Good Programming Practices......Page 40
1.2.2 Understand Formatting Guidelines......Page 41
CHAPTER 1 Test Your Thinking......Page 45
CHAPTER 2 PL/SQL Concepts......Page 46
LAB 2.1 PL/SQL in Client-Server Architecture......Page 47
2.1.1 Use PL/SQL Anonymous Blocks......Page 53
2.1.2 Understand How PL/SQL Gets Executed......Page 55
LAB 2.2 PL/SQL in SQL*Plus......Page 59
2.2.1 Use Substitution Variables......Page 64
2.2.2 Use the DBMS_OUTPUT.PUT_LINE Statement......Page 65
CHAPTER 2 Test Your Thinking......Page 69
CHAPTER 3 General Programming Language Fundamentals......Page 70
LAB 3.1 PL/SQL Programming Fundamentals......Page 71
3.1.1 Make Use of PL/SQL Language Components......Page 72
3.1.2 Make Use of PL/SQL Variables......Page 73
3.1.3 Handle PL/SQL Reserved Words......Page 75
3.1.4 Make Use of Identifiers in PL/SQL......Page 76
3.1.6 Declare and Initialize Variables......Page 77
3.1.7 Understand the Scope of a Block, Nested Blocks, and Labels......Page 79
CHAPTER 3 Test Your Thinking......Page 91
CHAPTER 4 SQL in PL/SQL......Page 94
LAB 4.1 Making Use of DML in PL/SQL......Page 95
4.1.1 Use the SELECT INTO Syntax for Variable Initialization......Page 96
4.1.2 Use DML in a PL/SQL Block......Page 97
4.1.3 Make Use of a Sequence in a PL/SQL Block......Page 99
LAB 4.2 Making Use of SAVEPOINT......Page 103
4.2.1 Make Use of COMMIT, ROLLBACK, and SAVEPOINT in a PL/SQL Block......Page 107
CHAPTER 4 Test Your Thinking......Page 112
CHAPTER 5 Conditional Control: IF Statements......Page 114
LAB 5.1 IF Statements......Page 115
5.1.1 Use the IF-THEN Statement......Page 120
5.1.2 Use the IF-THEN-ELSE Statement......Page 121
LAB 5.2 ELSIF Statements......Page 130
5.2.1 Use the ELSIF Statement......Page 134
LAB 5.3 Nested IF Statements......Page 141
5.3.1 Use Nested IF Statements......Page 144
CHAPTER 5 Test Your Thinking......Page 149
CHAPTER 6 Conditional Control: Case Statements......Page 152
LAB 6.1 CASE Statements......Page 153
6.1.1 Use the CASE Statement......Page 160
6.1.2 Use the Searched CASE Statement......Page 161
LAB 6.2 CASE Expressions......Page 171
6.2.1 Use the CASE Expression......Page 175
LAB 6.3 NULLIF and COALESCE Functions......Page 180
6.3.1 Use the NULLIF Function......Page 184
6.3.2 Use the COALESCE Function......Page 186
CHAPTER 6 Test Your Thinking......Page 193
CHAPTER 7 Error Handling and Built- In Exceptions......Page 196
LAB 7.1 Handling Errors......Page 197
7.1.1 Understanding the Importance of Error Handling......Page 200
LAB 7.2 Built-In Exceptions......Page 204
7.2.1 Use Built-In Exceptions......Page 210
CHAPTER 7 Test Your Thinking......Page 216
CHAPTER 8 Iterative Control......Page 218
LAB 8.1 Simple Loops......Page 219
8.1.1 Use Simple Loops with EXIT Conditions......Page 223
8.1.2 Use Simple Loops with EXIT WHEN Conditions......Page 225
LAB 8.2 WHILE Loops......Page 233
8.2.1 Use WHILE Loops......Page 238
LAB 8.3 Numeric FOR Loops......Page 244
8.3.1 Use Numeric FOR Loops with the IN Option......Page 249
8.3.2 Use Numeric FOR Loops with the REVERSE Option......Page 250
LAB 8.4 Nested Loops......Page 257
8.4.1 Use Nested Loops......Page 260
CHAPTER 8 Test Your Thinking......Page 266
CHAPTER 9 Introduction to Cursors......Page 268
LAB 9.1 Cursor Manipulation......Page 269
9.1.1 Make Use of Record Types......Page 274
9.1.2 Processing an Explicit Cursor......Page 275
9.1.3 Making Use of Cursor Attributes......Page 280
9.1.4 Putting It All Together......Page 281
LAB 9.2 Using Cursor FOR Loops and Nesting Cursors......Page 291
9.2.2 Process Nested Cursors......Page 292
CHAPTER 9 Test Your Thinking......Page 299
CHAPTER 10 Exceptions......Page 300
LAB 10.1 Exception Scope......Page 301
10.1.1 Understand the Scope of an Exception......Page 304
LAB 10.2 User-Defined Exceptions......Page 312
10.2.1 Use User-Defined Exceptions......Page 317
LAB 10.3 Exception Propagation......Page 323
10.3.1 Understand How Exceptions Propagate......Page 329
10.3.2 Re-raising Exceptions......Page 330
CHAPTER 10 Test Your Thinking......Page 340
CHAPTER 11 Exceptions: Advanced Concepts......Page 342
LAB 11.1 RAISE_APPLICATION_ERROR......Page 343
11.1.1 Use RAISE_APPLICATION_ERROR......Page 347
LAB 11.2 EXCEPTION_INIT Pragma......Page 354
11.2.1 Use EXCEPTION_INIT Pragma......Page 356
LAB 11.3 SQLCODE and SQLERRM......Page 361
11.3.1 Use SQLCODE and SQLERRM......Page 364
CHAPTER 11 Test Your Thinking......Page 368
CHAPTER 12 Procedures......Page 370
LAB 12.1 Creating Procedures......Page 371
12.1.1 Create Procedures......Page 373
12.1.2 Query the Data Dictionary for Information on Procedures......Page 374
LAB 12.2 Passing Parameters In and Out of Procedures......Page 377
12.2.1 Use IN and OUT Parameters with Procedures......Page 379
CHAPTER 12 Test Your Thinking......Page 382
CHAPTER 13 Functions......Page 384
LAB 13.1 Creating and Using Functions......Page 385
13.1.1 Create Stored Functions......Page 386
13.1.2 Make Use of Functions......Page 387
13.1.4 Write Complex Functions......Page 388
CHAPTER 13 Test Your Thinking......Page 393
CHAPTER 14 Packages......Page 394
LAB 14.1 The Benefits of Utilizing Packages......Page 395
14.1.1 Create the Package Specification......Page 396
14.1.2 Create the Package Body......Page 398
14.1.3 Call Stored Packages......Page 399
14.1.4 Create Private Objects......Page 400
14.1.5 Create Package Variables and Cursors......Page 401
CHAPTER 14 Test Your Thinking......Page 408
CHAPTER 15 Advanced Cursors......Page 410
LAB 15.1 Using Parameters with Cursors and FOR UPDATE Cursors......Page 411
15.1.1 Use Parameters in a Cursor......Page 412
15.1.2 Use a FOR UPDATE Cursor......Page 414
15.1.3 WHERE CURRENT OF Clause......Page 416
LAB 15.2 Cursor Variables......Page 420
15.2.1 Make Use of Cursor Variables......Page 424
CHAPTER 16 Stored Code......Page 432
16.1.1 Get Stored Code Information from the Data Dictionary......Page 433
16.1.2 Enforce Purity Level with RESTRICT_REFERENCES Pragma......Page 435
16.1.3 Overload Modules......Page 437
CHAPTER 16 Test Your Thinking......Page 450
CHAPTER 17 Triggers......Page 452
LAB 17.1 What Triggers Are......Page 453
17.1.1 Understand What a Trigger Is......Page 458
17.1.2 Use BEFORE and AFTER Triggers......Page 460
LAB 17.2 Types of Triggers......Page 466
17.2.1 Use ROW and STATEMENT Triggers......Page 472
17.2.2 Use INSTEAD OF Triggers......Page 473
LAB 17.3 Mutating Table Issues......Page 481
17.3.1 Understand Mutating Tables......Page 485
CHAPTER 17 Test Your Thinking......Page 491
CHAPTER 18 Collections......Page 492
LAB 18.1 PL/SQL Tables......Page 493
18.1.1 Use Index-By Tables......Page 503
18.1.2 Use Nested Tables......Page 505
LAB 18.2 Varrays......Page 515
18.2.1 Use Varrays......Page 519
LAB 18.3 Multilevel Collections......Page 525
18.3.1 Use Multilevel Collections......Page 527
CHAPTER 18 Test Your Thinking......Page 533
CHAPTER 19 Records......Page 536
LAB 19.1 Records......Page 537
19.1.1 Use Table-Based and Cursor-Based Records......Page 545
19.1.2 Use User-Defined Records......Page 546
LAB 19.2 Nested Records......Page 558
19.2.1 Use Nested Records......Page 560
LAB 19.3 Collections of Records......Page 566
19.3.1 Use Collections of Records......Page 567
CHAPTER 19 Test Your Thinking......Page 573
CHAPTER 20 Native Dynamic SQL......Page 574
LAB 20.1 EXECUTE IMMEDIATE Statements......Page 575
20.1.1 Use the EXECUTE IMMEDIATE Statement......Page 582
LAB 20.2 OPEN-FOR, FETCH, and CLOSE Statements......Page 590
20.2.1 Use OPEN-FOR, FETCH, and CLOSE Statements......Page 593
CHAPTER 20 Test Your Thinking......Page 601
CHAPTER 21 Oracle Supplied Packages......Page 602
LAB 21.1 Make Use of Oracle Supplied Packages......Page 603
21.1.2 Schedule Jobs with DBMS_JOB......Page 629
21.1.3 Generate an Explain Plan with DBMS_XPLAN......Page 630
21.1.4 Create an HTML Page with the Oracle Web Toolkit......Page 631
APPENDIX A: Answers to Self-Review Questions......Page 642
APPENDIX B: PL/SQL Formatting Guide......Page 660
APPENDIX C: Student Database Schema......Page 664
APPENDIX D: Answers to Test Your Thinking Sections......Page 672
APPENDIX E: ANSI SQL Standards......Page 750
C......Page 756
I......Page 757
P......Page 758
V......Page 759
Z......Page 760

✦ Subjects

Reference