Oracle 9I Developer: Pl/SQL Programming,9780619159092

Oracle 9I Developer: Pl/SQL Programming

by
Edition: 1st
ISBN13: 9780619159092
ISBN10: 061915909X
Format: Paperback
Pub. Date: 2003-05-09
Publisher(s): Cengage Learning
Upgraded Edition: Click here!
List Price: $167.95

Rent Textbook

Select for Price
Add to Cart
There was a problem. Please try again later.

New Textbook

We're Sorry
Sold Out

Used Textbook

We're Sorry
Sold Out

eTextbook

We're Sorry
Not Available

Buy from our Marketplace starting at $115.16

How Marketplace Works:

  • This item is offered by an independent seller and not shipped from our warehouse
  • Item details like edition and cover design may differ from our description; see seller's comments before ordering.
  • Sellers much confirm and ship within two business days; otherwise, the order will be cancelled and refunded.
  • Marketplace purchases cannot be returned to eCampus.com. Contact the seller directly for inquiries; if no response within two days, contact customer service.
  • Additional shipping costs apply to Marketplace purchases. Review shipping costs at checkout.

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.

Table of Contents

PREFACE xv
CHAPTER ONE Introduction to PL/SQL 1(26)
Programming and Procedural Languages
2(1)
Application Programming and PL/SQL
2(3)
Application Programming
2(2)
History of PL/SQL
4(1)
Application Models
5(3)
Two-Tier or Client/Server Application Model
6(1)
Three-Tier Application Model
7(1)
Documentation on the Web and on CD-ROM
8(1)
SQL and PL/SQL Tools
9(4)
Software Tools Used in this Book
9(3)
Third-Party PL/SQL Development Tools
12(1)
Databases Used in this Book
13(4)
Chapter Summary
17(1)
Review Questions
18(2)
Hands-on Assignments
20(3)
Assignment 1-1: Review Data in the Brewbean's Database
20(1)
Assignment 1-2: Review Third-Party Software Tools
21(1)
Assignment 1-3: Identify Processing Steps
22(1)
Assignment 1-4: Use OTN Documentation
23(1)
Case Projects
23(4)
Case 1-1: Review Procedure Builder Documentation
23(1)
Case 1-2: The More Movies Database
24(3)
CHAPTER TWO Handling Data in PL/SQL Blocks 27(56)
The Current Challenge in the Brewbean's Application
28(1)
PL/SQL Block Structure
29(1)
Working with Variables
30(1)
Working with Scalar Variables
31(4)
Variable Declarations in Code
32(1)
Variable Initialization
32(1)
NOT NULL and CONSTANT
33(1)
The Role of Scalar Variables in the BEGIN Section
34(1)
Using DBMS OUTPUT to Check Values
34(1)
Including SQL Within a PL/SQL Block
35(3)
Executing a PL/SQL Block with Errors
38(4)
Working with Host or Bind Variables
42(1)
Using the %TYPE Attribute
43(1)
Working with Composite Data Types
44(7)
Record Data Type
44(2)
The Role of the %ROWTYPE Attribute
46(1)
Table of Records
47(4)
Processing with IF Statements and Looping Actions
51(2)
Simple IF Statements
51(1)
Loops: Basic and FOR
52(1)
Working with Collections
53(3)
Index-by Tables
53(3)
Other Collections: VARRAPS and Nested Tables
56(1)
Working with Cursors
56(11)
Implicit Cursors
57(1)
Explicit Cursors
58(8)
Cursor Variables
66(1)
Working with Variable Scope
67(4)
Chapter Summary
71(1)
Review Questions
72(2)
Advanced Review Questions
74(1)
Hands-on Assignments
75(7)
Assignment 2-1: Use Scalar Variables
75(2)
Assignment 2-2: Use a Record Variable
77(1)
Assignment 2-3: Use an Explicit Cursor
78(2)
Assignment 2-4: Use a CURSOR FOR loop
80(1)
Assignment 2-5: Use Implicit Cursors
81(1)
Assignment 2-6: Use Variable Scope
81(1)
Assignment 2-7: Use Scalar Variables for Data Retrieval
81(1)
Assignment 2-8: Use a Record Variable for Data Retrieval
81(1)
Case Projects
82(1)
Case 2-1:Variable Types
82(1)
Case 2-2: More Movie Rentals
82(1)
CHAPTER THREE PL/SQL Processing 83(56)
The Current Challenge in the Brewbean's Application
84(1)
Rebuilding Your Database
84(1)
Control Structures
85(13)
IF Statement Logic
85(8)
CASE Statements
93(5)
Looping Constructs
98(6)
Basic Loop
98(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

An electronic version of this book is available through VitalSource.

This book is viewable on PC, Mac, iPhone, iPad, iPod Touch, and most smartphones.

By purchasing, you will be able to view this book online, as well as download it, for the chosen number of days.

Digital License

You are licensing a digital product for a set duration. Durations are set forth in the product description, with "Lifetime" typically meaning five (5) years of online access and permanent download to a supported device. All licenses are non-transferable.

More details can be found here.

A downloadable version of this book is available through the eCampus Reader or compatible Adobe readers.

Applications are available on iOS, Android, PC, Mac, and Windows Mobile platforms.

Please view the compatibility matrix prior to purchase.