Oracle PL/SQL Interactive Workbook

Summary

Build Oracle PL/SQL applications-now. Fully updated for Oracle 9i No Oracle PL/SQL experience necessary. Real-life labs and "Test Your Thinking" Q&As Start developing applications with Oracle PL/SQL-fast. This integrated book-and-Web learning solution teaches all the Oracle PL/SQL skills you need, hands on, through real-world labs, exercises, projects, and a complete Web-based training site. Completely updated for Oracle 9i, it covers all the fundamentals-and a whole lot more. Master PL/SQL syntax, iterative and conditional control, scoping, anchored datatypes, cursors, triggers, security, tables, and packaging-plus powerful new techniques for working with exceptions, cursors, collections, and records. Your free Web-based training module includes a Virtual Study Lounge where you can interact with other learners, work on new projects, and get updates. Totally integrated with a FREE, state-of-the-art Oracle 9i learning Web site.Every Prentice Hall Oracle Interactive Workbook is fully integrated with its own exclusive Web site, giving you all this and more: "Test Your Thinking" project solutions and detailed explanations Additional self-review exercises with instant feedback and explanations An exclusive Virtual Study Lounge where you can interact with other students. Just the facts. No endless, boring discussions here. You'll learn hands on through practical exercises, self-review questions, and real-world answers. Exclusive "Test Your Thinking" projects guarantee you'll go beyond rote knowledge to really master the subject. It's an integrated learning system that's proven to work. Dozens of exercises cover the real-world tasks that matter most.Hundreds of self-review questions and answers make sure you understand.

Author Biography

BENJAMIN ROSENZWEIG is an Integration Specialist at IQ Financial Systems. Prior to that he was a principal consultant for more than 3 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 presentations centers at Goldman Sachs and managing a trading system at TIAA-CREF. His previous Prentice Hall PTR publications include Oracle PL/SQL: The Complete Video Course and Oracle Forms Developer: The Complete Video Course.

ELENA SILVESTROVA, a senior software engineer for a prominent New York brokerage firm and securities dealer, has taught relational database programming in Columbia University's CTA program during the past four years. She studied analysis and design at Columbia University and applied mathematics at Baku State University in Azerbaijan.

(NOTE: Each chapter concludes with Test Your Thinking, except chapter 15.)
Acknowledgments.
About the Authors.
Introduction.
1. Programming Concepts.

LAB 1.1 The Nature of a Computer Program and Programming Languages. LAB 1.2 Good Programming Practices.

2. PL/SQL Concepts.

LAB 2.1 PL/SQL in Client-Server Architecture. LAB 2.2 PL/SQL in SQL*Plus.

3. General Programming Language Fundamentals.

LAB 3.1 PL/SQL Programming Fundamentals.

4. SQL in PL/SQL.

LAB 4.1 Making Use of DML in PL/SQL. LAB 4.2 Making Use of SAVEPOINT.

5. Conditional Control: IF Statements.

LAB 5.1 IF Statements. LAB 5.2 ELSIF Statements. LAB 5.3 Nested IF Statements.

6. Conditional Control: Case Statements.

LAB 6.1 CASE Statements. LAB 6.2 CASE Expressions. LAB 6.3 NULLIF and COALESCE Functions. CHAPTER 7 Error Handling and Built-In Exceptions.
LAB 7.1 Handling Errors. LAB 7.2 Built-In Exceptions.

8. Iterative Control.

LAB 8.1 Simple Loops. LAB 8.2 WHILE Loops. LAB 8.3 Numeric FOR Loops. LAB 8.4 Nested Loops.

9. Introduction to Cursors.

LAB 9.1 Cursor Manipulation. LAB 9.2 Using Cursor FOR Loops and Nesting Cursors.

10. Exceptions.

LAB 10.1 Exception Scope. LAB 10.2 User-Defined Exceptions. LAB 10.3 Exception Propagation.

11. Exceptions: Advanced Concepts.

LAB 11.1 RAISE_APPLICATION_ERROR. LAB 11.2 EXCEPTION_INIT Pragma. LAB 11.3 SQLCODE and SQLERRM.

12. Procedures.

