Amazon no longer offers textbook rentals. We do!

Oracle 9I Developer: Pl/SQL Programming

by: Casteel, Joan

Oracle 9I Developer: Pl/SQL Programming

by: Casteel, Joan

ISBN 13:
9780619159092
ISBN 10:
061915909X
Edition: 1st
Format: Paperback
Copyright: 05/09/2003
Publisher: Cengage Learning
Newer Edition

Rent

Sorry, this item is currently unavailable.

Note: Supplemental materials are not guaranteed with Rental or Used book purchases.

Extend or Purchase Your Rental at Any Time

Need to keep your rental past your due date? At any time before your due date you can extend or purchase your rental through your account.

Summary

This book offers complete coverage of the subject of programming with PL/SQL, including an introduction to PL/SQL at the beginning up through coverage of advanced topics. Also helps to prepare students for the Oracle9i PL/SQL Programming certification exam.

PREFACE

CHAPTER ONE Introduction to PL/SQL

(26)

Programming and Procedural Languages

(1)

Application Programming and PL/SQL

(3)

Application Programming

(2)

History of PL/SQL

(1)

Application Models

(3)

Two-Tier or Client/Server Application Model

(1)

Three-Tier Application Model

(1)

Documentation on the Web and on CD-ROM

(1)

SQL and PL/SQL Tools

(4)

Software Tools Used in this Book

(3)

Third-Party PL/SQL Development Tools

(1)

Databases Used in this Book

(4)

Chapter Summary

(1)

Review Questions

(2)

Hands-on Assignments

(3)

Assignment 1-1: Review Data in the Brewbean's Database

(1)

Assignment 1-2: Review Third-Party Software Tools

(1)

Assignment 1-3: Identify Processing Steps

(1)

Assignment 1-4: Use OTN Documentation

(1)

Case Projects

(4)

Case 1-1: Review Procedure Builder Documentation

(1)

Case 1-2: The More Movies Database

(3)

CHAPTER TWO Handling Data in PL/SQL Blocks

(56)

The Current Challenge in the Brewbean's Application

(1)

PL/SQL Block Structure

(1)

Working with Variables

(1)

Working with Scalar Variables

(4)

Variable Declarations in Code

(1)

Variable Initialization

(1)

NOT NULL and CONSTANT

(1)

The Role of Scalar Variables in the BEGIN Section

(1)

Using DBMS OUTPUT to Check Values

(1)

Including SQL Within a PL/SQL Block

(3)

Executing a PL/SQL Block with Errors

(4)

Working with Host or Bind Variables

(1)

Using the %TYPE Attribute

(1)

Working with Composite Data Types

(7)

Record Data Type

(2)

The Role of the %ROWTYPE Attribute

(1)

Table of Records

(4)

Processing with IF Statements and Looping Actions

(2)

Simple IF Statements

(1)

Loops: Basic and FOR

(1)

Working with Collections

(3)

Index-by Tables

(3)

Other Collections: VARRAPS and Nested Tables

(1)

Working with Cursors

(11)

Implicit Cursors

(1)

Explicit Cursors

(8)

Cursor Variables

(1)

Working with Variable Scope

(4)

Chapter Summary

(1)

Review Questions

(2)

Advanced Review Questions

(1)

Hands-on Assignments

(7)

Assignment 2-1: Use Scalar Variables

(2)

Assignment 2-2: Use a Record Variable

(1)

Assignment 2-3: Use an Explicit Cursor

(2)

Assignment 2-4: Use a CURSOR FOR loop

(1)

Assignment 2-5: Use Implicit Cursors

(1)

Assignment 2-6: Use Variable Scope

(1)

Assignment 2-7: Use Scalar Variables for Data Retrieval

(1)

Assignment 2-8: Use a Record Variable for Data Retrieval

(1)

Case Projects

(1)

Case 2-1:Variable Types

(1)

Case 2-2: More Movie Rentals

(1)

CHAPTER THREE PL/SQL Processing

(56)

The Current Challenge in the Brewbean's Application

(1)

Rebuilding Your Database

(1)

Control Structures

(13)

IF Statement Logic

(8)

CASE Statements

(5)

Looping Constructs

(6)

Basic Loop

(2)

WHILE Loop

100

(1)

FOR Loop

101

(2)

Common Errors While Using Looping Statements

103

(1)

GOTO Statement

104

(1)

Exception Handlers

105

(10)

Predefined Oracle Errors

105

(7)

Non-Predefined Oracle Errors

112

(1)

