9780131172616

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

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

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

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.

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.

Amazon no longer offers textbook rentals. We do!

Oracle PL/SQL by Example

Oracle PL/SQL by Example

0131172611

Summary

Author Biography

Table of Contents

Supplemental Materials

Excerpts