LAB 12.1 Creating Procedures. LAB 12.2 Passing Parameters In and Out of Procedures.

13. Functions.

LAB 13.1 Creating and Using Functions.

14. Packages.

LAB 14.1 The Benefits of Utilizing Packages.

15. Advanced Cursors.

LAB 15.1 Using Parameters with Cursors and FOR UPDATE Cursors. LAB 15.2 Cursor Variables.

16. Stored Code.

LAB 16.1 Gathering Stored Code Information.

17. Triggers.

LAB 17.1 What Triggers Are. LAB 17.2 Types of Triggers. LAB 17.3 Mutating Table Issues.

18. Collections.

LAB 18.1 PL/SQL Tables. LAB 18.2 Varrays. LAB 18.3 Multilevel Collections.

19. Records.

LAB 19.1 Records. LAB 19.2 Nested Records. LAB 19.3 Collections of Records.

Appendix A. Answers to Self-Review Questions.
Appendix B. PL/SQL Formatting Guide.
Appendix C. Student Database Schema.
Appendix D. Answers to Test Your Thinking Sections.
Appendix E. Oracle 9i SQL New Features.
Index.

Supplemental Materials

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

Excerpts

Introduction The Oracle PL/SQL Interactive Workbook, 2nd edition, presents the Oracle PL/SQL programming language in a unique and highly effective format. It challenges you to learn Oracle PL/SQL by using it rather than by simply reading about it. Just as a grammar workbook would teach you about nouns and verbs by first showing you examples and then asking you to write sentences, the Oracle PL/SQL Interactive Workbook teaches you about cursors, loops, procedures, triggers, and so on by first showing you examples and then asking you to create these objects yourself. Who This Book Is For This book is intended for anyone who needs a quick but detailed introduction to programming with Oracle's PL/SQL language. The ideal readers are those with some relational database experience, with some Oracle experience, specifically with SQL and SQL*Plus, but with little or no experience with PL/SQL or with most other programming languages. The content of this book is based on the material that is taught in an Introduction to PL/SQL class at Columbia University's Computer Technology and Applications (CTA) program in New York City. The student body is rather diverse, in that there are some students who have years of experience with information technology (IT) and programming, but no experience with Oracle PL/SQL, and then there are those with absolutely no experience in IT or programming. The content of the book, like the class, is balanced to meet the needs of both extremes. The exercises in this book can be used as lab and homework assignments to accompany the lectures in such a PL/SQL course. How This Book Is Organized The intent of this workbook is to teach you about Oracle PL/SQL by presenting you with a series of challenges followed by detailed solutions to those challenges. The basic structure of each chapter is as follows: Chapter Lab Exercises Exercise Answers (with detailed discussion) Self-Review Questions Lab . . . Test Your Thinking Questions Each chapter contains interactive labs that introduce topics about Oracle PL/SQL. The topics are discussed briefly and then explored though exercises, which are the heart of each lab. Each exercise consists of a series of steps that you will follow to perform a specific task, along with questions that are designed to help you discover the important things about PL/SQL programming on your own. The answers to these questions are given at the end of the Exercises, along with more in-depth discussion of the concepts explored. The exercises are not meant to be closed-book quizzes to test your knowledge. On the contrary, they are intended to act as your guide and walk you through a task. You are encouraged to flip back and forth from the exercise question section to the exercise answer section so that, if need be, you can read the answers and discussions as you go along. At the end of each lab is a series of multiple-choice self-review questions. These are meant to be closed-book quizzes to test how well you understood the lab material. The answers to these questions appear in Appendix A. Finally, at the end of each chapter you will find a Test Your Thinking section, which consists of a series of projects designed to solidify all of the skills you have learned in the chapter. If you have successfully completed all of the labs in the chapter, you should be able to tackle these projects with few problems. You will find guidance and/or solutions to these in Appendix D and at the companion Web site. About The Companion Web Site The companion Web site is located at http://www.phptr.com/rosenzweig2e/ Here you will find two very important things: Files you will need before you begin reading the workbook: all of the exercises and questions are based on a sample database called STUDENT. The files required to create and install the STUDEN