User-Defined Exception

113

(2)

Additional Exception Concepts

115

(9)

WHEN OTHERS, SQLCODE, and SQLERRM

115

(4)

RAISE_APPLICATION_ERROR

119

(1)

Exception Propagation

120

(4)

Commenting Code

124

(1)

Chapter Summary

125

(1)

Review Questions

126

(2)

Advanced Review Questions

128

(1)

Hands-on Assignments

129

(7)

Assignment 3-1: Use IF Statements

129

(1)

Assignment 3-2: Use Searched CASE Statements

130

(2)

Assignment 3-3: Use a WHILE Loop

132

(1)

Assignment 3-4: Use Exception Handling

133

(2)

Assignment 3-5: Work with IF Statements

135

(1)

Assignment 3-6: Perform Exception Handling of Predefined Errors

135

(1)

Assignment 3-7: Perform Exception Handling of Non-Predefined Errors

136

(1)

Assignment 3-8: Perform Exception Handling with User-Defined Errors

136

(1)

Case Projects

136

(3)

Case 3-1: Brewbean's Application Exception Handlers

136

(1)

Case 3-2: More Movie Rentals

137

(2)

CHAPTER FOUR Procedures

139

(40)

The Current Challenge in the Brewbean's Application

140

(1)

Rebuilding Your Database

141

(1)

Introduction to Named Program Units

142

(2)

Client and Server Considerations

143

(1)

Types of Named Program Units

143

(1)

Making Procedures Reusable: Parameters

144

(1)

Create Procedure Statement

145

(2)

The Name

146

(1)

The Mode

146

(1)

The Data Type

146

(1)

Create a Procedure in SQL*Plus

147

(5)

When a CREATE PROCEDURE Statement Produces Errors

148

(1)

Testing a Procedure

149

(3)

Using the IN OUT Parameter Mode

152

(1)

Calling a Procedure from Another PL/SQL Block

153

(3)

Creating a Procedure That Calls Another Procedure

153

(1)

Testing the Procedure

154

(2)

DESCRIBE Command

156

(1)

Debugging in SQL*Plus by Displaying Messages to the Screen

157

(5)

Software Utilities Available to Assist in Program Unit Development

162

(1)

Subprograms

162

(1)

Exception Handling and Transaction Scope

163

(4)

Error Handling Using RAISE APPLICATION ERROR

167

(2)

Removing Procedures

169

(1)

Chapter Summary

169

(1)

Review Questions

170

(2)

Advanced Review Questions

172

(1)

Hands-on Assignments

173

(5)

Assignment 4-1: Create a Procedure

173

(1)

Assignment 4-2: Use a Procedure with IN Parameters

174

(1)

Assignment 4-3: Calculate the Tax on an Order

174

(1)

Assignment 4-4: Update Columns in a Table

175

(1)

Assignment 4-5: Update the Status of an Order

175

(1)

Assignment 4-6: Return Order Status Information

176

(1)

Assignment 4-7: Identify Customers

176

(1)

Assignment 4-8: Add Items to a Cart

177

(1)

Assignment 4-9: Create a Logon Procedure

177

(1)

Case Projects

178

(1)

Case 4-1: Reporting and Analysis Summary Tables

178

(1)

Case 4-2:The More Movie Rentals Company Rental Process

178

(1)

CHAPTER FIVE Functions

179

(34)

The Current Challenge in the Brewbean's Application

180

(1)

Rebuilding Your Database

181

(1)

An Introduction to Functions

182

(2)

Creating a Stored Function in SQL*Plus

184

(6)

Invoking and Testing a Created Function

185

(2)

Using a Function in an SQL Statement

187

(1)

Building and Testing a Function for the Brewbean's Member Name Display

188

(2)

Using the OUT Parameter Mode in a Function

190

(2)

Multiple RETURN Statements

192

(1)

Using a RETURN Statement in a Procedure

193

(1)

Actual and Formal Parameter Constraints

194

(2)

Techniques of Passing Parameter Values

196

(1)

Controlling Which Value Passing Technique Is Used

196

(2)

Function Purity Levels

198

(3)

Data Dictionary Information on Program Units

201

(1)

Deleting Program Units

202

(1)

Chapter Summary

203

(1)

Review Questions

204

(2)

Advanced Review Questions

206

(2)

Hands-on Assignments

208

(3)

Assignment 5-1: Format Numbers as Currency

208

(1)

Assignment 5-2: Calculate Total Shopper Spending

209

(1)

