Oracle 9I Developer: Pl/SQL Programming

by Casteel, Joan

• ISBN13: 9780619159092
• ISBN10: 061915909X
• Edition: 1st
• Format: Paperback
• Copyright: 2003-05-09
• Publisher: Cengage Learning

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