Oracle PL/SQL by Example

by Rosenzweig, Benjamin; Silvestrova, Elena

• ISBN13: 9780131172616
• ISBN10: 0131172611
• Edition: 3rd
• Format: Paperback
• Copyright: 2003-09-10
• Publisher: Prentice Hall

Summary

A new edition of this title is available, ISBN-10: 0137144229 ISBN-13: 9780137144228

Author Biography

Benjamin Rosenzweig is a Loan Integration Manager at IQ Financial Systems 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

Table of Contents

Acknowledgments	xiii
About the Authors	xv
Introduction	xvii
CHAPTER 1 Programming Concepts	1	(20)
LAB 1.1 The Nature of a Computer Program and Programming Languages	2	(7)
1.1.1 Understand the Nature of Computer Programs and Programming Languages	4	(2)
1.1.2 Understand the Differences Between Interpreted and Compiled Languages	6	(3)
LAB 1.2 Good Programming Practices	9	(11)
1.2.1 Understand the Nature of Good Programming Practices	15	(1)
1.2.2 Understand Formatting Guidelines	16	(4)
CHAPTER 1 Test Your Thinking	20	(1)
CHAPTER 2 PL/SQL Concepts	21	(24)
LAB 2.1 PL/SQL in Client-Server Architecture	22	(12)
2.1.1 Use PL/SQL Anonymous Blocks	28	(2)
2.1.2 Understand How PL/SQL Gets Executed	30	(4)
LAB 2.2 PL/SQL in SQL*Plus	34	(10)
2.2.1 Use Substitution Variables	39	(1)
2.2.2 Use the DBMS OUTPUT.PUT LINE Statement	40	(4)
CHAPTER 2 Test Your Thinking	44	(1)
CHAPTER 3 General Programming Language Fundamentals	45	(24)
LAB 3.1 PL/SQL Programming Fundamentals	46	(20)
3.1.1 Make Use of PL/SQL Language Components	47	(1)
3.1.2 Make Use of PL/SQL Variables	48	(2)
3.1.3 Handle PL/SQL Reserved Words	50	(1)
3.1.4 Make Use of Identifiers in PL/SQL	51	(1)
3.1.5 Make Use of Anchored Data Types	52	(1)
3.1.6 Declare and Initialize Variables	52	(2)
3.1.7 Understand the Scope of a Block, Nested Blocks, and Labels	54	(12)
CHAPTER 3 Test Your Thinking	66	(3)
CHAPTER 4 SQL in PL/SQL	69	(20)
LAB 4.1 Making Use of DML in PL/SQL	70	(8)
4.1.1 Use the SELECT INTO Syntax for Variable Initialization	71	(1)
4.1.2 Use DML in a PL/SQL Block	72	(2)
4.1.3 Make Use of a Sequence in a PL/SQL Block	74	(4)
LAB 4.2 Making Use of SAVEPOINT	78	(9)
4.2.1 Make Use of COMMIT, ROLLBACK, and SAVEPOINT in a PL/SQL Block	82	(5)
CHAPTER 4 Test Your Thinking	87	(2)
CHAPTER 5 Conditional Control: IF Statements	89	(38)
LAB 5.1 IF Statements	90	(15)
5.1.1 Use the IF-THEN Statement	95	(1)
5.1.2 Use the IF-THEN-ELSE Statement	96	(9)
LAB 5.2 ELSIF Statements	105	(11)
5.2.1 Use the ELSIF Statement	109	(7)
LAB 5.3 Nested IF Statements	116	(8)
5.3.1 Use Nested IF Statements	119	(5)
CHAPTER 5 Test Your Thinking	124	(3)
CHAPTER 6 Conditional Control: Case Statements	127	(44)
LAB 6.1 CASE Statements	128	(18)
6.1.1 Use the CASE Statement	135	(1)
6.1.2 Use the Searched CASE Statement	136	(10)
LAB 6.2 CASE Expressions	146	(9)
6.2.1 Use the CASE Expression	150	(5)
LAB 6.3 NULLIF and COALESCE Functions	155	(13)
6.3.1 Use the NULLIF Function	159	(2)
6.3.2 Use the COALESCE Function	161	(7)
CHAPTER 6 Test Your Thinking	168	(3)
CHAPTER 7 Error Handling and Built-In Exceptions	171	(22)
LAB 7.1 Handling Errors	172	(7)
7.1.1 Understanding the Importance of Error Handling	175	(4)
LAB 7.2 Built-In Exceptions	179	(12)
7.2.1 Use Built-In Exceptions	185	(6)
CHAPTER 7 Test Your Thinking	191	(2)
CHAPTER 8 Iterative Control	193	(50)
LAB 8.1 Simple Loops	194	(14)
8.1.1 Use Simple Loops with EXIT Conditions	198	(2)
8.1.2 Use Simple Loops with EXIT WHEN Conditions	200	(8)
LAB 8.2 WHILE Loops	208	(11)
8.2.1 Use WHILE Loops	213	(6)
LAB 8.3 Numeric FOR Loops	219	(13)
8.3.1 Use Numeric FOR Loops with the IN Option	224	(1)
8.3.2 Use Numeric FOR Loops with the REVERSE Option	225	(7)
LAB 8.4 Nested Loops	232	(9)
8.4.1 Use Nested Loops	235	(6)
CHAPTER 8 Test Your Thinking	241	(2)
CHAPTER 9 Introduction to Cursors	243	(32)
LAB 9.1 Cursor Manipulation	244	(22)
9.1.1 Make Use of Record Types	249	(1)
9.1.2 Processing an Explicit Cursor	250	(5)
9.1.3 Making Use of Cursor Attributes	255	(1)
9.1.4 Putting It All Together	256	(10)
LAB 9.2 Using Cursor FOR Loops and Nesting Cursors	266	(8)
9.2.1 Use Cursor FOR Loops	267	(1)
9.2.2 Process Nested Cursors	267	(7)
CHAPTER 9 Test Your Thinking	274	(1)
CHAPTER 10 Exceptions	275	(42)
LAB 10.1 Exception Scope	276	(11)
10.1.1 Understand the Scope of an Exception	279	(8)
LAB 10.2 User-Defined Exceptions	287	(11)
10.2.1 Use User-Defined Exceptions	292	(6)
LAB 10.3 Exception Propagation	298	(17)
10.3.1 Understand How Exceptions Propagate	304	(1)
10.3.2 Re-raising Exceptions	305	(10)
CHAPTER 10 Test Your Thinking	315	(2)
CHAPTER 11 Exceptions: Advanced Concepts	317	(28)
LAB 11.1 RAISE APPLICATION_ERROR	318	(11)
11.1.1 Use RAISE APPLICATION_ERROR	322	(7)
LAB 11.2 EXCEPTION_INIT Pragma	329	(7)
11.2.1 Use EXCEPTION_INIT Pragma	331	(5)
LAB 11.3 SQLCODE and SQLERRM	336	(7)
11.3.1 Use SQLCODE and SQLERRM	339	(4)
CHAPTER 11 Test Your Thinking	343	(2)
CHAPTER 12 Procedures	345	(14)
LAB 12.1 Creating Procedures	346	(6)
12.1.1 Create Procedures	348	(1)
12.1.2 Query the Data Dictionary for Information on Procedures	349	(3)
LAB 12.2 Passing Parameters 1n and Out of Procedures	352	(5)
12.2.1 Use IN and OUT Parameters with Procedures	354	(3)
CHAPTER 12 Test Your Thinking	357	(2)
CHAPTER 13 Functions	359	(10)
LAB 13.1 Creating and Using Functions	360	(8)
13.1.1 Create Stored Functions	361	(1)
13.1.2 Make Use of Functions	362	(1)
13.1.3 Invoke Functions in SQL Statements	363	(1)
13.1.4 Write Complex Functions	363	(5)
CHAPTER 13 Test Your Thinking	368	(1)
CHAPTER 14 Packages	369	(16)
LAB 14.1 The Benefits of Utilizing Packages	370	(13)
14.1.1 Create the Package Specification	371	(2)
14.1.2 Create the Package Body	373	(1)
14.1.3 Call Stored Packages	374	(1)
14.1.4 Create Private Objects	375	(1)
14.1.5 Create Package Variables and Cursors	376	(7)
CHAPTER 14 Test Your Thinking	383	(2)
CHAPTER 15 Advanced Cursors	385	(22)
LAB 15.1 Using Parameters with Cursors and FOR UPDATE Cursors	386	(9)
15.1.1 Use Parameters in a Cursor	387	(2)
15.1.2 Use a FOR UPDATE Cursor	389	(2)
15.1.3 WHERE CURRENT OF Clause	391	(4)
LAB 15.2 Cursor Variables	395	(12)
15.2.1 Make Use of Cursor Variables	399	(8)
CHAPTER 16 Stored Code	407	(20)
LAB 16.1 Gathering Stored Code Information	408	(17)
16.1.1 Get Stored Code Information from the Data Dictionary	408	(2)
16.1.2 Enforce Purity Level with RESTRICT_REFERENCES Pragma	410	(2)
16.1.3 Overload Modules	412	(13)
CHAPTER 16 Test Your Thinking	425	(2)
CHAPTER 17 Triggers	427	(40)
LAB 17.1 What Triggers Are	428	(13)
17.1.1 Understand What a Trigger Is	433	(2)
17.1.2 Use BEFORE and AFTER Triggers	435	(6)
LAB 17.2 Types of Triggers	441	(15)
17.2.1 Use ROW and STATEMENT Triggers	447	(1)
17.2.2 Use INSTEAD OF Triggers	448	(8)
LAB 17.3 Mutating Table Issues	456	(10)
17.3.1 Understand Mutating Tables	460	(6)
CHAPTER 17 Test Your Thinking	466	(1)
CHAPTER 18 Collections	467
LAB 18.1 PL/SQL Tables	468	(22)
18.1.1 Use Index-By Tables	478	(2)
18.1.2 Use Nested Tables	480	(10)
LAB 18.2 Varrays	490	(10)
18.2.1 Use Varrays	494	(6)
LAB 18.3 Multilevel Collections	500	(8)
18.3.1 Use Multilevel Collections	502	(6)
CHAPTER 18 Test Your Thinking	508
CHAPTER 19 Records 51	1	(548)
LAB 19.1 Records	512	(21)
19.1.1 Use Table-Based and Cursor-Based Records	520	(1)
19.1.2 Use User-Defined Records	521	(12)
LAB 19.2 Nested Records	533	(8)
19.2.1 Use Nested Records	535	(6)
LAB 19.3 Collections of Records	541	(7)
19.3.1 Use Collections of Records	542	(6)
CHAPTER 19 Test Your Thinking	548	(1)
CHAPTER 20 Native Dynamic SQL	549	(28)
LAB 20.1 EXECUTE IMMEDIATE Statements	550	(15)
20.1 Use the EXECUTE IMMEDIATE Statement	557	(8)
LAB 20.2 OPEN-FOR, FETCH, and CLOSE Statements	565	(11)
20.2.1 Use OPEN-FOR, FETCH, and CLOSE Statements	568	(8)
CHAPTER 20 Test Your Thinking	576	(1)
CHAPTER 21 Oracle Supplied Packages	577	(40)
LAB 21.1 Make Use of Oracle Supplied Packages	578	(39)
21.1.1 Access Files with UTL_F1LE	604	(1)
21.1.2 Schedule Jobs with DBMS JOB	604	(1)
21.1.3 Generate an Explain Plan with DBMS XPLAN	605	(1)
21.1.4 Create an HTML Page with the Oracle Web Toolkit	606	(11)
APPENDIX A Answers to Self-Review Questions	617	(18)
APPENDIX B PL/SQL Formatting Guide	635	(4)
APPENDIX C Student Database Schema	639	(8)
APPENDIX D Answers to Test Your Thinking Sections	647	(78)
APPENDIX E ANSI SQL Standards	725	(6)
Index	731

Excerpts

Preface Oracle PL/SQL by Example, 3rd 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, Oracle PL/SQL by Example 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.