Assignment 5-3: Calculate the Count of Orders by a Shopper

209

(1)

Assignment 5-4: Identify the Day of the Week for the Order Date

210

(1)

Assignment 5-5: Calculate Days Between Ordering and Shipping

210

(1)

Assignment 5-6: Identify the Description of an Order Status Code

210

(1)

Assignment 5-7: Calculate the Tax Amount for an Order

211

(1)

Assignment 5-8: Identify Products That Are on Sale

211

(1)

Case Projects

211

(2)

Case 5-1: Update Basket Data Upon Order Completion

211

(1)

Case 5-2: More Movies Rentals

212

(1)

CHAPTER SIX PL/SQL Packages

213

(42)

The Current Challenge in the Brewbean's Application

214

(1)

Rebuilding Your Database

214

(1)

Package Specification

215

(2)

Declarations in a Package Specification

215

(1)

Ordering of Items Within a Specification

216

(1)

Package Body

217

(3)

Invoking Package Constructs

220

(2)

Package Construct Scope

222

(2)

Package Global Constructs

224

(7)

Testing the Persistence of Packaged Variables

225

(2)

Package Specifications with No Body

227

(1)

Improving Processing Efficiency

228

(3)

Forward Declaration in Packages

231

(3)

One Time Only Procedure

234

(3)

Overloading Program Units in Packages

237

(3)

Managing Packaged Function SQL Restrictions

240

(4)

Why Developers Indicate Purity Levels

241

(1)

PRAGMA RESTRICT_REFERENCES in Action

242

(1)

Default Purity Level for Packaged Functions

243

(1)

Functions Written in External Languages

243

(1)

Program Unit and Package Execute Privileges

244

(1)

Data Dictionary Information for Packages

244

(2)

Deleting Packages

246

(1)

Chapter Summary

246

(1)

Review Questions

247

(3)

Advanced Review Questions

250

(1)

Hands-on Assignments

251

(3)

Assignment 6-1: Create a Package

251

(1)

Assignment 6-2: Use Packaged Program Units

251

(1)

Assignment 6-3: Create a Package with Private Program Units

252

(1)

Assignment 6-4: Use Packaged Variables

252

(1)

Assignment 6-5: Package Overloading

253

(1)

Assignment 6-6: Create a Package with a Specification Only

253

(1)

Assignment 6-7: Use a Cursor in a Package

253

(1)

Assignment 6-8: Use a One Time Only Procedure in a Package

254

(1)

Case Projects

254

(1)

Case 6-1: Brewbean's Order Checkout Package

254

(1)

Case 6-2: More Movies Program Unit Packaging

254

(1)

CHAPTER SEVEN Program Unit Dependencies

255

(38)

The Current Challenge in the Brewbean's Application

256

(1)

Rebuilding Your Database

256

(1)

Local Dependency Activity

257

(4)

Identifying Direct and Indirect Dependencies

261

(1)

Data Dictionary Views for Dependencies

262

(1)

The Dependency Tree Utility

263

(5)

Package Dependencies

268

(4)

Remote Object Dependencies

272

(4)

Remote Dependency Invalidation Methods

276

(2)

Tips to Avoid Recompilation Errors

278

(1)

Granting Program Unit Privileges

279

(2)

Chapter Summary

281

(2)

Review Questions

283

(3)

Advanced Review Questions

286

(2)

Hands-on Assignments

288

(4)

Assignment 7-1: Review Dependency Information in the Data Dictionary

288

(1)

Assignment 7-2: Test Dependencies on Stand-alone Program Units

289

(1)

Assignment 7-3: Test Dependencies on Packaged Program Units

290

(1)

Assignment 7-4: Test Remote Object Dependencies

291

(1)

Assignment 7-5: Identify All Dependencies Using the Dependency Tree Utility

291

(1)

Assignment 7-6: Review the utldtree.sgl Script

292

(1)

Assignment 7-7: Avoid Recompilation Errors

292

(1)

Assignment 7-8: Identify the Types of Dependencies

292

(1)

Case Projects

292

(1)

Case 7-1: The Brewbean's Application Maintenance

292

(1)

Case 7-2: The More Movies Rental Application

292

(1)

CHAPTER EIGHT Database Triggers

293

(42)

The Current Challenge in the Brewbean's Application

294

(2)

Rebuilding Your Database

296

(1)

Introduction to Database Triggers

296

(8)

Database Trigger Syntax and Options

297

(1)

Database Trigger Code Example

298

(1)

Trigger Timing and Correlation Identifiers

