Acknowledgments About the Authors Introduction Chapter 1 PL/SQL Concepts Chapter 2 General Programming Language Fundamentals Chapter 3 SQL in PL/SQL Chapter 4 Conditional Control: IF Statements Chapter 5 Conditional Control: CASE Statements Chapter 6 Iterative Control: Part 1 Chapter 7 Iterative Control: Part 2 Chapter 8 Error Handling and Built-In Exceptions Chapter 9 Exceptions Chapter 10 Exceptions: Advanced Concepts Chapter 11 Introduction to Cursors Chapter 12 Advanced Cursors Chapter 13 Triggers Chapter 14 Compound Triggers Chapter 15 Collections Chapter 16 Records Chapter 17 Native Dynamic SQL Chapter 18 Bulk SQL Chapter 19 Procedures Chapter 20 Functions Chapter 21 Packages Chapter 22 Stored Code Chapter 23 Object Types in Oracle Chapter 24 Oracle Supplied Packages Appendix A PL/SQL Formatting Guide Appendix B Student Database Schema Appendix C ANSI SQL Standards Appendix D Answers to Try it Yourself Sections
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 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 refl ects 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, 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 Acknowledgments About the Authors Introduction Chapter 1 PL/SQL Concepts Lab 1.1 PL/SQL in Client-Server Architecture 1.1.1 Use PL/SQL Anonymous Blocks 1.1.2 Understand How PL/SQL Gets Executed Lab 1.2 PL/SQL in SQL*Plus 1.2.1 Use Substitution Variables 1.2.2 Use the DBMS_OUTPUT.PUT_LINE Statement Chapter 1 Try It Yourself Chapter 2 General Programming Language Fundamentals Lab 2.1 PL/SQL Programming Fundamentals 2.1.1 Make Use of PL/SQL Language Components 2.1.2 Make Use of PL/SQL Variables 2.1.3 Handle PL/SQL Reserved Words 2.1.4 Make Use of Identifiers in PL/SQL 2.1.5 Make Use of Anchored Data Types 2.1.6 Declare and Initialize Variables 2.1.7 Understand the Scope of a Block, Nested Blocks, and Labels Chapter 2 Try It Yourself Chapter 3 SQL in PL/SQL Lab 3.1 PL/SQL Programming Fundamentals 3.1.1 Make Use of PL/SQL Language Components 3.1.2 Make Use of PL/SQL Variables 3.1.3 Handle PL/SQL Reserved Words 3.1.4 Make Use of Identifiers in PL/SQL 3.1.5 Make Use of Anchored Data Types 3.1.6 Declare and Initialize Variables 3.1.7 Understand the Scope of a Block, Nested Blocks, and Labels Chapter 3 Try It Yourself Chapter 4 Conditional Control: IF Statements Lab 4.1 IF Statements 4.1.1 Use the IF-THEN Statement 4.1.2 Use the IF-THEN-ELSE Statement Lab 4.2 ELSIF Statements 4.2.1 Use the ELSIF Statement Lab 4.3 Nested IF Statements 4.3.1 - Use Nested IF Statements Chapter 4 Try It Yourself Chapter 5 Conditional Control: CASE Statements Lab 5.1 CASE Statements 5.1.1 Use the CASE Statement 5.1.2 Use the SEARCHED CASE Statement Lab 5.2 CASE Expressions 5.2.1 Use the CASE Expression Lab 5.3 NULLIF and COALESCE Functions 5.3.1 Use The NULLIF Function 5.3.2 Use The COALESCE Function Chapter 5 Try It Yourself Chapter 6 Iterative Control: Part 1 Lab 6.1 Simple Loops 6.1.1 Use Simple Loops with EXIT Conditions 6.1.2 Use Simple Loops with EXIT WHEN Conditions Lab 6.2 WHILE Loops 6.2.1 Use WHILE Loops Lab 6.3 Numeric FOR Loops 6.3.1 Use Numeric FOR Loops with the IN Option 6.3.2 Use Numeric For Loops With the REVERSE Option Chapter 6 Try It Yourself Chapter 7 Iterative Control: Part 2 Lab 7.1 CONTINUE Statement 7.1.1 Use the CONTINUE Statement 7.1.2 - Use the CONTINUE WHEN Condition Lab 7.2 Nested Loops 7.2.1 Use Nested Loops Chapter 7 Try It Yourself Chapter 8 Error Handling and Built-In Exceptions Lab 8.1 Handling Errors 8.1.1 Understanding the Importance of Error Handling Lab 8.2 Built-In Exceptions 8.2.1 Use Built-In Exceptions Chapter 8 Try It Yourself Chapter 9 Exceptions Lab 9.1 Exception Scope 9.1.1 Understand the Scope of an Exception Lab 9.2 User-Defined Exceptions 9.2.1 Use User-Defined Exceptions Lab 9.3 Exception Propagation 9.3.1 Understand How Exceptions Propagate 9.3.2 Re-raise Exceptions Chapter 9 Try It Yourself Chapter 10 Exceptions: Advanced Concepts Lab 10.1 RAISE_APPLICATION_ERROR 10.1.1 Use RAISE_APPLICATION_ERROR Lab 10.2 EXCEPTION_INIT PRAGMA 10.2.1 Use EXCEPTION_INIT PRAGMA Lab 10.3 SQLCODE and SQLERRM 10.3.1 Use SQLCODE AND SQLERRM Chapter 10 Try It Yourself Chapter 11 Introduction to Cursors Lab 11.1 Cursor Manipulation 11.1.1 Make Use of Record Types 11.1.2 Process An Explicit Cursor 11.1.3 Make Use of Cursor Attributes 11.1.4 Put it All Together Lab 11.2 Using Cursor FOR LOOPS and Nesting Cursors 11.2.1 Using a Cursor FOR LOOP 11.2.2 Process Nested Cursors Chapter 11 Try It Yourself Chapter 12 Advanced Cursors Lab 12.1 Using Parameters with Cursors and Nested Cursors 12.1.1 Cursors with Parameters 12.1.2 Complex Nested Cursors Lab 12.2 FOR UPDATE and WHERE CURRENT Cursors 12.2.1 Use FOR UPDATE and WHERE CURRENT Cursors Chapter 12 Try It Yourself Chapter 13 Triggers Lab 13.1 What Triggers Are 13.1.1 Understand What a Trigger Is 13.1.2 Use Before and After Triggers Lab 13.2 Types of Triggers 13.2.1 Use ROW And STATEMENT Triggers 13.2.2 Use INSTEAD OF Triggers Chapter 13 Try It Yourself Chapter 14 Compound Triggers Lab 14.1 Mutating Table Issues 14.1.1 Understand Mutating Tables Lab 14.2 Compound Triggers 14.2.1 Understand COMPOUND Triggers Chapter 14 Try It Yourself Chapter 15 Collections Lab 15.1 PL/SQL Tables 15.1.1 Use Associative Arrays 15.1.2 Use Nested Tables Lab 15.2 Varrays 15.2.1 Use Varrays Lab 15.3 Multilevel Collections 15.3.1 Use Multilevel Collections Chapter 15 Try It Yourself Chapter 16 Records Lab 16.1 Record Types 16.1.1 Use Table-Based and Cursor-Based Records 16.1.2 Use User-Defined Records Lab 16.2 Nested Records 16.2.1 Use Nested Records Lab 16.3 Collections of Records 16.3.1 Use Collections of Records Chapter 16 Try It Yourself Chapter 17 Native Dynamic SQL Lab 17.1 EXECUTE IMMEDIATE Statements 17.1.1 Use the EXECUTE IMMEDIATE Statement 17.1.2 Use BEFORE and AFTER Triggers Lab 17.2 OPEN-FOR, FETCH, and CLOSE Statements 17.2.1 Use OPEN-FOR, FETCH, and CLOSE Statements 17.2.2 Use INSTEAD OF Triggers Chapter 17 Try It Yourself Chapter 18 Bulk SQL Lab 18.1 FORALL Statement 18.1.1 Use FORALL Statement Lab 18.2 BULK COLLECT Clause 18.2.1 Use BULK COLLECT Statement Chapter 18 Try It Yourself Chapter 19 Procedures Lab 19.1 Creating Procedures 19.1.1 Create Procedures 19.1.2 Query the Data Dictionary for Information on Procedures Lab 19.2 Passing Parameters In and Out of Procedures 19.2.1 Use IN and OUT Parameters with Procedures Chapter 19 Try It Yourself Chapter 20 Functions Lab 20.1 Creating and Using Functions 20.1.1 Create Stored Functions 20.1.2 Make Use of Functions 20.1.3 Invoke Functions in SQL Statements 20.1.4 Write Complex Functions Chapter 20 Try It Yourself Chapter 21 Packages Lab 21.1 The Benefits of Utilizing Packages 21.1.1 Create Package Specifications 21.1.2 Create Package Bodies 21.1.3 Call Stored Packages 21.1.4 Create Private Objects 21.1.5 Create Package Variables and Cursors Lab 21.2 Cursor Variables 21.2.1 Make Use of Cursor Variables Lab 21.3 Extending the Package 21.3.1 Understand how to Extend Packages Chapter 21 Try It Yourself Chapter 22 Stored Code Lab 22.1 Gathering Stored Code Information 22.1.1 Get Stored Code Information from the Data Dictionary 22.1.2 Enforce Purity Level with RESTRICT_REFERENCES Pragma 22.1.3 Overload Modules Chapter 22 Try It Yourself Chapter 23 Object Types in Oracle Lab 23.1 Object Types 23.1.1 Use Object Types 23.1.2 Use Object Types with Collections Lab 23.2 Object Type Methods 23.2.1 Use Object Type Methods Chapter 23 Try It Yourself Chapter 24 Oracle Supplied Packages Lab 24.1 Making Use of Oracle Supplied Packages to Profile PL/SQL, Access Files and Schedule Jobs 24.1.1 Access Files with UTL_FILE 24.1.2 Job Scheduling with DBMS_JOB 24.1.3 Submitting Jobs Lab 24.2 Making Use of Oracle Supplied Packages to Generate an Explain Plan and create HTML pages 24.2.1 Generate an Explain Plan Lab 24.3 Creating Web pages with the Oracle Web Toolkit 24.3.1 Create an HTML Page with the Oracle Web Toolkit Appendix A PL/SQL Formatting Guide Appendix B Student Database Schema Appendix C ANSI SQL Standards Appendix D Answers to Try it Yourself Sections
Benjamin Rosenzweig is a software development manager at Misys Treasury & Capital Markets, where he has worked since 2002. Prior to that he was a principal consultant for more than three years at Oracle Corporation in the Custom Development Department. His computer experience ranges from creating an electronic Tibetan--English dictionary in Kathmandu, Nepal, to supporting presentation centers at Goldman Sachs and managing a trading system at TIAA-CREF. Rosenzweig has been an instructor at the Columbia University Computer Technology and Application program in New York City since 1998. In 2002 he was awarded the Outstanding Teaching Award from the chair and director of the CTA program. He holds a B.A. from Reed College and a certificate in database development and design from Columbia University. His previous books with Prentice Hall are Oracle Forms Developer: The Complete Video Course (ISBN: 0-13-032124-9) and Oracle Web Application Programming for PL/SQL Developers (ISBN: 0-13-047731-1). Elena Silvestrova Rakhimov has more than 15 years of experience in database development in a wide spectrum of enterprise and business environments, ranging from nonprofit organizations to Wall Street. She currently works at Alea Software, where she serves as Senior Developer and Team Lead. Her determination to stay hands-on notwithstanding, Rakhimov has 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. She currently resides in Vancouver, Canada.