298

(3)

Trigger Events

301

(1)

Trigger Body

301

(2)

Conditional Predicates

303

(1)

Creating and Testing a DML Trigger in SQL*Plus

304

(3)

Creating and Testing an Instead-Of Trigger

307

(4)

System Triggers

311

(2)

Applying Triggers to Address Processing Needs

313

(3)

Restrictions of Trigger Use Including Mutating Tables

316

(4)

The ALTER TRIGGER Statement

320

(1)

Delete a Trigger

321

(1)

Data Dictionary Information for Triggers

321

(1)

Chapter Summary

322

(1)

Review Questions

323

(3)

Advanced Review Questions

326

(1)

Hands-on Assignments

327

(6)

Assignment 8-1: Create a Trigger to Address Product Restocking

327

(1)

Assignment 8-2: Update Stock Information When a Product Request Is Filled

328

(2)

Assignment 8-3: Update the Stock Level If a Product Fulfillment Is Cancelled

330

(1)

Assignment 8-4: Update Stock Levels When an Order Is Cancelled

331

(1)

Assignment 8-5: Process Discounts

331

(1)

Assignment 8-6: Use Triggers to Maintain Referential Integrity

332

(1)

Assignment 8-7: Update Summary Data Tables

332

(1)

Assignment 8-8: Maintain an Audit Trail of Product Table Changes

333

(1)

Case Projects

333

(2)

Case 8-1: Map the Flow of Database Triggers

333

(1)

Case 8-2: More Movies Inventory Processing

334

(1)

CHAPTER NINE Oracle-Supplied Packages

335

(46)

The Current Challenge in the Brewbean's Application

336

(1)

Rebuilding Your Database

337

(1)

Communications

337

(9)

DBMS_PIPE Package

338

(2)

DBMS_ALERT Package

340

(1)

UTL_SMTP Package

341

(3)

UTL_HTTP Package

344

(1)

UTL_TCP Package

345

(1)

Generating Output

346

(8)

DBMS_OUTPUT Package

346

(5)

UTL_FILE Package

351

(3)

Large Objects

354

(3)

DBMS_LOB Package

355

(1)

Using DBMS_LOB to Manipulate Images

355

(2)

Dynamic SQL and PL/SQL

357

(2)

Miscellaneous Packages

359

(10)

DBMS_JOB Package

359

(7)

DBMS_DDL Package

366

(2)

Exploring Additional Oracle-Supplied Packages

368

(1)

Chapter Summary

369

(1)

Review Questions

370

(2)

Advanced Review Questions

372

(1)

Hands-on Assignments

373

(6)

Assignment 9-1: Use the DBMS PIPE Package

373

(1)

Assignment 9-2: Use the DBMS ALERT Package

374

(1)

Assignment 9-3: Use the DBMS DDL Package

375

(2)

Assignment 9-4: Use the UTL FILE Package to Read and Insert Data

377

(1)

Assignment 9-5: Use the UTL FILE Package to Insert Data Columns

377

(1)

Assignment 9-6: Send E-mail Using UTL SMTP

378

(1)

Assignment 9-7: Use the DBMS_JOB Package

378

(1)

Assignment 9-8: Use DBMS_OUTPUT

379

(1)

Case Projects

379

(2)

Case 9-1: Search Oracle Built-In Packages

379

(1)

Case 9-2: The More Movies Company

379

(2)

CHAPTER TEN Introduction to Dynamic SQA and Object Technology

381

(44)

The Current Challenge in the Brewbean's Application

382

(1)

Rebuilding Your Database

382

(1)

Dynamic SQL

383

(16)

The DBMS_SQL Package

385

(9)

Native Dynamic SQL

394

(4)

DBMS_SQL Versus Native Dynamic SQL

398

(1)

Object Technology

399

(15)

Creating Object Types

399

(1)

Using an Object Type

400

(3)

Object Methods

403

(3)

Object Relations

406

(2)

REF Pointers Versus Foreign Keys

408

(2)

Object Views

410

(2)

Sorting and Comparing Object Type Columns

412

(2)

Chapter Summary

414

(1)

Review Questions

415

(4)

Hands-on Assignments

419

(4)

Assignment 10-1: Use the DBMS SQL Package

419

(1)

Assignment 10-2: Use Native Dynamic SQL

420

(1)

Assignment 10-3: Create an Object Type

420

(1)

Assignment 10-4: Create Object Views

421

(1)

Assignment 10-5: Create a Product Object Type with Sort Capability

422

(1)

Assignment 10-6: Use Native Dynamic SQL

423

(1)

Assignment 10-7: Object-oriented Programming

423

(1)

Assignment 10-8: Business Intelligence

423

(1)

Case Projects

423

(2)

Case 10-1: The Brewbean's Ad Hoc Query System

423

(1)

Case 10-2: The More Movies Database

423

(2)

CHAPTER ELEVEN Performance Tuning

425

(40)

The Current Challenge in the Brewbean's Application

426

(1)

Rebuilding Your Database

426

(1)

Tuning Concepts and Issues

426

(13)

Identifying Problem Areas in Coding

427

(5)

Processing and the Optimizer

432

(2)

The Cost-Based Optimizer

434

(1)

The Explain Plan and AUTOTRACE

434

(4)

Timing Feature

438

(1)

SQL Statement Tuning

439

(13)

Avoiding Unnecessary Column Selection

439

(1)

Cost Versus Rule Basis

440

(2)

Index Suppression

442

(3)

Concatenated Indexes

445

(1)

Subqueries

446

(2)

Joins

448

(1)

Optimizer Hints

448

(4)

PL/SQL Statement Tuning

452

(4)

Program Unit Iterations

452

(2)

Using ROWID When Updating

454

(1)

Variable Comparisons with the Same Data Type

454

(1)

Ordering Conditions by Frequency

455

(1)

Using the PLS_INTEGER llata Type

455

(1)

Pinning Stored Program Units

456

(1)

Chapter Summary

456

(1)

Review Questions

457

(2)

Hands-on Assignments

459

(4)

Assignment 11-1: Review Statement Execution Plans

459

(1)

Assignment 11-2: Use the Timing Feature in SQL*Plus

460

(1)

Assignment 11-3: Compare Explain Plans

461

(1)

Assignment 11-4: Use ROWID to Improve Updates

461

(1)

Assignment 11-5: Index Suppression

462

(1)

Assignment 11-6: Optimizer Hints

462

(1)

Assignment 11-7: Execution Plan

462

(1)

Assignment 11-8: Focus Tuning Efforts

463

(1)

Case Projects

463

(2)

Case 11-1: Brewbean's Professional Development

463

(1)

Case 11-2: More Movies Performance Tuning

463

(2)

APPENDIX A Tables for the Brewbean's Database

465

(12)

BB_SHOPPER

466

(1)

BB_BASKET

467

(1)

BB_BASKETITEM

468

(2)

BB_PRODUCT

470

(1)

BB_PRODUCTOPTION

471

(1)

BB_PRODUCTOPTIONDETAIL

472

(1)

BB_PRODUCTOPTIONCATEGORY

473

(1)

BB_DEPARTMENT

473

(1)

BB_BASKETSTATUS

474

(1)

BB_TAX

474

(1)

BB_SHIPPING

475

(2)

APPENDIX B Procedure Builder

477

(22)

Rebuilding Your Database

478

(1)

Creating a Procedure Using Procedure Builder

479

(6)

Starting Procedure Builder

479

(1)

Using the Program Unit Editor

480

(3)

Testing the Procedure in the Interpreter Panel

483

(2)

Debugging with Procedure Builder

485

(12)

Working with Breakpoints

485

(8)

Displaying Values to the Screen

493

(4)

Appendix Summary

497

(2)

APPENDIX C TOAD (Tool for Oracle Application Developers)

499

(22)

Rebuilding Your Database

500

(1)

Create a Procedure Using TOAD

500

(19)

Starting TOAD

500

(2)

Using the Procedure Editor

502

(4)

Testing a Procedure with TOAD

506

(2)

Debugging with TOAD

508

(1)

Creating Breakpoints

509

(8)

Displaying Variables

517

(2)

Appendix Summary

519

(2)

APPENDIX D

PL/SQL and Oracle9i Developer Forms (available at www.course.com via the "Student Downloads" link on the Web page for this textbook)

GLOSSARY

521

(4)

INDEX

525

Amazon no longer offers textbook rentals. We do!

Oracle 9I Developer: Pl/SQL Programming

Oracle 9I Developer: Pl/SQL Programming

9780619159092

061915909X

Summary

Table of Contents

Supplemental Materials

Write a Review

Amazon no longer offers textbook rentals. We do!

Oracle 9I Developer: Pl/SQL Programming

Oracle 9I Developer: Pl/SQL Programming

9780619159092

061915909X

Summary

Table of Contents

Supplemental Materials

Write a Review

Related Products